Use the template provided and complete problem 10-23.

Please show all work in excel

Start with the partial model in the file Ch10 P23 Build a Model.xls on the textbookWeb site. Gardial Fisheries is considering two mutually exclusive investments.

Thanks

12/7/2012 Chapter: Problem: 10 23 Gardial Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: Time 0 1 2 3 4 5 6 7 Expected Net Cash Flows Project A Project B ($375) ($575) ($300) $190 ($200) $190 ($100) $190 $600 $190 $600 $190 $926 $190 ($200) $0 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 the proper choice? @ 12% cost of capital @ 18% cost of capital WACC = WACC = 12% NPV A = NPV A = NPV B = Use Excel's NPV function as explained in 18% this chapter's Tool Kit. Note that the range does not include the costs, which are added separately. NPV B = 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 Project B 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% Project A Project B c. What is each project's IRR? We find the internal rate of return with Excel's IRR function: Note in the graph above that the X-axis intercepts are equal to the two projects' IRRs. IRR A = IRR B = d. What is the crossover rate, and what is its significance? Time 0 1 2 3 4 5 6 7 Cash flow differential Crossover rate = 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 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 = MIRR B = MIRR A = MIRR B = f. What is the regular payback period for these two projects? Project A Time period Cash flow Cumulative cash flow Payback Project B 0 (375) 1 (300) 2 (200) 3 (100) 4 600 5 $600 6 $926 7 ($200) Time period Cash flow Cumulative cash flow Payback g. 0 (575) 1 190 2 190 3 190 4 190 5 $190 6 $190 7 $0 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 Discounted Payback 0 (375) 1 (300) 2 (200) 3 (100) 4 600 5 $600 6 $926 7 ($200) 0 (575) 1 190 2 190 3 190 4 190 5 $190 6 $190 7 $0 Project B Time period Cash flow Disc. cash flow Disc. cum. cash flow Discounted Payback 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: PV of future cash flows for B: PI of B: