Capital Budgeting 1 |
|
|
|
|
|
|
|
|
|
| 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 colums 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. Therefor, 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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
