**fyi, vmanohar1729 helped me last time, but there were four things that I forgot to mention that were to be included with the calculations, which were the NPV, IRR, Profit Index and the Payback period**

I am taking an Applied Managerial class and I need help on the calculations for the 3 below project scenarios. I have attached my spreadsheets from the previous tutor for assistance. Other than what is noted in each project scenario, all other costs will remain constant, and remember to only evaluate the incremental changes to cash flows.

The Net Present Value (NPV), Internal Rate of Return (IRR), the Profitability Index (PI) and the Payback Period also must be calculated. If you can also provide the calculation formulas on a spreadsheet, that would really helpful.

Project A: Major Equipment Purchase

• A new major equipment purchase, which will cost $10 million; however, it is projected to reduce cost of sales by 5% per yearfor 8 years.
• The equipment is projected to be sold for salvage value estimated to be $500,000 at the end of year 8.
• Being a relatively safe investment, the required rate of return of the project is 8%.
• The equipment will be depreciated at a MACRS 7-year schedule.
• Annual sales for year 1 are projected at $20 million and should stay the same per year for 8 years.
• Before this project, cost of sales has been 60%.
• The marginal corporate tax rate is presumed to be 25%.

Project B: Expansion into Europe

• Expansion into Western Europe has a forecast to increasesales/revenues and cost of sales by 10% per year for 5 years.
• Annual sales for the previous year were $20 million.
• Start-up costs are projected to be $7 million and an upfront needed investment in net working capital of $1 million. The working capital amount will be recouped at the end of year 5.
• Because of the higher European tax rate, the marginal corporate tax rate is presumed to be 30%.
• Being a risky investment, the required rate of return of the project is 12%.

Project C: Marketing/Advertising Campaign

• A major new marketing/advertising campaign, which will cost $2 million per year and last 6 years.
• It is forecast that the campaign will increase sales/revenues and costs of sales by 15% per year.
• Annual sales for the previous year were $20 million.
• The marginal corporate tax rate is presumed to be 25%.
• Being a moderate risk investment, the required rate of return of the project is 10%.

