Transcript PPT Presentation, Supplement C

Supplement C
Introduction to Simulation
Operations Management
by
R. Dan Reid & Nada R. Sanders
3rd Edition © Wiley 2007
PowerPoint Presentation by
Roger B. Grinde, University of New Hampshire
Morgan E. Henrie, University of Alaska Anchorage
© Wiley 2007
Learning Objectives





Explain why simulation is a valuable tool for
decision making.
Define the steps involved in the simulation
modeling process
Generate random numbers from various
distributions in Excel
Develop and run a simulation model in Excel
Analyze the results from a simulation in Excel
© Wiley 2007
Uncertainty and Risk in
Decision Making





Mangers face decisions every day
Most decisions involve uncertainty
Risk may also be present
Quantitative models can provide a
tremendous insight and assistance in
decision making
Quantitative models may ignore
uncertainty in real situations
© Wiley 2007
Simulation Overview and
Software


A model that mimics what might happen in reality.
Examples




Weather forecasting
Rocket simulators
Military war-game simulations
Business Examples




Capital projects
Business process redesign
Production process analysis
Service system analysis © Wiley 2007
Simulation Overview and
Software (continued)




Much of the time, uncertainty is present in the
system we wish to study.
Simulation provides a way to directly model the
uncertainty and/or dynamic behavior of the system.
Monte-Carlo simulation is the focus of this chapter. It
focuses on assessing the uncertainty and risk of a
particular situation or decision.
Discrete-Event simulation is another major branch of
simulation. It focuses on studying the dynamic
behavior of systems as they operate over time.
© Wiley 2007
Simulation Overview and
Software (continued)


Monte-Carlo simulation that repeatedly
samples from probability distributions and
computes the resulting performance
measures of the system
Discrete-event simulation is a methodology
that models the state of a system as it
changes as a result of certain events that
occur randomly in time
© Wiley 2007
Monte-Carlo Simulation
Schematic




Some inputs are fixed, or known with certainty (e.g., the price of our
product).
Some inputs are uncertain, or random (e.g., the demand for our product at a
given price).
We may have decision variables (e.g., quantity to produce).
Since some of the inputs are random, the output of the simulation is also
random.
© Wiley 2007
Simulation Modeling Process





Develop a deterministic spreadsheet model.
Determine the appropriate probability distributions
and parameters to use for the random inputs.
Modify the deterministic model by incorporating
the random inputs.
Re-calculate the model many times (each
calculation is called a replication or trial).
Analyze the probability distribution of the output
using statistical concepts.
© Wiley 2007
Probability Distributions


Probability Distributions are used to model
the random inputs.
You’re already probably familiar with some
probability distributions, even if you don’t
know their names.



Normal (“bell” curve)
Bernoulli (e.g., flip of a coin)
Discrete Uniform (e.g., roll of a die)
© Wiley 2007
Probability Distributions continued


A very important part of simulation is modeling the
random behavior of a situation using probability
distributions.
As noted before, since some of the inputs are random, the
output of a simulation is also random.


However, the probability distribution of the output of a simulation
does not necessarily look like one of the standard probability
distributions.
The next few slides show how we can generate random
values in Excel from several different probability
distributions.
© Wiley 2007
Random Number Generation
In Excel

Key to random number generation is
generating random values that are
“uniformly” distributed between 0 and 1.


It turns out that if we can do this, then we can
transform this value into a sample value from any
probability distribution.
“Uniformly” distributed simply means that any
value in between 0 and 1 is equally likely.
© Wiley 2007
Random Number Generation
In Excel - continued

Fortunately, Excel has a built-in function, =RAND(),
which does exactly this.




=RAND() (empty parentheses are required!) returns a
value between 0 and 1.
If you enter this function in a cell, and then copy it to
some other cells, all the values will be different!
Also, if you hit the F9 key (which recalculates the
worksheet), the values of cells with =RAND() in them will
change!
This is simulation at work. Just like in real life, some
things are uncertain (e.g., commuting time, waiting time
at the food court).
© Wiley 2007
=RAND()





