Transcript HCM540-Modeling-2-MonteCarloSim

Monte-Carlo
Simulation
Simulation with
Spreadsheets
1
Monte-Carlo Simulation
• A method for explicitly modeling uncertainty in a decision
support model such as the spreadsheets we’ve built
• Descriptive – estimate probability distribution of key model
outputs
– Goes beyond expected values and point estimates
• Quasi-static model – often doesn’t really capture detailed flow of
events over time
– Discrete event simulation used for this – we’ll do later
• Often called Risk Analysis
– widely used in financial services industry
• @RISK and Crystal Ball
– products that facilitate Monte-Carlo simulation from within
spreadsheets
• Facilitates comparing multiple scenarios over multiple
performance measures
– it’s really a way of running controlled experiments
– like most experiments, output must be analyzed statistically
2
Why Important to Model
Uncertainty?
• The world is uncertain
• Replacing random quantities with averages or
single “guesstimates” can be dangerous
– The Flaw of Averages
• Allows prediction of distribution of results
– Not just one predicted number or outcome
• Sensitivity analysis of outputs to inputs
– Which inputs really affect the outputs?
Useful Documents
• Distributions, Simulation, and Excel
Functions
– This is the “Distributions, Simulation, and
Excel Functions” document created by Prof.
D. Doane. I’ve posted it on the web in Session
5.
• [email protected]
– We’ll do this in class today
4
What is @Risk?
• @Risk is a widely used Excel add-in that
facilitates creation and analysis of
spreadsheet based simulation models
• Contains many probability distributions for
modeling random inputs
• Includes tool (BestFit) for fitting input
distributions
• Automates bookkeeping for running many
simulation replications and creates a ton of
tabular and graphical output reports
5
Building a Spreadsheet Based Simulation Model
(1) Build
deterministic
model
(2) Choose
inputs to model
as random
Inputs
Formulas
Outputs
Deterministic Inputs
Inputs
Stochastic or Uncertain or Random Inputs
(3) Model uncertain inputs with probability distributions
Exponential
Empirical
Discrete Probability Distribution of Demand
0.35
0.30
0.25
Probability
Uniform
0.20
0.15
0.10
0.05
0.00
100
150
200
250
300
Demand
Many more…
Normal
Poisson
See “Distributions, Simulation and Excel Functions” handout that Doane created.
See “Distributions, Simulation and Excel Functions” handout that Doane created.
Building a Spreadsheet Based Simulation
Model
“Running the model”
(4) Recalculate
spreadsheet many times
(4.1) “Manually”, through formulas and
either many rows or VBA
2 options
(4.2) Use spreadsheet simulation add-in
such as @Risk or Crystal Ball
• @Risk
– www.palisade.com
• Crystal Ball
– www.decisioneering.co
m
• Each recalculation of the
spreadsheet is a
“simulation run” or the
result of an experiment
• Like standard
experiments, results must
be analyzed using
statistics
Examples
• JCHP – Break Even
– Let’s add some uncertainty to this model
• Demand
• Costs
• JCHP-BreakEven-Simulation-01.xls
• Portable Monitoring Devices
– We’ll look at this later this session or next
session
(5) Analyze outputs
JCHP-Simulation-01.xls
Demand = RiskNormal(2870,200)
Demand = RiskNormal(2609,200)
Probability distribution of output variable, NOT just expected value.
Output Histogram
Graph and Table are linked
Sliders
Stats
The Bigger Picture
• Create several or many scenarios to
simulation
– Input variable values (including parameters for
probability distributions
– Perhaps model structure itself
• Analyze performance measures for each
scenario
• Synthesize results
– Pick “best” system or “best k out of n” systems
– Interpret results and “make decision”
– Simulation results just add another dimension to
the discussion regarding the decision
– Embed in optimization framework to formally
compare many scenarios with respect to some
objective function
Some Advantages
• Much cheaper to experiment with simulation model
than real system
• Provides nice analytical framework for comparing
multiple scenarios when uncertainty in key inputs is
significant
• Get a prediction of the distribution of the output
variables we are interested in
• Often models can be reused
• Simulation models usually require fewer simplifying
assumptions than analytical models are are often
easier to use
– We’ll explore queueing models soon and this will be clearer
• Simulation may provide only available approach to
analyzing complex systems
Some Disadvantages
• Development may be time consuming
• Data may be difficult or impossible to obtain
• Decision makers may not understand the technique
nor be able to properly interpret the results
• Dazzle of the technique can mask flaws in design
and/or input data
• Simple analytical models might be overlooked
• Run times may be “long”
• You, as the decision maker, must use your brain to
come up with the scenarios to be simulated