A B C E F G H Project A: Major Equipment Replacement Year 1 2 5 6 7 Cast of Major Equipment 10000000 Selavage value of Equipment 500000 Savings in Cost of sales (20 *0.05/ -20008030 0.05 20030080 0.05 -20080300 0 05 -20000807 0.05 =20000000*0.05 20000000*0 05 -30008030 0.05 -20000080 0.05 Depreciation rate 0.1439 0.2449 0 1749 0.1249 010893 0.0892 0.0495 0.0446 Depreciation =$03 G6 =-5053 16 =-9053 106 Tax Saving on Depreciation =+07'0.25 =+[7 0.25 =+F7 0.25 =+07*0.25 =+H7*0.25 =+17 0.25 =+/7 0 25 =+(7 0.25 Tax expense on saving of cost and Salvage value =-05 0 25 -E5*0.25 =-F5 0 25 =-65 0.25 =-H5 0.25 -15 0 25 =-15*0.25 =-15 *0.25-(34*0.25 Net Cash Flows 43 #+05+08+09 HES+E8-89 4H5+HB+H9 415+13+19 #+/5-18+19 #485+10-19+14 11 Py factors @ 84 1 =1/1.04 =+011/1.08 #+611/1.08 = F11/1.08 =G11/1.0 #+H11/1.08 =4011/1.04 12 Present values =+010 Ddi SHE10 E11 #+F10 F11 = G10 611 +110 111 11 Present value +5UM|C12:KIZ 15 Project B:Expansion in to Europe 15 Year 1 2 4 5 IT Startup costs 7000000 Working capital -1090900 1000030 19 Sales =30000000 1.1 =20000000 11 =20000000 11 =20000000 1.1 =30000000*1.1 Cast of sales JON [60+10) = D19 0. E1950.7 =F19 0.7 =-G19 0.7 =419 0.7 21 Profit =SUM(D19 020) =SUME19:120] -SUM F19 F20 =SUM|G19:G20 =SUM|H19:H20) 22 =-021 0.3 =-E21 0.3 =-F21*0.3 =-621 0 3 =-H21 0.3 73 Net Cash Flow +017+018 #+021+022 HE214612 4F21+FZ2 #:621+621 4H21+H27+H18 24 Py Factor # 12% 1 =1/1.12 =4064/1.12 #+634/1.12 = F34/1.12 =4G74/1.12 25 Present values +(23 CN4 =+012 024 =+F23 F24 = G23 G24 =4H23'H24 25 Present Value =+SUM/C25:H25) 27A B C D E F G H 1 J K L H Project A : Major Equipment Replacement Year 0 1 2 3 4 5 6 7 8 3 Cost of Major Equipment -10000000 Salavage value of Equipment 500000 Savings in Cost of sales (20 *0.05) 1000000 1000000 1000000 1000000 1000000 1000000 1000000 1000000 Depreciation rate 14.29% 24.49% 17.49%% 12.49% 8.939% 8.92% 8.93% 4.46% Depreciation 1429000 2449000 1749000 1249000 893000 892000 893000 446000 8 Tax Saving on Depreciation 357250 612250 437250 312250 223250 223000 223250 111500 9 Tax expense on saving of cost and Salvage value -250000 -250000 -250000 -250000 -250000 -250000 -250000 -375000 10 Net Cash Flows -10000000 1107250 1362250 1187250 1062250 973250 973000 973250 1236500 11 Pv factors @ 8%% 1 0.925926 0.857339 0.793832 0.73503 0.680583 0.6301696 0.58349 0.540269 12 Present values -10000000 1025231 1167910 942477.3 780785.5 662377.6 613155.05 567882 668042.5 13 Present value -3572139 14 15 Project B :Expansion in to Europe 16 Year 0 1 2 3 4 5 17 Startup costs -7000000 18 Working capital -1000000 1000000 19 Sales 0 22000000 22000000 22000000 22000000 22000000 20 Cost of sales 70% (60+10) -15400000 -15400000 -15400000 -15400000 -15400000 21 Profit 6600000 6600000 6600000 6600000 6600000 22 Tax -1980000 -1980000 -1980000 -1980000 -1980000 23 Net Cash Flow -8000000 4620000 4620000 4620000 4620000 5620000 24 Pv Factor @ 12% 1 0.892857 0.797194 0.71178 0.635518 0.567427 25 Present values 8000000 4125000 3683036 3288425 2936094 3188939 26 Present Value 9221493A B C D E F G H K 27 28 Project C: Marketing/Advertising campaign 29 Year 1 2 3 4 51 6 30 Sales 23000000 23000000 23000000 23000000 23000000 23000000 31 Cost of Sales 75%% (60+15) -17250000 -17250000 -17250000 -17250000 -17250000 - -17250000 32 Advertisement cost 2000000 -2000000 -2000000 -2000000 2000000 -2000000 33 Profit 3750000 3750000 3750000 3750000 3750000 3750000 34 Tax 937500 -937500 -937500 -937500 -937500 -937500 35 Net Profit 2812500 2812500 2812500 2812500 2812500 2812500 36 Pv factors @ 10% 0.909091 0.826446 0.751315 0.683013 0.620921 0.564474 37 Present Values 2556818 2324380 2113073 1920975 1746341 1587583 38 Present Value 12249171 39 40 A B C D E F G H K 27 28 Project C: Marketing/Advertising campaign 29 Year 1 7 4 5 6 30 Sales -20080000*1.15 =20008000 1 15 =20008080 1 15 =20000080"1 15 =20000000"1 15 =20000000 1.15 31 Cost of Sales 75% (60+15 =-(30*0.75 -D30*0.75 =-E30*0.75 :-F30 0.75 -G30*0.75 :-H3010.75 32 Advertisement coit -200000 2000000 2000000 -2000000 -2000000 200000 33 Profit -SUM(30:032) SUM(D30:032) =SUM(E30:E32) =SUM(F30:F32 =SUM G30:632) =SUM(H30:H32 34 = (33 0.25 -D33 0.25 -E33 0.25 =-F33 0.25 -G33 0.25 -H33 0.25 Net Profit -+33+034 =+033+034 =+F33+F34 -+G33+G34 =+H33+H34 Pvfactors @ 10% =1/1.1 :+036/1.1 =+036/1.1 =+636/1.1 :+F36/1.1 2+G34/1.1 Prevent Values #+(35*036 =4035 036 =4635 636 =4F35*F36 =4G35 G36 =+435 H36 Present Value =+SUM(C37:437)