Management Science

1
Management Science
QM 6433 -- Spring 2007

• Using Crystal Ball for Simulation Modeling
• Week of 2/19/2007

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
• Control basic simulation execution and output
• Perform repeated sampling from simulation models
  for multiple values of decision variables
• Use the confidence interval approach for
  interpreting simulation results
• 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
• Reinforce concept this week
• Use Crystal Ball to facilitate simulation
  analysis
• Multicriteria decision making
• Project management concepts tools

4
Overview Steps Involved in Basic Simulation
Analysis with CB

• Define the problem / identify alternatives (per
  the SPSF)
• Develop a base case model and use Excel to
  implement it test the model to verify it works
  correctly
• Start Crystal Ball and open the base case model
• Using CB functions, convert the uncertain inputs
  to random values
• Specify run parameters in CB
• Identify the forecast cell(s)
• Indicate the number of trials to be conducted
• Specify which cells contain decision variables
• Provide decision table parameters
• Run the simulation
• Review the results
• Note the charts and statistics
• Perform any appropriate statistical analyses

5
Using CB to Develop a Simulation Model for the
Gaming Company

• Start with the base case model (from the homework
  for the week of 1/15/2007) start CB and open
  this model
• Now, lets make the model more realistic
• The demand input is not only uncontrollable but
  also uncertain
• So convert demand to a truncated normal random
  variable by using CB.Normal() with the following
  arguments
• Average -- reference the cell containing the
  observed average
• Standard Deviation -- reference the cell
  containing the observed standard deviation
• Minimum -- reference the cell containing the
  observed minimum
• Maximum -- reference the cell containing the
  observed minimum
• Example
• CB.Normal(DataAnalysis!E4,DataAnalysis!E7,Dat
  aAnalysis!E2,DataAnalysis!E3)
• Note the normal distribution assumption is just
  a convenience
• Lets address one of the problems concerning the
  normal distribution . . .

6
About the Normal Distribution

• Note that the demand values generated by CB are
  not integers (but should be)
• This problem occurs because were using a
  continuous distribution to represent a discrete
  variable
• We can overcome this easily (at least
  mechanically)
• Use Excels rounding function
  ROUND(value,places)
• Use the CB.Normal() formula from above for the
  value
• Specify 0 decimal places
• Although its better to use a discrete
  distribution to when the variable is discrete,
  sometimes its OK to use a continuous
  distribution as an approximation
• In general, we use the historical data
  (statistics, histogram, etc.) to choose/specify
  the probability distributions we use
• More on the choice of probability distribution
  later
• Now, however, lets run the simulation . . .

7
Running the Simulation

• First, verify that the random inputs really do
  vary
• Click on
• Define Cell Preferences (in the new version of
  CB)
• Cell Cell Preferences (in the old version of
  CB)
• Make sure Set to Distribution is unchecked
• We can start out simply (refer to the screenshot)
• Using the F9 key, we can simulate results for 12
  horizons, twice
• First, use a (55,35) policy, then use a (60,30)
• Record results, then do a statistical analysis
  more on this later
• Recall
• Any given stream of demand leads to a single
  total cost observation
• Pressing F9 constitutes random sampling for total
  cost
• However, this is too much work!
• Consider the CB way . . .

8
Using CB for the Gaming Company

• So far, theres not much benefit to using CB
• At least we dont need to use complex
  mathematical functions to specify randomness
• Its slightly simpler than using VLOOKUP()
• We still need to press the F9 key numerous times,
  record the results, etc.
• Where we can really benefit is by having CB
• Automate the multiple pressing of the F9 key
• Record the results, so we dont have to
• Calculate the basic statistics and create charts
• Automatically change the values of the decision
  variables after a certain number of trials for
  each combination

9
Using CB to Run the Simulation

• Specify controls and then do the sampling
• Define the run parameters
• Cell (or cells) to track
• Indicate the cell with the output measure to be
  tracked (e.g., C25 for total cost)
• Click on
• Define Define Forecast (in the new version)
• Cell Define Forecast (in the old version)
• Number of runs
• Click on Run Run Preferences
• Indicate value for Maximum Number of Trials
• Click on
• Run Start Simulation (in the new version)
• Run Run (in the old version)
• Look at the output . . .

10
When CB Is Done

• At least one result window will appear
• If they disappear
• Click on Analyze Forecast Charts and select
  (in the new version)
• In the old version
• Click on Run Forecast Windows
• Select Open All Forecasts
• Choose results
• Click on View Statistics
• Click on View Frequency Chart (not actually a
  histogram)
• Other views are available
• Can copy and paste into other applications (see
  example)
• We can save the results
• Click on Analyze (in the new version) or Run (in
  the old version)
• Generate report spreadsheet select Create
  Report
• Actual data select Extract Data

11
Working with Various Combinations of Decision
Variables

• One simulation run is just multiple repetitions
  of sampling from a single scenario
• Decision variables (e.g., Q, ROP)
• Deterministic inputs (e.g., setup cost)
• Thus, only one alternative is being evaluated
• CB provides a way for automating the what-if
  process
• That is, CB simplifies evaluating the results of
  numerous possible alternatives

12
Simulating Results for Multiple Alternatives

• Its the same initial process as when dealing
  with single scenario
• Define forecast cells
• Specify other run parameters (e.g., number of
  repetitions)
