Transcript Spreadsheet Modeling & Decision Analysis:

Spreadsheet Modeling &
Decision Analysis
A Practical Introduction to
Management Science
4th edition
Cliff T. Ragsdale
Chapter 12
Introduction to Simulation
Using Crystal Ball
12-2
Introduction to Simulation
• In many spreadsheets, the value for one or
more cells representing independent variables
is unknown or uncertain.
• As a result, there is uncertainty about the
value the dependent variable will assume:
Y = f(X1, X2, …, Xk)
• Simulation can be used to analyze these
types of models.
12-3
Random Variables & Risk
• A random variable is any variable whose value cannot
be predicted or set with certainty.
• Many “input cells” in spreadsheet models are actually
random variables.
– the future cost of raw materials
– future interest rates
– future number of employees in a firm
– expected product demand
• Decisions made on the basis of uncertain information
often involve risk.
• “Risk” implies the potential for loss.
12-4
More examples on risk:
• If there are no variation (i.e., risk) in
investment, what would happen?
• What do insurance companies offer?
• As you are applying graduate study in U.S.,
what are the main factors you consider? For
example, the % of getting admission varies
a lot or holds steady.
12-5
Why Analyze Risk?
• Plugging in expected values for uncertain cells tells us
nothing about the variability of the performance
measure we base decisions on.
• Suppose an $1,000 investment is expected to return
$10,000 in two years. Would you invest if...
– the outcomes could range from $9,000 to $11,000?
– the outcomes could range from -$30,000 to $50,000?
• Alternatives with the same expected value may involve
different levels of risk.
12-6
SUPPOSE YOU COULD CHOOSE TO PLAY
ONE OF TWO GAMES: (Are you rational?)
Game A
There is a
10% chance that
you will win
$1,000,000….and a
90% chance that
you will win
$50,000.
Game B
There is a
10% chance that
you will win
$2,000,000….and a
90% chance that
you will win
$0.
Which would you choose?
12-7
Game A
There is a
10% chance that
you will win
$1,000,000….and a
90% chance that
you will win
$50,000.
expected value =
(.1)(1,000,000)+
(.9)(50,000) =
145,000
Game B
There is a
10% chance that
you will win
$2,000,000….and a
90% chance that
you will win
$0.
expected value =
(.1)(2,000,000)+
(.9)(0) =
200,000
12-8
• Question: Why do people tend to choose the
action with lower expected value?
 My explanation is that people are willing
to sacrifice some in the average value, in
exchange for a more stable result.
 Question: What are the variations in the
