Introduction to Simulation

1
Introduction to Simulation

• Using Excel only and using Excel with _at_Risk

2
Computer Simulation

• Build a computerized, descriptive, model of a
  stochastic (contains random elements) system
• Explicitly model uncertainty by representing
  uncertain variables with probability
  distributions
• Several important types of simulation models
• Discrete event or process simulation
• Monte-carlo (spreadsheet)
• System Dynamics (differential equations)
• Cellular automata
• Facilitates experimentation (What if?)
• easier and less expensive to experiment with a
  model than real system
• Art of Modeling applies here
• great complexity can be captured
• Model scope and grain are important design
  decisions
• Many powerful commercial tools available
• combination of powerful hardware and software
  make simulation viable
• I plan on creating a discrete event simulation
  course very soon in the SBA
• School of Engineering teaches one with a heavy
  manufacturing focus using Arena, a widely used
  simulation language
• well do Monte-Carlo simulation in this course

3
Computer Simulation is Experimentation

• A technique for conducting experiments with a
  computer on a model of a business (for example)
  system.
• Imitation of operation of real world process or
  system over time using a computer
• generates an artificial history of the process
• statistical inferences can be drawn from analysis
  of this artificial history

• Fast food operations (Burger King, Taco Bell)
• Telephone call-centers (telesales, technical
  support, reservations, appointments)
• Healthcare
• War games
• Biological systems (epidemics)

• Material handling systems
• Manufacturing systems
• Computer/communications systems
• Financial decisions (portfolio planning, capital
  budgeting)
• Utilities
• Transportation

4
Example of Specific Questions

• Do the benefits outweigh the costs in this new
  road project in Denmark?
• Given a certain number of ambulances, can the
  local EMS company really meet the contractual
  service level agreement of 90 of all calls
  having an ambulance on site in lt 12 minutes?
• How does time spent waiting in line to order a
  Giganto Coffee change if we add another staffed
  cash register between 4p-6p?
• How does the distribution of revenue look given
  our historic pattern of demand forecast errors?
• How does the average and 90th percentile of
  response time change in our computer network if
  we increase bandwidth by 30?
• What is the likelihood that this project will
  have a negative NPV? What is the probability that
  NPVgtX?
• How do different organ allocation policies affect
  the death rates among people on organ transplant
  lists?

5
Cost Structure of simulation software

• Huge range in pricing for simulation packages
• _at_Risk, Crystal Ball 800-1800
• ARENA, ProModel - 15K
• Extend - 2300
• iGrafx Process 1000
• Simul8 - 1500
• Academic pricing differs dramatically
• Open source or free options
• Simkit

6
Why Important to Model Uncertainty?

• The world is uncertain
• Replacing random quantities with averages or
  single guesstimates can be dangerous
• Flaw of Averages
• Plans based on average assumptions will be wrong
  on average.
• Allows prediction of distribution of results
• Not just one predicted number or outcome
• Distribution ? think, Histogram
• Sensitivity analysis of outputs to inputs
• Which inputs really affect the outputs?

7
Disciplined IntuitionConnecting the Seat of the
Intellect to the Seat of the Pants
http//www.stanford.edu/savage/faculty/savage/ind
ex.html has animated flaw of averages lets
look at it
www.drsamsavage.com
www.analycorp.com
Nice tutorial on Monte-Carlo simulation
at http//analycorp.com/uncertainty/
8
Monte-Carlo becoming more widely used in
financial services industry
http//www.pbs.org/wgbh/nova/stockmarket/
http//columbus.bizjournals.com/columbus/stories/2
001/07/23/focus1.html
http//www.nysscpa.org/cpajournal/2005/905/perspec
tives/p12.htm
http//www.financialengines.com/
Company founded by Nobel laureate William Sharpe
9
Modeling Examples

• Walton1-template.xls
• Basic simulation with Excel only and Flaw of
  Averages example
• Walton2-DataTable.xls
• Using Data Tables for replications and
  simulation optimization
• Walton4-RiskTemplate.xls
• Simulation with _at_Risk
• Walton6-MoreUncertainty.xls
• Multiple independent random inputs
• Stocks-CorrelatedInputs.xls
• Impact of correlated inputs and risk
  diversification
• Rate-TemporalCorrelation.xls
• Modeling correlation over time
• DrugProduction.xls
• Modeling the passage of time and uncertain events
• MarchMadnessMen2005.xls
• Modeling sports tournaments
• More to come

10
The Walton Bookstore Problem (Walton1-template.xl
s)

• Bookstore needs to set order quantity for
  specific book
• Unit cost and selling price known
• Unsold books can be returned for partial refund
  (amount known)
• Demand unknown - probability distribution
• Decision Variable - order quantity
• What order quantity maximizes expected profit?

