Management Science

1
Management Science
QM 6433 -- Spring 2007

• Introducing Simulation Modeling

Instructor John Seydel, Ph.D.
2
This Weeks Student Objectives

• Upon completion of this weeks course
  activities, you should be able to
• Develop simulation models to be processed with
  Excel
• Identify decision factors that are stochastic
• Incorporate randomness into spreadsheets for
  modeling decision outcomes
• Use Crystal Ball to specify variability for
  inputs in spreadsheet models
• Summarize the simulation modeling process

3
Refer to Our Course Outline

• Spreadsheet modeling techniques
• Further reinforce/strengthen this week
• Basic decision modeling concepts
• Further reinforce/strengthen this week
• Specific modeling/solution techniques
• Decision analysis
• Linear programming
• Simulation modeling analysis
• Introduce concept this week
• Multicriteria decision making
• Project management concepts tools

4
Recall the Four Basic Types of Decision Modeling

• Recall the Four Basic Types of Decision Modeling
• What-if analysis
• Making changes in the controllable inputs to see
  how a problems outcome is affected e.g., the
  Gaming Company case
• Sensitivity analysis
• Determining how changes in the uncontrollable
  inputs affect a problems solution e.g., the MBA
  case (often used in conjunction with
  optimization)
• Goal-seeking
• Making changes in the controllable inputs until
  a desired outcome results
• Optimization
• Using an mathematical approach to determine the
  values of the controllable inputs that result in
  the best (i.e., optimum) problem outcome e.g.,
  decision analysis (the Fish House exercise and
  the SkullNet mini-case)
• Simulation modeling is primarily an automated
  what-if analysis that also incorporates
  sensitivity analysis of the stochastic inputs
• Stochastic
• Means random, typically described by
  probability distributions e.g., demand in the
  Gaming Company case
• The opposite is deterministic

5
The Gaming Company A Summary of the Exercises
So Far

• Recall the Gaming Company case note our
  progression
• Build a model that will allow us to experiment
  with different order quantities each week
• Use that model to determine by trial and error
  what is the best order quantity for each week
• Then modify that model so that the order
  quantities are determined automatically according
  to a standard (Q,r) fixed order quantity /
  reorder point rule call this GameCo1.xls
• Use that model to determine by trial and error
  what is the best (Q,r) combination
• Further modify the model by incorporating a
  two-way decision table that will tabulate our
  results and reduce the trial and error burden
  let this be called GameCo2.xls
• For all of that we assumed we already knew what
  future demand would be this is not a realistic
  assumption!

6
A Monte Carlo Demo/Exercise

• Download GameCo0.xls (a blank worksheet/template)
• Dont open it instead save the file locally
• Start Excel and then open the file
• Add functionality by entering the basic formulae
• Note the second worksheet, entitled DataAnalysis
• Includes a frequency table and histogram
• Also has a lookup table (a cell range named
  table1) with a title Monte Carlo
• Review the lookup function in cell K5
• Refers to cell K4, which contains RAND(), a
  random number generating function
• Compares the random number to the CRF values in
  J10J22 and returns the simulated demand value
  from column 3 of the table
• This is a process known as random number or
  random deviate generation . . .

7
Generating Random Deviates

• No, were not talking about various types of
  social misfits!
• A random deviate is just a simulated value for a
  random variable
• RAND() generates a random fraction that has a
  value that is equally likely to be any number
  between 0.000 and 1.000
• When compared to a cumulative relative frequency
  (CRF) distribution (as in the lookup table), the
  random fraction will generate random deviates
  with the same relative frequency distribution as
  the variable that produced the CRF distribution
• In short
• We generate a random fraction
• We then compare it to a CRF distribution
• The CRF data value that corresponds to the random
  fraction is the simulated random variable
• This way, we can incorporate the appropriate
  variability into our results so that we can
  better be prepared for the variation that is
  likely to occur in the future
• Well do this in just a bit, but first lets
  review the problem . . .

8
The Decision Model for the Gaming Company

• There is an underlying mathematical function
  (i.e, model)
• y f(x1, x2, a1, a2, . . . , am)
• We have no idea what the actual function is
• We could figure it out
• But we dont need to, thanks to Excel
• Components of this model
• Inputs
• Controllable (xi) order quantity, reorder point
• Uncontrollable (aj)
• Cost parameters (setup, carrying, stockout)
• Weekly demand values (described by probability
  distribution)
• Output (y) total cost over the 10 week planning
  horizon
• A new solution procedure simulation analysis

9
Replacing Fixed Demand with Simulated Values

• Refer to the example
• On the main worksheet of GameCo1.xls insert a row
  above the Demand row
• Enter RAND() into the first cell (C6) of the new
  row
• Copy that formula across the row
• In the first cell of the Demand row enter a
  formula like that in K5 on the DataAnalysis
  worksheet copy that across the Demand row
• Save the result as GameCo3a.xls
• You should now be able to see a new set of
  results every time you press the F9 key
• Do that 20 times for a (60,30) order policy
  (order quantity, reorder point), and record the
  resulting total cost
• Do the same for a (50,30) policy
• Is there a statistically significant difference
  in the outcomes for the two policies?
• Note this is a random sampling process when we
  press the F9 key multiple times and record the
  results

10
Now, Use Crystal Ball to Make Things Easier

