Need help filling in the cells b40-b55 and c40-c55

Solution Chapter: Problem: 10 23 Gardial Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are as follow Expected Net Cash Flows Project A Project B ($375) ($575) ($300) $190 ($200) $190 ($100) $190 $600 $190 $600 $190 $926 $190 ($200) $0 Time 0 1 2 3 4 5 6 7 a. If each project's cost of capital is 12%, which project should be selected? If the cost of capital is 18%, what project is proper choice? @ 12% cost of capital @ 18% cost of capital NPV A = $226.96 NPV A = Use Excel's NPV function as explained i 18% this chapter's Tool Kit. Note that the ran does not include the costs, which are $18.24 added separately. NPV B = $206.17 NPV B = $89.54 WACC = 12% WACC = At a cost of capital of 12%, Project A should be selected. However, if the cost of capital rises to 18%, then the choice is reversed, and Project B should be accepted. b. Construct NPV profiles for Projects A and B. Before we can graph the NPV profiles for these projects, we must create a data table of project NPVs relative to differing costs of capital. Project A $226.96 0% 2% 4% 6% 8% 10% 12% 14% Project B $206.17 16% 18% 20% 22% 24% 26% 28% 30% c. What is each project's IRR? We find the internal rate of return with Excel's IRR function: IRR A = Note in the graph above that the X-axis intercepts are equal to the two 18.64% projects' IRRs. IRR B = 23.92% d. What is the crossover rate, and what is its significance? Time 0 1 2 3 4 5 6 7 Cash flow differential $200 ($490) ($390) ($290) $410 $410 $736 ($200) Crossover rate = 13.14% The crossover rate represents the cost of capital at which the two projects value, at a cost of capital of 13.14% is: have the same net present value. In this scenario, that common net present $182 e. What is each project's MIRR at a cost of capital of 12%? At r = 18%? Hint: note that B is a 6-year project. @ 12% cost of capital @ 18% cost of capital MIRR A = 15.43% MIRR A = 18.34% MIRR B = 17.01% MIRR B = 20.47% f. What is the regular payback period for these two projects? Project A Time period Cash flow Cumulative cash flow 0 (375) -$375 Intermediate calculation for payback Payback using intermediate calculations 1 (300) -$675 - 4.625 2 (200) -$875 - 3 4 (100) 600 -$975 -$375 - - 5 $600 $225 4.625 Project B Time period Cash flow Cumulative cash flow Intermediate calculation for payback Payback using intermediate calculations Payback using PERCENTRANK g. 0 -$575 -$575 3.026 3.026 1 $190 -$385 - 2 $190 -$195 - 3 $190 -$5 - 4 $190 $185 3.026 5 $190 $375 - Ok because cash flows follow normal pattern. At a cost of capital of 12%, what is the discounted payback period for these two projects? WACC = 12% Project A Time period Cash flow Disc. cash flow Disc. cum. cash flow Intermediate calculation for payback Payback using intermediate calculations 0 -$375 -$375 -$375 1 -$300 -$268 -$643 - 2 -$200 -$159 -$802 - 3 -$100 -$71 -$873 - 4 $600 $381 -$492 - 5 $600 $340 -$152 - 1 $190 $169.64 -$405 - 2 $190 $151 -$254 - 3 $190 $135 -$119 - 4 $190 $121 $2 5.103 5 $190 $108 $110 - 5.323 Project B Time period Cash flow Disc. cash flow Disc. cum. cash flow Intermediate calculation for payback Payback using intermediate calculations Discounted Payback using PERCENTRANK 0 -$575 -$575 -$575 5.103 3.983 Ok because cash flows follow normal pattern. h. What is the profitability index for each project if the cost of capital is 12%? PV of future cash flows for A: PI of A: $601.96 1.61 PV of future cash flows for B: PI of B: $781.17 1.36 3/18/2015 sh flows are as follows: 18%, what project is the unction as explained in Kit. Note that the range he costs, which are , then the choice is s relative to differing equal to the two project. 6 $926 $1,151 - 7 ($200) $951 - 6 $190 $565 - 7 $0 $565 - 6 $926 $469.14 $317 5.323 7 -$200 -$90 $227 - 6 $190 $96 $206 - 7 $0 $0 $206