11
Building a Spreadsheet Based Simulation Model
(1) Build deterministic model
Formulas
Inputs
Outputs
Deterministic Inputs
(2) Choose inputs to model as random
Inputs
Stochastic or Uncertain or Random Inputs
(3) Model uncertain inputs with probability
distributions
Uniform
Normal
Poisson
Exponential
Empirical
Many more
12
Building a Spreadsheet Based Simulation Model
Running the model
(4.1) Manually, through formulas and either
many rows or VBA (Ex 11.1)
(4) Recalculate spreadsheet many times
2 options
(4.2) Use spreadsheet simulation add-in such as
_at_Risk or Crystal Ball (Ex 11.2)

• _at_Risk
• www.palisade.com

• Crystal Ball
• www.decisioneering.com

13
(5) Analyze outputs
Probability distribution of output variable, NOT
just expected value.
14
The Bigger Picture

• Create several or many scenarios to simulation
• Input variable values (including parameters for
  probability distributions)
• Perhaps model structure itself
• Example Different order quantities for Walton
  problem
• Analyze performance measures for each scenario
• Synthesize results
• Pick best system or best k out of n systems
• Interpret results and make decision
• Embed in optimization framework to formally
  compare many scenarios with respect to some
  objective function
• Example Using Data Table in Walton problem to
  find maximum expected profit for different order
  quantities

15
Accomplishments

• In the body of the simulation rows 19-1018, we
  randomly generated 1000 possible demands and the
  corresponding profits.
• Lets confirm that our simulated demands match
  the demand distribution that we supposedly are
  generating
• There are only five possible demand values and
  also for our order quantity, 200, the profit is
  500 regardless of whether demand is 200, 250, or
  300.
• Why?
• There are 290 trials with profit equal to - 250,
  227 trials with profit equal to 125, and 483
  trials with profit equal to 500. (Answers may
  differ because of the random numbers.)
• So, we get a distribution of outputs, not one
  number
• The average of the 1000 profits is 197.38 and
  their standard deviation is 328.58. (Answers may
  differ because of the random numbers.)

16
Probability Distributions

• The probability distribution of profit is as
  follows
• P(Profit -250) 290/1000 0.29
• P(Profit -125) 227/1000 0.227
• P(Profit -500) 483/1000 .483
• We also estimate the mean of this distribution to
  be 197.38 and its standard deviation to be
  328.58.
• It is important to be aware that with computer
  simulation each time it is run the answers will
  be slightly different.
• This is the reason for the confidence interval.
• Before getting into these, lets look at two
  other techniques for simulating with spreadsheets

17
Using Data Tables to do Replications

• Open Walton2-DataTable.xls
• Notice just one row of formulas for the model
• We take advantage of how Data Tables work to do
  the spreadsheet recalculations
• First tab is for one order quantity
• Second tab lets us do order quantity optimization
  via a 2-way data table

18
Confidence Intervals

• The confidence intervals can be found in cells
  E13 and E14.
• This interval expresses our uncertainty about the
  mean of the profit distribution.
• Our best guess is the value we observed but
  because the corresponding confidence interval is
  very wide, from 177.43 to 217.32, we are not
  sure of the true mean of the profit distribution.

19
Confidence Intervals -- continued

• It is common in computer simulation to estimate
  the mean of some distribution by the average of
  many realizations of the output variable of
  interest.
• The usual practice is then to accompany this
  estimate with a confidence interval, which
  indicates the accuracy of the estimate.
• simulation is essentially a controlled experiment
• we analyze experimental output with Statistics
• You might recall from statistics that to obtain a
  confidence interval for the mean, you start with
  the estimated mean and then add and subtract a
  multiple of the standard error of the estimated
  mean.

20
The large sample 100(1-a) CI on the sample mean
Let a0.05
1-a
a/2
a/2
Z1-a/2 NORMSINV(.975)1.96
Za/2 - Z1-a/2 NORMSINV(.25)1.96
sample mean
S sample standard deviation
21
Finding the Best Order Quantity

• So far we have ran the simulation for only a
  single order quantity, 200.
• Waltons ultimate goal is to find the best order
  quantity - that is, the order quantity that
  maximizes the mean profit.
• This goal can be achieved by using a Data Table
  to rerun the simulation for other order
  quantities.
• Should compare confidence intervals or do t-tests
  on the mean profits to see if statistically
  different results are obtained
• Lets do this with Walton2-DataTable.xls

22
First look at _at_Risk

• Quit Excel
• Download Walton4-Risk.xls from course web
• Start Programs Palisade Decision Tools
  _at_Risk 4.5
• Click Enable macros when asked
• Excel will launch automatically
• _at_Risk toolbar should appear
• Open Walton-Risk.xls
• _at_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

23
Building a Spreadsheet Based Simulation Model
(1) Build deterministic model
Formulas
Inputs
Outputs
Deterministic Inputs
(2) Choose inputs to model as random
Inputs
Stochastic or Uncertain or Random Inputs
(3) Model uncertain inputs with probability
distributions
Uniform
Normal
Poisson
Exponential
Empirical
Many more
24
For now, lets assume we are given the input
distributions

• Well do BestFit (a module in _at_Risk) after we
  learn how to create and run simulation models
• Its a bit statistical and will slow down our
  progress in getting an overall grasp of _at_Risk
• In the Walton4-Risk problem, lets assume demand
  is triangularly distributed with min100,
  mode175, max300
