| 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 | |||||
| Decision to invest | |||||||||||
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||||
| 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 | |||||||||||
| In this model we extend the problem we solved in Budget1. Once again, a company needs to make a decision | |||||||||||
| how to invest in 6 different opportunities. This time however, the company can only go with an investment 100% | |||||||||||
| or ignore the opportunity and thus invest 0%. | |||||||||||
| Solution | |||||||||||
| The solution is almost identical to the one in Budget1. The variables and objective have remained the same. | |||||||||||
| The only difference is in the logical constraints. In Budget1 the investments needed to be between 0 and 100%. | |||||||||||
| Now they are required to be 0 or 100% (or 0 or 1). | |||||||||||
| These kinds of (binary) decision variables often occur in models. They come up when decisions have to be made, | |||||||||||
| such as: open or closed, yes or no, buy or not buy, etc. The Solver allows you to use these kind of variables by | |||||||||||
| entering a constraint that says the variables must be binary integer. In Budget1 we used: | |||||||||||
| investments <= 1 and | |||||||||||
| investments >= 0 via the Assume Non-Negative option. | |||||||||||
| In place of these constraints, we can tell the Solver to use binary integer variables, with: | |||||||||||
| Investment_decisions = binary | |||||||||||
| This will force the variables to be either 0 or 1. | |||||||||||
| Remarks | |||||||||||
| By making the variables 0 or 1, there is less flexibility in the investments. In mathematical terms, we have | |||||||||||
| tightened the constraints. Because of this we can expect our goal, the total NPV, to be less than in Budget1. | |||||||||||
| Compare the 2 models and make sure this is indeed the case. | |||||||||||
| You might be surprised by the investment decisions of this model compared to the solution of Budget1. In the | |||||||||||
| previous model we were told to invest 100% in opportunity 2. In the second model we are advised not to invest in | |||||||||||
| opportunity 2 at all! The explanation is that we have a limited budget. Because the Solver can only choose between | |||||||||||
| 0 or 1 in the variables, this can lead to surprising results. It is important to realize that simply 'rounding' the results | |||||||||||
| of the first model clearly does not guarantee an optimal (or even feasible!) solution. | |||||||||||
