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