Capital Budgeting Models

1
Example 6.1

• Capital Budgeting Models

2
Background Information

• The Tatham Company is considering seven
  investments. The cash required for each
  investment and the net present value (NPV) each
  investment adds to the form are given in the
  table shown here.

Data for Tatham Capital Budgeting Example Data for Tatham Capital Budgeting Example Data for Tatham Capital Budgeting Example
Cash Required NPV Added
Investment 1 5,000 16,000
Investment 2 2,500 8,000
Investment 3 3,500 10,000
Investment 4 6,000 20,000
Investment 5 7,000 22,000
Investment 6 4,500 12,000
Investment 7 3,000 8,000
3
Background Information -- continued

• The cash available for investment is 15,000.
• Tatham wants to find the investment policy that
  maximizes its NPV.
• The crucial assumption here is that if Tatham
  wishes to take part in any of these investments,
  it must go all the way.
• It cannot, for example, go halfway in investment
  1 by investing 2500 and realizing an NPV of
  8000.

4
Solution

• The solution of this problem is quite
  straightforward. Tatham must keep track of
• Investments chosen
• Total cash required for the chosen investments
• Total NPV from the chose investments

5
TATHAM.XLS

• To keep track of which investments are chosen, we
  use a 0-1 variable for each investment.
• If a particular investment is chosen, the 0-1
  variable for this investment will equal 1 if it
  is not chosen, the 0-1 variable will equal 0.
• This file contains the spreadsheet model. The
  spreadsheet is shown on the next slide.

6
(No Transcript)
7
Developing the Model

• To develop this model, proceed as follows.
• Inputs. Enter the NPV for each investment in the
  NPVs range, the cost required by each investment
  in the Costs range, and the amount of available
  cash in the Budget cell.
• 0-1 values for investments. Enter any trial 0-1
  values for the investments in the Investments
  range. (Even fractional values such as 0.5 can be
  entered in these cells. The Solve constraints
  will eventually force them to be 0 or 1.
• NPV contributions. Calculate the NPV contributed
  by the investments in the TotNPV cell with the
  formula SUMPRODUCT(Investments,NPVs). Note that
  this formula picks up the NPV only for those
  investments with 0-1 variables equal to 1.

8
Developing the Model -- continued

• Cash invested. Calculate the total cash invested
  in the TotCost cell with the formula
  SUMPRODUCT(Investments,Costs). Again, this picks
  up only the costs of investments with 0-1
  variables equal to 1.

9
Using the Solver

• The Solver dialog box is shown here.

10
Using the Solver -- continued

• We want to maximize the total NPV, subject to
  staying within the budget.
• However, we also need to constrain the changing
  cells to be 0-1.
• With the Solve for Excel 97/2000 this is simple,
  as shown in the dialog box here.

11
Using the Solver -- continued

• We add a constraint with Investments in the left
  box and choose the bin option in the middle
  box.
• The binary in the right box is added
  automatically.
• Note that if all changing cells are binary, we do
  not need to check Solvers Assume Non-Negative
  option, but we should still check the Assume
  Linear Model option if it applies, as it does
  here.

12
Solution

• The optimal solution that was shown indicates
  that Tatham can obtain a maximum NPV of 46,000
  by selecting investments 1, 3, and 4.
• These three investments use up only 14,500 of
  the available budget, with 500 left over.
  However this 500 is not enough investing all
  the way is required to invest in any of the
  remaining investments.
• If we rank Tathams investments on the basis of
  NPV per dollar invested, the ranking from best to
  worst is 4, 1, 2, 5, 3, 6, 7.

13
Solution -- continued

• Using your economic intuition, you might expect
  the investments to be chosen in this order, until
  the budget runs out.
• However, the optimal solution does not do this.
  It selects investment 3 instead of 2 or 5.
• To understand why this is the case, suppose
  Tatham invests in the three highest-ranking
  investments 4, 1, and 2.
• This uses up 13,500 of the budget, with 1500
  left over and unusable.

14
Solution -- continued

• A better solution is to choose investments 4, 1,
  and 3, which uses the budget more efficiently.
• In general, the trick is to select a combination
  of investments that have good NPVs and use up
  all or almost all of the budget.

15
Sensitivity Analysis

• SolverTable can be used on models with binary
  variables exactly as we have used it in previous
  models.
• Here we see the total NPV varies as the budget
  increases.
• We select the Budget cell as the single input
  cell, allow it to vary from 15,000 to 25,000 in
  increments of 1000, and keep track of the total
  NPV, the amount of the budget used, and the
  binary variables.

16
Sensitivity Analysis -- continued

• The results appear on the next slide.Clearly,
  Tatham can achieve a larger NPV with a larger
  budget, but as the numbers and the chart show,
  each extra 1000 of budget does not have the same
  effect on total NPV.
• The first few 1000 increases to the budget each
  add 4000 to total NPV. Then the jumps from
  18,000 to 19,000 and from 19,000 to 20,000
  add only 2000 to total NPV, but the jump from
  20,000 to 21,000 again adds 4000 to total NPV.
• Note also how selected investments vary wildly as
  the budget increases. This somewhat strange
  behavior is due to the all-or-nothing nature of
  the problem.

17
(No Transcript)