Build a Model (BAM) Chapter 10 need to complete the spreedsheat for evaluating cash flow... formulas are in the spreadsheet as well

Contain formulas and references to calculations. Click on the cells and see the calculations or references Answers Contain formulas and references to calculations. Click on the cells and see the calculations or references 10-1 WACC Years Cash Flows (52,125.00) 12% 1 12,000.00 2 12,000.00 3 ### 4 12,000.00 5 12,000.00 (52,125.00) 1 12,000.00 2 12,000.00 3 ### 4 12,000.00 5 12,000.00 NPV Function 10-2 IRR Function 10-3 MIRR Function 10-4 PI Present Value Outlay PI 10-5 Payback Years Cash Flows Cumulative CF Number of Years before turning Positive Portion of Next Year to Turn Positive 10-6 Discount Payback WACC (52,125.00) 12% 1 12,000.00 2 12,000.00 3 ### 0 (15,000,000) 5% 1 5,000,000 2 10,000,000 3 20,000,000 5% 1 20,000,000 2 10,000,000 3 6,000,000 10% 1 2 3 Years Cash Flows Discount Amount Cumulative Discount CF Number of Years before turning Positive Portion of Next Year to Turn Positive 10-7 A Years Cash Flows WACC NPV B Years Cash Flows WACC 0 (15,000,000) NPV A Years WACC 0 4 12,000.00 5 12,000.00 Cash Flows (15,000,000) 5,000,000 10,000,000 20,000,000 10% 1 20,000,000 2 10,000,000 3 6,000,000 15% 1 5,000,000 2 10,000,000 3 20,000,000 15% 1 20,000,000 2 10,000,000 3 6,000,000 NPV B Years Cash Flows WACC 0 (15,000,000) NPV A Years Cash Flows WACC 0 (15,000,000) NPV B Years Cash Flows NPV WACC 0 (15,000,000) 10-8 Truck Years Cash Flows WACC (17,100.00) 14% 1 5,100.00 2 5,100.00 3 ### 4 5,100.00 5 5,100.00 (22,430.00) 14% 1 7,500.00 2 7,500.00 3 ### 4 7,500.00 5 7,500.00 NPV IRR MIRR Pulley Years Cash Flows NPV IRR MIRR WACC 10-9 Electric Powered Truck WACC Years Cash Flows (22,000.00) 12% 1 6,290.00 2 6,290.00 3 ### 4 6,290.00 5 6,290.00 (17,500.00) 12% 1 5,000.00 2 5,000.00 3 ### 4 5,000.00 5 5,000.00 NPV IRR MIRR Gas Powered Truck WACC Years Cash Flows NPV IRR MIRR 10-10 Project S WACC Years Cash Flows (10,000.00) 12% 1 3,000.00 2 3,000.00 3 ### 4 3,000.00 5 3,000.00 (25,000.00) 12% 1 7,400.00 2 7,400.00 3 ### 4 7,400.00 5 7,400.00 NPV IRR MIRR Project L Years Cash Flows NPV IRR MIRR Profitability Index S Profitability Index L WACC 10-11 Project X Years Cash Flows WACC (1,000.00) 12% 1 100.00 2 300.00 3 400.00 4 700.00 (1,000.00) 12% 1 1,000.00 2 100.00 3 50.00 4 ### 1 350,000.00 2 350,000.00 3 ### 4 350,000.00 5 350,000.00 10% 1 2 3 4 5 MIRR Project Y Years Cash Flows WACC MIRR 10-12 Minning Equipment Installation Project L Years Cash Flows WACC 900,000.00 165,000.00 (1,065,000.00) 14% NPV IRR 10-13 a Project A Years WACC Cash Flows (300.00) (387.00) (193.00) (100.00) 600.00 600.00 (405.00) 10% 1 134.00 2 134.00 3 ### 4 134.00 5 134.00 (300.00) 17% 1 (387.00) 2 (193.00) 3 (100.00) 4 600.00 5 600.00 (405.00) 17% 1 134.00 2 134.00 3 ### 4 134.00 5 134.00 (c) NPV (b) IRR (d)MIRR Project B Years Cash Flows WACC (c)NPV (b) IRR (d)MIRR Project A Years Cash Flows WACC (c )NPV (b)IRR (d)MIRR Project B Years Cash Flows (c )NPV (b)IRR (d)MIRR WACC Crossover Rate Time Project A 0 1 2 3 4 5 6 7 Project B (300.00) (387.00) (193.00) (100.00) 600.00 600.00 850.00 (180.00) A-B (405.00) 134.00 134.00 134.00 134.00 134.00 134.00 - 105.00 (521.00) (327.00) (234.00) 466.00 466.00 716.00 (180.00) IRR 10-14 Plan A 0 (10,000,000.00) 1 12,000,000.00 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 B Difference (10,000,000.00) 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 1,750,000.00 IRR Crossover Rate 0.00% If the WACC is > than the crossover rate take Project A, if 0. If the projects are mutully exclusive and both projects have a positive NPV, then select the project with the higher NPV. Project B NPV IRR MIRR 3 8,000,000 f Difference in Cash Flows 0 1 2 3 4 IRR Crossover Rate g See c above 10-22 WACC 10% Year Cash Flows Salvage Value 0 (22,500) 22,500 1 6,250 17,500 2 6,250 14,000 3 6,250 11,000 4 6,250 5,000 5 6,250 NPV 1 2 3 4 5 operate for 3 years - highest NPV b Nope or references or references 6 12,000.00 7 ### 8 12,000.00 6 12,000.00 7 ### 8 12,000.00 6 12,000.00 7 ### 8 12,000.00 6 6,290.00 6 5,000.00 6 7 850.00 (180.00) 6 134.00 7 - 6 850.00 7 (180.00) 6 134.00 7