Capital Budgeting 2 |
|
|
|
|
|
|
|
|
|
| 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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|