| 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. | |||||||||||
