Transcript 07ax

Session 7a
Overview
Monte Carlo Simulation
– Basic concepts and history
Excel Tricks
– RAND(), IF, Boolean
Crystal Ball
– Probability Distributions
• Normal, Gamma, Uniform, Triangular
– Assumption and Forecast cells
– Run Preferences
– Output Analysis
Examples
– Coin Toss, TSB Account, Preventive Maintenance, NPV
Decision Models -- Prof. Juran
2
Decision Models: 2 Modules
• Module I: Optimization
• Module II: Spreadsheet Simulation
Decision Models -- Prof. Juran
3
Monte Carlo Simulation
Using theoretical probability distributions to model
real-world situations in which randomness is an
important factor.
Differences from other spreadsheet models
•No optimal solution
•Explicit modeling of random variables in special cells
•Many trials, all with different results
•Objective function studied using statistical inference
Decision Models -- Prof. Juran
4
Decision Models -- Prof. Juran
5
Decision Models -- Prof. Juran
6
Decision Models -- Prof. Juran
7
Decision Models -- Prof. Juran
8
Decision Models -- Prof. Juran
9
Decision Models -- Prof. Juran
10
Decision Models -- Prof. Juran
11
Origins of Monte Carlo
Stanislaw M. Ulam (1909 - 1984)
Nicholas Metropolis (1915-1999)
Decision Models -- Prof. Juran
12
Example: Coin Toss
Imagine a game where you flip a coin once.
If you get “heads”, you win $3.00
If you get “tails”, you lose $1.00
The coin is not fair; it lands on “heads” 35% of the time
What is the expected value of this game?
Decision Models -- Prof. Juran
13
Simulation “By Hand”
Set up a spreadsheet model
Add an element of randomness
• Excel built-in random number generator
• Use F9 key to create repetitive iterations of the
random system (“realizations”)
Keep track of the results
Decision Models -- Prof. Juran
14
A
B
1
2 Random # 0.2002
3 Outcome
Head
4 Profit
$3.00
Decision Models -- Prof. Juran
C
D
E
=RAND()
=IF(B2<0.35,"Head","Tail")
=IF(B2<0.35,3,-1)
15
What Does =RAND() Do?
Uniform random number between 0 and 1
Never below 0; never above 1
All values between 0 and 1 are equally likely
0.35
0.65
P(X<0.35) = 0.35
Decision Models -- Prof. Juran
16
What Does =IF Do?
Evaluates a logical expression (true or false)
Gives one result for true and a different result for false
In our “coin” model, RAND and IF work together to
generate heads and tails (and profits and losses) from a
specific probability distribution
A
B
1
2 Random # 0.7438
3 Outcome
Tail
4 Profit
-$1.00
Decision Models -- Prof. Juran
C
D
E
=RAND()
=IF(B2<0.35,"Head","Tail")
=IF(B2<0.35,3,-1)
17
Some Random Results
Sample means from 15 trials:
A
B
C
1 Random # Outcome Profit
2
0.4619
Tail -$1.00
3
0.4118
Tail -$1.00
4
0.5815
Tail -$1.00
5
0.9792
Tail -$1.00
6
0.2852
Head $3.00
7
0.9064
Tail -$1.00
8
0.9855
Tail -$1.00
9
0.9988
Tail -$1.00
10
0.2206
Head $3.00
11
0.0986
Head $3.00
12
0.9696
Tail -$1.00
13
0.8026
Tail -$1.00
14
0.8189
Tail -$1.00
15
0.7137
Tail -$1.00
16
0.9258
Tail -$1.00
17
-$0.20
D
E
=AVERAGE(C2:C16)
Decision Models -- Prof. Juran
F
A
B
C
1 Random # Outcome Profit
2
0.1979
Head $3.00
3
0.9185
Tail -$1.00
4
0.4688
Tail -$1.00
5
0.6670
Tail -$1.00
6
0.0902
Head $3.00
7
0.3757
Tail -$1.00
8
0.1492
Head $3.00
9
0.4518
Tail -$1.00
10
0.8503
Tail -$1.00
11
0.1392
Head $3.00
12
0.1924
Head $3.00
13
0.0179
Head $3.00
14
0.4799
Tail -$1.00
15
0.5064
Tail -$1.00
16
0.3051
Head $3.00
17
$0.87
D
E
F
=AVERAGE(C2:C16)
18
Problems with this Model
Hitting F9 thousands of times is tedious
Keeping track of the results (and summary statistics) is
even more tedious
What if we want to simulate something other than a
uniform distribution between 0 and 1?
Decision Models -- Prof. Juran
19
Simulation with Crystal Ball
Special cells for random variables (Assumptions)
Special cells for objective functions (Forecasts)
Run Preferences
•Number of trials
•Random number seed
•Sampling method
Output Analysis
•Studying forecasts
•Extracting data
Decision Models -- Prof. Juran
20
Assumption Cell
An input random number; a building block for a
simulation model
The “Define Assumption” button:
Must be a “value” cell (a number, not a function)
Can be ANY number
Gives Crystal Ball permission to generate random
numbers in that cell according to a specific probability
distribution
Decision Models -- Prof. Juran
21
Decision Models -- Prof. Juran
22
Decision Models -- Prof. Juran
23
A
B
C
D
1
2 Random # 0.5000
3 Outcome
Tail
4 Profit
-$1.00
5
6
Decision Models -- Prof. Juran
24
Assumption Cell
Keeps track of important outcome cells during the simulation
run. Select the “profit” cell and click on the forecast button.
You can enter a name and units if you want. Then click OK.
Decision Models -- Prof. Juran
25
Decision Models -- Prof. Juran
26
Now click on the run preferences button.
Decision Models -- Prof. Juran
27
Crystal Ball has buttons for controlling the simulation run,
similar to the buttons on a DVD player:
Play
Stop
Rewind
Single Step
Decision Models -- Prof. Juran
28
The Crystal Ball toolbar:
The “play” button
Decision Models -- Prof. Juran
29
Decision Models -- Prof. Juran
30
The simulation will run until it reaches the
maximum number of trials, at which point it will
display this message:
Decision Models -- Prof. Juran
31
Conclusions
Crystal Ball performs the tedious functions of running a
simulation in a spreadsheet model
We can use statistical inference (confidence intervals and
hypothesis tests) to study the results
The results are only estimates, but they can be very precise
estimates
Much depends on the validity of our model; how well it
represents the real-world system we really want to learn
about
Decision Models -- Prof. Juran
32
Random Number Generator
Built into Excel
• RAND() function
• Tools – Data Analysis – Random Number
Generation
Built into all simulation software
Not really random; correctly called pseudo-random
Decision Models -- Prof. Juran
33
Random Number Generator
Needs a “seed” to get started
Each random number becomes the “seed” for its successor
Decision Models -- Prof. Juran
34
Example: Tax-Saver Benefit
A TSB (Tax Saver Benefit) plan allows you to put
money into an account at the beginning of the calendar
year that can be used for medical expenses. This
amount is not subject to federal tax — hence the phrase
TSB.
Decision Models -- Prof. Juran
35
As you pay medical expenses during the year, you are
reimbursed by the administrator of the TSB until the TSB
account is exhausted. From that point on, you must pay
your medical expenses out of your own pocket. On the
other hand, if you put more money into your TSB than
the medical expenses you incur, this extra money is lost to
you.
Your annual salary is $50,000 and your federal income tax
rate is 30%.
Decision Models -- Prof. Juran
36
Assume that your medical expenses in a year are
normally distributed with mean $2000 and standard
deviation $500.
Build a Crystal Ball model in which the output is the
amount of money left to you after paying taxes,
putting money in a TSB, and paying any extra
medical expenses. Experiment with the amount of
money put in the TSB, and identify an amount that is
approximately optimal.
Decision Models -- Prof. Juran
37
First, we set up a spreadsheet to organize all of the
information. In particular, we want to make sure we’ve
identified the decision variable (how much to have taken
out of our salary and put into the TSB account — here in
cell B1), the objective (Maximize net income — after tax,
and after extra medical expenses not covered by the TSB —
which we have here in cell B14), and the random variable
(in this case the amount of medical expenses — here in cell
B9).
Decision Models -- Prof. Juran
38
Decision Models -- Prof. Juran
39
Note (this is important): We will never get a simulation
model to tell us directly what is the optimal value of the
decision variable.
We will try different values (here we have arbitrarily
started with $2000 in cell B1) and see how the objective
changes.
Through educated trial-and-error, we will eventually
come to some conclusion about what is the best amount
of money to put into the TSB account.
Decision Models -- Prof. Juran
40
Now we add the element of randomness by making B9 into
an assumption cell. First, enter the mean and standard
deviation for the medical expenses random variable (we
put them in cells B16 and B17, respectively).
A
16 Mean
17 Standard Deviation
Decision Models -- Prof. Juran
B
$ 2,000.00
$
500.00
41
Select the assumption cell B9 and click on the
assumption button.
Select “Normal” and click “OK”.
Decision Models -- Prof. Juran
42
We are presented with a screen where we can enter the parameters
for this normal distribution. We can enter values (2000 and 500) or
we can use cell references. Here we enter the cell references.
Decision Models -- Prof. Juran
43
A
8
9
10
11
12
Total Medical Expenses
Amount in TSB
Expenses Not Covered (Must Be Paid Out-Of-Pocket)
Money Left Over in TSB (Lost)
Decision Models -- Prof. Juran
B
C
$ 2,339.74
$ 3,000.00
$
$
660.26
44
Now we need to tell Crystal Ball to keep track of our objective
cell during all of our simulation runs, so we can see its mean
and standard deviation over many trials. Select the net
income cell B14 and click on the forecast button.
You can enter a name and units if you want. Then click OK.
Decision Models -- Prof. Juran
45
A
13
14 Net Income After Medical Expenses (Objective)
15
Decision Models -- Prof. Juran
B
C
$ 32,900.00
46
Now click on the run preferences button.
Decision Models -- Prof. Juran
47
Decision Models -- Prof. Juran
48
Now click on the “start” button:
The simulation will run until it reaches the maximum number of
trials, at which point it will display this message:
Decision Models -- Prof. Juran
49
Decision Models -- Prof. Juran
50
Decision Models -- Prof. Juran
51
To see the summary statistics from the 1000 simulations, we click
on the extract data button.
Select one of the options (here we pick statistics):
Decision Models -- Prof. Juran
52
We see the following information appear in a new
worksheet:
This gives us everything we need to perform analysis such
as making a confidence interval for the true mean net
income when we put $2000 into the TSB account.
Decision Models -- Prof. Juran
53
The formula for a 95% confidence interval:
X  z 2
s
n
To perform normal distribution calculations in Excel, we
use the NORMSDIST and NORMSINV functions.
Decision Models -- Prof. Juran
54
NORMSDIST
A
1
2 Lower Tail Area
3 z-value
Decision Models -- Prof. Juran
B
0.9332
1.500
C
D
E
=NORMSDIST(B3)
55
NORMSINV
A
1
2 Lower Tail Area
3 z-value
Decision Models -- Prof. Juran
B
0.05
-1.645
C
D
=NORMSINV(B2)
56
95% Confidence Interval for the Population Mean
X  z 2
Decision Models -- Prof. Juran
s
n
57
Unfortunately, we can’t tell whether $2000 is the optimal amount
without trying many other possible amounts. This could entail a long
and tedious series of simulation runs, but fortunately it is possible to
test many values at once.
We set up numerous columns in the worksheet, so that we can
perform simulation experiments on many possible TSB amounts
simultaneously:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
A
TSB Amount (Decision Variable)
$
B
1,000
$
D
1,500
$
E
1,750
$
F
2,000
$
G
2,250
$
H
2,500
$
I
2,750
$
J
3,000
$ 2,250.00
$
425.05
$
-
$ 2,500.00
$
175.05
$
-
$ 2,750.00
$
$
74.95
$ 3,000.00
$
$
324.95
Net Income After Medical Expenses (Objective)
$ 32,624.95
$ 32,699.95
$ 32,774.95
$ 32,849.95
$ 32,924.95
$ 32,999.95
$ 33,074.95
$ 33,075.00
$ 32,900.00
Mean
Standard Deviation
$ 2,000.00
$
500.00
$
$
$
50,000
30%
47,250
14,175
33,075
$
$ 2,000.00
$
675.05
$
-
$
$
$
50,000
30%
47,500
14,250
33,250
$
$ 1,750.00
$
925.05
$
-
$
$
$
50,000
30%
47,750
14,325
33,425
$
$ 1,500.00
$ 1,175.05
$
-
$
$
$
50,000
30%
48,000
14,400
33,600
$
$ 1,250.00
$ 1,425.05
$
-
$
$
$
50,000
30%
48,250
14,475
33,775
$
$ 2,675.05
$ 1,000.00
$ 1,675.05
$
-
$
$
$
50,000
30%
48,500
14,550
33,950
$
Total Medical Expenses
Amount in TSB
Expenses Not Covered (Must Be Paid Out-Of-Pocket)
Money Left Over in TSB (Lost)
$
$
$
50,000
30%
48,750
14,625
34,125
$
$
Decision Models -- Prof. Juran
$
C
1,250
Annual Salary
Tax Rate
After TSB Income
Taxes Owed
Net Income Before Medical Expenses
$
$
$
50,000
30%
49,000
14,700
34,300
$
$
$
$
50,000
30%
47,000
14,100
32,900
58
Here we have set up different columns, each with its
own possible amount to be put into the TSB account in
row 1. In row 14 we have the net income forecast for
each possible value of the decision variable.
To make the output easy to interpret, we had to select
each forecast cell, click on the “define forecast” button,
and give each of them a logical name. This is a pain, but
it pays off later.
Decision Models -- Prof. Juran
59
Now we re-run the simulation, click on extract data, select
“all” forecasts, and get summary statistics for all of our
possible values for the TSB:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A
Statistics
Trials
Mean
Median
Mode
Standard Deviation
Variance
Skewness
Kurtosis
Coeff. of Variability
Range Minimum
Range Maximum
Range Width
Mean Std. Error
B
C
D
E
F
G
$1000
$1250
$1500
$1750
$2000
$2250
1000
1000
1000
1000
1000
1000
$33,314.57 $33,378.53 $33,425.38 $33,440.97 $33,411.55 $33,334.34
$33,324.43 $33,399.43 $33,474.43 $33,549.43 $33,600.00 $33,425.00
$34,300.00 $34,125.00 $33,950.00 $33,775.00 $33,600.00 $33,425.00
$481.84
$462.07
$422.72
$359.41
$276.89
$190.27
$232,173.49 $213,508.34 $178,691.94 $129,178.73 $76,666.31 $36,202.62
-0.07
-0.25
-0.54
-0.97
-1.59
-2.55
2.62
2.49
2.57
3.21
5.06
9.77
0.01
0.01
0.01
0.01
0.01
0.01
$31,886.47 $31,961.47 $32,036.47 $32,111.47 $32,186.47 $32,261.47
$34,300.00 $34,125.00 $33,950.00 $33,775.00 $33,600.00 $33,425.00
$2,413.53
$2,163.53
$1,913.53
$1,663.53 $1,413.53 $1,163.53
$15.24
$14.61
$13.37
$11.37
$8.76
$6.02
Decision Models -- Prof. Juran
H
$2500
1000
$33,214.52
$33,250.00
$33,250.00
$115.05
$13,235.87
-4.17
22.23
0.00
$32,336.47
$33,250.00
$913.53
$3.64
I
$2750
1000
$33,064.00
$33,075.00
$33,075.00
$61.44
$3,774.51
-6.68
51.79
0.00
$32,411.47
$33,075.00
$663.53
$1.94
J
$3000
1000
$32,897.02
$32,900.00
$32,900.00
$26.15
$683.68
-10.98
138.39
0.00
$32,486.47
$32,900.00
$413.53
$0.83
K
$3250
1000
$32,724.66
$32,725.00
$32,725.00
$6.62
$43.80
-20.99
466.46
0.00
$32,561.47
$32,725.00
$163.53
$0.21
60
TSB Simulation Analysis Results
$33,500
$33,400
Mean Net Income
$33,300
$33,200
$33,100
$33,000
$32,900
$32,800
$32,700
$32,600
$32,500
$1000
$1250
$1500
$1750
$2000
$2250
$2500
$2750
$3000
$3250
Amount Put Into TSB Account
Decision Models -- Prof. Juran
61
What if we assume that annual medical expenses
follow a gamma distribution?
To have the same mean and standard deviation as
our normal distribution, we would use $0 for the
location parameter, $125 for the scale parameter
(sometimes symbolized with β), and 16 for the shape
parameter (sometimes symbolized with ).
Decision Models -- Prof. Juran
62
Gamma vs. Normal
Normal Distribution
Probability
Gamma Distribution
$0
$1,000
$2,000
$3,000
$4,000
$5,000
Medical Expense
Decision Models -- Prof. Juran
63
Decision Models -- Prof. Juran
64
TSB Simulation Analysis Results
$33,500
$33,400
Mean Net Income
$33,300
$33,200
$33,100
$33,000
$32,900
$32,800
$32,700
$32,600
$32,500
$1000
$1250
$1500
$1750
$2000
$2250
$2500
$2750
$3000
$3250
Amount Put Into TSB Account
Decision Models -- Prof. Juran
65
Conclusions
• The best amount to put into the TSB is
apparently about $1,750 per year.
• This result is robust over different
distributions of medical costs.
• This result is based on sample statistics, not
known population parameters.
• We have confidence in these sample statistics
because of the large sample size (1,000).
Decision Models -- Prof. Juran
66
Probability Trick: Uniform to Normal
A
1 Parameters for Uniform
2
3
4
5
0.5000
6
7
0.0000
8
9 Parameters for Normal
10
11
12
13 Normal
14
0.6659%
B
C
Min
D
Max
0
1
Uniform
=NORMSINV(A5)
Standard Normal
Mean
StDev
0.6659%
2.9679%
=B11+A7*C11
(Forecast)
Start with template file: s-uniform-normal-0.xls
Decision Models -- Prof. Juran
67