Financial Management Excel

profilerobin65
CashFlow.xlsx
Home>Business & Finance homework help>Management homework help>Financial Management Excel

Sheet1

Inputs
Equipment & shipping & installation costs $710,000,000
The salvage value of the fixed assets (7.5%) $53,250,000
Units sold, Year 1 2,500,000
Annual change in units sold, after Year 1 8.50%
Sales price per unit, Year 1 $260.00
Annual change in sales price, after Year 1 2.50%
Variable cost per unit (VC), Year 1 $205.00
Annual change in VC, after Year 1 2.50%
Nonvariable cost (Non-VC), Year 1 $0
Annual change in Non-VC, after Year 1 0.00%
Project WACC 12.25%
Tax rate 26.70%
Working capital as % of next year's sales 10.00%
Accelerated Depreciation
Depreciable basis: $710,000,000 Rate/year 10% 18% 14% 12% 9% 7% 7% 7% 7% 7% 3%
Dollars/year $71,000,000 $127,800,000 $102,240,000 $81,792,000 $65,462,000 $52,327,000 $46,505,000 $46,505,000 $46,576,000 $46,505,000 $23,288,000
Cash Flows
Variables Used in the Cash Flow Forecast 0 1 2 3 4 5 6 7 8 9 10 11
Unit sales 2,500,000 2,712,500 2,943,063 3,193,223 3,464,647 3,759,142 4,078,669 4,425,356 4,801,511 5,209,639 5,652,459
Sales price per unit $260 $267 $273 $280 $287 $294 $302 $309 $317 $325 $333
Variable cost per unit $205 $210 $215 $221 $226 $232 $238 $244 $250 $256 $262
Cash Flows At End of Year
Investment Outlays at Time = 0 0 1 2 3 4 5 6 7 8 9 10 11
Equipment -$710,000,000
Initial investment in working capital -65,000,000
Net Cash Flows Over the Project's Life
Sales revenues = Units × Price/unit $650,000,000 $722,881,250 $803,934,310 $894,075,445 $994,323,654 $1,105,812,194 $1,229,801,386 $1,367,692,866 $1,521,045,429 $1,691,592,648 $1,881,262,473
Variable costs = Units × Cost/unit 512,500,000 569,964,063 633,871,283 704,944,101 783,985,958 871,890,383 969,651,093 1,078,373,221 1,199,285,819 1,333,755,741 1,483,303,104
Depreciation: Accelerated, from table above 71,000,000 127,800,000 102,240,000 81,792,000 65,462,000 52,327,000 46,505,000 46,505,000 46,576,000 46,505,000 23,288,000
Operating profit (EBIT) $66,500,000 $25,117,188 $67,823,027 $107,339,344 $144,875,696 $181,594,810 $213,645,293 $242,814,645 $275,183,610 $311,331,906 $374,671,369
Taxes on operating profit 17,755,500 6,706,289 18,108,748 28,659,605 38,681,811 48,485,814 57,043,293 64,831,510 73,474,024 83,125,619 100,037,256
Net operating profit after taxes $48,744,500 $18,410,898 $49,714,279 $78,679,739 $106,193,885 $133,108,996 $156,602,000 $177,983,135 $201,709,586 $228,206,287 $274,634,114
Add back depreciation 71,000,000 127,800,000 102,240,000 81,792,000 65,462,000 52,327,000 46,505,000 46,505,000 46,576,000 46,505,000 23,288,000
Salvage value (taxed as ordinary income) 53,250,000
Tax on salvage value -14,217,750
Change in WC: Outflow (–) or recovery (+) -7,288,125 -8,105,306 -9,014,113 -10,024,821 -11,148,854 -12,398,919 -13,789,148 -15,335,256 -17,054,722 -18,966,983 188,126,247
Project net cash flows: Time Line -$775,000,000 $112,456,375 $138,105,592 $142,940,165 $150,446,918 $160,507,031 $173,037,077 $189,317,852 $209,152,878 $231,230,864 $255,744,305 $525,080,611

2. Sens.