=RAND() entered in
one cell, then
copied.
All values are
different.
If you do this, your
values will be
different too.
Hit F9 to recalculate all 50
values.
RAND() produces a
“U(0,1)” random
number.
© Wiley 2007
How “Uniform” are the values
from RAND?
Histogram
(5000 Random Values from =RAND() function)
Histogram
(50 Random Values from =RAND() function)
600
8
7
500
5
Frequency
Frequency
6
4
3
400
300
200
2
1
100
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1.0
0
0.1
Upper End of Category


0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
Upper End of Category
As more values are generated, histograms become more
uniform.
Just as in sampling, a bigger sample brings more
precision.
© Wiley 2007
1.0
Bernoulli Distribution

Bernoulli Distribution: 2 outcomes


Example: Flip of a coin
How to convert a value from RAND() to a
“heads” or a “tails”?



Equal probability
=IF(RAND()<0.5, “heads”, “tails”)
Nothing special about the 0.5…could simulate an
unfair coin, or defective/non-defective parts,
complaining or non-complaining customers, etc.
© Wiley 2007
Bernoulli Distribution Example





Formulas entered in
row 4, then copied
down to simulate
A
B
C
D
E
F
G
H
100 coin flips.
1 Coin Toss Simulation
A4: =RAND()
Some rows hidden 2
3
U(0,1)
H/T
here.
4
0.010
Heads
Count up number of 5 0.031 Heads
B4: =IF(A4<0.5,"Heads","Tails")
heads, tails.
6
0.908
Tails
Tails
What would happen 7 0.936
E8: =COUNTIF(B$4:B$103,"Heads")
8
0.196
Heads
Number
Heads
43
E9: =COUNTIF(B$4:B$103,"Tails")
if we were to re9
0.667
Tails
Number Tails
57
calculate this
10
0.713
Tails
Total
100
spreadsheet?
11
0.355
Heads
What would happen 102 0.151 Heads
103 0.757
Tails
if we were to
simulate 10,000 coin
flips rather than
100?
© Wiley 2007
Discrete Uniform Distribution

Finite number of outcomes, each with the same
probability.




Coin flip is a simple example of this.
Roll of a die is a more complex example.
Practical example: selecting someone at random from a
known number of entries.
Implementation


We could use an IF statement as before, but this
becomes difficult because of the many possible
outcomes. We would have to “nest” IF statements within
others, and there is an Excel limit on this.
A better way is to use the VLOOKUP function in Excel.
© Wiley 2007
Discrete Uniform: Roll a Die
A






Key: Divide up the range between 0
and 1 into 6 equal intervals. Assign
the possible die rolls (1, 2, …, 6) to
each one of these intervals.
Table in D5:G12 sets up the
intervals.
Column A generates a U(0,1) value.
Column B converts the U(0,1) value
into a die roll by looking up the
U(0,1) value in the table to see
which interval it falls into. Then it
returns the corresponding die roll.
Results section tallies up the results
from 100 die rolls.
Is there anything special about the
equal probabilities? Could we
simulate an “unfair” die just by
changing the probabilities in
D7:D12?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
102
103
B
C
Die Roll Simulation
U(0,1)
0.607
0.127
0.005
0.911
0.533
0.505
0.496
0.167
0.198
0.294
0.631
0.924
0.421
0.245
0.453
0.556
0.663
0.077
0.711
0.776
0.385
0.468
0.352
0.668
0.440
0.008
0.503
0.614
Roll
4
1
1
6
4
4
3
2
2
2
4
6
3
2
3
4
4
1
5
5
3
3
3
5
3
1
4
4
© Wiley 2007
D
E
F
G
E8: =F7
A4: =RAND()
(copied down)
F7: =E7+D7
(copied down)
B4: =VLOOKUP(A4,E$7:G$12,3)
Cumulative
Probability Begin
0.167
0.000
0.167
0.167
0.167
0.333
0.167
0.500
0.167
0.667
0.167
0.833
Probability Distrib
End
Outcome
0.167
1
0.333
2
0.500
3
0.667
4
0.833
5
1.000
6
Results of Simulation
Roll
Frequency Fraction
1
18
0.18
2
15
0.15
3
24
0.24
4
20
0.20
5
10
0.10
6
13
0.13
100
E23: =SUM(E17:E22)
E17: =COUNTIF(B$4:B$103,D17)
(copied down)
F17: =E17/E$23
(copied down)
General Discrete Distribution

