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.