Inputs from Sc
Inputs for Simulation
Probability Distributions
Random Variables Used in
Current Simulation Trial
Expected
Value of
Input
Standard
Deviation of
Input
Standard Normal
Random Variable
Value Used in
Current Trial
Assumed correlation between units sold in
Year 1 and annual change in units sold in
later years:
r
=

Panel B: Project Analysis for Current Trial in Simulation Using Inputs from Figure 11-7 Column F
Intermediate Calculations
0
1
2
3
Unit sales
9,326
10,101
10,940
Sales price per unit
$1.37
$1.42
$1.48
Variable cost per unit (excl. depr.)
$0.99
$1.02
$1.05
Nonvariable costs (excl. depr.)
###
###
###
Sales revenues = Units × Price/unit
###
###
###
###
###
###
###
Basis for depreciation
###
Annual depreciation rate (MACRS)
33.33%
44.45%
14.81%
Annual depreciation expense
###
###
###
Remaining undepreciated value
###
###
###
Cash Flow Forecast
Cash Flows at End of Year
0
1
2
3
Sales revenues = Units × Price/unit
$12,730
$14,339
$16,152
Variable costs
= Units × Cost/unit
$9,247
$10,316
$11,508
Nonvariable costs (excluding depreciation)
$1,993
$2,052
$2,114
Depreciation
$2,511
$3,349
$1,116
Earnings before interest and taxes (EBIT)
−$1,021
−$1,379
$1,413
Taxes on operating profit
(40% rate)
−$421
−$569
$583
Net operating profit after taxes
−$600
−$810
$831
Add back depreciation
$2,511
$3,349
$1,116
Equipment purchases
−$7,535
Profit from salvage value
Cash flow due to tax on salvage value (40% rate)
Cash flow due to change in WC
−$1,909
−$241
−$272
−$306
Opportunity cost, after taxes
$0
$0
$0
$0
After-tax cannibalization or complementary effect
$0
$0
$0
Project net cash flows: Time Line
−$9,444
$1,670
$2,267
$1,640
Project Evaluation Measures
NPV
-$1,267
=NPV(E69,F111:I111)+E111
IRR
4.61% =IRR(E111:I111)
MIRR
6.11% =MIRR(E111:I111,E69,E69)
Profitability index
0.87
=NPV(E69,F111:I111)/(-E111)
Payback
3.74
=PERCENTRANK(E120:I120,0,6)*I119
Discounted payback
#VALUE! =PERCENTRANK(E122:I122,0,6)*I119
Calculations for Payback
Year:
0
1
2
3
Cumulative cash flows for payback
###
###
###
###
Discounted cash flows for disc. payback
###
###
###
###
Cumulative discounted cash flows
###
###
###
###
How the Simulation Works
Column input cell to "trick" Excel into updating random variables in Data Table:
1
Don't change th
NOWC
t
= 15%(Revenues
t+1
)
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 ins
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 N
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
Table (the variable to be changed in each row) to numbers from 1-100. We don't really use these numbers anywhere in the analysis, bu
Data Table to treat these as the Column inputs, Excel will recalculate all items in the Data Table, including the random inputs and the re
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
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
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 rando
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 row

#### You've reached the end of your free preview.

Want to read all 547 pages?

- Fall '09
- BRUM
- Depreciation, Corporate Finance, data tables