In the last example, is there anything special
about the equal probabilities?



Could we simulate an “unfair” die just by changing
the probabilities in D7:D12?
General Discrete Distributions are handled in
the same way.
Examples


Demand for products or services
Number of machines breaking down in a day
© Wiley 2007
General Discrete Distribution
Example
A


Note the different
probabilities for the
different possible
demand values.
Results for 100
trials roughly
correspond to the
input probabilities
(obviously, more
trials would result
in a closer match).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
102
103
B
C
D
E
F
G
General Discrete Distribution
A4: =RAND()
U(0,1)
Demand
0.034
100
0.549
250
B4:
=VLOOKUP(A4,E$7:G$13,3)
0.039
100
0.356
200
0.569
250
0.207
200
0.139
150
0.049
100
0.534
250
0.477
250
0.036
100
0.470
250
0.772
300
0.066
150
0.682
250
0.389
200
0.450
250
0.962
400
0.502
250
0.651
250
0.738
300
0.644
250
0.419
250
0.545
250
©
Wiley 2007
0.800
300
Probability
0.05
0.10
0.25
0.30
0.15
0.10
0.05
1.00
Cumulative Distribution
Begin
End
Demand
0.00
0.05
100
0.05
0.15
150
0.15
0.40
200
0.40
0.70
250
0.70
0.85
300
0.85
0.95
350
0.95
1.00
400
Results of Simulation
Demand Frequency Fraction
100
10
0.10
150
8
0.08
200
18
0.18
250
36
0.36
300
18
0.18
350
5
0.05
400
5
0.05
100
Continuous Probability
Distributions

So far, we’ve only dealt with discrete distributions.




Discrete distribution is one where the outcome can be
only one of a finite number of possibilities (technically,
a “countable” number possibilities).
Continuous distribution allow any possible value,
possibly bounded above and/or below.
Actually, RAND() is an example of a continuous
probability distribution, U(0,1).
Here we’ll look at the uniform distribution, the
normal distribution, and the exponential
distribution.
© Wiley 2007
Continuous Uniform
Distribution

Uniform distribution between a (minimum) and b
(maximum) is designated U(a,b).


Convert value from RAND() into value from U(a,b).




RAND() returns a U(0,1) random value.
X = a + (b−a)*RAND()
If a=10, b=50, and RAND()=0.37, then X = 10 + (50-10)*0.37 =
10 + 14.8 = 24.8
What if RAND()=0? What if RAND()=1?
Examples



Time to complete a task, based on minimum and maximum time
estimates.
Unit costs
Demand
© Wiley 2007
U(10,50) Example: Histogram
based on 250 trials
Frequency (% , n=250)
14.0%
12.0%
10.0%
8.0%
6.0%
4.0%
2.0%
0.0%
12.08 16.04 19.99 23.95 27.90 31.86 35.82 39.77 43.73 47.68
Midpoint of Range
© Wiley 2007
Discrete Uniform Distribution:
A Reprise



Earlier we used a VLOOKUP function to
simulate a Discrete Uniform distribution
If the possible values are integers, we can
use what we’ve learned about the continuous
uniform distribution to be more efficient.
Suppose we want a discrete uniform
distribution between a and b, inclusive,
designated DU(a,b).

Use =INT(a+(b−a+1)*RAND())
© Wiley 2007
Discrete Uniform Distribution:
A Reprise - continued

Example: DU(10,50), suppose RAND()=0.37


INT returns the integer part of a number


X=INT(10+(50−10+1)*0.37) = INT(25.17) = 25
The “+1” is needed to ensure nothing smaller than a is
returned, and that b is an actual possibility.
This approach is easier than the VLOOKUP
approach especially when there are many
possibilities (e.g., choosing one person at random
out of a list of 5000 entries).
© Wiley 2007
Normal Distribution