• Then do the following
• Specify which cells contain decision variables
• Select cell(s), then
• Define Define Decision
• Cell Define Decision
• Indicate range and step size
• Select Decision Table
• From Run Tools (in the new version
• From CBTools (in the old version)
• Work through the Decision Table wizard and click
  on Start
• Review the results as summarized in the new Excel
  workbook
• Compare forecast charts
• Use overlay charts to enhance comparison
• You may find it helpful to save the new workbook

13
Statistical Estimation

• Simulation results are just the beginning
  analysis must now be done
• Distributional analyses
• Other analyses, based upon initial hypotheses
• Basic confidence interval estimates
• Bear in mind that simulation results are just
  point estimates
• Averages (e.g., total overall cost involved)
• Proportions (e.g., times with negative fund
  balance)
• How good are these estimates?
• We need to
• Look at the variability in the simulation results
• Incorporate that variability into the estimates
• That is
• True measure observed margin of error
• This is a confidence interval estimate
• This allows us to look at the margin of error to
  see if the estimate is sufficiently precise

14
Confidence Interval Estimation

• Assume we desire to be 95 certain for large
  samples (ngt30), z 1.96 2
• For averages, the margin of error is
• So the estimate is
• For proportions (probabilities), the margin of
  error is
• So the estimate is
• Examples Gaming Company (Total Cost Service
  Level)

15
So, How Many Repetitions?

• Look at the formulae for the margin of error (e)
• Solve for the required number of repetitions (n)
• We may need to start with guesses for s and p
• Examples Gaming Company (continued)

16
Summary The Simulation Process

• Involves the following
• Identify the 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
• Note how this corresponds to the SPSF

17
Recall 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
• Because the uncontrollable inputs include
  uncertain values (demand) we use simulation
  analysis to address this problem

18
Note Where Simulation Fits

• 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

19
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

20
Simulation Involves Three Stages

• Preparation
• Understanding the problem
• Analyzing the input data
• Modeling and running the simulation
• Building the model
• Using Crystal Ball (or other software)
• Analyzing the results

21
Preparation for Simulation Analysis

• Input values arent simply made-up
• We must use realistic values
• Look at historical data (e.g., Gaming Company
  demand)
• Determine (estimate) distributions involved
• Type of distribution (e.g., normal, exponential,
  beta, gamma, triangular, . . . )
• Parameters (e.g., average standard deviation)
• Tools available
• Informal analysis compare curve to histogram
• Goodness of fit tests (e.g., chi-square,
  Kolmogorov)
• Software (e.g., UniFit) try CBs distribution
  fitting tool
• Select an empty cell
• Define Define Assumptions
• Click the Fit button
• Select range of raw data and click the OK button

22
The Actual Simulation Using Crystal Ball

• What 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

23
Analyzing the Results

• Remember, simulation is just a sampling process
• The controls and deterministic inputs are set
• Multiple repetitions result in multiple
  observations of the performance measures (i.e.,
  the forecast values)
• Thus, statistical analysis is called for
• Distributional analysis (descriptive statistics)
• Best case
• Worst case
• Most likely results (averages, medians, modes)
• Estimate of risk (i.e., variability) involved
• Inferential statistics
• Estimation
• Hypothesis testing
• When its all done, its time to make and
  implement the decision at hand

24
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
• Control basic simulation execution and output
• Perform repeated sampling from simulation models
  for multiple values of decision variables
• Use the confidence interval approach for
  interpreting simulation results
• Summarize the simulation modeling process

25
Appendix
26
Creating the Base Case Model

• 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 formulae for
  Week 1 cells, Week 2 beginning inventory, and
  total overall cost
• 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 formulae in rows 5-12 across the worksheet
• Save this as GameCo1.xls

27
A Blank Worksheet for The Gaming Company
28
Screenshot of GameCo4a.xls
29
One Possible Set of Results
30
Crystal Ball Output Frequency Chart
31
Crystal Ball Output Statistics
32
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

33
Gaming Company with Service Level Added
34
Total Cost Simulation Results
35
Service Level Simulation Results
36
Interval Estimates

• Average Total Costs (if needed, refer to the
  results slide)
• (55,35) policy
• 59.49 2(2.30v12)
• 58.16 lt----gt 60.82
• (60,30) policy
• 53.62 2(4.54v12)
• 51.00 lt----gt 56.24
• Conclusion these intervals dont overlap,
  indicating that we can be confident at the 95
  level that theres a difference in average total
  costs for the two policies being considered
• Service Level (if needed, refer to the results
  slide)
• (55,35) policy
• 0.892 2v0.892(1-0.892)/12
• 71 lt----gt 100 (maximum possible)
• (60,30) policy
• 0.908 2v0.908(1-0.908)/12
• 74 lt----gt 100 (maximum possible)
• Conclusion these intervals overlap, indicating
  that we cant be confident at the 95 level that
  theres a difference in service levels for the
  two policies being considered

37
Determining the Required Number of Trials

• Average Total Costs (if needed, refer to the
  results slide)
• Suppose we need an estimate thats within 10
  cents (e 0.10) of the true overall average
• n 4 (2.302)/(0.102) 2,124 observations
• That is, we need an additional 2,112 trials
• Service Level (if needed, refer to the results
  slide)
• Suppose we need an estimate thats within 5 (e
  0.05) of the true overall service level
• n 4(0.89)(1-0.89)/0.052) 133 observations
• That is, we need another 121 trials
• Thus, in order for both levels of precision to be
  reached, we need to run the simulation for
  another 2,112 trials