ERROR:#REF!
Section 11.5 Sensitivity Analysis
Tab 2 extends the basic model (shown in Tab 1) to include sensitivity analysis. Tab 2 also illustrates special cases of sensitivity analysis, incuding breakeven analysis, one-way data tables with multiple outputs, and two-way data tables. We also include a brief tutorial for Data Tables.
For ease of reference, we repeat Figure 11-1, Analysis of an Expansion Project: Inputs and Key Results (Dollars in Thousands)
Part 1. Inputs and Key Results
Inputs Base-Case Key Results
Equipment cost $3,400 NPV $36
Salvage value, equipment, Year 4 $300 IRR 10.35%
Opportunity cost $0 MIRR 10.23%
Externalities (cannibalization) $0 PI 1.01
Units sold, Year 1 550 Payback 3.41
Annual change in units sold, after Year 1 4.00% Discounted payback 3.98
Sales price per unit, Year 1 $11.60
Annual change in sales price, after Year 1 2.00%
Variable cost per unit (VC), Year 1 $6.00
Annual change in VC, after Year 1 2.00%
Nonvariable cost (Non-VC), Year 1 $2,000
Annual change in Non-VC, after Year 1 2.00%
Project WACC 10.00%
Tax rate 40.00%
Working capital as % of next year's sales 12.65%
If you change any of the blue values above, the model below will change instantly, causing changes in NPV and other output variables. You can see the effect in the Key Results box shown above. If you change an input value but later want to return to the base case, use Scenario Manager to select the Base-Case. In Excel 2003, select Tools, Scenarios. In Excel 2007, select Data, What-If-Analysis, Scenario Manager.
For ease of reference, we repeat Figure 11-2. Analysis of a New (Expansion) Project: Cash Flows and Performance Measures (Dollars in Thousands)
Part 2. Cash Flows and Performance Measures
Variables Used in the Cash Flow Forecast 0 1 2 3 4
Unit sales 550 572 595 619
Sales price per unit $11.60 $11.83 $12.07 $12.31
Variable cost per unit $6.00 $6.12 $6.24 $6.37
Nonvariable costs (excluding depreciation) $2,000 $2,040 $2,081 $2,122
Cash Flows At End of Year
Investment Outlays at Time = 0 0 1 2 3 4
Equipment -$3,400
Initial investment in working capital -807
Opportunity cost, after taxes 0
Net Cash Flows Over the Project's Life
Sales revenues = Units × Price/unit $6,380 $6,768 $7,179 $7,616
Variable costs = Units × Cost/unit 3,300 3,501 3,713 3,939
Nonvariable costs (excluding depreciation) 2,000 2,040 2,081 2,122
Depreciation: Accelerated, from table below 1,122 1,530 510 238
Operating profit (EBIT) -$42 -$303 $875 $1,316
Taxes on operating profit -17 -121 350 526
Net operating profit after taxes -$25 -$182 $525 $790
Add back depreciation 1,122 1,530 510 238
Opportunity cost, after taxes 0 0 0 0
Cannibalization or complementary effects, after taxes 0 0 0 0
Salvage value (taxed as ordinary income) 300
Tax on salvage value (SV is taxed at 40%) -120
Change in WC: Outflow (–) or recovery (+) -49 -52 -55 963
Project net cash flows: Time Line -$4,207 $1,048 $1,296 $980 $2,171
Project Evaluation
Results
NPV $36
IRR 10.35%
MIRR 10.23%
Profitability index 1.01
Payback 3.41
Discounted payback 3.98
Calculations for Payback Year: 0 1 2 3 4
Cumulative cash flows for payback -$4,207 -$3,159 -$1,863 -$883 $1,288
Discounted cash flows for disc. payback -$4,207 $952 $1,071 $736 $1,483
Cumulative discounted cash flows -$4,207 -$3,255 -$2,183 -$1,447 $36
Accelerated Depreciation
Depreciable basis: $3,400 Rate/year 33% 45% 15% 7%
Dollars/year $1,122 $1,530 $510 $238
SENSITIVITY ANALYSIS (Section 11.5)
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 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. We use data tables below to examine the project's sensitivity to changes in the input variables.
Following is a tutorial for constructing a Data Table to be used in sensitivity analysis. This section may be skipped if you already know how to construct data tables.
Instructions for Constructing Data Tables:
Step 1:
Deviation Sales NPV Set up the Data Table by typing in the labels and numbers shown here. The column for sales price/unit is the input range and the column for NPV is the output range. Data Tables take each input value and then automatically calculate a new output based on the input. Be sure to type in the actual sales price of $11.60 and not a formula. Every year we have students who make this mistake! Don't be one of them!
from Base Price/unit
-30%
0% $11.60
30%
Step 2:
Deviation Sales NPV Enter the formula =$B$117*(1+A116) into the light green cell and then copy it into the light blue cell. This sets up the input range's values of sales prices for which you want new NPV's to be calculated. It is ok to have a formula in the input range, but be sure that none of these inputs is a formula that refers back to the actual value of sales in the input section of the worksheet.
from Base Price/unit
-30% $8.12
0% $11.60
30% $15.08
Step 3:
Deviation Sales NPV Enter into the tan cell a formula that refers to the cell in the results section which shows the NPV for the given set of inputs. In this example, that is =$I$15. Notice that the tan cell will show the current value of NPV.
from Base Price/unit $35.84
-30% $8.12
0% $11.60
30% $15.08
Deviation Sales NPV Now use your cursor to hightlight the range we show in gray (this is called the Data Table range); notice that this highlighted range includes the cells for the new inputs for price and the cell for the reference to NPV.
from Base Price/unit $35.84
-30% $8.12
0% $11.60
30% $15.08
With the range still highlighted, open the Table dialog box. In Excel 2003, you go the Main Menu, select Data, then Table. In Excel 2007, select Data, What-If-Analysis, then Data Table.
This next step is a bit tricky, so be careful. The cursor in the dialog box will be blinking in the "Row input cell:" box. Here you have to tell Excel if the inputs in your Data Table are arranged in a row or a column. Excel assumes a row, but this is not correct in our example--your inputs are in a column, Column B. So, you click on the "Column input cell" box, causing the cursor to blink in that box.
Excel wants to know where the input variable, sales price, first enters the model. If you look up in the Input Data section, you will see that it enters in cell E21, so you type E21 in the Column input cell (or click on cell E21 to enter it). Here's the final, completed, dialog box:
When you click OK, Excel will calculate NPV at the three input values specified in your Data Table, insert them in the table, leaving the Data Table as shown below.
Deviation Price NPV
from Base $35.84
-30% $8.12 -$3,838.74
0% $11.60 $35.84
30% $15.08 $3,910.42
We used Data Tables to create inputs for the sensitivity graph. (First, be sure the Base-Case scenario is showing.) Note that the portion of the rows that are in the Data Tables are shown in shaded colors.
Deviation
Mike Ehrhardt: Change the cells below to get different deviations. All other inputs will be updated automatically.		 Equipment NPV Deviation Unit Sales NPV Deviation Sales Price/unit NPV
from Base $36 from Base $36 from Base $36
-30% $2,380 $716 -30% 385 -$1,791 -30% $8.12 -$3,839
0% 3,400 36 0% 550 36 0% 11.60 36
30% 4,420 -645 30% 715 1,863 30% 15.08 3,910
Deviation VC/Unit NPV Deviation Non-VC NPV Deviation Project WACC NPV
from Base $36 from Base $36 from Base $36
-30% $4.20 $2,083 -30% $1,400 $1,209 -30% 7.00% $361
0% 6.00 36 0% 2,000 36 0% 10.00% 36
30% 7.80 -2,011 30% 2,600 -1,137 30% 13.00% -254
The following graph is meaningful only if the scenario is set to the Base-Case.
Figure 11-3. Sensitivity Graph for Solar Water Heater Project (Dollars in Thousands)
Data for Sensitivity Graph
Deviation NPV with Variables at Different Deviations from Base
from Base Equipment Price Units VC/Unit Non-VC WACC
-30% $716 -$3,839 -$1,791 $2,083 $1,209 $361
0% $36 $36 $36 $36 $36 $36
30% -$645 $3,910 $1,863 -$2,011 -$1,137 -$254
Range $1,361 $7,749 $3,655 $4,095 $2,346 $615
Tornado Diagrams
Tornado diagrams are another way to present results from sensitivity analysis. The first step is to rank the range of possible NPV's for each of the input variables that is being changed. In our example, the range for sales price/unit is the largest and the range for WACC is the smallest. The ranges for each variable are then plotted, with the largest range on top and the smallest range on the bottom. It is helpful to also plot a vertical line showing the base-case NPV. We present a tornado diagram in Figure 11-4. Notice that the diagram is like a tornado in that it is widest at the top and smallest at the bottom, hence its name. The tornado diagram makes it immediately obvious which inputs have the biggest impact on NPV.
Additional data for Tornado Diagram
Scratch for Tornado Diagram Below
Rank of Range of NPV from Sensitivity Table Above
Equipment Price Units VC/Unit Non-VC WACC Base NPV = Y-axis
Rank 2 6 4 5 3 1 $36 8
For diagram below 2 6 4 5 3 1 36 6
2 6 4 5 3 1 36 1
Figure 11-4. Tornado Diagram for Solar Water Heater Project: Range of Outcomes for Input Deviations from Base-Case (Dollars in Thousands)
NPV Breakeven Analysis
In breakeven analysis, we find the value of the input variable that produes a zero NPV. It is easiest to do this with Goal Seek. For example, the screen shot below shows the Goal Seek inputs we used to set the cell for NPV to a value of zero by changing the cell for the sales price. We repeated this for the other inputs.
Table 11-1. NPV Breakeven Analysis (Dollars in Thousands)
Input Input Value that Produces Zero NPV
Sales price per unit, Year 1 $11.57
Variable cost per unit (VC), Year 1 $6.03
Annual change in units sold, after Year 1 3.58%
Units sold, Year 1 547
Nonvariable cost (Non-VC), Year 1 $2,018
Project WACC 10.35%
Data Tables: Multiple Outputs for a Single Input
Data tables can easily be extended to show multiple outputs for a single input. Simply add an additional column with a cell reference to the desired additional output. Highlight the specified values for the input and highlight all the columns for the output as we show shaded in gray below (be sure to also highlight the cell references above the outputs). Then use the Data, Tables, and set "Column input" to the cell refernce of the desired input.
Example: NPV and IRR for Changes in Sales Price
% Deviation SALES PRICE
from Sales NPV IRR
Base Case Price $36 10.4%
-30% $8.12 -$3,839 -45.0%
-15% $9.86 -$1,901 -11.3%
0% $11.60 $36 10.4%
15% $13.34 $1,973 27.8%
30% $15.08 $3,910 42.8%
Two-Way Data Tables: Two Inputs and One Output
Data tables can also be extended to show the output given two inputs. Put one set of input variables in the left-most column of the data table (shown in a red font below) and the other set of inputs in the top row of the data table (shown in white font); put the cell reference to the output you want (like NPV) in the intersection of the row and column for inputs (we show this in a pale green font). Highlight the range that includes the specified values for the inputs, as shown in the gray shaded region below (this will also highlight the cell reference for the output). Then use the Data, Tables, and set "Row input" to the cell reference for the inputs shown in the table's row E19 for units sold) and set "Column input" to the cell refernce for the input shown in the table's column E21 for sales price).
Example: NPV for Changes in Sales Price and Units Sold
% Deviation from Base Case
-30% -15% 0% 15% 30%
% Deviation from NPV cell reference Units Sold
Base Case $36 385 468 550 633 715
-30% Sales Price $8.12 -$4,504 -$4,171 -$3,839 -$3,506 -$3,174
-15% $9.86 -$3,148 -$2,525 -$1,901 -$1,278 -$655
0% $11.60 -$1,791 -$878 $36 $949 $1,863
15% $13.34 -$435 $769 $1,973 $3,177 $4,382
30% $15.08 $921 $2,416 $3,910 $5,405 $6,900
Price

-0.3 0 0.3 -3838.7448808082895 35.836539226936111 3910.4179592621649 Units

-0.3 0 0.3 -1791.4880445614981 35.836539226936111 1863.1611230153703 VC/Unit

-0.3 0 0.3 2083.0933754737289 35.83 6539226936111 -2011.4202970198558 Non-VC

-0.3 0 0.3 1208.9157551274893 35.836539226936111 -1137.2426766736166 Equipment

-0.3 0 0.3 716.21367193644983 35.836539226936111 -644.54059348257761 WACC

-0.3 0 0.3 360.566849929417 35.836539226936111 -253.98814890681342

% Deviation from Base

NPV ($)

Price

-3838.7448808082895 35.836539226936111 3910.4179592621649 6 6 6 Units

-1791.4880445614981 35.836539226936111 1863.1611230153703 4 4 4 VC/Unit

2083.0933754737289 35.836539226936111 -2011.4202970198558 5 5 5 Non-VC

1208.9157551274893 35.836539226 936111 -1137.2426766736166 3 3 3 Equipment

716.21367193644983 35.836539226936111 -644.54059348257761 2 2 2 WACC

360.566849929417 35.836539226936111 -253.98814890681342 1 1 1 Base NPV =

35.836539226936111 35.836539226936111 35.836539226936111 8 6 1

NPV

1

2

3

1

2

4

3

3

3. Scen.