Normal Distribution characterized by a mean (µ)
and standard deviation ().


Designated N(µ,)
Random Number Generation



=NORMINV(RAND(),µ,)
NORMINV is the “inverse” of the normal distribution
function. RAND acts like a cumulative probability value
(between 0 and 1).
If µ=0 and =1, then =NORMINV(RAND(),0,1)
essentially returns a random Z-value from a normal
distribution.
© Wiley 2007
N(80,10) Example: Histogram
based on 250 trials
Frequency (% , n=250)
30.0%
25.0%
20.0%
15.0%
10.0%
5.0%
0.0%
55.45 61.44 67.42 73.41 79.40 85.38 91.37 97.36 103.34 109.33
Midpoint of Range
© Wiley 2007
Exponential Distribution


Very common distribution when modeling
customer arrivals to service systems,
machine breakdowns, etc.
Characterized by a mean, denoted µ. We
refer to an exponential distribution as
EXP(µ).

For example, µ would represent the average
time between customer arrivals, the average
time between machine breakdowns, etc.
© Wiley 2007
Exponential Distribution continued

Generating an EXP(µ) random value:




=−µ*LN(RAND())
LN is the natural logarithm function (which is the
mathematical inverse of the EXP function.
The minus sign is needed because the natural logarithm
of a value between 0 and 1 is negative.
Example


Suppose µ=25, and RAND()=0.68
Then X = −25*LN(0.68) = 9.64
© Wiley 2007
EXP(10) Example: Histogram
of 250 trials
Frequency (% , n=250)
50.0%
40.0%
30.0%
20.0%
10.0%
0.0%
2.45
7.30
12.15 17.00 21.86 26.71 31.56 36.41 41.26 46.12
Midpoint of Range
© Wiley 2007
Example: DG Outerwear

Decide number of coats to order




Place order in June, but demand not realized until
Fall. No chance for another order.
If we order too few, we lose out on sales.
If we order too many, we must sell the
remainder at a loss.
Demand for coats at the regular price is
random. We believe we can sell any coats left
over, but the salvage price itself is uncertain.
© Wiley 2007
DG Outerwear Problem
Parameters






Unit Cost: $75
Regular Sales Price: $100
Demand at regular price: Uniformly distributed
between 20 and 40.
Salvage Price: May be $15, $20, $25, or $30 with
respective probabilities 0.05, 0.30, 0.50, and 0.15.
Assume all leftover coats will be sold at a single
salvage price.
DG considering ordering 35 coats. Is this a good
idea?
© Wiley 2007
Simulation Model
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
C
D
E
F
G
H
Fixed Inputs
Purchase Price of Coat $75
Regular Sales Price $100
Demand Distribution (Discrete Uniform)
Minimum
20
Maximum
40
Decision Variable
Purchase Quantity
Salvage Price Distribution (Discrete)
Cumulative Distribution
Probability Begin
End Price
0.05
0.00
0.05
$15
0.30
0.05
0.35
$20
0.50
0.35
0.85
$25
0.15
0.85
1.00
$30
1.00
35



J
Salv
Rev
$450
Profit
($175)
Simulation Logic
Replication
1
RN1 RN2 Demand
0.003 0.992
20
Reg
Sales
Qty
20
Salv
Sales
Qty
15
Salv
Reg
Price
Rev
$30 $2,000
Key Formulas

I
DG Winter Coats
16
17

B
B17, C17: =RAND()
E17 = MIN(B$8,D17)
G17 = VLOOKUP(C17,E$10:G$13,3)
I17 = G17*F17
D17 = INT(E$4+(E$5−E$4+1)*B17)
F17 = B$8−E17
H17 = B$5*E17
J17 ©
= H17+I17−(B$8*B$4)
Wiley 2007
Simulation Model (continued)




This is for one replication, or trial. We need to
run many trials to get a good sense of the
results of this decision.
We’ve used relative and absolute cell
references in the model so that we can copy
the formulas in Row 17 down.
We’ll copy this row down so that we have 250
trials of the simulation.
Then, we’ll calculate summary statistics of the
resulting profit values.
© Wiley 2007
Simulation Model Replicated, with
Summary Statistics
A


Note: Many rows hidden
(but calculations use all
rows)
Summary Statistics for a
Purchase Quantity of 35





Average Profit = $435
Std.Dev. = $392
Minimum = −$325
Maximum = $875
95% Confidence
Interval = ($386, $483)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
265
266
267
268
269
270
271
272
273
274
275
B
C
D
E
F
G
H
I
J
Salv
Rev
$0
$250
$350
$75
$175
$0
$100
$350
Profit
$875
$125
($175)
$650
$350
$875
$575
($175)
DG Winter Coats
Fixed Inputs
Purchase Price of Coat $75
Regular Sales Price $100
Demand Distribution (Discrete Uniform)
Minimum
20
Maximum
40
Decision Variable
Purchase Quantity
Salvage Price Distribution (Discrete)
Cumulative Distribution
Probability Begin
End Price
0.05
0.00
0.05
$15
0.30
0.05
0.35
$20
0.50
0.35
0.85
$25
0.15
0.85
1.00
$30
1.00
35
Simulation Logic
Replication
1
2
3
4
5
6
249
250
RN1
0.911
0.279
0.064
0.595
0.404
0.932
0.544
0.084
RN2 Demand
0.701
39
0.724
25
0.585
21
0.355
32
0.405
28
0.302
39
0.795
31
0.823
21
Reg
Sales
Qty
35
25
21
32
28
35
31
21
Salv
Sales
Qty
0
10
14
3
7
0
4
14
Salv
Price
$25
$25
$25
$25
$25
$20
$25
$25
Reg
Rev
$3,500
$2,500
$2,100
$3,200
$2,800
$3,500
$3,100
$2,100
Average $435
Standard Deviation $392
Minimum ($325)
Maximum $875
© Wiley 2007
95% Confidence Interval on Average
Lower CL $386
Upper CL $483
DG Coats Example: Comments


Purchasing 35 coats results in an average profit of
$435. Can we do better? What do the standard
deviation, minimum, and maximum values tell us?
95% confidence interval on mean profit



We are 95% confident the true value of the mean profit
lies somewhere in the interval from $386 to $483.
What effect would more (or less) simulation trials have
on this confidence interval?
What does the confidence interval say about an
individual value of profit, such as what we will get this
year if we order 35 coats?
© Wiley 2007
DG: Finding Optimal Order
Quantity
We can change the purchase quantity, press F9, and calculate the 250 trials and
summary statistics for this new purchase quantity. This is a powerful tool!



Purchase
Quantity
Average
Standard
Deviation
Minimum
Maximum
20
$500
$0
$500
$500
25
$568
$117
$225
$625
30
$551
$264
-$50
$750
35
$435
$392
-$325
$875
40
$230
$460
-$600
$1000
The highest average profit occurs when we purchase 25 coats.
Thought Question: The average demand is 30 (remember demand was uniformly
distributed between 20 and 40). Why is the highest average profit at a purchase
quantity less than this average?

Does making decisions based on the average always make sense?
© Wiley 2007
DG: Histogram of Profit
Values
250
200
150
100
50
Upper End of Category
© Wiley 2007
More
$598
$572
$545
$518
$492
$465
$438
$412
$385
$358
$332
$305
$278
0
$252

Histogram of Profit Values for Purchase Quantity = 25 (250
total replications)
$225

Purchase
Quantity = 25
Histogram of
profit values
Why does this
histogram
shape make
sense?
Frequency

Bin
Frequency
$225
3
$252
13
$278
1
$305
3
$332
5
$358
1
$385
5
$412
6
$438
2
$465
4
$492
4
$518
0
$545
6
$572
4
$598
0
More
193
Simulation Using Data Tables



In the DG example we simply copied the formulas
down to replicate the model 250 times. This only
works when the logic of the model is simple enough
to arrange in a single row of the spreadsheet.
For more complex models, we need to use Excel’s
Data Table feature (first used in Supplement A).
With a Data Table approach, we build the logic of the
simulation for one single trial. Then the Data Table
effectively recalculates the model for as many times
as we wish.
© Wiley 2007
Data Table Approach for DG
Problem


We only need
the logic for
a single trial
(Row 17).
Next slide
gives steps
for Data
Table.
© Wiley 2007
Data Table Steps for DG Problem

Steps
1.
2.
3.
4.
5.
Label two columns, one for replications and one for profit. In the
replications column, from A22 to A271, enter the numbers 1…250.
Do not put anything in Cell A21.
Cell B21, enter “=J17.” This references the profit value from the
simulation logic.
Select A21:B271. Be sure to include the top rows.
Keeping A21:B271, go to Data/Table, “Row Input Cell” blank, and
click on A21 (or any blank cell on the worksheet) for the “Column
Input Cell.” Click “OK.” The results from 250 replications should now
be showing in B22:B271.
If all the values in B22:B271 are the same, press the F9 key to force
recalculation of the worksheet.
© Wiley 2007
Data Table Steps for DG Problem
Steps

6.
7.
Compute summary statistics from the results.
If desired, freeze the results from the simulation in Cells
B22:B271 using Edit/Copy, Edit/Paste Special/Values.
Comments


The “column input cell” must not have anything in it.
This is a different use of the Data Table than in
Supplement A. Here, the Data Table is being “faked”
into recalculating the simulation output measure 250
times.
© Wiley 2007
Supplement C Highlights






A computer simulation is a model that mimics what might happen in reality.
Computer simulations model the uncertainty present in a system by generating
random numbers from known probability distributions.
Simulation is a valuable tool because it can simultaneously consider the
uncertainty present in many factors of a problem, and provide outputs that show
how the “input” uncertainty translates into uncertainty in the output measure.
Monte-Carlo Simulation can be conducted using Excel without any add -ins.
Commercial add -ins such as Crystal Ball and @Risk provide additional
functionality that is more difficult to employ using stand-alone Excel.
Simple Discrete- Event Simulations can be conducted in Excel, but separate
software products, such as ProModel, ProcessModel, and Extend are better
suited to modeling of systems whose state and behavior change over time.
The simulation modeling process in spreadsheets consists of developing a
deterministic model with correct logic, determining the appropriate probability
distributions to use for the random inputs, incorporating those distributions in
the model itself, running many replications of the simulation model, any
analyzing the simulation results by computing and interpreting summary
statistical measures.
© Wiley 2007
Supplement C Highlights
(continued)




Each time Excel’s RAND() function calculates, it generates a uniformly distributed random number between 0 and 1, denoted U(0,1).
Random numbers from probability distributions (e.g., Bernoulli, discrete
uniform, general discrete, continuous uniform, normal, and exponential,
among others) are derived from a U(0,1) random number through
mathematical calculations.
Replications of simulation models in Excel can be performed by copying the
entire logic itself or by using Excel’s Data Table feature. For simple models
where the logic fits into a single row, copying the logic itself is acceptable.
However, for more complex models, the Data Table feature should be used.
At a minimum, one should consider basic summary statistics such as the
average, standard deviation, minimum, and maximum when interpreting
results from a simulation. One should also compute a confidence interval to
assess the precision of the estimate for the mean, and to determine whether
additional replications should be run. It is also a good idea to generate a
histogram of the results to see the actual probability distribution of the output
measure.
© Wiley 2007
The End
Copyright © 2007 John Wiley & Sons, Inc. All rights
reserved. Reproduction or translation of this work
beyond that permitted in Section 117 of the 1976
United State Copyright Act without the express
written permission of the copyright owner is unlawful.
Request for further information should be addressed
to the Permissions Department, John Wiley & Sons,
Inc. The purchaser may make back-up copies for
his/her own use only and not for distribution or
resale. The Publisher assumes no responsibility for
errors, omissions, or damages, caused by the use of
these programs or from the use of the information
contained herein.
© Wiley 2007