• Crystal Ball (CB) is an Excel add-in
• Close Excel, along with other programs and
  install CB
• Start CB by clicking on the desktop shortcut or
  going through the Start Programs menu
• When CB is running, youll just see Excel with an
  extra toolbar
• Lets make some changes
• Note that a triangular distribution (see Figure
  12.8 on page 570) more or less fits the histogram
  
• Therefore convert demand to a triangular random
  variable
• Delete the row with the RAND() functions
• Use CB.Triangular(min,mode,max) in the Demand row
• Refer to the example
• Do you see any problems with this? Use the
  appropriate Excel function to repair this
• Save this as GameCo3b.xls
• We could now go ahead and press F9 a bunch more
  times and record the results, but . . .

11
Saving Effort and Time with Crystal Ball

• Pressing the F9 key is unnecessary with CB, since
  the sampling process (and more) is automated
• What then does CB do?
• Monte Carlo simulation of random inputs
• Provides framework for organizing, record
  keeping, and reporting
• Why use CB?
• Simplifies the incorporation of variability
  (i.e., risk) into spreadsheet models
• Specifying distributions for uncontrollable
  inputs
• May not need to worry about functional forms of
  distributions
• Automates the what-if analysis process
• Repeated sampling
• Tries various combinations of decision variables
• Report generation calculates descriptive
  statistics
• Numeric measures
• Graphical displays

12
Whats Happening When Crystal Ball Generates
Random Values

• Essentially uses Excels RAND() function
• Compares that random fraction to the cumulative
  probability distribution involved
• Returns a data value that corresponds to that
  cumulative probability
• Typical CB random number functions
• CB.Normal(m,s,min,max)
• CB.Poisson(l)
• CB.Triangular(min,mode,max)
• CB.Custom(range) when nothing else seems to be
  close
• May need to round the result
• Dont just alter the display
• Instead use Excels ROUND() function

13
Now, Lets Put This into Perspective

• Recall the scientific problem-solving framework
• Note the simulation process and its
  correspondence to the SPSF
• Identify problem (decision, factors, . . . )
• Determine the alternatives (values for decision
  variables)
• Evaluate the alternatives
• Determine relationships and build model
• Incorporate variability
• Experiment numerous times with each combination
  of decision variables
• Analyze results (inferential statistics)
• Reiterate as appropriate
• Make an informed decision
• Monitor results
• Modify as appropriate

14
Spreadsheet-Based Simulations

• Start with base case spreadsheet
• Assume all input values known with certainty
• Tinker with decision variables and see what
  happens to performance measures
• Determine best and worst case results
• Identify optimal course of action
• Incorporate variability
• Replace deterministic inputs with values
  generated from distributions of these variables
• For any given combination of decision variables,
  run repeated iterations, keeping track of the
  results
• Based upon results, perform statistical inference
  (typically confidence interval estimation)
• Keep in mind that each resulting value for a
  performance measure is just a single sample
  observation
• Try other combinations of the decision variables

15
Why Use Simulation?

• Allows our models to reflect reality
• Many model inputs have a tendency to vary
• This way we can incorporate that variability
• We can build a model and see how it behaves
• Rather than experimenting with the real system
• Takes far less time to develop results
• Costs much less than experimenting with the real
  system
• Consider the timing of traffic lights
• We can play with various settings of the actual
  lights and see how that affects traffic
• Doing so will at best aggravate many people
• Imagine the accidents that might occur
• The human cost could be prohibitive
• Alternatively, we can build a simulation model
  and tinker with it
• Sometimes simulation is referred to as faking
  it, not making it!

16
Summary of Objectives

• Develop simulation models to be processed with
  Excel
• Identify decision factors that are stochastic
• Incorporate randomness into spreadsheets for
  modeling decision outcomes
• Use Crystal Ball to specify variability for
  inputs in spreadsheet models
• Summarize the simulation modeling process

17
Appendix
18
A Blank Worksheet for The Gaming Company
19
Formulae for The Gaming Company Worksheet

• Formulae for Week 1 cells and Week 2 beginning
  inventory
• C5 C15
• D5 C7C8
• C7 MAX(0,C5-C6)
• C9 IF(C8gt0,C16,0)
• C10 C7C17
• C11 IF(C6gtC5,(C6-C5)C18,0)
• C12 SUM(C9C11)
• C25 SUM(C12L12)
• Copy these across the worksheet
• Save this as GameCo1.xls

20
Gaming Company Worksheet with Random Demand
21
Gaming Company Worksheet Using Crystal Ball
22
Installing Crystal Ball

• Use the CD that came with the textbook
• Follow the instructions given in the PDF file
  available from the Handouts page at the course
  website
• It will probably be helpful if you review the
  instructions in their entirety before getting
  started with the actual installation process
• Closing all applications before starting
  installation is strongly recommended
• Note that youll need to restart your computer
  when the installation procedure is completed, but
  keep the CD in the drive throughout the reboot
  process the first time after installation

23
The Scientific Problem-Solving Framework (SPSF)

• Define the problem
• Define decision variables
• Determine criteria of importance
• Specify whether criteria are associated with
  goals or with objectives
• Identify constraints
• Consider alternatives
• Identify them
• Evaluate them
• Select best one
• Implement solution
• Monitor and revise solution re-solve if
  appropriate