## 14 Nov Build a Model

please see the attachment,

excel table needs completion

I need a "shell" on how the math is done, there is a online assessment I take on my own

## Build a Model

Build a Model | 11/26/18 | |||||||||

Chapter: | 10 | |||||||||

Problem: | 23 | |||||||||

Gardial Fisheries is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: | ||||||||||

Expected Net Cash Flows | ||||||||||

Time | Project A | Project B | ||||||||

0 | ($375) | ($575) | ||||||||

1 | ($300) | $190 | ||||||||

2 | ($200) | $190 | ||||||||

3 | ($100) | $190 | ||||||||

4 | $600 | $190 | ||||||||

5 | $600 | $190 | ||||||||

6 | $926 | $190 | ||||||||

7 | ($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 | |||||||||

Use Excel's NPV function as explained in this chapter's Tool Kit. Note that the range does not include the costs, which are added separately. | ||||||||||

WACC = | 12% | WACC = | 18% | |||||||

NPV A = | NPV A = | |||||||||

NPV B = | 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% | ||||||||||

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 projects' IRRs. | |||||||||

IRR B = | ||||||||||

d. What is the crossover rate, and what is its significance? | ||||||||||

Cash flow | ||||||||||

Time | differential | |||||||||

0 | ||||||||||

1 | ||||||||||

2 | Crossover rate = | |||||||||

3 | ||||||||||

4 | 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 | |||||||||

5 | ||||||||||

6 | ||||||||||

7 | ||||||||||

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 = | DII Labs: Use Excel's MIRR function | DII Labs: The difference in cash flows between Project "A" and Project "B". | DII Labs: Net Present Value of "A" discounted at a WACC of 12% | DII Labs: The IRR for the Cash Flow Differential | DII Labs: Net Present Value of "A" discounted at a WACC of 18% | MIRR A = | ||||

MIRR B = | MIRR B = | |||||||||

f. What is the regular payback period for these two projects? | ||||||||||

Project A | ||||||||||

Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||

Cash flow | (375) | (300) | (200) | (100) | 600 | $600 | $926 | ($200) | ||

Cumulative cash flow | ||||||||||

Intermediate calculation for payback | ||||||||||

Payback using intermediate calculations | ||||||||||

Project B | ||||||||||

Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||

Cash flow | -$575 | $190 | $190 | $190 | $190 | $190 | $190 | $0 | ||

Cumulative cash flow | ||||||||||

Intermediate calculation for payback | ||||||||||

Payback using intermediate calculations | ||||||||||

Payback using PERCENTRANK | Ok because cash flows follow normal pattern. | |||||||||

g. At a cost of capital of 12%, what is the discounted payback period for these two projects? | ||||||||||

WACC = | 12% | |||||||||

Project A | ||||||||||

Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||

Cash flow | -$375 | -$300 | -$200 | -$100 | $600 | $600 | $926 | -$200 | ||

Disc. cash flow | ||||||||||

Disc. cum. cash flow | ||||||||||

Intermediate calculation for payback | ||||||||||

Payback using intermediate calculations | ||||||||||

Project B | ||||||||||

Time period | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | ||

Cash flow | ||||||||||

Disc. cash flow | ||||||||||

Disc. cum. cash flow | ||||||||||

Intermediate calculation for payback | ||||||||||

Payback using intermediate calculations | ||||||||||

Discounted Payback using PERCENTRANK | 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: | ||||||||||

PV of future cash flows for B: | ||||||||||

PI of B: | ||||||||||

NPV Profiles

0 0.02 0.04 0.06 0.08 0.1 0.12 0. 14000000000000001 0.16 0.18 0.2 0.22 0.24 0.26 0.28000000000000003 0.3 0 0.02 0.04 0.06 0.08 0.1 0.12 0.14000000000000001 0.16 0.18 0.2 0.22 0.24 0.26 0.28000000000000003 0.3

Cost of Capital

NPV

Project A

Project B