In: Finance
The Vice President of Finance at Kramerica, Inc. has determined a capital investment budget of $8,000,000 for the next year. Unfortunately, this amount is not sufficient to accept all positive NPV projects available to the firm, which are listed in the table below.
Region |
Project |
Cost |
NPV |
Europe |
1 |
$1,200,000 |
$172,036 |
Europe |
2 |
$2,000,000 |
$362,283 |
Europe |
3 |
$500,000 |
$147,487 |
Asia |
4 |
$600,000 |
$72,076 |
Asia |
5 |
$700,000 |
$129,352 |
Asia |
6 |
$3,000,000 |
$527,127 |
Asia |
7 |
$1,500,000 |
$343,632 |
Africa |
8 |
$200,000 |
$52,836 |
Africa |
9 |
$2,400,000 |
$275,936 |
Africa |
10 |
$1,000,000 |
$175,824 |
Help the Vice President of Finance to choose which set of investments should be accepted.
a. Using the Solver, determine which projects should be accepted if the firm’s goal is to maximize shareholders wealth, subject to the maximum investment constraint. (Note: Make sure to set the Solving Method to Simplex LP.) Keep this solution on a sheet named 2a. Copy the sheet to start 2b.
b. Now assume that the Vice President of Finance has informed you that the capital budget has been cut to $4,000,000 for the next year, and that at least one project must be accepted for each region. Change your Solver constraints to account for this new information and find the new solution. Keep this solution on one sheet and name it 2b. Copy the sheet to start 2c.
c. In addition to the constraints from part b, the Vice President of Finance has now informed you that projects 3, 5, and 8 are mutually exclusive, so, at most, one of them can be selected. Change your Solver constraints to account for this new information and find the new solution. Keep this solution on one sheet and name it 2c.
Hints for parts b&c. You will need to set up cells on your spreadsheet that would add up the number of projects in the restricted categories. For example, in b, you create a cell to add all the 1's for a region and then have a constraint that requires that cell to be at least one.