A company wants to maximize the combined Net Present Value (NPV) of a maximum of 6 opportunities | |||||||||||

that require up to 6 yearly investments. In each year there is only a limited amount of money available. | |||||||||||

All amounts are give in millions of dollars. Interest rate is | 5% | ||||||||||

Expected Investment Cash Flows and Net Present Value | |||||||||||

Opp. 1 | Opp. 2 | Opp. 3 | Opp. 4 | Opp. 5 | Opp. 6 | ||||||

Year 1 | ($5.00) | ($9.00) | ($12.00) | ($7.00) | ($20.00) | ($18.00) | |||||

Year 2 | ($6.00) | ($6.00) | ($10.00) | ($5.00) | $6.00 | ($15.00) | |||||

Year 3 | ($16.00) | $6.10 | ($5.00) | ($20.00) | $6.00 | ($10.00) | |||||

Year 4 | $12.00 | $4.00 | ($5.00) | ($10.00) | $6.00 | ($10.00) | |||||

Year 5 | $14.00 | $5.00 | $25.00 | ($15.00) | $6.00 | $35.00 | |||||

Year 6 | $15.00 | $5.00 | $15.00 | $75.00 | $6.00 | $35.00 | |||||

NPV | $8.01 | $2.20 | $1.85 | $7.51 | $5.69 | $5.93 | |||||

Percentage to invest | |||||||||||

0% | 0% | 0% | 0% | 0% | 0% | ||||||

Cash Flow | Total | Budget | Surplus | ||||||||

Year 1 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $45.00 | $45.00 | ||

Year 2 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $30.00 | $30.00 | ||

Year 3 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $20.00 | $20.00 | ||

Year 4 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||

Year 5 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||

Year 6 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||

Revenue | Total | ||||||||||

NPV | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | ||||

Problem | |||||||||||

A company has six different opportunities to invest money. Each opportunity requires a certain investment over a | |||||||||||

period of 6 years or less. The company wants to invest in those opportunities that maximize the combined Net | |||||||||||

Present Value. It also has an investment budget that needs to be met for each year. | |||||||||||

We assume that it is possible to invest partially in an opportunity. For instance, if the company decides to invest | |||||||||||

50% of the required amount in an opportunity, the return will also be 50%. How should the company invest? | |||||||||||

Solution | |||||||||||

1) The variables are the cells in the worksheet that we want to change. In this model, they are the percentages that | |||||||||||

are invested in each opportunity. By changing these values, the Net Present Value of the combined investments also | |||||||||||

changes. The variables in this model are given the name investments in the worksheet. | |||||||||||

2) The constraints are the limitations we have when changing the variables. It is not possible to invest more than | |||||||||||

100% in an opportunity. This gives: | |||||||||||

investments <=1 | |||||||||||

We can tell the Solver not to invest a negative amount of money, using the Assume Non-Negative option. | |||||||||||

It is a common mistake to forget these kinds of logical constraints. | |||||||||||

The last constraint is given by the fact that the company has a budget. The sum of the expected cash flow of the | |||||||||||

investments and the budget must be positive. This leads to: | |||||||||||

Monthly_surplus >= 0 | |||||||||||

3) The objective is to maximize the NPV which is given the name Total_NPV on the worksheet. This amount is | |||||||||||

calculated by adding the NPV's of each investment, multiplied by the percentages that are invested in them. | |||||||||||

Remarks | |||||||||||

When creating this model we start out by putting the characteristics of the 6 opportunities on the worksheet. In this | |||||||||||

worksheet we decided to lay out the opportunities (horizontally) vs. the years (vertically). It would be perfectly fine | |||||||||||

to switch this around and have different columns for different years. | |||||||||||

We then assign cells to the variables we are using. In this case we used 6 cells for 6 different investments and | |||||||||||

defined them as investments in the worksheet. When dealing with a linear model as this one, it does not matter | |||||||||||

what the initial values of these variables are. In non-linear models, however, it is very important to give the | |||||||||||

variables an initial value that you expect to be close to the solution. Therefore, it is good practice to give the | |||||||||||

variables reasonable starting values. In this model, 50% for instance. | |||||||||||

After the variables have been created, we must put the constraints on the worksheet. Normally, no extra work is | |||||||||||

necessary for logical constraints. We simply tell the solver to keep the investments between 0 and 100% when | |||||||||||

defining the model. The other constraints do require some work. In this model we want the sum of the expected | |||||||||||

cash-flow of the investments and the yearly budget to be positive. The easiest way to do this is to create cells that | |||||||||||

calculate this sum and tell the Solver that the values of these cells must be positive. In the worksheet these cells are | |||||||||||

defined as Monthly_surplus. Finally, we create a cell that calculates the combined NPV of all investments. | |||||||||||

You may notice that we also created cells that calculate the NPV for each individual investment. This is not strictly | |||||||||||

necessary, but it makes the model easier to read and understand, and it provides an easy way of calculating NPV. | |||||||||||