Example 6 1
1 / 17

Example 6.1 - PowerPoint PPT Presentation

  • Uploaded on

Example 6.1. Capital Budgeting Models. 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. Background Information -- continued.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Example 6.1' - gallagher

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Example 6 1 l.jpg

Example 6.1

Capital Budgeting Models

Background information l.jpg
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.

Background information continued l.jpg
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.

Solution l.jpg

  • 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

Tatham xls l.jpg

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

Developing the model l.jpg
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.

Developing the model continued l.jpg
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.

Using the solver l.jpg
Using the Solver

  • The Solver dialog box is shown here.

Using the solver continued l.jpg
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.

Using the solver continued11 l.jpg
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 Solver’s Assume Non-Negative option, but we should still check the Assume Linear Model option if it applies, as it does here.

Solution12 l.jpg

  • 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 Tatham’s investments on the basis of NPV per dollar invested, the ranking from best to worst is 4, 1, 2, 5, 3, 6, 7.

Solution continued l.jpg
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.

Solution continued14 l.jpg
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.

Sensitivity analysis l.jpg
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.

Sensitivity analysis continued l.jpg
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.