estimasi aliran kas
Post on 14-Oct-2015
30 Views
Preview:
TRANSCRIPT
-
ESTIMASI ALIRAN KAS
-
PENDAHULUANEstimasi aliran kas (cash flow estimation) merupakan tahap yang paling penting dan sekaligus paling sulit dalam proses capital budgeting.
Dua hal penting dalam estimasi aliran kas:
1. Keputusan dalam capital budgeting harus didasarkan pada aliran kas bukan laba bersih.
2. Hanya incremental cash flow yang relevan dalam estimasi aliran kas.
-
TIGA KELOMPOK UTAMA ALIRAN KASInitial investment outlay: kelompok aliran kas yang terjadi pada awal proyek (sebelum proyek beroperasi). Misalnya: pembelian tanah, gedung, mesin, peraatan dan modal kerja.
Operating cash flow: kelompok aliran kas yang terjadi selama proyek beroperasi (umur proyek).
Terminal year cash flow: kelompok aliran kas yang terjadi pada saat proyek dianggap berakhir (terminate). Misalkan: nilai sisa bersih dan pengembalian modal kerja
-
TIGA KELOMPOK UTAMA ALIRAN KAS0123456Initial inv. outlayOperating cash flowTerminal year cash flow Tanah, gedung Mesin, perlengkapan Modal kerja bersih Penjualan Harga pokok penjualan Laba kotorB. AdministrasiB. DepresiasiLaba sebelum pajakPajakLaba bersihOperating CF = Laba bersih + Depresiasi
NIlai sisa bersihPengembalian modal kerja bersihOperating CF = Laba bersih + Depresiasi
-
INPUT DATA
Sheet1
12model2/25/03 23:065/8/00
Chapter 12. Spreadsheet Models for Capital Budgeting
This worksheet contains the model used to analyze BQC's new project decision as described in the text.
In addition, models for analyzing replacement decisions and bond refunding decisions are
provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"
and "Bond Refunding" at the bottom of the screen.
Model for Evaluating A New Capital Budgeting Project:
The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs
used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.
Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the
building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of
the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,
and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs
result in changes in the key outputs.
Note that all dollars are shown in thousands; this is done for convenience.
Identifying the relevant cash flows
For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,
operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from
the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS
Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the
determination of net salvage values to be incorporated into our cash flow estimation.
Table 12-1. Analysis of a New (Expansion) Project
Part 1. Input Data (in thousands of dollars)Data used in Scenario Analysis
Key Output: NPV =51,661,753Sale PriceUnitsVCGrowth
Building cost (= Depreciable basis)120,000,000
Equipment cost (= Depreciable basis)80,000,000Market value of building in 200575,000,000Good$3.9026,000$1.4730%
Net Operating WC60,000,000Market value of equip. in 200520,000,000Base$3.0020,000$2.100%
First year sales (in units)2,000,000Tax rate40%Bad$2.1014,000$2.73-30%
Growth rate in units sold0.0%WACC12%
Sales price per unit300Inflation: growth in sales price0.0%Change the inputs to the above to get the NPVs
Variable cost per unit210Inflation: growth in VC per unit0.0%for the scenarios below and the cash flows for the
Fixed costs80,000,000Inflation: growth in fixed costs0.0%decision tree. Change to the indicated variables,
then use copy>Paste Special>Values to
YearsCumulativerecord the results below, and then make the next
1234Depr'nset of changes. When finished, return to base
Building Depr'n Rate1.3%2.6%2.6%2.6%case variable levels.
Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000
Ending Book Val: Cost - Cum. Depr'n118,440,000115,320,000112,200,000109,080,000
Equipment Depr'n Rate20.0%32.0%19.0%12.0%
Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000
Ending Book Val: Cost - Cum. Depr'n64,000,00038,400,00023,200,00013,600,000
to determine the depreciation expense for the year. See Appendix 12A for a review of MACRS depreciation rates.
Part 3 of Table 12-1. Net Salvage Values in 2005
BuildingEquipmentTotal
Estimated Market Value in 200575,000,00020,000,000
109,080,00013,600,000
-34,080,0006,400,000
Taxes paid or tax credit-13,632,0002,560,000
88,632,00017,440,000106,072,000
building, accumulated depreciation equals $1,092, so book value equals $12,000 - $1,092 = $10,908. For the equipment,
accumulated depreciation equals $6,640, so book value equals $8,000 - $6,640 = $1,360.
taken versus "true" depreciation, and it is treated as an operating expense for 2005. Equipment: $2,000 market value -
$1,360 book value = $640 profit. Here the depreciation charge exceeds the "true" depreciation, and the difference is called
"depreciation recapture". It is taxed as ordinary income in 2005.
net salvage value = $7,500 - (-$1,363) = $8,863.
Part 4 of Table 12-1. Projected Net CashYears
01234
20012002200320042005
Investment Outlays at Time Zero
Building-120,000,000
Equipment-80,000,000
Increase in Net Operating WC-60,000,000
Operating Cash Flows over the Project's Life
Units sold2,000,0002,000,0002,000,0002,000,000
Sales price300300300300
Sales revenue600,000,000600,000,000600,000,000600,000,000
Variable costs420,000,000420,000,000420,000,000420,000,000
Fixed operating costs80,000,00080,000,00080,000,00080,000,000
Depreciation (building)1,560,0003,120,0003,120,0003,120,000
Depreciation (equipment)16,000,00025,600,00015,200,0009,600,000
Oper. income before taxes (EBIT)82,440,00071,280,00081,680,00087,280,000
Taxes on operating income (40%)32,976,00028,512,00032,672,00034,912,000
Net Operating Profit After Taxes (NOPAT)49,464,00042,768,00049,008,00052,368,000
Add back depreciation17,560,00028,720,00018,320,00012,720,000
Operating cash flow67,024,00071,488,00067,328,00065,088,000
Terminal Year Cash Flows
60,000,000
Net salvage value106,072,000
Total termination cash flows166,072,000
Net Cash Flow (Time line of cash flows)-260,000,00067,024,00071,488,00067,328,000231,160,000
sold off and receivables are collected.
Part 5 of Table 12-1. Key Output and Appraisal of the Proposed Project
Net Present Value (at 12%)51,661,753
IRR19.33%
MIRR17.19%Years
01234
Cumulative cash flow for payback(260,000,000)(192,976,000)(121,488,000)(54,160,000)177,000,000
Cum. CF > 0, hence Payback Year:0.000.000.000.003.23
Payback found with Excel function =3.23See note below for an explanation of the Excel calculation.
Check: Payback = 3 + 5,416/23,116 =3.23Manual calculation for the base case.
The Excel payback calculation is based on the logical IF function. Returns FALSE if the cumulative CF is negative or
the actual payback if the cumulative CF is positive. Then, we use the MIN (minimum) function to find first year when
payback is positive. The min function procedure is necessary for projects with longer lives, because then values, not the
word FALSE, would appear in a number of cells. The min function picks the smallest number, which is the payback.
Based on the firm's 12% weighted average cost of capital, this project has a NPV of $5,166. Since the NPV is positive,
we tentatively conclude that the project should be accepted. The IRR and MIRR confirm this decision because both
exceed the cost of capital. Note, though, that no risk analysis has been conducted. It is possible that the firm's
managers, after appraising the project's risk, might conclude that its projected return is insufficient to compensate
for its risk, and reject it.
Part 6. Evaluating Risk: Sensitivity Analysis
Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected outcome.
For example, if there were a high probability that the $5,166 expected NPV as calculated above will actually turn
out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected
outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project turned
out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than expected,
then the output will likewise be worse than expected. In Part 6 we use Excel to examine the project's sensitivity to
changes in the input variables.
I. Sensitivity of NPV and to Variations in Unit Sales.
Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
% DeviationWACC% Deviation1st YEAR UNIT SALES
fromNPVfromUnitsNPVGoal Seek finds the value of some input variable that
Base CaseWACC51,661,753Base CaseSold$51,661,7531st Year Sales wouldcaused an output variable to hit a specified level. In
-30%8.4%$8,294-30%14,000-$4,675the project break eventhis case, we want to know the 1st year unit sales that
-15%10.2%$6,674-15%17,000$246in the sense that NPVwill cause NPV = 0, holding other things constant.
0%12.0%$5,166Base Case0%20,000$5,166= $0?
15%13.8%$3,76115%23,000$10,087Click Tools, then Goal Seek, to get the following dialog
30%15.6%$2,45030%26,000$15,007box, which we completed:
NPV against Sales and
see about where NPV
% DeviationVARIABLE COSTS% DeviationGROWTH RATE, UNITS= 0. Alternatively, you
fromVariableNPVfromGrowthNPVcould use Tools > Goal
Base CaseCost$51,661,753Base CaseRate %$51,661,753Seek as described in
-30%$1.47$28,129-30%-30%-$5,847the columns to the right.
-15%$1.79$16,647-15%-15%-$907The answer is 16,850
0%$2.10$5,166Base Case0%0%$5,166units.
15%$2.42-$6,31515%15%$12,512
30%$2.73-$17,79630%30%$21,269
We want to get NPV = 0, so we specify the "Set cell" as
being G148 (Excel adds the dollar signs), and the "To
% DeviationSALES PRICE% DeviationFIXED COSTSvalue" to be 0. We specify the cell to change as F148,
fromSalesNPVfromFixedNPVand when we click OK, G148 changes to $0 and F148
Base CasePrice$51,661,753Base CaseCosts$51,661,753goes to 16,850.
-30%$2.10-$27,637-30%$5,600$9,540
-15%$2.55-$11,236-15%$6,800$7,353The Goal Seek feature is easy to use, and it often
0%$3.00$5,166Base Case0%$8,000$5,166comes in handy.
15%$3.45$21,56815%$9,200$2,979
30%$3.90$37,97030%$10,400$792
We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:
Figure 12-1. Evaluating Risk: Sensitivity Analysis (Dollars in Thousands)
DeviationNPV at Different Deviations from Base
fromSalesVariableGrowthYear 1Fixed
Base CasePriceCost/UnitRateUnits SoldCostWACC
-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294
-15%($11,236)$16,647($907)$246$7,353$6,674
0%$5,166$5,166$5,166$5,166$5,166$5,166
15%$21,568($6,315)$12,512$10,087$2,979$3,761
30%$37,970($17,796)$21,269$15,007$792$2,450
Range65,60745,92527,11619,6828,7485,844
We see from the tables and graph that NPV is most sensitive to changes in the sales price and variable
costs, somewhat sensitive to changes in first-year sales and the sales growth rate, and not very sensitive to
changes in WACC and fixed costs. Thus, the real issue is our confidence in the forecasts of the sales
price and variable costs, as well as the first-year sales and the growth rate in units sold.
NPV can change dramatically if the key input variables change, but we do not know how much the
variables are likely to change. For example, if we were buying components under a fixed price contract,
then variable costs might be locked in and not likely to rise more than say 5%, and we might have a firm
contract to sell the projected number of units at the indicated price per unit. In that case, the "bad
conditions" would not materialize, and a positive NPV would be pretty well guaranteed. We go on to look at
the probabilities of different conditions in Part 7.
Part 7. Evaluating Risk: Scenario Analysis
Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence
to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilities of
changes in the key variables. Part 7 provides a scenario analysis of the computer project.
We saw from the sensitivity analysis that the key variables are sales price, variable costs, unit sales, and the unit
growth rate. Therefore, in our sensitivity analysis we hold the other variables at their base case levels and then
examine the situation when the key variables change. We assume that the company regards the worst case as one
where each of the three variables is 30% worse than the base level, and the best case has each variable 30% better
than base. We also assume that there is a 25% chance of the best and worst cases, and a 50% chance of base case
levels.
Table 12-2. Scenario Analysis (Dollars in Thousands)Squared
Deviation
SalesUnitVariableGrowthTimes
ScenarioProbabilityPriceSalesCostsRateNPVProbability
Best Case25%$3.9026,000$1.4730%$144,0243307034782Manually changed the Part 1 inputs, then put the
Base Case50%$3.0020,000$2.100%$5,166284276572NPV as calculated with the modified variables here.
Worst Case25%$2.1014,000$2.73-30%($38,315)1133161977Took deviations from the expected values, squared
4724473330them, and multiplied by the probabilities to get the
Expected NPV = sum, prob times NPV$29,010numbers shown.
Standard Deviation = Sq Root of column I sum$68,735Summed the squared deviations and took sq root
Coefficient of Variation = Std Dev / Expected NPV2.37Std Dev divided by Expected NPV
a. Probability Graph
Probability
50%
25%
(38,315)029,010144,024
5,166NPV ($)
Most LikelyMean of distribution
b. Continuous Approximation
Probability Density
(38,315)029,010144,024
NPV ($)
5,166
The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a
25% probability that the project would result in a loss of $38.3 million. There is also a 25% probability that it could
produce an NPV of $144 million. The standard deviation is high, at $68.7 million, and the coefficient of variation is a
high 2.37.
Note that the expected NPV in the scenario analysis is much higher than the base case value. This occurs because
under good conditions we have high numbers multiplied by other high numbers, giving a very high result.
This analysis suggest that the project is relatively risky, hence that the base case NPV should be recalculated using a
higher WACC. At a WACC of 15% (versus 12% for an average risk project), the base case NPV is:$2,877
That number is not very high in relation to the project's cost.
Changing the WACC would also change the scenario analysis. Here are new figures:
ProbabilityNPVDev. Sqd^2
Worst Case25%($38,102)992811800
Base Case50%$2,877242855067
Best Case25%$132,0112867361381
Expected NPV:$24,916
Standard Deviation:$64,055
Coefficient of Variation:2.57
At this point, the project looks risky but acceptable. There is a good chance that it will produce an NPV of $2,877, but
there is also a chance that the NPV could be dramatically higher or lower.
If the bad conditions occur, this will hurt but not bankrupt the firm--this is just one project for a large company.
We indicate at the start that this project's returns would be highly correlated with the firm's other projects'
returns and also with the general stock market. Thus, its stand-alone risk (which is what we have been analyzing)
also reflects its within-firm and market risk. If this were not true, then we would need to make further risk
adjustments.
Finally, recall that we stated at the start that if the firm undertakes the project, it will be committed to operate it for the
full 4-year life. That is important, because if it were not so committed, then if the bad conditions occurred during the
first year of operations, the firm could simply close down operations. This would cut its losses, and the worse case
scenario would not be nearly as bad as we indicated. Then, the expected NPV would be higher, and the standard
deviation and coefficient of variation would be lower. We extend the model to deal with abandonment in the next section,
which is designed to be examined after reading Chapter 13.
EXTENDING THE MODEL TO ALLOW FOR ABANDONMENT
This section is an "extension" that deals with a "real option," the option to abandon the project. This topic is covered in
Chapter 13, so it will be more clear after you have read that chapter.
abandoned, then the company can sell the related assets at the end of the year after abandonment and realize a net cash
flow equal to the asset's book value at the end of the abandonment year. Use a decision tree format to set up the scenarios
with and without the abandonment option. Thus, if it has bad results in Year 1, it can abandon the project and receive the
end-of-Year 1 book value as a cash flow at the end of Year 2.
Situation 1. Decision Tree Where Firm Cannot Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697
25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995
Expected NPV = sum, prob times NPV$29,010
Standard Deviation = Sq Root of column K sum$68,735
Coefficient of Variation = Std Dev / Expected NPV2.37
Situation 2. Decision Tree Where Firm Has the Option to Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553
25%0%-$7,356-$6,660$10,5010%-$38,315$00
-$9,390
25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
Expected NPV = sum, prob times NPV$36,390,006
Standard Deviation = Sq Root of column K sum$62,940,247
Coefficient of Variation (CV) = Std Dev / Expected NPV1.73
Where the abandonment option exists, the firm would definitely abandon the project at the end of Year 1 if the worse case
scenario developed. Therefore, we show a zero probability of continuing to operate in that case, and a 25% probability
that case will develop and the project will be abandoned.
Under abandonment, the worse case scenario still results in a negative NPV, but it is less negative than if abandonment
were not possible. This raises the expected NPV and lowers the standard deviation, and both of these changes work to
lower the CV. Thus, the project is seen to be more profitable and to have a lower risk if it can be abandoned.
Note that we discounted cash flows at 12% in the decision tree analysis. However, if 12% were appropriate given the
risk as measured by the CV in the no-abandonment case, then a lower WACC would be appropriate if the abandonment
option exists. Thus, it would make sense to go through another iteration and find the project's with-abandonment NPV
using a somewhat lower WACC. No precise procedure is available for determining the risk-adjusted discount rate, so
the adjustment would have to be made on a judgmental basis.
Note also that this type of analysis can be use to estimate the value of the option to abandon--its value is the difference
between the NPVs with and without abandonment. In this case, with our figures, it is
Expected NPV with Abandonment$36,390,006
Expected NPV without Abandonment$29,010
Approximate Value of Option$36,360,996
Thus, if abandonment were not possible due to an agreement with a labor union, BQC could afford to offer up to $4.619
million to get out of that constraint.
Finally, note that other types of real options, including timing options, expansion options, production flexibility options,
and the like can be analyzed using the decision tree format. Decision trees provide a powerful tool for investment
decision analysis.
Sheet1
-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866
-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544
5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066
21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519
37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813
Sales price
Variable cost
Growth rate
Units sold
Fixed cost
WACC
NPV
Sheet2
Sheet3
-
Tabel 1. Klasifikasi Berdasarkan Jenis dan Umur Proyek
Class Life
Type of property
3 years
Certain special manufacturing tools
5 years
Automobiles, light-duty trucks, computers, and certain special manufacturing equipment
7 years
Most industrial equipment, office furniture, and fixtures
10 years
Certain longer-lived types of equipment
27,5 years
Residential rental real property such as apartment buildings
39 years
All nonresidential real property, including commercial and industrial buildings
-
Tabel 2. Beban Depresiasi Berdasarkan Kelas Aset
Ownership Year
Class of Investment
3 year
5 year
7 year
10 year
1
33%
20%
14%
10%
2
45
32
25
18
3
15
19
17
14
4
7
12
13
12
5
11
9
9
6
6
9
7
7
9
7
8
4
7
9
7
10
6
11
3
100%
100%
100%
100%
-
SKEDUL DEPRESIASI
Sheet1
12model2/25/03 23:125/8/00
Chapter 12. Spreadsheet Models for Capital Budgeting
This worksheet contains the model used to analyze BQC's new project decision as described in the text.
In addition, models for analyzing replacement decisions and bond refunding decisions are
provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"
and "Bond Refunding" at the bottom of the screen.
Model for Evaluating A New Capital Budgeting Project:
The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs
used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.
Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the
building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of
the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,
and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs
result in changes in the key outputs.
Note that all dollars are shown in thousands; this is done for convenience.
Identifying the relevant cash flows
For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,
operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from
the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS
Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the
determination of net salvage values to be incorporated into our cash flow estimation.
Table 12-1. Analysis of a New (Expansion) Project
Part 1. Input Data (in thousands of dollars)Data used in Scenario Analysis
Key Output: NPV =51,661,753Sale PriceUnitsVCGrowth
Building cost (= Depreciable basis)120,000,000
Equipment cost (= Depreciable basis)80,000,000Market value of building in 200575,000,000Good$3.9026,000$1.4730%
Net Operating WC60,000,000Market value of equip. in 200520,000,000Base$3.0020,000$2.100%
First year sales (in units)2,000,000Tax rate40%Bad$2.1014,000$2.73-30%
Growth rate in units sold0.0%WACC12%
Sales price per unit300Inflation: growth in sales price0.0%Change the inputs to the above to get the NPVs
Variable cost per unit210Inflation: growth in VC per unit0.0%for the scenarios below and the cash flows for the
Fixed costs80,000,000Inflation: growth in fixed costs0.0%decision tree. Change to the indicated variables,
then use copy>Paste Special>Values to
YearsCumulativerecord the results below, and then make the next
1234Depr'nset of changes. When finished, return to base
Building Depr'n Rate1.3%2.6%2.6%2.6%case variable levels.
Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000
Ending Book Val: Cost - Cum. Depr'n118,440,000115,320,000112,200,000109,080,000
Equipment Depr'n Rate20.0%32.0%19.0%12.0%
Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000
Ending Book Val: Cost - Cum. Depr'n64,000,00038,400,00023,200,00013,600,000
to determine the depreciation expense for the year. See Appendix 12A for a review of MACRS depreciation rates.
Part 3 of Table 12-1. Net Salvage Values in 2005
BuildingEquipmentTotal
Estimated Market Value in 200575,000,00020,000,000
109,080,00013,600,000
-34,080,0006,400,000
Taxes paid or tax credit-13,632,0002,560,000
88,632,00017,440,000106,072,000
building, accumulated depreciation equals $1,092, so book value equals $12,000 - $1,092 = $10,908. For the equipment,
accumulated depreciation equals $6,640, so book value equals $8,000 - $6,640 = $1,360.
taken versus "true" depreciation, and it is treated as an operating expense for 2005. Equipment: $2,000 market value -
$1,360 book value = $640 profit. Here the depreciation charge exceeds the "true" depreciation, and the difference is called
"depreciation recapture". It is taxed as ordinary income in 2005.
net salvage value = $7,500 - (-$1,363) = $8,863.
Part 4 of Table 12-1. Projected Net CashYears
01234
20012002200320042005
Investment Outlays at Time Zero
Building-120,000,000
Equipment-80,000,000
Increase in Net Operating WC-60,000,000
Operating Cash Flows over the Project's Life
Units sold2,000,0002,000,0002,000,0002,000,000
Sales price300300300300
Sales revenue600,000,000600,000,000600,000,000600,000,000
Variable costs420,000,000420,000,000420,000,000420,000,000
Fixed operating costs80,000,00080,000,00080,000,00080,000,000
Depreciation (building)1,560,0003,120,0003,120,0003,120,000
Depreciation (equipment)16,000,00025,600,00015,200,0009,600,000
Oper. income before taxes (EBIT)82,440,00071,280,00081,680,00087,280,000
Taxes on operating income (40%)32,976,00028,512,00032,672,00034,912,000
Net Operating Profit After Taxes (NOPAT)49,464,00042,768,00049,008,00052,368,000
Add back depreciation17,560,00028,720,00018,320,00012,720,000
Operating cash flow67,024,00071,488,00067,328,00065,088,000
Terminal Year Cash Flows
60,000,000
Net salvage value106,072,000
Total termination cash flows166,072,000
Net Cash Flow (Time line of cash flows)-260,000,00067,024,00071,488,00067,328,000231,160,000
sold off and receivables are collected.
Part 5 of Table 12-1. Key Output and Appraisal of the Proposed Project
Net Present Value (at 12%)51,661,753
IRR19.33%
MIRR17.19%Years
01234
Cumulative cash flow for payback(260,000,000)(192,976,000)(121,488,000)(54,160,000)177,000,000
Cum. CF > 0, hence Payback Year:0.000.000.000.003.23
Payback found with Excel function =3.23See note below for an explanation of the Excel calculation.
Check: Payback = 3 + 5,416/23,116 =3.23Manual calculation for the base case.
The Excel payback calculation is based on the logical IF function. Returns FALSE if the cumulative CF is negative or
the actual payback if the cumulative CF is positive. Then, we use the MIN (minimum) function to find first year when
payback is positive. The min function procedure is necessary for projects with longer lives, because then values, not the
word FALSE, would appear in a number of cells. The min function picks the smallest number, which is the payback.
Based on the firm's 12% weighted average cost of capital, this project has a NPV of $5,166. Since the NPV is positive,
we tentatively conclude that the project should be accepted. The IRR and MIRR confirm this decision because both
exceed the cost of capital. Note, though, that no risk analysis has been conducted. It is possible that the firm's
managers, after appraising the project's risk, might conclude that its projected return is insufficient to compensate
for its risk, and reject it.
Part 6. Evaluating Risk: Sensitivity Analysis
Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected outcome.
For example, if there were a high probability that the $5,166 expected NPV as calculated above will actually turn
out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected
outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project turned
out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than expected,
then the output will likewise be worse than expected. In Part 6 we use Excel to examine the project's sensitivity to
changes in the input variables.
I. Sensitivity of NPV and to Variations in Unit Sales.
Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
% DeviationWACC% Deviation1st YEAR UNIT SALES
fromNPVfromUnitsNPVGoal Seek finds the value of some input variable that
Base CaseWACC51,661,753Base CaseSold$51,661,7531st Year Sales wouldcaused an output variable to hit a specified level. In
-30%8.4%$8,294-30%14,000-$4,675the project break eventhis case, we want to know the 1st year unit sales that
-15%10.2%$6,674-15%17,000$246in the sense that NPVwill cause NPV = 0, holding other things constant.
0%12.0%$5,166Base Case0%20,000$5,166= $0?
15%13.8%$3,76115%23,000$10,087Click Tools, then Goal Seek, to get the following dialog
30%15.6%$2,45030%26,000$15,007box, which we completed:
NPV against Sales and
see about where NPV
% DeviationVARIABLE COSTS% DeviationGROWTH RATE, UNITS= 0. Alternatively, you
fromVariableNPVfromGrowthNPVcould use Tools > Goal
Base CaseCost$51,661,753Base CaseRate %$51,661,753Seek as described in
-30%$1.47$28,129-30%-30%-$5,847the columns to the right.
-15%$1.79$16,647-15%-15%-$907The answer is 16,850
0%$2.10$5,166Base Case0%0%$5,166units.
15%$2.42-$6,31515%15%$12,512
30%$2.73-$17,79630%30%$21,269
We want to get NPV = 0, so we specify the "Set cell" as
being G148 (Excel adds the dollar signs), and the "To
% DeviationSALES PRICE% DeviationFIXED COSTSvalue" to be 0. We specify the cell to change as F148,
fromSalesNPVfromFixedNPVand when we click OK, G148 changes to $0 and F148
Base CasePrice$51,661,753Base CaseCosts$51,661,753goes to 16,850.
-30%$2.10-$27,637-30%$5,600$9,540
-15%$2.55-$11,236-15%$6,800$7,353The Goal Seek feature is easy to use, and it often
0%$3.00$5,166Base Case0%$8,000$5,166comes in handy.
15%$3.45$21,56815%$9,200$2,979
30%$3.90$37,97030%$10,400$792
We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:
Figure 12-1. Evaluating Risk: Sensitivity Analysis (Dollars in Thousands)
DeviationNPV at Different Deviations from Base
fromSalesVariableGrowthYear 1Fixed
Base CasePriceCost/UnitRateUnits SoldCostWACC
-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294
-15%($11,236)$16,647($907)$246$7,353$6,674
0%$5,166$5,166$5,166$5,166$5,166$5,166
15%$21,568($6,315)$12,512$10,087$2,979$3,761
30%$37,970($17,796)$21,269$15,007$792$2,450
Range65,60745,92527,11619,6828,7485,844
We see from the tables and graph that NPV is most sensitive to changes in the sales price and variable
costs, somewhat sensitive to changes in first-year sales and the sales growth rate, and not very sensitive to
changes in WACC and fixed costs. Thus, the real issue is our confidence in the forecasts of the sales
price and variable costs, as well as the first-year sales and the growth rate in units sold.
NPV can change dramatically if the key input variables change, but we do not know how much the
variables are likely to change. For example, if we were buying components under a fixed price contract,
then variable costs might be locked in and not likely to rise more than say 5%, and we might have a firm
contract to sell the projected number of units at the indicated price per unit. In that case, the "bad
conditions" would not materialize, and a positive NPV would be pretty well guaranteed. We go on to look at
the probabilities of different conditions in Part 7.
Part 7. Evaluating Risk: Scenario Analysis
Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence
to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilities of
changes in the key variables. Part 7 provides a scenario analysis of the computer project.
We saw from the sensitivity analysis that the key variables are sales price, variable costs, unit sales, and the unit
growth rate. Therefore, in our sensitivity analysis we hold the other variables at their base case levels and then
examine the situation when the key variables change. We assume that the company regards the worst case as one
where each of the three variables is 30% worse than the base level, and the best case has each variable 30% better
than base. We also assume that there is a 25% chance of the best and worst cases, and a 50% chance of base case
levels.
Table 12-2. Scenario Analysis (Dollars in Thousands)Squared
Deviation
SalesUnitVariableGrowthTimes
ScenarioProbabilityPriceSalesCostsRateNPVProbability
Best Case25%$3.9026,000$1.4730%$144,0243307034782Manually changed the Part 1 inputs, then put the
Base Case50%$3.0020,000$2.100%$5,166284276572NPV as calculated with the modified variables here.
Worst Case25%$2.1014,000$2.73-30%($38,315)1133161977Took deviations from the expected values, squared
4724473330them, and multiplied by the probabilities to get the
Expected NPV = sum, prob times NPV$29,010numbers shown.
Standard Deviation = Sq Root of column I sum$68,735Summed the squared deviations and took sq root
Coefficient of Variation = Std Dev / Expected NPV2.37Std Dev divided by Expected NPV
a. Probability Graph
Probability
50%
25%
(38,315)029,010144,024
5,166NPV ($)
Most LikelyMean of distribution
b. Continuous Approximation
Probability Density
(38,315)029,010144,024
NPV ($)
5,166
The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a
25% probability that the project would result in a loss of $38.3 million. There is also a 25% probability that it could
produce an NPV of $144 million. The standard deviation is high, at $68.7 million, and the coefficient of variation is a
high 2.37.
Note that the expected NPV in the scenario analysis is much higher than the base case value. This occurs because
under good conditions we have high numbers multiplied by other high numbers, giving a very high result.
This analysis suggest that the project is relatively risky, hence that the base case NPV should be recalculated using a
higher WACC. At a WACC of 15% (versus 12% for an average risk project), the base case NPV is:$2,877
That number is not very high in relation to the project's cost.
Changing the WACC would also change the scenario analysis. Here are new figures:
ProbabilityNPVDev. Sqd^2
Worst Case25%($38,102)992811800
Base Case50%$2,877242855067
Best Case25%$132,0112867361381
Expected NPV:$24,916
Standard Deviation:$64,055
Coefficient of Variation:2.57
At this point, the project looks risky but acceptable. There is a good chance that it will produce an NPV of $2,877, but
there is also a chance that the NPV could be dramatically higher or lower.
If the bad conditions occur, this will hurt but not bankrupt the firm--this is just one project for a large company.
We indicate at the start that this project's returns would be highly correlated with the firm's other projects'
returns and also with the general stock market. Thus, its stand-alone risk (which is what we have been analyzing)
also reflects its within-firm and market risk. If this were not true, then we would need to make further risk
adjustments.
Finally, recall that we stated at the start that if the firm undertakes the project, it will be committed to operate it for the
full 4-year life. That is important, because if it were not so committed, then if the bad conditions occurred during the
first year of operations, the firm could simply close down operations. This would cut its losses, and the worse case
scenario would not be nearly as bad as we indicated. Then, the expected NPV would be higher, and the standard
deviation and coefficient of variation would be lower. We extend the model to deal with abandonment in the next section,
which is designed to be examined after reading Chapter 13.
EXTENDING THE MODEL TO ALLOW FOR ABANDONMENT
This section is an "extension" that deals with a "real option," the option to abandon the project. This topic is covered in
Chapter 13, so it will be more clear after you have read that chapter.
abandoned, then the company can sell the related assets at the end of the year after abandonment and realize a net cash
flow equal to the asset's book value at the end of the abandonment year. Use a decision tree format to set up the scenarios
with and without the abandonment option. Thus, if it has bad results in Year 1, it can abandon the project and receive the
end-of-Year 1 book value as a cash flow at the end of Year 2.
Situation 1. Decision Tree Where Firm Cannot Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697
25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995
Expected NPV = sum, prob times NPV$29,010
Standard Deviation = Sq Root of column K sum$68,735
Coefficient of Variation = Std Dev / Expected NPV2.37
Situation 2. Decision Tree Where Firm Has the Option to Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553
25%0%-$7,356-$6,660$10,5010%-$38,315$00
-$9,390
25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
Expected NPV = sum, prob times NPV$36,390,006
Standard Deviation = Sq Root of column K sum$62,940,247
Coefficient of Variation (CV) = Std Dev / Expected NPV1.73
Where the abandonment option exists, the firm would definitely abandon the project at the end of Year 1 if the worse case
scenario developed. Therefore, we show a zero probability of continuing to operate in that case, and a 25% probability
that case will develop and the project will be abandoned.
Under abandonment, the worse case scenario still results in a negative NPV, but it is less negative than if abandonment
were not possible. This raises the expected NPV and lowers the standard deviation, and both of these changes work to
lower the CV. Thus, the project is seen to be more profitable and to have a lower risk if it can be abandoned.
Note that we discounted cash flows at 12% in the decision tree analysis. However, if 12% were appropriate given the
risk as measured by the CV in the no-abandonment case, then a lower WACC would be appropriate if the abandonment
option exists. Thus, it would make sense to go through another iteration and find the project's with-abandonment NPV
using a somewhat lower WACC. No precise procedure is available for determining the risk-adjusted discount rate, so
the adjustment would have to be made on a judgmental basis.
Note also that this type of analysis can be use to estimate the value of the option to abandon--its value is the difference
between the NPVs with and without abandonment. In this case, with our figures, it is
Expected NPV with Abandonment$36,390,006
Expected NPV without Abandonment$29,010
Approximate Value of Option$36,360,996
Thus, if abandonment were not possible due to an agreement with a labor union, BQC could afford to offer up to $4.619
million to get out of that constraint.
Finally, note that other types of real options, including timing options, expansion options, production flexibility options,
and the like can be analyzed using the decision tree format. Decision trees provide a powerful tool for investment
decision analysis.
Sheet1
-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866
-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544
5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066
21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519
37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813
Sales price
Variable cost
Growth rate
Units sold
Fixed cost
WACC
NPV
Sheet2
Sheet3
-
NET SALVAGE VALUENSV = MV - {(MV - BV) * T}= 20 jt - {(20jt-13jt) *0.4}= 20 jt - 2.560.000= 17.440.000
Sheet1
12model2/25/03 23:145/8/00
Chapter 12. Spreadsheet Models for Capital Budgeting
This worksheet contains the model used to analyze BQC's new project decision as described in the text.
In addition, models for analyzing replacement decisions and bond refunding decisions are
provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"
and "Bond Refunding" at the bottom of the screen.
Model for Evaluating A New Capital Budgeting Project:
The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs
used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.
Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the
building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of
the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,
and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs
result in changes in the key outputs.
Note that all dollars are shown in thousands; this is done for convenience.
Identifying the relevant cash flows
For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,
operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from
the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS
Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the
determination of net salvage values to be incorporated into our cash flow estimation.
Table 12-1. Analysis of a New (Expansion) Project
Part 1. Input Data (in thousands of dollars)Data used in Scenario Analysis
Key Output: NPV =51,661,753Sale PriceUnitsVCGrowth
Building cost (= Depreciable basis)120,000,000
Equipment cost (= Depreciable basis)80,000,000Market value of building in 200575,000,000Good$3.9026,000$1.4730%
Net Operating WC60,000,000Market value of equip. in 200520,000,000Base$3.0020,000$2.100%
First year sales (in units)2,000,000Tax rate40%Bad$2.1014,000$2.73-30%
Growth rate in units sold0.0%WACC12%
Sales price per unit300Inflation: growth in sales price0.0%Change the inputs to the above to get the NPVs
Variable cost per unit210Inflation: growth in VC per unit0.0%for the scenarios below and the cash flows for the
Fixed costs80,000,000Inflation: growth in fixed costs0.0%decision tree. Change to the indicated variables,
then use copy>Paste Special>Values to
YearsCumulativerecord the results below, and then make the next
1234Depr'nset of changes. When finished, return to base
Building Depr'n Rate1.3%2.6%2.6%2.6%case variable levels.
Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000
Ending Book Val: Cost - Cum. Depr'n118,440,000115,320,000112,200,000109,080,000
Equipment Depr'n Rate20.0%32.0%19.0%12.0%
Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000
Ending Book Val: Cost - Cum. Depr'n64,000,00038,400,00023,200,00013,600,000
to determine the depreciation expense for the year. See Appendix 12A for a review of MACRS depreciation rates.
Part 3 of Table 12-1. Net Salvage Values in 2005
BuildingEquipmentTotal
Estimated Market Value in 200575,000,00020,000,000
109,080,00013,600,000
-34,080,0006,400,000
Taxes paid or tax credit-13,632,0002,560,000
88,632,00017,440,000106,072,000
building, accumulated depreciation equals $1,092, so book value equals $12,000 - $1,092 = $10,908. For the equipment,
accumulated depreciation equals $6,640, so book value equals $8,000 - $6,640 = $1,360.
taken versus "true" depreciation, and it is treated as an operating expense for 2005. Equipment: $2,000 market value -
$1,360 book value = $640 profit. Here the depreciation charge exceeds the "true" depreciation, and the difference is called
"depreciation recapture". It is taxed as ordinary income in 2005.
net salvage value = $7,500 - (-$1,363) = $8,863.
Part 4 of Table 12-1. Projected Net CashYears
01234
20012002200320042005
Investment Outlays at Time Zero
Building-120,000,000
Equipment-80,000,000
Increase in Net Operating WC-60,000,000
Operating Cash Flows over the Project's Life
Units sold2,000,0002,000,0002,000,0002,000,000
Sales price300300300300
Sales revenue600,000,000600,000,000600,000,000600,000,000
Variable costs420,000,000420,000,000420,000,000420,000,000
Fixed operating costs80,000,00080,000,00080,000,00080,000,000
Depreciation (building)1,560,0003,120,0003,120,0003,120,000
Depreciation (equipment)16,000,00025,600,00015,200,0009,600,000
Oper. income before taxes (EBIT)82,440,00071,280,00081,680,00087,280,000
Taxes on operating income (40%)32,976,00028,512,00032,672,00034,912,000
Net Operating Profit After Taxes (NOPAT)49,464,00042,768,00049,008,00052,368,000
Add back depreciation17,560,00028,720,00018,320,00012,720,000
Operating cash flow67,024,00071,488,00067,328,00065,088,000
Terminal Year Cash Flows
60,000,000
Net salvage value106,072,000
Total termination cash flows166,072,000
Net Cash Flow (Time line of cash flows)-260,000,00067,024,00071,488,00067,328,000231,160,000
sold off and receivables are collected.
Part 5 of Table 12-1. Key Output and Appraisal of the Proposed Project
Net Present Value (at 12%)51,661,753
IRR19.33%
MIRR17.19%Years
01234
Cumulative cash flow for payback(260,000,000)(192,976,000)(121,488,000)(54,160,000)177,000,000
Cum. CF > 0, hence Payback Year:0.000.000.000.003.23
Payback found with Excel function =3.23See note below for an explanation of the Excel calculation.
Check: Payback = 3 + 5,416/23,116 =3.23Manual calculation for the base case.
The Excel payback calculation is based on the logical IF function. Returns FALSE if the cumulative CF is negative or
the actual payback if the cumulative CF is positive. Then, we use the MIN (minimum) function to find first year when
payback is positive. The min function procedure is necessary for projects with longer lives, because then values, not the
word FALSE, would appear in a number of cells. The min function picks the smallest number, which is the payback.
Based on the firm's 12% weighted average cost of capital, this project has a NPV of $5,166. Since the NPV is positive,
we tentatively conclude that the project should be accepted. The IRR and MIRR confirm this decision because both
exceed the cost of capital. Note, though, that no risk analysis has been conducted. It is possible that the firm's
managers, after appraising the project's risk, might conclude that its projected return is insufficient to compensate
for its risk, and reject it.
Part 6. Evaluating Risk: Sensitivity Analysis
Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected outcome.
For example, if there were a high probability that the $5,166 expected NPV as calculated above will actually turn
out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected
outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project turned
out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than expected,
then the output will likewise be worse than expected. In Part 6 we use Excel to examine the project's sensitivity to
changes in the input variables.
I. Sensitivity of NPV and to Variations in Unit Sales.
Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
% DeviationWACC% Deviation1st YEAR UNIT SALES
fromNPVfromUnitsNPVGoal Seek finds the value of some input variable that
Base CaseWACC51,661,753Base CaseSold$51,661,7531st Year Sales wouldcaused an output variable to hit a specified level. In
-30%8.4%$8,294-30%14,000-$4,675the project break eventhis case, we want to know the 1st year unit sales that
-15%10.2%$6,674-15%17,000$246in the sense that NPVwill cause NPV = 0, holding other things constant.
0%12.0%$5,166Base Case0%20,000$5,166= $0?
15%13.8%$3,76115%23,000$10,087Click Tools, then Goal Seek, to get the following dialog
30%15.6%$2,45030%26,000$15,007box, which we completed:
NPV against Sales and
see about where NPV
% DeviationVARIABLE COSTS% DeviationGROWTH RATE, UNITS= 0. Alternatively, you
fromVariableNPVfromGrowthNPVcould use Tools > Goal
Base CaseCost$51,661,753Base CaseRate %$51,661,753Seek as described in
-30%$1.47$28,129-30%-30%-$5,847the columns to the right.
-15%$1.79$16,647-15%-15%-$907The answer is 16,850
0%$2.10$5,166Base Case0%0%$5,166units.
15%$2.42-$6,31515%15%$12,512
30%$2.73-$17,79630%30%$21,269
We want to get NPV = 0, so we specify the "Set cell" as
being G148 (Excel adds the dollar signs), and the "To
% DeviationSALES PRICE% DeviationFIXED COSTSvalue" to be 0. We specify the cell to change as F148,
fromSalesNPVfromFixedNPVand when we click OK, G148 changes to $0 and F148
Base CasePrice$51,661,753Base CaseCosts$51,661,753goes to 16,850.
-30%$2.10-$27,637-30%$5,600$9,540
-15%$2.55-$11,236-15%$6,800$7,353The Goal Seek feature is easy to use, and it often
0%$3.00$5,166Base Case0%$8,000$5,166comes in handy.
15%$3.45$21,56815%$9,200$2,979
30%$3.90$37,97030%$10,400$792
We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:
Figure 12-1. Evaluating Risk: Sensitivity Analysis (Dollars in Thousands)
DeviationNPV at Different Deviations from Base
fromSalesVariableGrowthYear 1Fixed
Base CasePriceCost/UnitRateUnits SoldCostWACC
-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294
-15%($11,236)$16,647($907)$246$7,353$6,674
0%$5,166$5,166$5,166$5,166$5,166$5,166
15%$21,568($6,315)$12,512$10,087$2,979$3,761
30%$37,970($17,796)$21,269$15,007$792$2,450
Range65,60745,92527,11619,6828,7485,844
We see from the tables and graph that NPV is most sensitive to changes in the sales price and variable
costs, somewhat sensitive to changes in first-year sales and the sales growth rate, and not very sensitive to
changes in WACC and fixed costs. Thus, the real issue is our confidence in the forecasts of the sales
price and variable costs, as well as the first-year sales and the growth rate in units sold.
NPV can change dramatically if the key input variables change, but we do not know how much the
variables are likely to change. For example, if we were buying components under a fixed price contract,
then variable costs might be locked in and not likely to rise more than say 5%, and we might have a firm
contract to sell the projected number of units at the indicated price per unit. In that case, the "bad
conditions" would not materialize, and a positive NPV would be pretty well guaranteed. We go on to look at
the probabilities of different conditions in Part 7.
Part 7. Evaluating Risk: Scenario Analysis
Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence
to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilities of
changes in the key variables. Part 7 provides a scenario analysis of the computer project.
We saw from the sensitivity analysis that the key variables are sales price, variable costs, unit sales, and the unit
growth rate. Therefore, in our sensitivity analysis we hold the other variables at their base case levels and then
examine the situation when the key variables change. We assume that the company regards the worst case as one
where each of the three variables is 30% worse than the base level, and the best case has each variable 30% better
than base. We also assume that there is a 25% chance of the best and worst cases, and a 50% chance of base case
levels.
Table 12-2. Scenario Analysis (Dollars in Thousands)Squared
Deviation
SalesUnitVariableGrowthTimes
ScenarioProbabilityPriceSalesCostsRateNPVProbability
Best Case25%$3.9026,000$1.4730%$144,0243307034782Manually changed the Part 1 inputs, then put the
Base Case50%$3.0020,000$2.100%$5,166284276572NPV as calculated with the modified variables here.
Worst Case25%$2.1014,000$2.73-30%($38,315)1133161977Took deviations from the expected values, squared
4724473330them, and multiplied by the probabilities to get the
Expected NPV = sum, prob times NPV$29,010numbers shown.
Standard Deviation = Sq Root of column I sum$68,735Summed the squared deviations and took sq root
Coefficient of Variation = Std Dev / Expected NPV2.37Std Dev divided by Expected NPV
a. Probability Graph
Probability
50%
25%
(38,315)029,010144,024
5,166NPV ($)
Most LikelyMean of distribution
b. Continuous Approximation
Probability Density
(38,315)029,010144,024
NPV ($)
5,166
The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a
25% probability that the project would result in a loss of $38.3 million. There is also a 25% probability that it could
produce an NPV of $144 million. The standard deviation is high, at $68.7 million, and the coefficient of variation is a
high 2.37.
Note that the expected NPV in the scenario analysis is much higher than the base case value. This occurs because
under good conditions we have high numbers multiplied by other high numbers, giving a very high result.
This analysis suggest that the project is relatively risky, hence that the base case NPV should be recalculated using a
higher WACC. At a WACC of 15% (versus 12% for an average risk project), the base case NPV is:$2,877
That number is not very high in relation to the project's cost.
Changing the WACC would also change the scenario analysis. Here are new figures:
ProbabilityNPVDev. Sqd^2
Worst Case25%($38,102)992811800
Base Case50%$2,877242855067
Best Case25%$132,0112867361381
Expected NPV:$24,916
Standard Deviation:$64,055
Coefficient of Variation:2.57
At this point, the project looks risky but acceptable. There is a good chance that it will produce an NPV of $2,877, but
there is also a chance that the NPV could be dramatically higher or lower.
If the bad conditions occur, this will hurt but not bankrupt the firm--this is just one project for a large company.
We indicate at the start that this project's returns would be highly correlated with the firm's other projects'
returns and also with the general stock market. Thus, its stand-alone risk (which is what we have been analyzing)
also reflects its within-firm and market risk. If this were not true, then we would need to make further risk
adjustments.
Finally, recall that we stated at the start that if the firm undertakes the project, it will be committed to operate it for the
full 4-year life. That is important, because if it were not so committed, then if the bad conditions occurred during the
first year of operations, the firm could simply close down operations. This would cut its losses, and the worse case
scenario would not be nearly as bad as we indicated. Then, the expected NPV would be higher, and the standard
deviation and coefficient of variation would be lower. We extend the model to deal with abandonment in the next section,
which is designed to be examined after reading Chapter 13.
EXTENDING THE MODEL TO ALLOW FOR ABANDONMENT
This section is an "extension" that deals with a "real option," the option to abandon the project. This topic is covered in
Chapter 13, so it will be more clear after you have read that chapter.
abandoned, then the company can sell the related assets at the end of the year after abandonment and realize a net cash
flow equal to the asset's book value at the end of the abandonment year. Use a decision tree format to set up the scenarios
with and without the abandonment option. Thus, if it has bad results in Year 1, it can abandon the project and receive the
end-of-Year 1 book value as a cash flow at the end of Year 2.
Situation 1. Decision Tree Where Firm Cannot Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697
25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995
Expected NPV = sum, prob times NPV$29,010
Standard Deviation = Sq Root of column K sum$68,735
Coefficient of Variation = Std Dev / Expected NPV2.37
Situation 2. Decision Tree Where Firm Has the Option to Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553
25%0%-$7,356-$6,660$10,5010%-$38,315$00
-$9,390
25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
Expected NPV = sum, prob times NPV$36,390,006
Standard Deviation = Sq Root of column K sum$62,940,247
Coefficient of Variation (CV) = Std Dev / Expected NPV1.73
Where the abandonment option exists, the firm would definitely abandon the project at the end of Year 1 if the worse case
scenario developed. Therefore, we show a zero probability of continuing to operate in that case, and a 25% probability
that case will develop and the project will be abandoned.
Under abandonment, the worse case scenario still results in a negative NPV, but it is less negative than if abandonment
were not possible. This raises the expected NPV and lowers the standard deviation, and both of these changes work to
lower the CV. Thus, the project is seen to be more profitable and to have a lower risk if it can be abandoned.
Note that we discounted cash flows at 12% in the decision tree analysis. However, if 12% were appropriate given the
risk as measured by the CV in the no-abandonment case, then a lower WACC would be appropriate if the abandonment
option exists. Thus, it would make sense to go through another iteration and find the project's with-abandonment NPV
using a somewhat lower WACC. No precise procedure is available for determining the risk-adjusted discount rate, so
the adjustment would have to be made on a judgmental basis.
Note also that this type of analysis can be use to estimate the value of the option to abandon--its value is the difference
between the NPVs with and without abandonment. In this case, with our figures, it is
Expected NPV with Abandonment$36,390,006
Expected NPV without Abandonment$29,010
Approximate Value of Option$36,360,996
Thus, if abandonment were not possible due to an agreement with a labor union, BQC could afford to offer up to $4.619
million to get out of that constraint.
Finally, note that other types of real options, including timing options, expansion options, production flexibility options,
and the like can be analyzed using the decision tree format. Decision trees provide a powerful tool for investment
decision analysis.
Sheet1
-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866
-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544
5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066
21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519
37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813
Sales price
Variable cost
Growth rate
Units sold
Fixed cost
WACC
NPV
Sheet2
Sheet3
-
INVESTMENT OUTLAY
Sheet1
12model2/25/03 23:175/8/00
Chapter 12. Spreadsheet Models for Capital Budgeting
This worksheet contains the model used to analyze BQC's new project decision as described in the text.
In addition, models for analyzing replacement decisions and bond refunding decisions are
provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"
and "Bond Refunding" at the bottom of the screen.
Model for Evaluating A New Capital Budgeting Project:
The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs
used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.
Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the
building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of
the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,
and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs
result in changes in the key outputs.
Note that all dollars are shown in thousands; this is done for convenience.
Identifying the relevant cash flows
For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,
operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from
the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS
Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the
determination of net salvage values to be incorporated into our cash flow estimation.
Table 12-1. Analysis of a New (Expansion) Project
Part 1. Input Data (in thousands of dollars)Data used in Scenario Analysis
Key Output: NPV =51,661,753Sale PriceUnitsVCGrowth
Building cost (= Depreciable basis)120,000,000
Equipment cost (= Depreciable basis)80,000,000Market value of building in 200575,000,000Good$3.9026,000$1.4730%
Net Operating WC60,000,000Market value of equip. in 200520,000,000Base$3.0020,000$2.100%
First year sales (in units)2,000,000Tax rate40%Bad$2.1014,000$2.73-30%
Growth rate in units sold0.0%WACC12%
Sales price per unit300Inflation: growth in sales price0.0%Change the inputs to the above to get the NPVs
Variable cost per unit210Inflation: growth in VC per unit0.0%for the scenarios below and the cash flows for the
Fixed costs80,000,000Inflation: growth in fixed costs0.0%decision tree. Change to the indicated variables,
then use copy>Paste Special>Values to
YearsCumulativerecord the results below, and then make the next
1234Depr'nset of changes. When finished, return to base
Building Depr'n Rate1.3%2.6%2.6%2.6%case variable levels.
Building Depr'n1,560,0003,120,0003,120,0003,120,00010,920,000
Ending Book Val: Cost - Cum. Depr'n118,440,000115,320,000112,200,000109,080,000
Equipment Depr'n Rate20.0%32.0%19.0%12.0%
Equipment Depr'n16,000,00025,600,00015,200,0009,600,00066,400,000
Ending Book Val: Cost - Cum. Depr'n64,000,00038,400,00023,200,00013,600,000
to determine the depreciation expense for the year. See Appendix 12A for a review of MACRS depreciation rates.
Part 3 of Table 12-1. Net Salvage Values in 2005
BuildingEquipmentTotal
Estimated Market Value in 200575,000,00020,000,000
109,080,00013,600,000
-34,080,0006,400,000
Taxes paid or tax credit-13,632,0002,560,000
88,632,00017,440,000106,072,000
building, accumulated depreciation equals $1,092, so book value equals $12,000 - $1,092 = $10,908. For the equipment,
accumulated depreciation equals $6,640, so book value equals $8,000 - $6,640 = $1,360.
taken versus "true" depreciation, and it is treated as an operating expense for 2005. Equipment: $2,000 market value -
$1,360 book value = $640 profit. Here the depreciation charge exceeds the "true" depreciation, and the difference is called
"depreciation recapture". It is taxed as ordinary income in 2005.
net salvage value = $7,500 - (-$1,363) = $8,863.
Years
01234
20032004200520062007
Investment Outlays at Time Zero
Building-120,000,000
Equipment-80,000,000
Increase in NOWC-60,000,000
Operating Cash Flows over the Project's Life
Units sold2,000,0002,000,0002,000,0002,000,000
Sales price300300300300
Sales revenue600,000,000600,000,000600,000,000600,000,000
Variable costs420,000,000420,000,000420,000,000420,000,000
Fixed operating costs80,000,00080,000,00080,000,00080,000,000
Depreciation (building)1,560,0003,120,0003,120,0003,120,000
Depreciation (equipment)16,000,00025,600,00015,200,0009,600,000
Oper. income before taxes (EBIT)82,440,00071,280,00081,680,00087,280,000
Taxes on operating income (40%)32,976,00028,512,00032,672,00034,912,000
Net Operating Profit After Taxes (NOPAT)49,464,00042,768,00049,008,00052,368,000
Add back depreciation17,560,00028,720,00018,320,00012,720,000
Operating cash flow67,024,00071,488,00067,328,00065,088,000
Terminal Year Cash Flows
60,000,000
Net salvage value106,072,000
Total termination cash flows166,072,000
Net Cash Flow (Time line of cash flows)-260,000,00067,024,00071,488,00067,328,000231,160,000
sold off and receivables are collected.
Part 5 of Table 12-1. Key Output and Appraisal of the Proposed Project
Net Present Value (at 12%)51,661,753
IRR19.33%
MIRR17.19%Years
01234
Cumulative cash flow for payback(260,000,000)(192,976,000)(121,488,000)(54,160,000)177,000,000
Cum. CF > 0, hence Payback Year:0.000.000.000.003.23
Payback found with Excel function =3.23See note below for an explanation of the Excel calculation.
Check: Payback = 3 + 5,416/23,116 =3.23Manual calculation for the base case.
The Excel payback calculation is based on the logical IF function. Returns FALSE if the cumulative CF is negative or
the actual payback if the cumulative CF is positive. Then, we use the MIN (minimum) function to find first year when
payback is positive. The min function procedure is necessary for projects with longer lives, because then values, not the
word FALSE, would appear in a number of cells. The min function picks the smallest number, which is the payback.
Based on the firm's 12% weighted average cost of capital, this project has a NPV of $5,166. Since the NPV is positive,
we tentatively conclude that the project should be accepted. The IRR and MIRR confirm this decision because both
exceed the cost of capital. Note, though, that no risk analysis has been conducted. It is possible that the firm's
managers, after appraising the project's risk, might conclude that its projected return is insufficient to compensate
for its risk, and reject it.
Part 6. Evaluating Risk: Sensitivity Analysis
Risk in capital budgeting really means the probability that the actual outcome will be worse than the expected outcome.
For example, if there were a high probability that the $5,166 expected NPV as calculated above will actually turn
out to be negative, then the project would be classified as relatively risky. The reason for a worse-than-expected
outcome is, typically, because sales were lower than expected, costs were higher than expected, or the project turned
out to have a higher than expected initial cost. In other words, if the assumed inputs turn out to be worse than expected,
then the output will likewise be worse than expected. In Part 6 we use Excel to examine the project's sensitivity to
changes in the input variables.
I. Sensitivity of NPV and to Variations in Unit Sales.
Here we use an Excel "Data Table" to find NPV different unit sales, holding other thing constant.
% DeviationWACC% Deviation1st YEAR UNIT SALES
fromNPVfromUnitsNPVGoal Seek finds the value of some input variable that
Base CaseWACC51,661,753Base CaseSold$51,661,7531st Year Sales wouldcaused an output variable to hit a specified level. In
-30%8.4%$8,294-30%14,000-$4,675the project break eventhis case, we want to know the 1st year unit sales that
-15%10.2%$6,674-15%17,000$246in the sense that NPVwill cause NPV = 0, holding other things constant.
0%12.0%$5,166Base Case0%20,000$5,166= $0?
15%13.8%$3,76115%23,000$10,087Click Tools, then Goal Seek, to get the following dialog
30%15.6%$2,45030%26,000$15,007box, which we completed:
NPV against Sales and
see about where NPV
% DeviationVARIABLE COSTS% DeviationGROWTH RATE, UNITS= 0. Alternatively, you
fromVariableNPVfromGrowthNPVcould use Tools > Goal
Base CaseCost$51,661,753Base CaseRate %$51,661,753Seek as described in
-30%$1.47$28,129-30%-30%-$5,847the columns to the right.
-15%$1.79$16,647-15%-15%-$907The answer is 16,850
0%$2.10$5,166Base Case0%0%$5,166units.
15%$2.42-$6,31515%15%$12,512
30%$2.73-$17,79630%30%$21,269
We want to get NPV = 0, so we specify the "Set cell" as
being G148 (Excel adds the dollar signs), and the "To
% DeviationSALES PRICE% DeviationFIXED COSTSvalue" to be 0. We specify the cell to change as F148,
fromSalesNPVfromFixedNPVand when we click OK, G148 changes to $0 and F148
Base CasePrice$51,661,753Base CaseCosts$51,661,753goes to 16,850.
-30%$2.10-$27,637-30%$5,600$9,540
-15%$2.55-$11,236-15%$6,800$7,353The Goal Seek feature is easy to use, and it often
0%$3.00$5,166Base Case0%$8,000$5,166comes in handy.
15%$3.45$21,56815%$9,200$2,979
30%$3.90$37,97030%$10,400$792
We summarize the data tables, arranged by sensitivity, and graphed the most sensitive items in the following chart:
Figure 12-1. Evaluating Risk: Sensitivity Analysis (Dollars in Thousands)
DeviationNPV at Different Deviations from Base
fromSalesVariableGrowthYear 1Fixed
Base CasePriceCost/UnitRateUnits SoldCostWACC
-30%($27,637)$28,129($5,847)($4,675)$9,540$8,294
-15%($11,236)$16,647($907)$246$7,353$6,674
0%$5,166$5,166$5,166$5,166$5,166$5,166
15%$21,568($6,315)$12,512$10,087$2,979$3,761
30%$37,970($17,796)$21,269$15,007$792$2,450
Range65,60745,92527,11619,6828,7485,844
We see from the tables and graph that NPV is most sensitive to changes in the sales price and variable
costs, somewhat sensitive to changes in first-year sales and the sales growth rate, and not very sensitive to
changes in WACC and fixed costs. Thus, the real issue is our confidence in the forecasts of the sales
price and variable costs, as well as the first-year sales and the growth rate in units sold.
NPV can change dramatically if the key input variables change, but we do not know how much the
variables are likely to change. For example, if we were buying components under a fixed price contract,
then variable costs might be locked in and not likely to rise more than say 5%, and we might have a firm
contract to sell the projected number of units at the indicated price per unit. In that case, the "bad
conditions" would not materialize, and a positive NPV would be pretty well guaranteed. We go on to look at
the probabilities of different conditions in Part 7.
Part 7. Evaluating Risk: Scenario Analysis
Scenario analysis extends risk analysis in two ways: (1) It allows us to change more than one variable at a time, hence
to see the combined effects of changes in several variables on NPV, and (2) It allows us to bring in the probabilities of
changes in the key variables. Part 7 provides a scenario analysis of the computer project.
We saw from the sensitivity analysis that the key variables are sales price, variable costs, unit sales, and the unit
growth rate. Therefore, in our sensitivity analysis we hold the other variables at their base case levels and then
examine the situation when the key variables change. We assume that the company regards the worst case as one
where each of the three variables is 30% worse than the base level, and the best case has each variable 30% better
than base. We also assume that there is a 25% chance of the best and worst cases, and a 50% chance of base case
levels.
Table 12-2. Scenario Analysis (Dollars in Thousands)Squared
Deviation
SalesUnitVariableGrowthTimes
ScenarioProbabilityPriceSalesCostsRateNPVProbability
Best Case25%$3.9026,000$1.4730%$144,0243307034782Manually changed the Part 1 inputs, then put the
Base Case50%$3.0020,000$2.100%$5,166284276572NPV as calculated with the modified variables here.
Worst Case25%$2.1014,000$2.73-30%($38,315)1133161977Took deviations from the expected values, squared
4724473330them, and multiplied by the probabilities to get the
Expected NPV = sum, prob times NPV$29,010numbers shown.
Standard Deviation = Sq Root of column I sum$68,735Summed the squared deviations and took sq root
Coefficient of Variation = Std Dev / Expected NPV2.37Std Dev divided by Expected NPV
a. Probability Graph
Probability
50%
25%
(38,315)029,010144,024
5,166NPV ($)
Most LikelyMean of distribution
b. Continuous Approximation
Probability Density
(38,315)029,010144,024
NPV ($)
5,166
The scenario analysis suggests that the project could be highly profitable, but also that it is quite risky. There is a
25% probability that the project would result in a loss of $38.3 million. There is also a 25% probability that it could
produce an NPV of $144 million. The standard deviation is high, at $68.7 million, and the coefficient of variation is a
high 2.37.
Note that the expected NPV in the scenario analysis is much higher than the base case value. This occurs because
under good conditions we have high numbers multiplied by other high numbers, giving a very high result.
This analysis suggest that the project is relatively risky, hence that the base case NPV should be recalculated using a
higher WACC. At a WACC of 15% (versus 12% for an average risk project), the base case NPV is:$2,877
That number is not very high in relation to the project's cost.
Changing the WACC would also change the scenario analysis. Here are new figures:
ProbabilityNPVDev. Sqd^2
Worst Case25%($38,102)992811800
Base Case50%$2,877242855067
Best Case25%$132,0112867361381
Expected NPV:$24,916
Standard Deviation:$64,055
Coefficient of Variation:2.57
At this point, the project looks risky but acceptable. There is a good chance that it will produce an NPV of $2,877, but
there is also a chance that the NPV could be dramatically higher or lower.
If the bad conditions occur, this will hurt but not bankrupt the firm--this is just one project for a large company.
We indicate at the start that this project's returns would be highly correlated with the firm's other projects'
returns and also with the general stock market. Thus, its stand-alone risk (which is what we have been analyzing)
also reflects its within-firm and market risk. If this were not true, then we would need to make further risk
adjustments.
Finally, recall that we stated at the start that if the firm undertakes the project, it will be committed to operate it for the
full 4-year life. That is important, because if it were not so committed, then if the bad conditions occurred during the
first year of operations, the firm could simply close down operations. This would cut its losses, and the worse case
scenario would not be nearly as bad as we indicated. Then, the expected NPV would be higher, and the standard
deviation and coefficient of variation would be lower. We extend the model to deal with abandonment in the next section,
which is designed to be examined after reading Chapter 13.
EXTENDING THE MODEL TO ALLOW FOR ABANDONMENT
This section is an "extension" that deals with a "real option," the option to abandon the project. This topic is covered in
Chapter 13, so it will be more clear after you have read that chapter.
abandoned, then the company can sell the related assets at the end of the year after abandonment and realize a net cash
flow equal to the asset's book value at the end of the abandonment year. Use a decision tree format to set up the scenarios
with and without the abandonment option. Thus, if it has bad results in Year 1, it can abandon the project and receive the
end-of-Year 1 book value as a cash flow at the end of Year 2.
Situation 1. Decision Tree Where Firm Cannot Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,0063,307,009,592
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583284,271,697
25%-$9,390-$7,356-$6,660$10,50125%-$38,315-$9,5791,133,160,995
Expected NPV = sum, prob times NPV$29,010
Standard Deviation = Sq Root of column K sum$68,735
Coefficient of Variation = Std Dev / Expected NPV2.37
Situation 2. Decision Tree Where Firm Has the Option to Abandon
End of Period:NPV thisProb.Data for
01234ProbabilityScenariox NPVStd Deviation
25%$33,810$45,629$59,997$95,60025%$144,024$36,006328,442,808,227,823
50%-$26,000$6,702$7,149$6,733$23,11650%$5,166$2,583661,928,278,462,553
25%0%-$7,356-$6,660$10,5010%-$38,315$00
-$9,390
25%$182,440,000$0$025%$145,405,66736,351,4172,971,103,619,388,260
Expected NPV = sum, prob times NPV$36,390,006
Standard Deviation = Sq Root of column K sum$62,940,247
Coefficient of Variation (CV) = Std Dev / Expected NPV1.73
Where the abandonment option exists, the firm would definitely abandon the project at the end of Year 1 if the worse case
scenario developed. Therefore, we show a zero probability of continuing to operate in that case, and a 25% probability
that case will develop and the project will be abandoned.
Under abandonment, the worse case scenario still results in a negative NPV, but it is less negative than if abandonment
were not possible. This raises the expected NPV and lowers the standard deviation, and both of these changes work to
lower the CV. Thus, the project is seen to be more profitable and to have a lower risk if it can be abandoned.
Note that we discounted cash flows at 12% in the decision tree analysis. However, if 12% were appropriate given the
risk as measured by the CV in the no-abandonment case, then a lower WACC would be appropriate if the abandonment
option exists. Thus, it would make sense to go through another iteration and find the project's with-abandonment NPV
using a somewhat lower WACC. No precise procedure is available for determining the risk-adjusted discount rate, so
the adjustment would have to be made on a judgmental basis.
Note also that this type of analysis can be use to estimate the value of the option to abandon--its value is the difference
between the NPVs with and without abandonment. In this case, with our figures, it is
Expected NPV with Abandonment$36,390,006
Expected NPV without Abandonment$29,010
Approximate Value of Option$36,360,996
Thus, if abandonment were not possible due to an agreement with a labor union, BQC could afford to offer up to $4.619
million to get out of that constraint.
Finally, note that other types of real options, including timing options, expansion options, production flexibility options,
and the like can be analyzed using the decision tree format. Decision trees provide a powerful tool for investment
decision analysis.
Sheet1
-27637.197684558528128.5363195023-5846.6005863442-4674.83662406299539.95831814878294.132048866
-11235.511212775916647.3557892545-906.974041171245.66931747197353.06678857776673.9003977544
5166.17525900665166.17525900665166.17525900665166.17525900665166.17525900665166.1752590066
21567.8617307892-6315.005271241212511.835117935910086.68120054142979.28372943563761.1990339519
37969.5482025718-17796.18580148921268.993339363815007.1871420762792.39219986462450.2106179813
Sales price
Variable cost
Growth rate
Units sold
Fixed cost
WACC
NPV
Sheet2
Sheet3
-
NET OPERATING CASH FLOW
Sheet1
12model2/25/03 23:205/8/00
Chapter 12. Spreadsheet Models for Capital Budgeting
This worksheet contains the model used to analyze BQC's new project decision as described in the text.
In addition, models for analyzing replacement decisions and bond refunding decisions are
provided on separate sheets that can be accessed by pressing the TAB keys labeled "Replacement Analysis"
and "Bond Refunding" at the bottom of the screen.
Model for Evaluating A New Capital Budgeting Project:
The first section of this worksheet contains a model for evaluating new projects. In Part 1, we first list the key inputs
used in the calculations. Part 2 goes on to calculate depreciation schedules for the building and for the equipment.
Part 3 then determines the after-tax salvage values (i.e., net cash flows) that will come from disposing of the
building and the equipment at the end of the project's life. Part 4 calculates the estimated cash flows over each year of
the project's life. Part 5 then uses the estimated cash flows to estimate the key outputs, the project's NPV, IRR, MIRR,
and Payback. Finally, in Parts 6 and 7, we consider the riskiness of the project by showing how changes in the inputs
result in changes in the key outputs.
Note that all dollars are shown in thousands; this is done for convenience.
Identifying the relevant cash flows
For a new project, the incremental cash flows can be divided into the following categories: initial investment outlay,
operating cash flows over the project's life, and terminal year cash flows. The data used in the model were taken from
the example in Chapter 12 of Fundamentals. In addition to the input data, we have included an excerpt from the MACRS
Depreciation Schedule for 39-year (building) and 5-year (equipment) depreciation, and a table outlining the
determination of net salvage values to be incorporated into our cash
top related