capital budgeting decision
Crownroyalevp
FIN419r6wk4CapitalBudgetingDecisionModelsTemplate.xlsxInstructions
| Instructions |
| 1. You have three problems - one on each tab of this Excel file. |
| 2. Please show your work in the cells. Use Excel formulas instead of writing the values/answers directly in the cell. |
| The instructor will then know where you made a mistake and provide you valuable feedback and partial credit (if appropriate). |
| Total Points: 100 |
Problem 1 NPV
| Calculate the NPV for each project and determine which project should be accepted. | ||||
| Project A | Project B | Project C | Project D | |
| Initial Outlay | (105,000.00) | (82,000.00) | (110,000.00) | (75,000.00) |
| Inflow year 1 | 53,000.00 | 51,000.00 | 25,000.00 | 45,000.00 |
| Inflow year 2 | 50,000.00 | 47,000.00 | 55,000.00 | 50,000.00 |
| Inflow year 3 | 48,000.00 | 41,000.00 | 15,000.00 | 30,000.00 |
| Inflow year 4 | 30,000.00 | 52,000.00 | 21,000.00 | 62,000.00 |
| Inflow year 5 | 35,000.00 | 40,000.00 | 35,000.00 | 68,000.00 |
| Rate | 7% | 10% | 13% | 18% |
| NPV = | ||||
| Answer: | ||||
Problem 2 Payback
| Your company is considering three independent projects. Given the following cash flow information, calculate the payback period for each. | |||
| If your company requires a three-year payback before an investment can be accepted, which project(s) would be accepted? | |||
| Project D | Project E | Project F | |
| Cost | 205,000.00 | 179,000.00 | 109,000.00 |
| Inflow year 1 | 53,000.00 | 51,000.00 | 25,000.00 |
| Inflow year 2 | 50,000.00 | 87,000.00 | 55,000.00 |
| Inflow year 3 | 48,000.00 | 41,000.00 | 21,000.00 |
| Inflow year 4 | 30,000.00 | 52,000.00 | 8,000.00 |
| Inflow year 5 | 24,000.00 | 40,000.00 | 35,000.00 |
| Payback Period | |||
| Answer: | |||
| Calculations | |||
| Year 1 | |||
| Year 2 | |||
| Year 3 | |||
| Year 4 | |||
| Year 5 |
Problem 3 WACC
| Using market value and book value (separately), find the adjusted WACC, using 16% tax rate. | ||||
| Component | Balance Sheet Value | Market Value | Cost of Capital | TAX |
| Debt | 5,000,000.00 | 6,850,000.00 | 8% | 16% |
| Preferred Stock | 4,000,000.00 | 2,200,000.00 | 11% | |
| Common Stock | 2,000,000.00 | 5,600,000.00 | 13% | |
| ANSWER | ||||
| Book Value Weights | Market Value Weights | Adjusted WACC | ||
| Debt | Debt | Market Value | ||
| Preferred Stock | Preferred Stock | Book Value | ||
| Common Stock | Common Stock | |||