• We use T(100,175,300) as shorthand when we write
  this
• In _at_Risk, this becomes RiskTrian(100,175,300)
• Of course, wed use cell references or range
  names

25
Defining Input Distn for a Cell
Define your distribution and parameter values
Define distributions
Click Apply when done
26
Another way to define inputs
But, how do you know what distribution to use?
27
Input Distribution Modeling
(3) Model uncertain inputs with probability
distributions
Uniform
Normal
Poisson
Exponential
Empirical

• Knowledge of the situation may let you
• hypothesize a few possible distributions
• use data and/or forecasts to find parameter
  values
• See ProbabilityDistributions.xls
• Use BestFit with raw data to fit a distribution
  and estimate parameter values
• still need a general understanding of probability
  distributions and when certain ones might apply

28
OK, our accomplishments so far

• Weve got the basic spreadsheet model built
• For now weve assumed some sort of distribution
  and associated parameter values for uncertain
  inputs in the model
• T(100,175,300)
• Now, we need to tell _at_Risk which variables to
  track as outputs during the simulation run
• Then well set up the simulation report
  settings and run the model and view the results

29
Tell _at_Risk which cells are Outputs
NOT, addition!
30
Getting Ready to Run the Model Simulation
Settings
Running the model
(4.1) Manually, through formulas and either
many rows or VBA
(4) Recalculate spreadsheet many times
2 options
(4.2) Use spreadsheet simulation add-in such as
_at_Risk
Iterationreplication Simulations is gt 1 if
using RiskSimTable(), else its 1.
31
Getting Ready to Run the Model Simulation
Settings
Leave at default of Latin Hypercube
Just affects how spreadsheet behaves when
recalced (F9).
Leave unchecked so RiskSimTable uses same seed
for different scenarios.
Controls how much data on inputs is collected for
results
Use Fixed if you need to control random numbers
generated, otherwise leave at Choose Randomly.
32
Pick Report Settings
_at_Risk results and/or Excel results
Pick reports to go into Excel automatically.
Should Excel reports go into same or new
workbook?
33
Run the Simulation Model
34
(5) Analyze outputs
Probability distribution of output variable, NOT
just expected value.
35
Output Histogram
Graph and Table are linked
Sliders
Stats
36
Live
Detailed stats on all outputs
Find any percentiles or probabilities
37
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
• Embed in optimization framework to formally
  compare many scenarios with respect to some
  objective function

38
Using RiskSimTable to optimize

• RiskSimTable() function works much like a Data
  Table.

In Walton problem we use RiskSimTable to explore
the 5 different possible order quantities.
Set this to of values in RiskSimTable argument.
39
Notes about _at_Risk

• The .rsk file contains such things as results,
  input fits, graphs, input/output statistics,
  simulation and report settings
• not critical to save unless you need those things
  and/or have not extracted what you need and put
  into .xls file
• Student version of _at_Risk does not allow models
  that span worksheets
• _at_Risk sometimes gets confused with separate
  models on separate worksheets, so...
• just create a new workbook for each model

40
Some Advantages

• Often models can be reused
• Simulation often easier than analytical models
  for complex systems
• Simulation usually requires fewer simplifying
  assumptions than analytical models
• Simulation may provide only available approach to
  analyzing complex systems

41
Some Disadvantages

• Development may be time consuming
• Dazzle of the technique can mask flaws in design
  and/or input data
• Simple analytical models might be overlooked
• Run times may be long
• More difficult to embed within optimization
  framework
• However, great advances made in simulation
  optimization
• RiskOptimizer from Palisade
• Optquest

42
The Game of Lifehttp//www.math.com/students/wond
ers/life/life.htmlhttp//www.ibiblio.org/lifepatt
erns/

• Developed by John Conway (1970)
• A simple simulation of life played out on a
  grid according to simple rules
• cellular automaton
• Self organizing systems
• complex patterns evolving from very simple rules
• Scientists use more complicated cellular automata
  to try to gain insight to basic questions of
  science
• e.g. could complex beings evolve from simple
  building blocks and rules?

43

• Still life
• Gliders
• Oscillators
• Lets check out the R-pentomino and some other
  starting patterns (Glider Gun on Zoom 1)
• Will R-pentomino ever stabilize?

44
Learning more about simulation

• Business Process Modeling, Simulation and Design
  Laguna and Markland
• Very accessible simulation book geared to
  B-school students
• Michigan Simulation Users Group -
  http//www.m-sug.org/
• INFORMS College on Simulation http//www.informs-c
  s.org/
• SWARM Development Group http//www.swarm.org/
• devoted to multi-agent simulation modeling
• developed at the Santa Fe Intitute
• Simulation Modeling and Analysis Law and Kelton
• the bible for discrete event simulation
• Business Dynamics systems thinking and modeling
  for a complex world Sterman, J.
• outstanding book on systems dynamics and
  continuous simulation modeling of complex
  business problems
• A New Kind of Science Wolfram, S.
• the long awaited book by the genius who gave us
  Mathematica
• argues that cellular automata can give us
  insights into many of the basic questions of
  science