3.80
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,649
10,956
12,440
Sales price per unit
$1.36
$1.41
$1.47
Variable cost per unit (excl. depr.)
$1.17
$1.21
$1.25
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
$13,125
$15,499
$18,302
Variable costs
= Units × Cost/unit
$11,333
$13,255
$15,501
Nonvariable costs (excluding depreciation)
$2,102
$2,166
$2,231
Depreciation
$2,697
$3,597
$1,199
Earnings before interest and taxes (EBIT)
−$3,008
−$3,518
−$628
Taxes on operating profit
(40% rate)
−$1,288
−$1,507
−$269
Net operating profit after taxes
−$1,720
−$2,012
−$359
Add back depreciation
$2,697
$3,597
$1,199
Equipment purchases
−$8,093
Profit from salvage value
Cash flow due to tax on salvage value (40% rate)
Cash flow due to change in WC
−$1,969
−$356
−$421
−$497
Opportunity cost, after taxes
$0
$0
$0
$0
After-tax cannibalization or complementary effect
$0
$0
$0
Project net cash flows: Time Line
−$10,061
$621
$1,165
$343
Project Evaluation Measures
NPV
-$5,174
IRR
-11.43%
MIRR
-8.17%
Profitability index
0.49
Payback
#VALUE!
Discounted payback
#VALUE!
Calculations for Payback
Year:
0
1
2
3
Cumulative cash flows for payback
###
###
###
###
Discounted cash flows for disc. payback
###
###
###
###
Cumulative discounted cash flows
###
###
###
###
sold in Year 1 and annual change in
units sold in later years:
r
=
NOWC
t
= 15%(Revenues
t+1
)

How the Simulation Works
Column input cell to "trick" Excel into updating random variables in Data Table:
1
Don't change th
Note: If results are 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
100
Input Variables
Average
$8,093
9,649
14%
$1.50
$1.07
$2,128
Standard deviation
$0
0
6%
$0.19
$0.08
$153
Maximum
$8,093
9,649
28%
$1.96
$1.24
$2,638
Minimum
$8,093
9,649
−1%
$0.95
$0.90
$1,710
Correlation with unit sales
−0%
NPV
We use a Data Table to perform the simulation (the Data Table is below shaded in lavender). When the Data Table is updated
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 t
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.)
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 num
anywhere in the analysis, but if we tell the Data Table to treat these as the Column inputs, Excel will recalculate all items in th
including the random inputs and the resulting NPV. In other words, we "trick" Excel into doing a simulation. We tell Excel to i
the Column inputs in the Data Table into the cell immediately below this box. This cell isn't linked to anything else, but each t
updates a row of the Data Table, all the random values will be updated.