Spreadsheet Problem
patricio11i need to solve problem attached in spreetshets
2 years ago
30
files (2)Ebooks-CengageeReader11.pdf
ch10_p23_build_a_model.xlsx
Ebooks-CengageeReader11.pdf
Book Title: eTextbook: Corporate Finance: A Focused Approach Chapter 10. The Basics of Capital Budgeting: Evaluating Cash Flows Spreadsheet Problem
(10-23)
Spreadsheet Problem
Build a Model: Capital Budgeting Tools
Resource
Start with the partial model in the file Ch10 P23 Build a Model.xlsx on the
textbook’s Web site. Gardial Fisheries is considering two mutually
exclusive investments. The projects’ expected net cash flows are as follows:
447
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?
Expected Net Cash Flows
Year 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
b. Construct NPV profiles for Projects A and B.
c. What is each project’s IRR?
d. What is the crossover rate, and what is its significance?
e. What is each project’s MIRR at a cost of capital of 12%? At r = 18%? (Hint:
Consider Period 7 as the end of Project B’s life.)
f. What is the regular payback period for these two projects?
g. At a cost of capital of 12%, what is the discounted payback period for these
two projects?
h. What is the profitability index for each project if the cost of capital is 12%?
ch10_p23_build_a_model.xlsx
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.3Cost of Capital
NPV
Project A
Project B
- A+ Work
- HIS
- Economic 202 answers #4
- uber
- Finance 330
- HRM 522 WEEK 8 DISCUSION
- Assignment 6
- MBA 6601 Unit III
- MBA 6601 Unit I
- Based on the learning materials this week and throughout the term, put forward your argument on whether we can have economic prosperity and a clean environment at the same time, and well into the future.
