Transcript PPT Presentation, Supplement C

Supplement C
Introduction to Simulation
Operations Management
by
R. Dan Reid & Nada R. Sanders
2nd Edition © Wiley 2005
PowerPoint Presentation by
Roger B. Grinde, University of New Hampshire
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
Computer Simulation


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
Computer Simulation (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.
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.
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.
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.





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.


Normal (“bell” curve)
Bernoulli (e.g., flip of a coin)
Discrete Uniform (e.g., roll of a die)
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.
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.
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).
=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.
A
B
C
D
1 50 Random Numbers between 0 and 1
2 Using =RAND() function
3
4
0.9945
0.5342
0.3149
0.6358
5
0.3848
0.5566
0.8701
0.2780
6
0.4480
0.6078
0.5301
0.8625
7
0.3980
0.1668
0.2904
0.8226
8
0.5517
0.6018
0.8232
0.5490
9
0.8009
0.2063
0.8286
0.0530
10 0.2333
0.8206
0.5036
0.3716
11 0.4665
0.4733
0.9441
0.3001
12 0.8273
0.6341
0.3681
0.0971
13 0.1276
0.2094
0.9277
0.7601
14
A4: =RAND()
15
E
0.5334
0.3822
0.3956
0.8638
0.9584
0.9614
0.1858
0.8261
0.5493
0.9193
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
1
100
0
0.1
0.2
0.3
0.4
0.5
0.6
0.7
Upper End of Category

300
200
2

400
0.8
0.9
1.0
0
0.1
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.
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.
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?
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.
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
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
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
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.
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
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
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).


Example: DU(10,50), suppose RAND()=0.37


X=INT(10+(50−10+1)*0.37) = INT(25.17) = 25
INT returns the integer part of a number


Use =INT(a+(b−a+1)*RAND())
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).
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.
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
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(µ).


Generating an EXP(µ) random value:




For example, µ would represent the average time between customer
arrivals, the average time between machine breakdowns, etc.
=−µ*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
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
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.
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?
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)
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.
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
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?
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?
DG: Histogram of Profit
Values
250
200
150
100
50
Upper End of Category
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.
Data Table Approach for DG
Problem
A
15 Simulation Logic


We only need16
17
the logic for 18
a single trial 19
20
(Row 17). 21
22
Next slide 23
gives steps 24
25
for Data
26
27
Table.
28
29
30
31
32
270
271
B
C
D
RN1 RN2 Demand
0.001 0.250
20
E
F
Reg
Sales
Qty
20
Salv
Sales
Qty
15
Data Table for Simulation Replications
B21: =J17
Replications
Profit
$ (325)
Summary Statistics
1
$ 875
Average
$ 413
2
$ 35
StdDev
$ 403
3
$ 795
Minimum $ (400)
4
$ (325)
Maximum $ 875
5
$ 75
6
$ (100)
95% Confidence Interval
7
$ 200
Lower CL $ 363
8
$ 200
Upper CL $ 463
9
$ (35)
10
$ (250)
11
$ (400)
249
$ 200
250
$ 715
G
H
Salv
Reg
Price
Rev
$20 $2,000
I
J
Salv
Rev
$300
Profit
($325)
Data Table Steps for DG Problem

Steps
1.
2.
3.
4.
5.
6.
7.

Two columns needed, for replications and 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.
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.
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.
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 theis “input” uncertainty translates into uncertainty in the output measure.
Monte-Carlo Simulation can be conducted using Excel without any aAdd -Iins.
Commercial aAdd -iIns 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.
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.
The End
Copyright © 2005 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.