random variable into a normal random variable with the desired mean and standard deviation. To see updated values, hit th
Panel A: Va
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 from Sc
Values for
Column E
used for
Figure 7 in
printed book.
See this cell's
Comment.
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
Must
be checked to put random variable in data table for simulation
A
B
C
D
E
F
G
H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

Units sold, Year 1
10,000
1,061
1.012
11,074
-0.045
10,000
%
in units sold, after Year 1
Δ
15.00%
7.07%
0.904
21.39%
0.198
15.00%
Sales price per unit, Year 1
$1.50
$0.18
−0.106
$1.48
0.837
$1.50
%
in sales price, after Year 1
Δ
—
—
4.00%
4.00%
Var. cost per unit (VC), Year 1
$1.07
$0.07
−0.165
$1.06
-0.706
$1.07
%
in VC, after Year 1
Δ
—
—
3.00%
3.00%
Nonvar. cost (Non-VC), Year 1
$2,120
$148
−1.479
$1,900
0.277
$2,120
%
in Non-VC, after Year 1
Δ
—
—
3.00%
3.00%
Project WACC
—
—
10.00%
10.00%
Tax rate
40.00%
7.07%
−1.686
28.08%
0.494
40.00%
NOWC as % of next year's sales
—
—
15.00%
15.00%
0.65
Key Results
Key Results Based on Current Trial
Base
NPV
$4,340
$1,048
IRR
23.31%
13.79%
MIRR
19.90%
12.78%
PI
1.41
1.10
Payback
3.16
3.39
Discounted payback
$3.44
$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
11,074
13,442
16,318
Sales price per unit
$1.48
$1.54
$1.60
Variable cost per unit (excl. depr.)
$1.06
$1.09
$1.12
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
$16,404
$20,709
$26,144
Variable costs
= Units × Cost/unit
$11,719
$14,653
$18,321
Nonvariable costs (excluding depreciation)
$1,900
$1,957
$2,016
Depreciation
$2,695
$3,594
$1,198
Earnings before interest and taxes (EBIT)
$89
$504
$4,610
Taxes on operating profit
(40% rate)
$25
$142
$1,294
Net operating profit after taxes
$64
$363
$3,315
Add back depreciation
$2,695
$3,594
$1,198
Equipment purchases
−$8,087
Salvage value
Cash flow due to tax on salvage value (40% rate)
Cash flow due to change in WC
−$2,461
−$646
−$815
−$1,029
Opportunity cost, after taxes
$0
$0
$0
$0
After-tax cannibalization or complementary effect
$0
$0
$0
Project net cash flows: Time Line
−$10,547
$2,113
$3,142
$3,484
Project Evaluation Measures
NPV
$4,340
IRR
23.31%
MIRR
19.90%
Profitability index
1.41
Payback
3.16
Assumed correlation between units
sold in Year 1 and annual change in
units sold in later years:
r
=
NOWC
t
= 15%(Revenues
t+1
)
A
B
C
D
E
F
G
H
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

Discounted payback
3.44
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
Note: If results are all zeros, go back to row 17 and "check" the box by clicking it with your cursor.

Want to read all 383 pages?

Want to read all 383 pages?

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

Want to read all 383 pages?

- Fall '16
- Depreciation, sales price