two games described above?
Note: The previous game is called “Paradox
of Alais.”
12-9
Methods of Risk Analysis
• Best-Case/Worst-Case Analysis
• What-if Analysis
• Simulation
12-10
Best-Case/Worst-Case Analysis
• Best case - plug in the most optimistic
values for each of the uncertain cells.
• Worst case - plug in the most pessimistic
values for each of the uncertain cells.
• This is easy to do but tells us nothing
about the distribution of possible
outcomes within the best and worst-case
limits.
12-11
Possible Performance Measure
Distributions Within a Range
worst case
best case
worst case
best case
worst case
best case
worst case
best case
12-12
What-If Analysis
• Plug in different values for the uncertain cells
and see what happens.
• This is easy to do with spreadsheets.
• Problems:
– Values may be chosen in a biased way.
– Hundreds or thousands of scenarios may be
required to generate a representative distribution.
– Does not supply the tangible evidence (facts and
figures) needed to justify decisions to
management.
12-13
Simulation
• Resembles automated what-if analysis.
• Values for uncertain cells are selected in an
unbiased manner.
• The computer generates hundreds (or thousands)
of scenarios.
• We analyze the results of these scenarios to
better understand the behavior of the
performance measure.
• This allows us to make decisions using solid
empirical evidence.
12-14
12-15
History of Monte Carlo simulation
• How did Monte Carlo simulation get its
name?
• The name and the systematic development
of Monte Carlo methods dates from about
1940’s.
• There are however a number of isolated and
undeveloped instances on much earlier
occasions.
12-16
History of Monte Carlo simulation (cont.)
• In the second half of the nineteenth century a
number of people performed experiments, in
which they threw a needle in a haphazard
manner onto a board ruled with parallel
straight lines and inferred the value of PI
=3.14… from observations of the number of
intersections between needle and lines.
• In 1899 Lord Rayleigh showed that a onedimensional random walk without absorbing
barriers could provide an approximate
solution to a parabolic differential equation.
12-17
Buffon's original form was to drop a needle of length
L at random on grid of parallel lines of spacing D.
For L less than or equal D we obtain
P(needle intersects the grid) = 2 • L / PI • D.
If we drop the needle N times and count R
intersections we obtain
P = R / N,
12-18
PI = 2 • L • N / R • D.
12-19
12-20
12-21
12-22
12-23
History of Monte Carlo simulation (cont.)
• In early part of the twentieth century, British
statistical schools indulged in a fair amount of
unsophisticated Monte Carlo work.
• In 1908 Student (W.S. Gosset) used
experimental sampling to help him towards
his discovery of the distribution of the
correlation coefficient.
• In the same year Student also used sampling
to bolster his faith in his so-called tdistribution, which he had derived by a
somewhat shaky and incomplete theoretical
12-24
analysis.
Student - William Sealy Gosset (1876 - 1937)
This birth-and-death process is suffering from labor
pains; it will be the death of me yet. (Student Sayings)
12-25
A. N. Kolmogorov (1903-1987)
In 1931 Kolmogorov showed the relationship
between Markov stochastic processes and certain
integro-differential equations.
12-26
History of Monte Carlo simulation (cont.)
• The real use of Monte Carlo methods as a research
tool stems from work on the atomic bomb during the
second world war.
• This work involved a direct simulation of the
probabilistic problems concerned with random
neutron diffusion in fissile material; but even at an
early stage of these investigations, von Neumann and
Ulam refined this particular "Russian roulette" and
"splitting" methods. However, the systematic
development of these ideas had to await the work of
Harris and Herman Kahn in 1948.
• About 1948 Fermi, Metropolis, and Ulam obtained
Monte Carlo estimates for the eigenvalues of 12-27
Schrodinger equation.
John von Neumann (1903-1957)
12-28
History of Monte Carlo simulation (cont.)
• In about 1970, the newly developing theory of
computational complexity began to provide a more
precise and persuasive rationale for employing the
Mont Carlo method.
• Karp (1985) shows this property for estimating
reliability in a planar multiterminal network with
randomly failing edges.
• Dyer (1989) establish it for estimating the volume of
a convex body in M-dimensional Euclidean space.
• Broder (1986) and Jerrum and Sinclair (1988)
establish the property for estimating the permanent of
a matrix or, equivalently, the number of perfect
matchings in a bipartite graph.
12-29
Example: Hungry Dawg Restaurants
• Hungry Dawg is a growing restaurant chain with a
self-insured employee health plan.
• Covered employees contribute $125 per month to
the plan, Hungry Dawg pays the rest.
• The number of covered employees changes from
month to month.
• The number of covered employees was 18,533 last
month and this is expected to increase by 2% per
month.
• The average claim per employee was $250 last
month and is expected to increase at a rate of 1%
per month.
12-30
Implementing the Model
See file Fig12-2.xls
12-31
Questions About the Model
• Will the number of covered employees really
increase by exactly 2% each month?
• Will the average health claim per employee
really increase by exactly 1% each month?
• How likely is it that the total company cost
will be exactly $36,125,850 in the coming
year?
• What is the probability that the total company
cost will exceed, say, $38,000,000?
12-32
Using simulation, the management
wants to determine the average time
a customer must wait for service!!
12-33
Simulation
• To properly assess the risk inherent in the
model we need to use simulation.
• Simulation is a 4 step process:
1) Identify the uncertain cells in the model.
2) Implement appropriate RNGs for each uncertain
cell.
3) Replicate the model n times, and record the value
of the bottom-line performance measure.
4) Analyze the sample values collected on the
performance measure.
12-34
What is Crystal Ball?
• Crystal Ball is a spreadsheet add-in that simplifies
spreadsheet simulation.
• A 120-day trial version of Crystal Ball is on the
CD-ROM accompanying this book.
• It provides:
– functions for generating random numbers
– commands for running simulations
– graphical & statistical summaries of simulation data
• For more info see:http://www.decisioneering.com
12-35
Random Number Generators (RNGs)
• A RNG is a mathematical function that
randomly generates (returns) a value from a
particular probability distribution.
• We can implement RNGs for uncertain cells to
allow us to sample from the distribution of
values expected for different cells.
12-36
How RNGs Work
• The RAND( ) function returns uniformly distributed
random numbers between 0.0 and 0.9999999.
• Suppose we want to simulate the act of tossing a
fair coin.
• Let 1 represent “heads” and 2 represent “tails”.
• Consider the following RNG:
=IF(RAND( )<0.5,1,2)
12-37
Simulating the Roll of a Die
• We want the values 1, 2, 3, 4, 5 & 6 to occur
randomly with equal probability of occurrence.
• Consider the following RNG:
=INT(6*RAND())+1
If 6*RAND( ) falls
in the interval:
0.0 to 0.999
1.0 to 1.999
2.0 to 2.999
3.0 to 3.999
4.0 to 4.999
5.0 to 5.999
INT(6*RAND( ))+1
returns the value:
1
2
3
4
5
6
12-38
Some of the RNGs Provided
By Crystal Ball
Distribution
RNG Function
Binomial
Custom
Gamma
Poisson
Continuous Uniform
Exponential
Normal
Triangular
CB.Binomial(p,n)
CB.Custom(range)
CB.Gamma(loc,shape,scale,min,max)
CB.Poisson(l)
CB.Uniform(min,max)
CB.Exponential(l)
CB.Normal(m,s,min,max)
CB.Triang(min, most likely, max)
12-39
Examples of Discrete Probability Distributions
CB.Binomial(0.2,10)
CB.Binomial(0.8,10)
CB.Binomial(0.5,10)
0.40
0.40
0.40
0.30
0.30
0.30
0.20
0.20
0.20
0.10
0.10
0.10
0.00
0.00
0
1
2
3
4
5
6
7
8
9 10
0.00
0
1
2
3
4
5
6
7
8
9 10
0
0.50
0.50
0.40
0.40
0.30
0.30
0.20
20
21
22
20
23
21
5
6
7
8
9 10
0.40
0.40
0.30
0.30
0.30
0.20
0.20
0.20
0.10
0.10
0.10
0.00
0.00
5
6
7
23
CB.Poisson(8)
0.40
4
22
CB.Poisson(2)
CB.Poisson(0.9)
3
4
0.00
0.00
2
3
0.20
0.10
0.10
1
2
INT(CB.Uniform(20,24))
CB.Custom(range)
0
1
8
9 10
0.00
0
1
2
3
4
5
6
7
8
9 10
0
2
4
6
8 10 12 14 16 18 20
12-40
Examples of Continuous Probability Distributions
CB.Normal(20,1.5)
CB.Normal(20,3)
0.30
0.25
0.20
0.15
0.10
0.05
0.00
CB.Normal(20,3,15,23)
0.30
0.25
0.20
0.15
0.10
0.05
0.00
12
14
16
18
20
22
24
26
28
0.30
0.25
0.20
0.15
0.10
0.05
0.00
12
14
16
CB.Gamma(0,1,2)
18
20
22
24
26
28
CB.Exponential(5)
CB.Gamma(0,5,2)
0.50
0.40
0.30
0.20
0.10
0.00
0.50
0.40
0.30
0.20
0.10
0.00
0.50
0.40
0.30
0.20
0.10
0.00
0
2
4
6
8
10
12
0
CB.Triangular(3,4,8)
0.50
0.40
0.30
0.20
0.10
0.00
2.5
3.5
4.5
5.5
6.5
11 13 15 17 19 21 23 25 27 29
7.5
2
4
6
8
10 12 14 16 18
0
CB.Triangular(3,7,8)
8.5
0.50
0.40
0.30
0.20
0.10
0.00
2.5
2
4
6
8
10
CB.Uniform(40,60)
0.15
0.10
0.05
3.5
4.5
5.5
6.5
7.5
8.5
0.00
30.0
40.0
50.0
60.0
70.0
12-41
Discrete vs. Continuous
Random Variables
• A discrete random variable may assume one of a
fixed set of (usually integer) values.
– Example: The number of defective tires on a new
car can be 0, 1, 2, 3, or 4.
• A continuous random variable may assume one
of an infinite number of values in a specified
range.
– Example: The amount of gasoline in a new car can
be any value between 0 and the maximum
capacity of the fuel tank.
12-42
Preparing the Model for Simulation
• Suppose we analyzed historical data and
found that:
– The change in the number of covered
employees each month is uniformly distributed
between a 3% decrease and a 7% increase.
– The average claim per employee follows a
normal distribution with mean increasing by
1% per month and a standard deviation of $3.
12-43
Revising & Simulating the Model
See file Fig12-8.xls
12-44
The Uncertainty of Sampling
• The replications of our model represent a sample from
the (infinite) population of all possible replications.
• Suppose we repeated the simulation and obtained a
new sample of the same size.
Q: Would the statistical results be the same?
A: No!
• As the sample size (# of replications) increases, the
sample statistics converge to the true population
values.
• We can also construct confidence intervals for a
number of statistics...
12-45
Constructing a Confidence Interval for the
True Population Mean
95% Lower Confidence Limit = y-1.96 
s
n
95% Upper Confidence Limit = y  1.96 
s
n
where:
y  the sample mean
s = the sample standard deviation
n = the sample size (and n  30)
Note that as n increases, the width of
the confidence interval decreases.
12-46
Constructing a Confidence Interval for the
True Population Proportion
95% Lower Confidence Limit = p-1.96 
95% Upper Confidence Limit = p  1.96 
p (1  p )
n
p (1  p )
n
where:
p  the proportion of the sample that is less than some value Yp
n = the sample size (and n  30)
Note again that as n increases, the width
of the confidence interval decreases.
12-47
Additional Uses of Simulation
• Simulation is used to describe the behavior,
distribution and/or characteristics of some
bottom-line performance measure when values
of one or more input variables are uncertain.
• Often, some input variables are under the
decision makers control.
• We can use simulation to assist in finding the
values of the controllable variables that cause
the system to operate optimally.
• The following examples illustrate this process.
12-48
An Reservation Management Example:
Piedmont Commuter Airlines
• PCA Flight 343 flies between a small regional airport
and a major hub.
• The plane has 19 seats & several are often vacant.
• Tickets cost $150 per seat.
• There is a 0.10 probability of a sold seat being vacant.
• If PCA overbooks, it must pay an average of $325 for
any passengers that get “bumped”.
• Demand for seats is random, as follows:
Demand
14 15 16 17 18 19 20 21 22 23 24 25
Probability
.03 .05 .07 .09 .11 .15 .18 .14 .08 .05 .03 .02
• What is the optimal number of seats to sell?
12-49
Random Number Seeds
• RNGs can be “seeded” with an initial value that
causes the same series of “random” numbers to
generated repeatedly.
• This is very useful when searching for the optimal
value of a controllable parameter in a simulation
model (e.g., # of seats to sell).
• By using the same seed, the same exact scenarios
can be used when evaluating different values for
the controllable parameter.
• Differences in the simulation results then solely
reflect the differences in the controllable
parameter – not random variation in the scenarios
12-50
used.
Implementing & Simulating the Model
See file Fig12-19.xls
12-51
Important Software Note
• OptQuest requires at least one Assumption cell to
be defined in the workbook being optimized.
• If you use Crystal Ball's built-in RNG functions to
implement your model (as done in this text) your
workbook may not contain any Assumption cells.
• To circumvent this limitation of OptQuest, simply
define one Assumption cell in any unused cell in
your workbook.
• This Assumption cell will have no influence on the
results of the model, but its presence is required
for OptQuest to optimize the Decision cells in your
model.
12-52
Inventory Control Example:
Millennium Computer Corporation (MCC)
•
•
•
•
•
MCC is a retail computer store facing fierce competition.
Stock outs are occurring on a popular monitor.
The current reorder point (ROP) is 28.
The current order size is 50.
Daily demand and order lead times vary randomly, viz.:
Units Demanded: 0
1
2
3
4
5
6
7
8
9 10
Probability:
0.01 0.02 0.04 0.06 0.09 0.14 0.18 0.22 0.16 0.06 0.02
Lead Time (days): 3
4
5
Probability:
0.2 0.6 0.2
• MCC’s owner wants to determine the ROP and order size
that will provide a 98% service level while minimizing
average inventory.
12-53
Implementing & Simulating the Model
See file Fig12-27.xls
12-54
A Project Selection Example:
TRC Technologies
• TRC has $2 million to invest in the following new R&D
projects.
Revenue Potential
Project
1
2
3
4
5
6
7
8
Initial Cost Prob. Of
($1,000s) Success
$250
0.9
$650
0.7
$250
0.6
$500
0.4
$700
0.8
$30
0.6
$350
0.7
$70
0.9
($1,000s)
Min Likely Max
$600 $750 $900
$1250 $1500 $1600
$500 $600 $750
$1600 $1800 $1900
$1150 $1200 $1400
$150 $180 $250
$750 $900 $1000
$220 $250 $320
• TRC wants to select the projects that will maximize the
firm’s expected profit.
12-55
Implementing & Simulating the Model
See file Fig12-34.xls
12-56
Risk Management
• The solution that maximizes the
expected profit also poses a significant
(10%) risk of losing money.
• Suppose TRC would prefer a solution
that maximizes the chances of earning
at least $1 million while incurring at
most a 5% chance of losing money.
• We can use OptQuest to find such a
solution...
12-57
End of Chapter 12
12-58