Rand Enterprises is considering a number of investment possibilities. Specifically, each investment under consideration will draw on the capital account during each of its first three years, but in the long run, each is predicted to achieve a positive net present value (NPV). Listed here are the investment alternatives, their net present values, and their capital requirements, and all figures are in millions of dollars. In addition, the amount of capital available to the investments in each of the next three years is predicted to be $20 million, $12 million, and $20 million, respectively. Project One-Phase Expansion Two-Phase Expansion Test Market Advertising Campaign Basic Research Purchase Equipment NPV 5.0 8.1 11.6 5.3 10.4 4.2 Year 1 Capital 3.6 3.0 7.2 2.4 6.0 1.2 Year 2 Capital 1.2 4.2 4.8 1.8 1.2 0.6 Year 3 Capital 4.8 4.2 6.0 2.2 4.8 1.1 In addition, the following constraints have to be met: a. The expansion investments are mutually exclusive and only one of them can be made. b. No more than four projects can be invested. c. The test market and advertising campaign should be undertaken together, if at all. Formulate a spreadsheet model for this problem and solve using Excel Solver. Which investments should be made to maximize NPV? What is the optimal NPV?
Rand Enterprises is considering a number of investment possibilities. Specifically, each investment under consideration will draw on the capital account during each of its first three years, but in the long run, each is predicted to achieve a positive
| Project | ||||||
| One-Phase Expansion |
Two-Phase Expansion |
Test Market |
Advertising Campaign |
Basic Research |
Purchase Equipment |
|
| NPV | 5.0 | 8.1 | 11.6 | 5.3 | 10.4 | 4.2 |
| Year 1 Capital | 3.6 | 3.0 | 7.2 | 2.4 | 6.0 | 1.2 |
| Year 2 Capital | 1.2 | 4.2 | 4.8 | 1.8 | 1.2 | 0.6 |
|
Year 3 Capital |
4.8 | 4.2 | 6.0 | 2.2 | 4.8 | 1.1 |
In addition, the following constraints have to be met:
a. The expansion investments are mutually exclusive and only one of them can be made.
b. No more than four projects can be invested.
c. The test market and advertising campaign should be undertaken together, if at all.
Formulate a spreadsheet model for this problem and solve using Excel Solver. Which investments should be made to maximize NPV? What is the optimal NPV?
Step by step
Solved in 2 steps with 4 images