ERROR:#REF!
Section 11.6 Scenario Analysis
Tab 3 extends the basic model (shown in Tab 1) to include scenario analysis. On this tab we modify the Tab 1 Basic Model in several ways (but note that only the accelerated depreciation case is analyzed here).
We add worst-case and best-case scenarios, including the probability that each scenario will occur, as shown below in Figure 11-5. Management determined that some of the inputs were not likely to stray far from the base-case levels, and the NPV was not terribly sensitive to them anyway, so in our analysis we change only 6 inputs: equipment cost, units sold in Year 1, annual change in units sold after Year 1, sales price per unit, variable cost per unit, nonvariable cost, and the tax rate. Management gathered advice from experts in their marketing, operations, logistics, HR, accounting, and finance departments for the probability of each scenario and the values to use for the worst-case and best-case scenarios.
We show these base-case, worst-case, and best-case value in the input columns for scenarios in Figure 11-5 below, identified by the cells with larger, non-black fonts. If you change any input for any scenario, the key results shown immediately below the input column will be updated, but our analysis focuses only on the 7 critical inputs determined by management.
If you change an input for the Base-Case scenario in Column E below, it will make changes in the analysis section for the Base-Case shown below Figure 11-5. If you make a change in the inputs for the Worst-Case scenario (in Column G below), it will make changes in the analysis section for the Worst-Case shown in yellow to the right and below Figure 11-5. Similarly, if you make make a change in the inputs for the Best-Case scenario (in Column I below), it will make changes in the analysis section for the Best-Case shown in green to the right and below Figure 11-5.
Analysis for Worst and Best Scenarios in yellow and green boxes below and to the right.
Figure 11-5. Inputs and Key Results for Each Scenario (Dollars in Thousands)
Don’t change any values in the yellow box below. If you want to change an input, do it in Column G to the left. Don’t change any values in the green box below. If you want to change an input, do it in Column I to the left.
Scenarios: Worst-Case Scenario Best-Case Scenario
Inputs: Base Worst Best Inputs Worst Inputs Best
Probability of Scenario 50% 25% 25%
Equipment cost $3,400 $4,250 $2,550 Equipment cost $4,250 Equipment cost $2,550
Salvage value, equipment, Year 4 $300 $300 $300 Salvage value, equipment, Year 4 $300 Salvage value, equipment, Year 4 $300
Opportunity cost $0 $0 $0 Opportunity cost $0 Opportunity cost $0
Externalities (cannibalization) $0 $0 $0 Externalities (cannibalization) $0 Externalities (cannibalization) $0
Units sold, Year 1 550 412 688 Units sold, Year 1 412 Units sold, Year 1 688
Annual change in units sold, after Year 1 4.00% -6.00% 14.00% Annual change in units sold, after Year 1 -6.00% Annual change in units sold, after Year 1 14.00%
Sales price per unit, Year 1 $11.60 $8.70 $14.50 Sales price per unit, Year 1 $8.70 Sales price per unit, Year 1 $14.50
Annual change in sales price, after Year 1 2.00% 2.00% 2.00% Annual change in sales price, after Year 1 2.00% Annual change in sales price, after Year 1 2.00%
Variable cost per unit (VC), Year 1 $6.00 $7.50 $4.50 Variable cost per unit (VC), Year 1 $7.50 Variable cost per unit (VC), Year 1 $4.50
Annual change in VC, after Year 1 2.00% 2.00% 2.00% Annual change in VC, after Year 1 2.00% Annual change in VC, after Year 1 2.00%
Nonvariable cost (Non-VC), Year 1 $2,000 $2,500 $1,500 Nonvariable cost (Non-VC), Year 1 $2,500 Nonvariable cost (Non-VC), Year 1 $1,500
Annual change in Non-VC, after Year 1 2.00% 2.00% 2.00% Annual change in Non-VC, after Year 1 2.00% Annual change in Non-VC, after Year 1 2.00%
Project WACC 10.00% 10.00% 10.00% Project WACC 10.00% Project WACC 10.00%
Tax rate 40.00% 50.00% 30.00% Tax rate 50.00% Tax rate 30.00%
Working capital as % of next year's sales 12.65% 12.65% 12.65% Working capital as % of next year's sales 12.65% Working capital as % of next year's sales 12.65%
Key Results: Base Worst Best Key Results: Worst Key Results: Best
NPV $36 -$5,847 $13,379 NPV -$5,847 NPV $13,379
IRR 10.35% Not found 112.01% IRR Not found IRR 112.01%
MIRR 10.23% -100.00% 60.30% MIRR -100.00% MIRR 60.30%
PI 1.01 -0.24 4.51 PI -0.24 PI 4.51
Payback 3.41 Not found 1.00 Payback Not found Payback 1.00
Discounted payback 3.98 Not found 1.09 Discounted payback Not found Discounted payback 1.09
Analysis for Base-Case Scenario shown in blue box below.
Base Worst-Case Scenario Best-Case Scenario
Variables Used in the Cash Flow Forecast 0 1 2 3 4 Variables Used in the Cash Flow Forecast 0 1 2 3 4 Variables Used in the Cash Flow Forecast 0 1 2 3 4
Unit sales 550 572 595 619 Unit sales 412 387 364 342 Unit sales 688 784 894 1,019
Sales price per unit $11.60 $11.83 $12.07 $12.31 Sales price per unit $8.70 $8.87 $9.05 $9.23 Sales price per unit $14.50 $14.79 $15.09 $15.39
Variable cost per unit $6.00 $6.12 $6.24 $6.37 Variable cost per unit $7.50 $7.65 $7.80 $7.96 Variable cost per unit $4.50 $4.59 $4.68 $4.78
Nonvariable costs (excluding depreciation) $2,000 $2,040 $2,081 $2,122 Nonvariable costs (excluding depreciation) $2,500 $2,550 $2,601 $2,653 Nonvariable costs (excluding depreciation) $1,500 $1,530 $1,561 $1,592
Cash Flows At End of Year Cash Flows At End of Year Cash Flows At End of Year
Investment Outlays at Time = 0 0 1 2 3 4 Investment Outlays at Time = 0 0 1 2 3 4 Investment Outlays at Time = 0 0 1 2 3 4
Equipment -$3,400 Equipment -$4,250 Equipment -$2,550
Initial investment in working capital -807 Initial investment in working capital -453 Initial investment in working capital -1,262
Opportunity cost, after taxes 0 Opportunity cost, after taxes 0 Opportunity cost, after taxes 0
Net Cash Flows Over the Project's Life Net Cash Flows Over the Project's Life Net Cash Flows Over the Project's Life
Sales revenues = Units × Price/unit $6,380 $6,768 $7,179 $7,616 Sales revenues = Units × Price/unit $3,584 $3,437 $3,295 $3,159 Sales revenues = Units × Price/unit $9,976 $11,600 $13,489 $15,685
Variable costs = Units × Cost/unit 3,300 3,501 3,713 3,939 Variable costs = Units × Cost/unit 3,090 2,963 2,841 2,724 Variable costs = Units × Cost/unit 3,096 3,600 4,186 4,868
Nonvariable costs (excluding depreciation) 2,000 2,040 2,081 2,122 Nonvariable costs (excluding depreciation) 2,500 2,550 2,601 2,653 Nonvariable costs (excluding depreciation) 1,500 1,530 1,561 1,592
Depreciation: Accelerated, from table below 1,122 1,530 510 238 Depreciation: Accelerated, from table below 1,403 1,913 638 298 Depreciation: Accelerated, from table below 842 1,148 383 179
Operating profit (EBIT) -$42 -$303 $875 $1,316 Operating profit (EBIT) -$3,408 -$3,988 -$2,784 -$2,515 Operating profit (EBIT) $4,539 $5,323 $7,359 $9,047
Taxes on operating profit -17 -121 350 526 Taxes on operating profit -1,704 -1,994 -1,392 -1,257 Taxes on operating profit 1,362 1,597 2,208 2,714
Net operating profit after taxes -$25 -$182 $525 $790 Net operating profit after taxes -$1,704 -$1,994 -$1,392 -$1,257 Net operating profit after taxes $3,177 $3,726 $5,152 $6,333
Add back depreciation 1,122 1,530 510 238 Add back depreciation 1,403 1,913 638 298 Add back depreciation 842 1,148 383 179
Opportunity cost, after taxes 0 0 0 0 Opportunity cost, after taxes 0 0 0 0 Opportunity cost, after taxes 0 0 0 0
Cannibalization or complementary effects, after taxes 0 0 0 0 Cannibalization or complementary effects, after taxes 0 0 0 0 Cannibalization or complementary effects, after taxes 0 0 0 0
Salvage value (taxed as ordinary income) 300 Salvage value (taxed as ordinary income) 300 Salvage value (taxed as ordinary income) 300
Tax on salvage value (SV is taxed at 40%) -120 Tax on salvage value (SV is taxed at 40%) -150 Tax on salvage value (SV is taxed at 40%) -90
Change in WC: Outflow (–) or recovery (+) -49 -52 -55 963 Change in WC: Outflow (–) or recovery (+) 19 18 17 400 Change in WC: Outflow (–) or recovery (+) -205 -239 -278 1,984
Project net cash flows: Time Line -$4,207 $1,048 $1,296 $980 $2,171 Project net cash flows: Time Line -$4,703 -$283 -$64 -$737 -$410 Project net cash flows: Time Line -$3,812 $3,813 $4,634 $5,256 $8,705
Project Evaluation Project Evaluation Project Evaluation
Results Results Results
NPV $36 NPV -$5,847 NPV $13,379
IRR 10.35% IRR ERROR:#NUM! IRR 112.01%
MIRR 10.23% MIRR -100.00% MIRR 60.30%
Profitability index 1.01 Profitability index -0.24 Profitability index 4.51
Payback 3.41 Payback ERROR:#N/A Payback 1.00
Discounted payback 3.98 Discounted payback ERROR:#N/A Discounted payback 1.09
Calculations for Payback Year: 0 1 2 3 4 Calculations for Payback Year: 0 1 2 3 4 Calculations for Payback Year: 0 1 2 3 4
Cumulative cash flows for payback -$4,207 -$3,159 -$1,863 -$883 $1,288 Cumulative cash flows for payback -$4,703 -$4,986 -$5,050 -$5,787 -$6,198 Cumulative cash flows for payback -$3,812 $1 $4,635 $9,892 $18,597
Discounted cash flows for disc. payback -$4,207 $952 $1,071 $736 $1,483 Discounted cash flows for disc. payback -$4,703 -$257 -$53 -$554 -$280 Discounted cash flows for disc. payback -$3,812 $3,466 $3,830 $3,949 $5,946
Cumulative discounted cash flows -$4,207 -$3,255 -$2,183 -$1,447 $36 Cumulative discounted cash flows -$4,703 -$4,961 -$5,013 -$5,567 -$5,847 Cumulative discounted cash flows -$3,812 -$346 $3,484 $7,434 $13,379
Accelerated Depreciation Accelerated Depreciation Accelerated Depreciation
Depreciable basis: $3,400 Rate/year 33% 45% 15% 7% Depreciable basis: $4,250 Rate/year 33% 45% 15% 7% Depreciable basis: $2,550 Rate/year 33% 45% 15% 7%
Dollars/year $1,122 $1,530 $510 $238 Dollars/year $1,403 $1,913 $638 $298 Dollars/year $842 $1,148 $383 $179
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.
Figure 11.6 (shown below) presents the cash flows for each scenario (the cash flows are obtained from the 3 scenarios' analsyes conducted above in the blue, bright yellow, and green boxes). It also shows the NPV for each scenario. Using the NPV and probability for each scenario, we calculate the expected NPV, the standard deviation, and the coefficient of variation. Later in the analysis we consider the possibility of abandoning the project if the worst case occurs, but our present analysis assumes that we cannot abandon the project.
Note: the scenario analysis below is meaningful only if the values in the input section in Cells E37:E51 are set to the original base-case.
Figure 11-6. Scenario Analysis: Expected NPV and Its Risk (Dollars in Thousands)
Predicted Cash Flows for Alternative Scenarios Calculating σ step-by-step Quick NPV and s
Prob: 0 1 2 3 4 WACC NPV Deviation Sqrd dev Sqrd*prob
Best 25% -$3,812 $3,813 $4,634 $5,256 $8,705 10.00% $13,379 11478 131755642 $32,938,910
Base 50% -$4,207 $1,048 $1,296 $980 $2,171 10.00% $36 -1865 3478447 $1,739,224
Worst
25% -$4,703 -$283 -$64 -$737 -$410 10.00% -$5,847 -7748 60037190 $15,009,298
Expected NPV = $1,901 Variance = $49,687,432 $1,901 =Expected NPV
Standard Deviation (SD) = $7,049 σ = $7,049 $7,049 = Standard Deviation (SD)
Coefficient of Variation (CV) = Std. Dev./Expected NPV = 3.71
Scratch work for chart
Worst-Case
-$5,847 25%
-$5,847 0%
Most-Likely
$36 50%
$36 0%
Best-Case
$13,379 25%
$13,379 0%
Expected NPV
$1,901 0%
$1,901 -15%
Scenario Manager
Rather than have 3 sets of analyses, it is possible to have only one set and use the Excel feature called Scenario Manager. (To open the Scenario Manager dialog box in Excel 2003, select Tools, Scenarios; in Excel 2007, select Data, What-if-Analysis, then Scenarios.) If you open the Scenario Manager dialog box, you see the box shown below:
You can select a scenario, click "Show", and the values for that scenario will be substituted into the input cells in Column E (E35:E51). So if we had not repeated the analysis in Columns K through AC, Scenario Manager makes it easy to show any set of inputs in a single model for analysis. Scenario Manager also makes it easy to create a summary of all scenarios using the Summary feature in the dialog box shown above. For example, if you select Summary, you will see the box below:
We selected the cells with key results, E53:E58. When you click OK, the output of the summary will be created in a new worksheet; this new worksheet is "8. Scenario Summary". It provides the same key results as the three separate analyses did, but you need only to create one analysis with multiple sets of inputs saved as scenarios. We encourgage you to explore the Scenaro Manager feature in our Excel Tutorial.
Figure 11-6. Scenario Analysis: Can Abandon
Predicted Cash Flows for Alternative Scenarios Calculating σ step-by-step
Prob: 0 1 2 3 4 WACC NPV Deviation Sqrd dev Sqrd*prob
Best 25% -$3,812 $3,813 $4,634 $5,256 $8,705 10.00% $13,379 $11,153 124,399,616 31,099,904
Base 50% -$4,207 $1,048 $1,296 $980 $2,171 10.00% $36 -$2,190 4,796,486 2,398,243
Worst -$4,703 -$283 -$64 -$737 -$410
25%
-$4,703 -$283 $500 $0 $0 10.00% -$4,547 -$6,773 45,877,342 11,469,336
If abandon, can liquidate for $500 at t = 2. Expected NPV = $2,226 Variance = 44,967,482
Standard Deviation (SD) = $6,706 σ = $6,706
Coefficient of Variation (CV) = Std. Dev./Expected NPV = 3.01
Figure 11-7. Decision Tree with Multiple Decision Points
Firm can abandon the project at t = 2 WACC = 10.0%
Time Periods, Cash Flows, Probabilities, and Decision Points WACC = 10.0%
0 1 2 3 4 5 6 WACC = 10.0% Product: NPV Calculating σ step-by-step
1st Invest Prob. 2nd Invest Prob. 3rd Invest Inflow Inflow Inflow Inflow NPV Joint Prob x Joint Prob Deviation Sqrd dev Sqrd*prob
45% -$3,812 $3,813 $4,634 $5,256 $8,705 $10,503 36% $3,781 $255 64782 $23,321
80% -$500 40% -$4,207 $1,048 $1,296 $980 $2,171 -$525 32% -$168 -3694 13649041 4367693.00407027
-$100 15% Stop $0 $0 $0 $0 -$555 12% -$67 -$3,593 12909858 $1,549,183
20% Stop $0 $0 $0 $0 $0 -$100 20% -$20 -3546 12577547 2515509
Expected NPV = $3,526 Sum = variance 8455707
Standard Deviation (SD) = $2,908 Sq root of Var = σ $2,908
Coefficient of Variation (CV) = Std. Dev./Expected NPV = 0.82

1

Probability Distribution of Scenarios: Outcomes and Probabilities

Worst-Case

-5847.4708240748023 -5847.4708240748023 0.25 0 Most-Likely

35.836539226936111 35.836539226936111 0.5 0 Best-Case

13379.382149246569 13379.382149246569 0.25 0 Expected NPV

1900.8961009064096 1900.8961009064096 0 -0.15

NPV

1

2

1

2

3

3

3

2

4. Sim.

ERROR:#REF!
Section 11.7 Scenario Analysis
Note: this section is relatively technical and some instructors may choose to skip it with no loss in continuity.
Monte Carlo simulation is similar to scenario analysis in that different values of key inputs are used. Unlike scenario analysis, Monte Carlo simulation draws a trial set of input values from specified probability distributions and then computes the NPV for this trial. This process is repeated for hundreds, or even thousands, of trials, with key results (like NPV) saved from each trial. After running the number of desired trials, the NPVs from the trials can be averaged to estimate the project's expected NPV; the trial results can also be used to provide a histogram showing the project's possible outcomes.
Panel A, shown in the blue-bordered box below and slightly to the right, shows the inputs from the previous scenario analysis. It also shows the expected value and standard deviation for those inputs based on the probability of each scenario. To compare apples and apples, we will assume that the inputs for the simulation analysis are drawn from a normal distribution with the same expected value and standard deviation as the inputs from the scenario analysis (these are shown Figure 11-7 in blue in Columns C and D below. However, any of the the blue values in Columns C and D may be changed by the user if desired. Cell D53 also has the input for the assumed correlation between units sold in Year 1 and changes in units sold in later years.
Fgirue 11-7, shown in the box below, also shows the trial inputs and key results. The inputs are shown in red and are drawn from a normal distribution with the mean and standard deviation specified in Columns C and D. We do this in a 2-step process. Column E shows a standard normal random variable created with Excel's random number generator. Column F transforms the standard normal random variable into a normal random variable with the desired mean and standard deviation. To see updated values,hit the F9 key.
Figure 11-7: Inputs and Key Results for the Current Simulation Trial (Dollars in Thousands) Panel A: Values from Scenario Analysis and Their Expected Values and Standard Deviations
To change an input, change one of the blue values in Columns C or D. To see an updated set of trial values, hit the F9 key. Inputs and key results will update for the current trial.
Inputs for Simulation Probability Distributions Random Variables Used in Current Simulation Trial Inputs from Scenario Analysis for Comparison to Simulation
Expected Value of Input Standard Deviation of Input Standard Normal Random Variable
Mike Ehrhardt: The RAND() function generates a random number between 0 and 1. When this value is the argument in the NORMSINV function, the NORMSINV interprets the value as the cumulative probability of a standard normal distribution. Then the NORMSINV function finds a standard normal variable Z such that its the probability of drawing a value of Z or less is equal to the argument. This means the formula =NORMSINV(RAND()) returns a random standard normal variable. 		Value used in Current Trial Base Worst Best Expected Value of Input Standard Deviation of Input
Probability of Scenario
50% 25% 25%
Inputs:
Equipment cost $3,400 $601 -0.69 $2,987 $3,400 $4,250 $2,550 $3,400 $601
Salvage value, equipment, Year 4 $300 $300 $300 $300
Opportunity cost $0 $0 $0 $0
Externalities (cannibalization) $0 $0 $0 $0
Units sold, Year 1 550 98 0.50 599 550 412 688 550 98
Annual change in units sold, after Year 1 4.00% 7.07% -0.13
Mike Ehrhardt: We must use a slightly different formula to get a standard normal for the annual change in units that is correlated with the unit sales in the first year. We do that by forming a variable that is a combination of the standard normal variable for units in the 1st year and an uncorrelated standard normal, with the "weights" in the combination depending on the desired correlation.		 3.08% 4.00% -6.00% 14.00% 4.00% 7.07%
Sales price per unit, Year 1 $11.60 $2.05 -0.05 $11.51 $11.60 $8.70 $14.50 $11.60 $2.05
Annual change in sales price, after Year 1 2.00% 2.00% 2.00% 2.00%
Variable cost per unit (VC), Year 1 $6.00 $1.06 1.33 $7.41 $6.00 $7.50 $4.50 $6.00 $1.06
Annual change in VC, after Year 1 2.00% 2.00% 2.00% 2.00%
Nonvariable cost (Non-VC), Year 1 $2,000 $354 0.38 $2,136 $2,000 $2,500 $1,500 $2,000 $354
Annual change in Non-VC, after Year 1 2.00% 2.00% 2.00% 2.00%
Project WACC 10.00% 10.00% 10.00% 10.00%
Tax rate 40.00% 7.07% -0.20 38.60% 40.00% 50.00% 30.00% 40.00% 7.07%
Working capital as % of next year's sales 12.65% 12.65% 12.65% 12.65%
Assumed correlation between units sold in Year 1 and annual change in units sold in later years:
r = 65.00%
Key Results Based on Current Trial Key Results
Base Worst Best
NPV -$1,342 $36 -$5,847 $13,379
IRR -4.95% 10.35% Not found 112.01%
MIRR -1.14% 10.23% -100.00% 60.30%
PI 0.65 1.01 -0.24 4.51
Payback Not found 3.41 Not found 1.00
Discounted payback Not found 3.98 Not found 1.09
Panel B: Project Analysis for Current Trial in Simulation
Variables Used in the Cash Flow Forecast 0 1 2 3 4
Unit sales 599 617 636 656
Sales price per unit $11.51 $11.74 $11.97 $12.21
Variable cost per unit $7.41 $7.55 $7.71 $7.86
Nonvariable costs (excluding depreciation) $2,136 $2,179 $2,223 $2,267
Cash Flows At End of Year
Investment Outlays at Time = 0 0 1 2 3 4
Equipment -$2,987
Initial investment in working capital -872
Opportunity cost, after taxes 0
Net Cash Flows Over the Project's Life
Sales revenues = Units × Price/unit $6,891 $7,245 $7,617 $8,009
Variable costs = Units × Cost/unit 4,435 4,663 4,902 5,154
Nonvariable costs (excluding depreciation) 2,136 2,179 2,223 2,267
Depreciation: Accelerated, from table below 986 1,344 448 209
Operating profit (EBIT) -$666 -$941 $44 $378
Taxes on operating profit -257 -363 17 146
Net operating profit after taxes -$409 -$578 $27 $232
Add back depreciation 986 1,344 448 209
Opportunity cost, after taxes 0 0 0 0
Cannibalization or complementary effects, after taxes 0 0 0 0
Salvage value (taxed as ordinary income) 300
Tax on salvage value (SV is taxed at 40%) -116
Change in WC: Outflow (–) or recovery (+) -45 -47 -50 1,013
Project net cash flows: Time Line -$3,858 $532 $719 $426 $1,639
Project Evaluation
Results
NPV -$1,342
IRR -4.95%
MIRR -1.14%
Profitability index 0.65
Payback ERROR:#N/A
Discounted payback ERROR:#N/A
Calculations for Payback Year: 0 1 2 3 4
Cumulative cash flows for payback -$3,858 -$3,326 -$2,607 -$2,182 -$543
Discounted cash flows for disc. payback -$3,858 $483 $594 $320 $1,119
Cumulative discounted cash flows -$3,858 -$3,375 -$2,780 -$2,461 -$1,342
Accelerated Depreciation
Depreciable basis: $2,987 Rate/year 33% 45% 15% 7%
Dollars/year $986 $1,344 $448 $209
How the Simulation Works
We use a Data Table to perform the simulation (the Data Table is below shaded in lavender). When the Data Table is updated, it will insert new random variables for each of the inputs we allow to change in Figure 11-7 above, run the analysis in Panel B above, and then save the NPV for each trial. (We also save the input variables for each trial so that we can verify that they are behaving as we expect.) We set the first column of the Data Table (the variable to be changed in each row) to numbers from 1-100. We don't really use these numbers anywhere in the analyis, but if we tell the Data Table to treat these as the Column inputs, Excel will recalculate all items in the Data Table, including the random inputs and the resulting NPV. In other words, we "trick" Excel into doing a simulation. We tell Excel to insert each of the Column inputs in the Data Table into the cell immediately below this box. This cell isn't linked to anything else, but each time Excel updates a row of the Data Table, all the random values will be updated.
Column input cell to "trick" Excel into updating random variables in Data Table: 1
Mike Ehrhardt: Do not delete or change this cell or row.		 Don't change the the red cell.
Excel normally updates all values in a Data Table each time any cell that is related to the Data Table changes. In our case, we have random variables in the Data Table, so each time any cell in the worksheet makes a calculation, the Data Table is updated. If the Data Table has many rows, updating it can take up to 20 or 30 seconds. This is ok when we want to update the Table, but it is annoying to wait 30 seconds any time we make any changes in the worksheet. The "check box" explained below helps with this annoyance.
To put random variables in the Data Table for the simulation, the box shown below must be checked; otherwise, the Data Table contains only zero's and doesn't update when the sheet makes a calculation (other than the first time you check this box or if you insert or delete rows or columns). If the box is unchecked and you check it, the check mark won't show up until the Table is updated, so don't get impatient and click it twice. After you have checked the box, the Data Table will update any time you change a cell in the worksheet. So to make the Data Table update, make sure the box is checked and then hit the F9 key.
Put a check in the box below to put trials into the data table; otherwise, the data table will have only zeros.
FALSE
Remember to uncheck the box above when you are through with the simulation, or the Data Table will recalculate any time you make a change in the worksheet, which will slow down all other calculations in the worksheet.
You don't need to change anything in this section. It will be updated automatically if you do a simulation. The summary of the simulation results and the histogram are based on the simulation trials n the Data Table below and are updated automatically when you do a simulation.
Note: If results ae all zeros, go back to row 144 and "check" the box by clicking it with your cursor.
Figure 11-8 Summary of Simulation Results (Thousands of Dollars)
Number of Trials = 0
Simulated Input Variables
Equipment cost Units sold, Year 1 Annual change in units sold, after Year 1 Sales price per unit, Year 1 Variable cost per unit (VC), Year 1 Nonvariable cost (Non-VC), Year 1 Tax rate Key Results:
NPV
Average $0 0 0.0% $0.00 $0.00 $0 0.0% $0
Standard deviation 0 0 0.0% 0.00 0.00 0 0.0% $0
Maximum 0 0 0.0% 0.00 0.00 0 0.0% $0
Minimum 0 0 0.0% 0.00 0.00 0 0.0% $0
Correlation with unit sales ERROR:#DIV/0!
Median $0
Probability of NPV > 0 ERROR:#DIV/0!
Coefficient of variation ERROR:#DIV/0!
Scratch work for chart: see comments.
Count
Mike Ehrhardt: This column counts the umber of simulation trials with NPVs greater than the bottom of range and less than top the top of the range.
Range bottom
Mike Ehrhardt: This column of data contains the ranges into which the NPV's are grouped. The numbers shown are the bottoms of each range. The ranges are automatically selected so that the ranges will fit the data for the particular simulation.
Mike Ehrhardt: The RAND() function generates a random number between 0 and 1. When this value is the argument in the NORMSINV function, the NORMSINV interprets the value as the cumulative probability of a standard normal distribution. Then the NORMSINV function finds a standard normal variable Z such that its the probability of drawing a value of Z or less is equal to the argument. This means the formula =NORMSINV(RAND()) returns a random standard normal variable. 		100 Percent
Mike Ehrhardt: This column shows the percent of trials with NPVs in the range.

Mike Ehrhardt: We must use a slightly different formula to get a standard normal for the annual change in units that is correlated with the unit sales in the first year. We do that by forming a variable that is a combination of the standard normal variable for units in the 1st year and an uncorrelated standard normal, with the "weights" in the combination depending on the desired correlation.		 $0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
$0 0 ERROR:#DIV/0!
Sum - 0 ERROR:#DIV/0!
Output of Simulation in Data Table
Trial Number Equipment cost Units sold, Year 1 Annual change in units sold, after Year 1 Sales price per unit, Year 1 Variable cost per unit (VC), Year 1 Nonvariable cost (Non-VC), Year 1 Tax rate NPV
0 0 0 0 0 0 0 0
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
3 0 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0
6 0 0 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0
8 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0
11 0 0 0 0 0 0 0 0
12 0 0 0 0 0 0 0 0
13 0 0 0 0 0 0 0 0
14 0 0 0 0 0 0 0 0
15 0 0 0 0 0 0 0 0
16 0 0 0 0 0 0 0 0
17 0 0 0 0 0 0 0 0
18 0 0 0 0 0 0 0 0
19 0 0 0 0 0 0 0 0
20 0 0 0 0 0 0 0 0
21 0 0 0 0 0 0 0 0
22 0 0 0 0 0 0 0 0
23 0 0 0 0 0 0 0 0
24 0 0 0 0 0 0 0 0
25 0 0 0 0 0 0 0 0
26 0 0 0 0 0 0 0 0
27 0 0 0 0 0 0 0 0
28 0 0 0 0 0 0 0 0
29 0 0 0 0 0 0 0 0
30 0 0 0 0 0 0 0 0
31 0 0 0 0 0 0 0 0
32 0 0 0 0 0 0 0 0
33 0 0 0 0 0 0 0 0
34 0 0 0 0 0 0 0 0
35 0 0 0 0 0 0 0 0
36 0 0 0 0 0 0 0 0
37 0 0 0 0 0 0 0 0
38 0 0 0 0 0 0 0 0
39 0 0 0 0 0 0 0 0
40 0 0 0 0 0 0 0 0
41 0 0 0 0 0 0 0 0
42 0 0 0 0 0 0 0 0
43 0 0 0 0 0 0 0 0
44 0 0 0 0 0 0 0 0
45 0 0 0 0 0 0 0 0
46 0 0 0 0 0 0 0 0
47 0 0 0 0 0 0 0 0
48 0 0 0 0 0 0 0 0
49 0 0 0 0 0 0 0 0
50 0 0 0 0 0 0 0 0
51 0 0 0 0 0 0 0 0
52 0 0 0 0 0 0 0 0
53 0 0 0 0 0 0 0 0
54 0 0 0 0 0 0 0 0
55 0 0 0 0 0 0 0 0
56 0 0 0 0 0 0 0 0
57 0 0 0 0 0 0 0 0
58 0 0 0 0 0 0 0 0
59 0 0 0 0 0 0 0 0
60 0 0 0 0 0 0 0 0
61 0 0 0 0 0 0 0 0
62 0 0 0 0 0 0 0 0
63 0 0 0 0 0 0 0 0
64 0 0 0 0 0 0 0 0
65 0 0 0 0 0 0 0 0
66 0 0 0 0 0 0 0 0
67 0 0 0 0 0 0 0 0
68 0 0 0 0 0 0 0 0
69 0 0 0 0 0 0 0 0
70 0 0 0 0 0 0 0 0
71 0 0 0 0 0 0 0 0
72 0 0 0 0 0 0 0 0
73 0 0 0 0 0 0 0 0
74 0 0 0 0 0 0 0 0
75 0 0 0 0 0 0 0 0
76 0 0 0 0 0 0 0 0
77 0 0 0 0 0 0 0 0
78 0 0 0 0 0 0 0 0
79 0 0 0 0 0 0 0 0
80 0 0 0 0 0 0 0 0
81 0 0 0 0 0 0 0 0
82 0 0 0 0 0 0 0 0
83 0 0 0 0 0 0 0 0
84 0 0 0 0 0 0 0 0
85 0 0 0 0 0 0 0 0
86 0 0 0 0 0 0 0 0
87 0 0 0 0 0 0 0 0
88 0 0 0 0 0 0 0 0
89 0 0 0 0 0 0 0 0
90 0 0 0 0 0 0 0 0
91 0 0 0 0 0 0 0 0
92 0 0 0 0 0 0 0 0
93 0 0 0 0 0 0 0 0
94 0 0 0 0 0 0 0 0
95 0 0 0 0 0 0 0 0
96 0 0 0 0 0 0 0 0
97 0 0 0 0 0 0 0 0
98 0 0 0 0 0 0 0 0
99 0 0 0 0 0 0 0 0
100 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

NPV ($)

Probability

Must be checked to put random variable in data table for simulation

5. Replacement

ERROR:#REF!
Section 11.9 Replacement Analysis
As this model is set up, the cost of the new machine, the salvage value of the old machine, the tax rate, the WACC, and the operating costs before depreciation for the new machine can be varied and the output will automatically recalculate. Only these input variables, in BLUE TYPE, should be changed unless you want to modify the model, which could be a fairly big job.
Note that the projects analyzed here are not related at all to the projects on other Tabs.
Figure 11-9. Replacement Analysis
Applies to:
Part I. Inputs: Both Machines Old Machine New Machine
Cost of new machine $2,000
After-tax salvage value old machine $400
Sales revenues (fixed) $2,500
Annual operating costs except depreciation $1,200 $280
Tax rate 40%
WACC 10%
Depreciation 1 2 3 4 Totals:
Depr. rates (new machine) 33% 45% 15% 7% 100%
Depreciation on new machine $660 $900 $300 $140 $2,000
Depreciation on old machine $400 $400 $400 $400 $1,600
∆: Change in depreciation $260 $500 -$100 -$260 $400
Part II. Net Cash Flows Before Replacement: Old Machine
0 1 2 3 4
Sales revenues $2,500 $2,500 $2,500 $2,500
Operating costs except depreciation 1,200 1,200 1,200 1,200
Depreciation 400 400 400 400
Total operating costs $1,600 $1,600 $1,600 $1,600
Operating income $900 $900 $900 $900
Taxes 40% 360 360 360 360
After-tax operating income $540 $540 $540 $540
Add back depreciation 400 400 400 400
Net cash flows before replacement $0 $940 $940 $940 $940
Part III. Net Cash Flows After Replacement: New Machine
0 1 2 3 4
New machine cost: -$2,000
After-tax salvage value, old machine $400
Sales revenues $2,500 $2,500 $2,500 $2,500
Operating costs except depreciation 280 280 280 280
Depreciation 660 900 300 140
Total operating costs $940 $1,180 $580 $420
Operating income $1,560 $1,320 $1,920 $2,080
Taxes 40% 624 528 768 832
After-tax operating income $936 $792 $1,152 $1,248
Add back depreciation 660 900 300 140
Net cash flows after replacement -$1,600 $1,596 $1,692 $1,452 $1,388
Part IV. Incremental CF: Row 51 - Row 38 -$1,600 $656 $752 $512 $448
Part V. Evaluation NPV = $308.51 IRR = 19.33% MIRR = 14.96%
Part VI. Alternative Calculation for Net Cash Flows
New machine cost -$2,000
Salvage value, old machine 400
Net cost of new machine -$1,600
Other operating cost savings = Old — New $920 $920 $920 $920
A-T savings = Other cost savings × (1 — Tax rate) 552 552 552 552
∆ Depreciation = (New — Old) 260 500 -100 -260
Depr'n tax savings = ∆ Depreciation × Tax rate 104 200 -40 -104
NCF = A-T cost savings + Depr'n tax savings -$1,600 $656 $752 $512 $448
The Net Cash Flow time line is the sum of the larger, boldfaced, boxed, red numbers. The cash flows on Row 64 are IDENTICAL to those on Row 53.

6. Dec. Trees

ERROR:#REF!
Section 11.10 Decision Trees
Tab 5 extends the scenario analysis (shown in Tab 3) to incorporate the possibility of abandoning the project if demand is low. We also provide an introduction to real options. For convenience, we repeat the scenario analysis before addressing abandonment.
See Tab 3 for details concerning the scenario analysis. Analysis for Worst and Best Scenarios in yellow and green boxes below and to the right.
Figure 11-5 (Repeated from Tab 3) Inputs and Key Results for Each Scenario (Dollars in Thousands)
Don’t change any values in the yellow box below. If you want to change an input, do it in Column G to the left. Don’t change any values in the green box below. If you want to change an input, do it in Column I to the left.
Scenarios: Worst-Case Scenario Best-Case Scenario
Inputs: Base Worst Best Inputs Worst Inputs Best
Probability of Scenario 50% 25% 25%
Equipment cost $3,400 $4,250 $2,550 Equipment cost $4,250 Equipment cost $2,550
Salvage value, equipment, Year 4 $300 $300 $300 Salvage value, equipment, Year 4 $300 Salvage value, equipment, Year 4 $300
Opportunity cost $0 $0 $0 Opportunity cost $0 Opportunity cost $0
Externalities (cannibalization) $0 $0 $0 Externalities (cannibalization) $0 Externalities (cannibalization) $0
Units sold, Year 1 550 412 688 Units sold, Year 1 412 Units sold, Year 1 688
Annual change in units sold, after Year 1 4.00% -6.00% 14.00% Annual change in units sold, after Year 1 -6.00% Annual change in units sold, after Year 1 14.00%
Sales price per unit, Year 1 $11.60 $8.70 $14.50 Sales price per unit, Year 1 $8.70 Sales price per unit, Year 1 $14.50
Annual change in sales price, after Year 1 2.00% 2.00% 2.00% Annual change in sales price, after Year 1 2.00% Annual change in sales price, after Year 1 2.00%
Variable cost per unit (VC), Year 1 $6.00 $7.50 $4.50 Variable cost per unit (VC), Year 1 $7.50 Variable cost per unit (VC), Year 1 $4.50
Annual change in VC, after Year 1 2.00% 2.00% 2.00% Annual change in VC, after Year 1 2.00% Annual change in VC, after Year 1 2.00%
Nonvariable cost (Non-VC), Year 1 $2,000 $2,500 $1,500 Nonvariable cost (Non-VC), Year 1 $2,500 Nonvariable cost (Non-VC), Year 1 $1,500
Annual change in Non-VC, after Year 1 2.00% 2.00% 2.00% Annual change in Non-VC, after Year 1 2.00% Annual change in Non-VC, after Year 1 2.00%
Project WACC 10.00% 10.00% 10.00% Project WACC 10.00% Project WACC 10.00%
Tax rate 40.00% 50.00% 30.00% Tax rate 50.00% Tax rate 30.00%
Working capital as % of next year's sales 12.65% 12.65% 12.65% Working capital as % of next year's sales 12.65% Working capital as % of next year's sales 12.65%
Key Results: Base Worst Best Key Results: Worst Key Results: Best
NPV $36 -$5,847 $13,379 NPV -$5,847 NPV $13,379
IRR 10.35% Not found 112.01% IRR Not found IRR 112.01%
MIRR 10.23% -100.00% 60.30% MIRR -100.00% MIRR 60.30%
PI 1.01 -0.24 4.51 PI -0.24 PI 4.51
Payback 3.41 Not found 1.00 Payback Not found Payback 1.00
Discounted payback 3.98 Not found 1.09 Discounted payback Not found Discounted payback 1.09
Analysis for Base-Case Scenario shown in blue box below.
Base Worst-Case Scenario Best-Case Scenario
Variables Used in the Cash Flow Forecast 0 1 2 3 4 Variables Used in the Cash Flow Forecast 0 1 2 3 4 Variables Used in the Cash Flow Forecast 0 1 2 3 4
Unit sales 550 572 595 619 Unit sales 412 387 364 342 Unit sales 688 784 894 1,019
Sales price per unit $11.60 $11.83 $12.07 $12.31 Sales price per unit $8.70 $8.87 $9.05 $9.23 Sales price per unit $14.50 $14.79 $15.09 $15.39
Variable cost per unit $6.00 $6.12 $6.24 $6.37 Variable cost per unit $7.50 $7.65 $7.80 $7.96 Variable cost per unit $4.50 $4.59 $4.68 $4.78
Nonvariable costs (excluding depreciation) $2,000 $2,040 $2,081 $2,122 Nonvariable costs (excluding depreciation) $2,500 $2,550 $2,601 $2,653 Nonvariable costs (excluding depreciation) $1,500 $1,530 $1,561 $1,592
Cash Flows At End of Year Cash Flows At End of Year Cash Flows At End of Year
Investment Outlays at Time = 0 0 1 2 3 4 Investment Outlays at Time = 0 0 1 2 3 4 Investment Outlays at Time = 0 0 1 2 3 4
Equipment -$3,400 Equipment -$4,250 Equipment -$2,550
Initial investment in working capital -807 Initial investment in working capital -453 Initial investment in working capital -1,262
Opportunity cost, after taxes 0 Opportunity cost, after taxes 0 Opportunity cost, after taxes 0
Net Cash Flows Over the Project's Life Net Cash Flows Over the Project's Life Net Cash Flows Over the Project's Life
Sales revenues = Units × Price/unit $6,380 $6,768 $7,179 $7,616 Sales revenues = Units × Price/unit $3,584 $3,437 $3,295 $3,159 Sales revenues = Units × Price/unit $9,976 $11,600 $13,489 $15,685
Variable costs = Units × Cost/unit 3,300 3,501 3,713 3,939 Variable costs = Units × Cost/unit 3,090 2,963 2,841 2,724 Variable costs = Units × Cost/unit 3,096 3,600 4,186 4,868
Nonvariable costs (excluding depreciation) 2,000 2,040 2,081 2,122 Nonvariable costs (excluding depreciation) 2,500 2,550 2,601 2,653 Nonvariable costs (excluding depreciation) 1,500 1,530 1,561 1,592
Depreciation: Accelerated, from table below 1,122 1,530 510 238 Depreciation: Accelerated, from table below 1,403 1,913 638 298 Depreciation: Accelerated, from table below 842 1,148 383 179
Operating profit (EBIT) -$42 -$303 $875 $1,316 Operating profit (EBIT) -$3,408 -$3,988 -$2,784 -$2,515 Operating profit (EBIT) $4,539 $5,323 $7,359 $9,047
Taxes on operating profit -17 -121 350 526 Taxes on operating profit -1,704 -1,994 -1,392 -1,257 Taxes on operating profit 1,362 1,597 2,208 2,714
Net operating profit after taxes -$25 -$182 $525 $790 Net operating profit after taxes -$1,704 -$1,994 -$1,392 -$1,257 Net operating profit after taxes $3,177 $3,726 $5,152 $6,333
Add back depreciation 1,122 1,530 510 238 Add back depreciation 1,403 1,913 638 298 Add back depreciation 842 1,148 383 179
Opportunity cost, after taxes 0 0 0 0 Opportunity cost, after taxes 0 0 0 0 Opportunity cost, after taxes 0 0 0 0
Cannibalization or complementary effects, after taxes 0 0 0 0 Cannibalization or complementary effects, after taxes 0 0 0 0 Cannibalization or complementary effects, after taxes 0 0 0 0
Salvage value (taxed as ordinary income) 300 Salvage value (taxed as ordinary income) 300 Salvage value (taxed as ordinary income) 300
Tax on salvage value (SV is taxed at 40%) -120 Tax on salvage value (SV is taxed at 40%) -150 Tax on salvage value (SV is taxed at 40%) -90
Change in WC: Outflow (–) or recovery (+) -49 -52 -55 963 Change in WC: Outflow (–) or recovery (+) 19 18 17 400 Change in WC: Outflow (–) or recovery (+) -205 -239 -278 1,984
Project net cash flows: Time Line -$4,207 $1,048 $1,296 $980 $2,171 Project net cash flows: Time Line -$4,703 -$283 -$64 -$737 -$410 Project net cash flows: Time Line -$3,812 $3,813 $4,634 $5,256 $8,705
Project Evaluation Project Evaluation Project Evaluation
Results Results Results
NPV $36 NPV -$5,847 NPV $13,379
IRR 10.35% IRR ERROR:#NUM! IRR 112.01%
MIRR 10.23% MIRR -100.00% MIRR 60.30%
Profitability index 1.01 Profitability index -0.24 Profitability index 4.51
Payback 3.41 Payback ERROR:#N/A Payback 1.00
Discounted payback 3.98 Discounted payback ERROR:#N/A Discounted payback 1.09
Calculations for Payback Year: 0 1 2 3 4 Calculations for Payback Year: 0 1 2 3 4 Calculations for Payback Year: 0 1 2 3 4
Cumulative cash flows for payback -$4,207 -$3,159 -$1,863 -$883 $1,288 Cumulative cash flows for payback -$4,703 -$4,986 -$5,050 -$5,787 -$6,198 Cumulative cash flows for payback -$3,812 $1 $4,635 $9,892 $18,597
Discounted cash flows for disc. payback -$4,207 $952 $1,071 $736 $1,483 Discounted cash flows for disc. payback -$4,703 -$257 -$53 -$554 -$280 Discounted cash flows for disc. payback -$3,812 $3,466 $3,830 $3,949 $5,946
Cumulative discounted cash flows -$4,207 -$3,255 -$2,183 -$1,447 $36 Cumulative discounted cash flows -$4,703 -$4,961 -$5,013 -$5,567 -$5,847 Cumulative discounted cash flows -$3,812 -$346 $3,484 $7,434 $13,379
Accelerated Depreciation Accelerated Depreciation Accelerated Depreciation
Depreciable basis: $3,400 Rate/year 33% 45% 15% 7% Depreciable basis: $4,250 Rate/year 33% 45% 15% 7% Depreciable basis: $2,550 Rate/year 33% 45% 15% 7%
Dollars/year $1,122 $1,530 $510 $238 Dollars/year $1,403 $1,913 $638 $298 Dollars/year $842 $1,148 $383 $179
Note: the scenario analysis below is meaningful only if the values in the input section in Cells E16:E30 are set to the original base-case.
Recall from Tab 3 that we can find the value of the project under the assumption that the project must be operated its full life. For convenience, we repeat that analysis below.
Figure 11-6 (Repeated from Tab 3). Scenario Analysis: Cannot Abandon
Predicted Cash Flows for Alternative Scenarios Calculating σ step-by-step Quick NPV and s
Prob: 0 1 2 3 4 WACC NPV Deviation Sqrd dev Sqrd*prob
Best 25% -$3,812 $3,813 $4,634 $5,256 $8,705 10.00% $13,379 11478 131755642 $32,938,910
Base 50% -$4,207 $1,048 $1,296 $980 $2,171 10.00% $36 -1865 3478447 $1,739,224
Worst
25% -$4,703 -$283 -$64 -$737 -$410 10.00% -$5,847 -7748 60037190 $15,009,298
Expected NPV = $1,901 Variance = $49,687,432 $1,901 =Expected NPV
Standard Deviation (SD) = $7,049 σ = $7,049 $7,049 = Standard Deviation (SD)
Coefficient of Variation (CV) = Std Dev/Expected NPV = 3.71
Now assume that the project may be terminated (abandoned) at Year 2 if the demand is low. The net after-tax cash flow from salvage, legal fees, liquidation of working capital, and all other termination cost/revenues is $500 and is shown at Year 2 for the low demand scenario. As shown in Figure 11-9, the ability to abandon a project can add significant value to its NPV.
Figure 11-10. Simple Decision Tree: Can Abandon Project in Worst-Case Scenario
Predicted Cash Flows for Alternative Scenarios Calculating σ step-by-step
Prob: 0 1 2 3 4 WACC NPV Deviation Sqrd dev Sqrd*prob
Best 25% -$3,812 $3,813 $4,634 $5,256 $8,705 10.00% $13,379 $11,153 124,399,616 31,099,904
Base 50% -$4,207 $1,048 $1,296 $980 $2,171 10.00% $36 -$2,190 4,796,486 2,398,243
Worst -$4,703 -$283 -$64 -$737 -$410
25%
-$4,703 -$283 $500 $0 $0 10.00% -$4,547 -$6,773 45,877,342 11,469,336
If abandon, can liquidate for $500 at t = 2. Expected NPV = $2,226 Variance = 44,967,482
Standard Deviation (SD) = $6,706 σ = $6,706
Coefficient of Variation (CV) = Std Dev/Expected NPV = 3.01
Figure 11-11. Decision Tree with Multiple Decision Points
Firm can abandon the project at t = 2 WACC = 10.0%
Time Periods, Cash Flows, Probabilities, and Decision Points WACC = 10.0%
0 1 2 3 4 5 6 WACC = 10.0% Product: NPV Calculating σ step-by-step
1st Invest Prob. 2nd Invest Prob. 3rd Invest Inflow Inflow Inflow Inflow NPV Joint Prob x Joint Prob Deviation Sqrd dev Sqrd*prob
45% -$3,812 $3,813 $4,634 $5,256 $8,705 $10,503 36% $3,781 $6,976 48668922 $17,520,812
80% -$500 40% -$4,207 $1,048 $1,296 $980 $2,171 -$525 32% -$168 -4051 16413941 5252460.98569415
-$100 15% Stop $0 $0 $0 $0 -$555 12% -$67 -$4,081 16654799 $1,998,576
-3526.4836911416
20% Stop $0 $0 $0 $0 $0 -$100 20% -$20 -3626 13151384 2630277
Expected NPV = $3,526 Sum = variance 27402125
Standard Deviation (SD) = $5,235 Sq root of Var = σ $5,235
Coefficient of Variation (CV) = Std Dev/Expected NPV = 1.48

1

1

2

1

2

3

3

3

2

7. App. A

DEPRECIATION TABLES ERROR:#REF!
Depreciation percentages personal property (i.e., assets other Actual IRS Depreciation Percentages for Personal
than real estate) estate), rounded for convenience. Property
Class of Investment Class of Investment
Ownership Year 3-Year 5-Year 7-Year 10-Year Ownership Year 3-Year 5-Year 7-Year 10-Year
1 33% 20% 14% 10% 1 33.33% 20.00% 14.29% 10.00%
2 45% 32% 25% 18% 2 44.45% 32.00% 24.49% 18.00%
3 15% 19% 17% 14% 3 14.81% 19.20% 17.49% 14.40%
4 7% 12% 13% 12% 4 7.41% 11.52% 12.49% 11.52%
5 11% 9% 9% 5 11.52% 8.93% 9.22%
6 6% 9% 7% 6 5.76% 8.92% 7.37%
7 9% 7% 7 8.93% 6.55%
8 4% 7% 8 4.46% 6.55%
9 7% 9 6.56%
10 6% 10 6.55% 10.00% 18.00% 14.40% 11.52% 9.22% 7.37% 6.55% 6.55% 6.56% 6.55% 3.28%
11 3% 11 3.28%
100% 100% 100% 100% 100.00% 100.00% 100.00% 100.00%
MACRS for Residential Real Property
Month Property Placed in Service
Year 1 2 3 4 5 6 7 8 9 10 11 12
1 3.485% 3.182% 2.879% 2.576% 2.273% 1.970% 1.667% 1.364% 1.061% 0.758% 0.455% 0.152%
2-27 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636%
28 1.970% 2.273% 2.576% 2.879% 3.182% 3.458% 3.636% 3.636% 3.636% 3.636% 3.636% 3.636%
29 0.000% 0.000% 0.000% 0.000% 0.000% 0.000% 0.152% 0.455% 0.758% 1.061% 1.364% 1.667%
99.99% 99.99% 99.99% 99.99% 99.99% 99.96% 99.99% 99.99% 99.99% 99.99% 99.99% 99.99%
MACRS for Nonresidential Real Property
Month Property Placed in Service
Year 1 2 3 4 5 6 7 8 9 10 11 12
1 2.461% 2.247% 2.033% 1.819% 1.605% 1.391% 1.177% 0.963% 0.749% 0.535% 0.321% 0.107%
2-39 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564% 2.564%
40 0.107% 0.321% 0.535% 0.749% 0.963% 1.177% 1.391% 1.605% 1.819% 2.033% 2.247% 2.461%
100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00% 100.00%
Rounded Percentages Used in Analysis
Property Life (in years): 39
Depreciation in Year 1 (assuming half-year convention): 1.30%
Rounded Depreciation in Years 2-39: 2.60%
Depreciation in Year 40: 1.30%

8. Scenario Summary

Scenario Summary
Current Values: Base-Case Scenario in Column E Show Worst-Case in Column E Show Best-Case in Column E
Created by Mike Ehrhardt on 5/17/2009 Modified by Mike Ehrhardt on 5/17/2009 Created by Mike Ehrhardt on 5/17/2009 Modified by Mike Ehrhardt on 5/17/2009 Created by Mike Ehrhardt on 5/17/2009 Modified by Mike Ehrhardt on 5/17/2009
Changing Cells:
$E$35 Base Base Worst Best
$E$36 50% 50% 25% 25%
$E$37 $3,400 $3,400 $4,250 $2,550
$E$38 $300 $300 $300 $300
$E$39 $0 $0 $0 $0
$E$40 $0 $0 $0 $0
$E$41 550 550 412 688
$E$42 4.00% 4.00% -6.00% 14.00%
$E$43 $11.60 $11.60 $8.70 $14.50
$E$44 2.00% 2.00% 2.00% 2.00%
$E$45 $6.00 $6.00 $7.50 $4.50
$E$46 2.00% 2.00% 2.00% 2.00%
$E$47 $2,000 $2,000 $2,500 $1,500
$E$48 2.00% 2.00% 2.00% 2.00%
$E$49 10.00% 10.00% 10.00% 10.00%
$E$50 40.00% 40.00% 50.00% 30.00%
$E$51 12.65% 12.65% 12.65% 12.65%
Result Cells:
$E$53 $36 $36 -$5,847 $13,379
$E$54 10.35% 10.35% Not found 112.01%
$E$55 10.23% 10.23% -100.00% 60.30%
$E$56 1.01 1.01 -0.24 4.51
$E$57 3.41 3.41 Not found 1.00
$E$58 3.98 3.98 Not found 1.09
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.