Transcript Chapter 18

Chapter 18
If mathematical analysis is too difficult, we can
try each possibility out on paper. That way we
can find which alternative appears to work best
over a series of hypothetical futures.
Simulation
1
Monte Carlo Simulation
 Simulation is a trial and error approach.
 Possible future cases are generated in
accordance with underlying probabilities.
 Reality is duplicated using simple bookkeeping type record keeping.
 No complex mathematics is needed.
 Numerical solutions are provided because:
 Analytic solutions may be difficult to obtain.
 Models require unrealistic assumptions.
2
Queuing Simulation of
Sammy Lee’s Barbershop
 Probability distributions are obtained for
times between arrival and for service.
 Random numbers generate time events.
 A log is kept.
 Reality is duplicated just as if real customers
were arriving for haircuts.
 Times are not real. Customers are not real.
 The log entries are analyzed for key
summary statistics.
 These may be analogous to what would be
instead obtained from a mathematical model.
3
Queuing Simulation of
Sammy Lee’s Barbershop
4
PERT Simulation
 Simulation is a valuable tool in PERT
because:
 Activity completion times are uncertain.
 The three-time-estimate approach gives useful
probability distributions.
 Traditional methods erroneously focus on
single paths.
 Simulation simply runs the project on paper
many times.
 Project completion times may be evaluated
statistically.
5
Probabilities for Time to
Construct a House
 The following data apply.
6
Simulating House Construction
with PERT
 QuickQuant provided these simulation results.
7
Simulating House Construction
with PERT
 The second QuickQuant screen tells us about
unexpected longest paths.
8
 The a priori critical path was of longest duration
only 437 times out of 500. In some projects, it
may be longest as little as 1% of the time, or less.
Simulation
Spreadsheet Templates and Software
 Simulation Templates
 Palisade Decision Tools @RISK 4.0
9
Simulation Spreadsheet Templates
 M/M/1 discrete arrivals and service
 M/M/1 exponential arrivals and service
 M/M/1 repeated simulation with Excel’s
data table option
 Inventory, Discrete Demand,
Backordering
 Forecasting two parameter exponential
smoothing
 Risk Analysis
 Palisade Decision Tools @RISK 4.0
10
Generating Random Numbers
(Figure 18-6)
Entering =RAND() in a cell returns a
random number between zero and one.
A
B
1 20 Random Numbers
2
3
0.04513 0.54380 3
4
0.81522 0.90410 4
5
0.49520 0.33119 5
6
0.59232 0.84140 6
7
0.39428 0.57553 7
8
0.40504 0.50753 8
9
0.10550 0.68953 9
10
0.29177 0.04796 10
11
0.70949 0.20935 11
12
0.10150 0.26348 12
11
C
A
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
D
E
B
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=RAND()
=100*RAND() returns a two digit
random number between 00 and 99
Simulation of Sammy Lee’s
Barbershop (Figure 18-7)
A
1. Enter the
arrival
distribution in
A29:B34 and the
service
distribution in
D29:E34 (shown
next).
2. Average
results, Wq, W,
Lq, and L are in
cells G28:H31
(shown after the
arrival and
service
distributions).
12
B
C
D
E
F
G
1
Sammy Lee's Barbershop Simulation Example
2
3
Time
Clock
Clock time
Clock time
4
Between
time at
at begin. Service at end of Waiting
5
Trial
Arrivals
arrival
of service time
service
time
6
1
0:10
9:10 AM
9:10 AM
0:25
9:35 AM
0:00
7
2
0:10
9:20 AM
9:35 AM
0:15
9:50 AM
0:15
8
3
0:25
9:45 AM
9:50 AM
0:10 10:00 AM
0:05
9
4
0:20 10:05 AM 10:05 AM
0:15 10:20 AM
0:00
10
5
0:20 10:25 AM 10:25 AM
0:20 10:45 AM
0:00
11
6
0:05 10:30 AM 10:45 AM
0:15 11:00 AM
0:15
12
7
0:10 10:40 AM 11:00 AM
0:10 11:10 AM
0:20
13
8
0:15 10:55 AM 11:10 AM
0:05 11:15 AM
0:15
14
9
0:15 11:10 AM 11:15 AM
0:20 11:35 AM
0:05
15
10
0:05 11:15 AM 11:35 AM
0:20 11:55 AM
0:20
16
11
0:20 11:35 AM 11:55 AM
0:10 12:05 PM
0:20
17
12
0:15 11:50 AM 12:05 PM
0:15 12:20 PM
0:15
18
13
0:15 12:05 PM 12:20 PM
0:30 12:50 PM
0:15
19
14
0:20 12:25 PM 12:50 PM
0:15
1:05 PM
0:25
20
15
0:25 12:50 PM
1:05 PM
0:30
1:35 PM
0:15
21
16
0:15
1:05 PM
1:35 PM
0:10
1:45 PM
0:30
22
17
0:25
1:30 PM
1:45 PM
0:15
2:00 PM
0:15
23
18
0:15
1:45 PM
2:00 PM
0:15
2:15 PM
0:15
24
19
0:10
1:55 PM
2:15 PM
0:15
2:30 PM
0:20
25
20
0:30
2:25 PM
2:30 PM
0:15
2:45 PM
0:05
26 Average 0:16:15
0:16:15
0:13:30
27
B
C D
E
F
G
=AVERAGE(E6:E25)
=AVERAGE(G6:G25)
35 26 =AVERAGE(B6:B25)
B
C
D
36
6 =VLOOKUP(RAND(),$A$29:$B$34,2) =+B6+9/(24) =+C6
37
7 =VLOOKUP(RAND(),$A$29:$B$34,2) =+C6+B7
=IF(C7<F6,F6,C7)
38 8 =VLOOKUP(RAND(),$A$29:$B$34,2) =+C7+B8
=IF(C8<F7,F7,C8)
39 9 =VLOOKUP(RAND(),A$29:B$34,2)
=+C8+B9
=IF(C9<F8,F8,C9)
40
E
F
G
41
6 =VLO O KUP(RAND(),$D$29:$E$34,2) =D6+E6
=D6-C6
7 =VLO O KUP(RAND(),$D$29:$E$34,2) =D7+E7
=D7-C7
42
8 =VLO O KUP(RAND(),$D$29:$E$34,2) =D8+E8
=D8-C8
43
3. Depress the
F9 key to get a
new simulation.
4. If more than
20 trials are
desired, copy
the formulas
down until the
desired number
is obtained. The
ranges in the
three average
formulas must
be adjusted to
take this into
account.
Arrival and Service Distributions
(Figure 18-8)
28
29
30
31
32
33
34
35
A
B
Arrival Distribution
0
0:05
0.1
0:10
0.25
0:15
0.5
0:20
0.75
0:25
0.9
0:30
C
29
30
31
32
33
34
B
=5/(24*60)
=10/(24*60)
=15/(24*60)
=20/(24*60)
=25/(24*60)
=30/(24*60)
D
E
Service Distribution
0
0:05
0.05
0:10
0.25
0:15
0.65
0:20
0.85
0:25
0.95
0:30
F
29
30
31
32
33
34
E
=5/(24*60)
=10/(24*60)
=15/(24*60)
=20/(24*60)
=25/(24*60)
=30/(24*60)
If the arrival and service distributions have more than 6
probabilities then the table ranges in columns B and E must be
adjusted to take this into account.
13
Summary Results
(Figure 18-9)
Average results: Wq, W, Lq, and L.
G
27
28
29
30
31
14
H
I
J
K
H
Wq =
W=
Lq =
L=
00:13:30
00:29:45
0.78
1.72
28
29
30
31
=G26
=SUM(E6:E25,G6:G25)/20
=SUM(G6:G25)/(F25-(C6-B6))
=SUM(G6:G25,E6:E25)/(F25-(C6-B6))
Inventory Simulation Data
(Figure 18-15 top portion)
Discrete Demand, Backordering
A
1. Enter the
problem
name in C3.
B
C
D
E
F
G
H
I
1 INVENTORY SIMULATION (Backordering, Deterministic Demand)
2
3 PROBLEM:
XYZ Copy Paper Policy
4
5
Problem Information
6
Fixed Cost per Order:
k=
20
7
Unit Cost of Procuring an Item:
c=
2
8
Annual Holding Cost per Dollar Value:
h=
0.25
2. Enter the
pS =
9
Penalty for Each Item Short:
2
problem
pR =
10
Selling Price per Unit:
4
11
Number of Periods per Year:
12
information
12
Beginning Inventory:
200
13
Order Quantity:
Q=
438
in G6:G15
14
Reorder Point:
r=
200
15
Lead Time:
1
16
17
Period
Demand
18
1
150
3. Enter the demands in
19
2
100
20
3
200
D18:E25. If you have
21
4
250
more than 8 periods,
22
5
200
23
6
150
expand the table down to
24
7
300
include all your demands.
25
8
250
.
15
11
12
Inventory Simulation Results
13
14
15
(Figure 18-15 bottom portion)
16
17
1. Simulation results.
18
2. The simulation details for each period.
19
20
21
22
A
B
C
D
E
F
Simulation Results (Backordering)
Number of periods
8
Procurement cost
$2628.00
Ordering cost
$60.00
Holding cost
$45.50
Shortage cost
$296.00
Total cost
$3029.50
Ending inventory
-86
Value of ending inventory
-$172.00
Net cost
$3201.50
Average net cost per period
$400.19
Number of trial periods simulated
8
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 Period
43
1
44
2
45
3
46
4
47
5
48
6
49
7
50
8
51 Ave.
Beginning
Inventory
200
50
388
188
-62
176
26
164
141
16
Quantity
Order
Received Quantity
0
438
0
0
438
0
438
0
164
438
0
0
438
0
438
0
438
219
G
H
150
100
200
250
200
150
300
250
200
J
K
L
3. If more than 8 demands are
entered in D18:E25 (shown
previously) copy row 50 down
the same number of rows and
adjust the AVERAGE formulas
in row 51 to include all the
periods.
Log of Inventory Simulation (Backordering)
Ending
Procurement
Due In
Demand Inventory
Cost
2
5
7
9
I
50
388
188
-62
176
26
164
-86
106
$0.00
$876.00
$0.00
$0.00
$876.00
$0.00
$876.00
$0.00
$328.50
Order
Cost
$0.00
$20.00
$0.00
$0.00
$20.00
$0.00
$20.00
$0.00
$7.50
Holding
Cost
$5.21
$9.13
$12.00
$3.92
$3.67
$4.21
$3.96
$3.42
$5.69
Shortage
Cost
$0.00
$0.00
$0.00
$124.00
$0.00
$0.00
$0.00
$172.00
$37.00
23
24
25
Total Cost
$5.21
$905.13
$12.00
$127.92
$899.67
$4.21
$899.96
$175.42
$378.69
8
9
1
Inventory Simulation Formulas
1
1
1
1
Simulation results formulas.
1
1
1
1
1
2
2
2
A
B
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 Period
43
1
44
2
45
3
46
4
47
5
48
6
49
7
Beginning
Inventory
200
50
388
188
-62
176
17
26
C
D
E
F
Simulation Results (Backordering)
Number of periods
8
Procurement cost
$2628.00
Ordering cost
$60.00
Holding cost
$45.50
Shortage cost
$296.00
Total cost
$3029.50
Ending inventory
-86
Value of ending inventory
-$172.00
Net cost
$3201.50
Average net cost per period
$400.19
Number of trial periods simulated
8
Quantity
Order
Received Quantity
0
438
0
0
438
0
438
438
0
0
438
0
438
0
G
28
29
30
31
32
33
34
35
36
37
38
F
H
=MAX(D18:D25)
=SUM(H43:H50)
=SUM(I43:I50)
=SUM(J43:J50)
=SUM(K43:K50)
=SUM(L43:L50)
=G50
=G50*G7
=ABS(F35-F33)
=F36/F28
=MAX(D18:D25)
Log of Inventory Simulation (Backordering)
Ending
Procurement
Due In
Demand Inventory
Cost
2
5
7
-
150
100
200
250
200
150
300
50
388
188
-62
176
26
164
$0.00
$876.00
$0.00
$0.00
$876.00
$0.00
$876.00
I
J
K
L
2
2
2
Order
Cost
$0.00
$20.00
$0.00
$0.00
$20.00
$0.00
$20.00
Holding
Cost
$5.21
$9.13
$12.00
$3.92
$3.67
$4.21
$3.96
Shortage
Cost
$0.00
$0.00
$0.00
$124.00
$0.00
$0.00
$0.00
Total Cost
$5.21
$905.13
$12.00
$127.92
$899.67
$4.21
$899.96
29
30
31
32
33
34
35
36
37
38
39
40
41
42 Period
43
1
44
2
45
3
46
4
47
5
48
6
49
7
50
8
51 Ave.
B
43 =G12
44 =G43
45 =G44
Procurement cost
$2628.00
Ordering cost
$60.00
Holding cost
$45.50
Shortage cost
$296.00
Total cost
$3029.50
Ending inventory
-86
Value of ending inventory
-$172.00
Net cost
$3201.50
Average net cost per period
$400.19
Number of trial periods simulatedSimulation detail
8
Inventory Simulation Formulas
Beginning
Inventory
200
50
388
188
-62
176
26
164
141
Quantity
Order
Received Quantity
0
438
0
0
438
0
438
0
164
438
0
0
438
0
438
0
438
219
formulas.
Log of Inventory Simulation (Backordering)
Ending
Procurement
Due In
Demand Inventory
Cost
2
5
7
9
150
100
200
250
200
150
300
250
200
50
388
188
-62
176
26
164
-86
106
$0.00
$876.00
$0.00
$0.00
$876.00
$0.00
$876.00
$0.00
$328.50
C
D
=IF(A43<=$G$15,0,OFFSET(C43,-$G$15,1)) =IF(B43+C43<=$G$14,$G$13,0)
=IF(A44<=$G$15,0,OFFSET(C44,-$G$15,1)) =IF(B44+C44<=$G$14,$G$13,0)
=IF(A45<=$G$15,0,OFFSET(C45,-$G$15,1)) =IF(B45+C45<=$G$14,$G$13,0)
G
43 =B43+C43-F43
44 =B44+C44-F44
45 =B45+C45-F45
H
I
=C43*$G$7 =IF(C43>0,$G$6,0)
=C44*$G$7 =IF(C44=$G$13,$G$6,0)
=C45*$G$7 =IF(H39=$G$13,$G$6,0)
J
43 =((IF(B43>0,B43,0)+IF(G43>0,G43,0))/2)*($G$8/$G$11)*$G$7
44 =((IF(B44>0,B44,0)+IF(G44>0,G44,0))/2)*($G$8/$G$11)*$G$7
45 =((IF(B45>0,B45,0)+IF(G45>0,G45,0))/2)*($G$8/$G$11)*$G$7
18
Order
Cost
$0.00
$20.00
$0.00
$0.00
$20.00
$0.00
$20.00
$0.00
$7.50
Holding
Cost
$5.21
$9.13
$12.00
$3.92
$3.67
$4.21
$3.96
$3.42
$5.69
Shortage
Cost
$0.00
$0.00
$0.00
$124.00
$0.00
$0.00
$0.00
$172.00
$37.00
Total Cost
$5.21
$905.13
$12.00
$127.92
$899.67
$4.21
$899.96
$175.42
$378.69
E
F
=IF(D43>0,A43+$G$15,"-") =E18
=IF(D44>0,A44+$G$15,"-") =E19
=IF(D45>0,A45+$G$15,"-") =E20
B
51 =AVERAGE(B43:B50)
K
L
=IF(G43<0,-G43*$G$9,0) =H43+I43+J43+K43
=IF(G44<0,-G44*$G$9,0) =H44+I44+J44+K44
=IF(G45<0,-G45*$G$9,0) =H45+I45+J45+K45
Other Inventory Simulation
Templates
 Discrete Demand, Lost Sales
 Normal Demand, Backordering
 Normal Demand, Lost Sales
19
Discrete Demand, Lost Sales
Inventory Simulation Results
1. The data
section (top
portion) of the
spreadsheet is
identical with
the backorders
A
B
case seen
27
previously.
28
Bottom Portion of Spreadsheet
C
D
E
F
Simulation Results (Lost Sales)
Number of periods
8
Procurement cost
$2628.00
Ordering cost
$60.00
Holding cost
$47.75
Shortage cost
$1096.00
Total cost
$3831.75
Ending inventory
188
Value of ending inventory
$376.00
Net cost
$3455.75
Average net cost per period
$431.97
Number of trial periods simulated
8
29
30
31
32
33
34
35
36
37
38
39
40
41
3. Formulas
not shown are
the same as
for the
backorders
case.
42 Period
43
1
44
2
45
3
46
4
47
5
48
6
49
7
50
8
51 Ave.
Beginning
Inventory
200
50
388
188
0
238
88
0
144
Quantity
Order
Received Quantity
0
438
0
0
438
0
0
438
164
438
0
0
438
0
0
438
0
164
Due In
2
5
8
-
G
H
28
29
30
31
32
33
I
F
=MAX(D18:D25)
=SUM(I43:I50)
=SUM(J43:J50)
=SUM(K43:K50)
=SUM(L43:L50)
=SUM(M43:M50)
Log of Inventory Simulation (Lost Sales)
Ending
Procurement
Demand Inventory Lost Units
Cost
150
100
200
250
200
150
300
250
200
50
388
188
0
238
88
0
188
143
0
0
0
62
0
0
212
0
34
G
H
43 =IF(B43+C43-F43<0,0,B43+C43-F43) =IF(B43+C43-F43<0,-B43-C43+F43,0)
44 =IF(B44+C44-F44<0,0,B44+C44-F44) =IF(B44+C44-F44<0,-B44-C44+F44,0)
K
43 =((IF(B43>0,B43,0)+IF(G43>0,G43,0))/2)*($G$8/$G$11)*$G$7
20
44 =((IF(B44>0,B44,0)+IF(G44>0,G44,0))/2)*($G$8/$G$11)*$G$7
J
$0.00
$876.00
$0.00
$0.00
$876.00
$0.00
$0.00
$876.00
$328.50
2. Modifications
L
M
are Kin columns
G,
H, and K. The:
ending inventory
cannot be
negative, lost sales
are computed, and
the lost sales
shortage cost is
utilized.
Order
Cost
Holding
Cost
$0.00
$20.00
$0.00
$0.00
$20.00
$0.00
$0.00
$20.00
$7.50
I
=C43*$G$7
=C44*$G$7
$5.21
$9.13
$12.00
$3.92
$4.96
$6.79
$1.83
$3.92
$5.97
Shortage
Cost
$0.00
$0.00
$0.00
$248.00
$0.00
$0.00
$848.00
$0.00
$137.00
Total Cost
$5.21
$905.13
$12.00
$251.92
$900.96
$6.79
$849.83
$899.92
$478.97
J
=IF(C43>0,$G$6,0)
=IF(C44=$G$13,$G$6,0)
L
M
=H43*($G$9+$G$10-$G$7) =I43+J43+K43+L43
=H44*($G$9+$G$10-$G$7) =I44+J44+K44+L44
Inventory Simulations
with Normal Demand
For normally distributed demands, the
spreadsheets are similar to the discrete demand
cases. The only modifications are two new rows
in the data section containing m and s, the mean
and standard deviation of the normal distribution,
and the demands in column F are generated
according to a normal distribution
=NORMINV(RAND(),m,s)
The templates for these cases are on the CD-ROM.
21
Forecasting Simulation
(Figure 18-16)
A
1. Enter the
problem name
in C3.
1
2
3
4
5
6
7
8
9
10
11
12
5. Mean Squared Error
22
13
14
15
16
17
18
19
20
21
22
23
24
25
26
112
113
114
115
116
117
B
C
D
E
FORECASTING SIMULATION RESULTS
PROBLEM:
Sentinel Diesel Fuel Forecasting
Problem Parameters
Slope of Actual Trend Line =
Standard Deviation for Actual Trend Line =
Smoothing Constant a =
Trend Smoothing Constant g =
Number of Trials =
20
5
0.10
0.20
100
B
2. Enter the
problem
parameters in
E7:E11
16 1000
17 =1000+$E$7*A16+ NORMINV(RAND(),0,$E$8)
Period
t
1
2
3
4
5
6
7
8
9
10
11
97
98
99
100
Actual
Sales, Yt
1,000
1,026
1,045
1,062
1,079
1,096
1,120
1,140
1,155
1,184
1,200
2,919
2,941
2,963
2,976
MSE =
Trend
Tt
1,000
1,028
1,055
1,081
1,106
1,131
1,155
1,178
1,201
1,223
2,918
2,938
2,958
2,978
456.82
Trend
Slope, bt
Forecast
Sales, Ft
25.8
26.2
26.38
26.3
26.1
25.9
25.5
25.0
24.6
24.1
19.6
19.7
19.8
19.8
1,025.8
1,054.0
1,081.2
1,107.3
1,132.3
1,157.0
1,180.7
1,203.2
1,225.9
2,917.8
2,937.6
2,957.6
2,978.0
3. Depress
the F9 key to
get a new
simulation.
4. Periods 12
- 96 are
hidden so the
results fit on
one page.
1. To repeat the
barbershop
simulation 100
times, enter the
numbers 1, 2, . .
. , 100 in cells
A44:A143.
Repeating Simulations with Excel’s
Data Table Option (Figure 18-17)
B
43 =$H$28
A
2. Enter the formulas
shown in cells
B43:D43 (they refer
back to Fig 18-8
shown previously).
3. Highlight cells
A43:E143, click on
Data on the menu bar
and select Table to get
the Table dialog box
shown next.
23
42
43 Trial
44
1
45
2
46
3
47
4
48
5
139
96
140
97
141
98
142
99
143
100
C
=$H$29
B
Wq
12.50
17.75
3.75
1.75
6.75
24.75
17.75
4.75
2.75
12.50
6.75
D
=$H$30
C
W
29.25
37.50
19.25
17.75
22.75
43.75
34.00
20.00
17.50
29.75
24.50
E
=$H$31
D
Lq
0.6757
0.8256
0.1807
0.0729
0.3418
1.1786
0.9221
0.2500
0.1358
0.6757
0.3553
E
L
1.5811
1.7442
0.9277
0.7396
1.1519
2.0833
1.7662
1.0526
0.8642
1.6081
1.2895
4. Click the cursor in the Column input cell line, then on an empty cell,
and then click the OK button. Cells A44:E143 will fill with the results of
100 simulations. The numbers you obtain will be different because of the
random nature of the simulation process.
Repeating Simulations with Excel’s
Data Table Option (Figure 18-17)
Clicking on Data and selecting the Data
Table option yields the Table dialog box.
Click in the Column input cell line, then click on
any empty cell, and finally click the OK button.
The result is 100 repetitions of the barbershop
simulation.
24
If a different number of repetitions is desired, highlight a different number of
rows (the number of repetitions is equal to the number of rows highlighted).
Frequency Distribution
(Figure 18-18)
Frequency Distribution of L, the Mean
Number of Customers in the Barbershop
25
20
Frequency
1. Using the
Chart Wizard to
graph the
results of
repeated
simulation trials
makes it easy to
see how
simulations
results vary.
15
10
5
0
0.25 0.75
1
1.25 1.5 1.75
2
2.25 2.5 2.75
3
3.25 3.5 More
L, Mean number of Customers in the Barbershop
2. Here the 100 repetitions of L are graphed for the
barbershop simulation. The L from the one simulation in
Fig 18-9 is 1.72. It appears to be a rather untypical value.
25
M/M/S Data Table Simulation
Template
1. Enter the problem
name in C3.
A
B
C
L
3 =VLOOKUP(I4,A14:F100,3)
2. Enter the problem
parameters in E7:E11
D
E
F
G
4 =VLOOKUP(I4,A14:F100,5)
H
I
J
K
L
B ASIC Q UE UIN G SY ST EM E VAL UAT IO N - - M UL TIP LE S ER VE RS
1
2
3
P R O B L EM :
M in C o s t =
M illio n T e lle r B a n k
O p tim a l S =
4
5
L q = 0 .2 7
$3 7.64
W
3
q
= 0 .3 1
P a r a m e t e r V a lu e s :
6
M e a n C u s t o m e r A rr iv a l R a t e : la m b d a =
0 .875
7
M e a n C u s t o m e r S e r v ic e R a t e :  m u  =
0 .564
8
C u s t o m e r C o s t p e r U n it o f T im e =
9
S e rv e r C o s t p e r U n it o f T im e =
6
12
10
I
3 =MIN(L14:L100)
4 =MATCH(I3,L14:L100,0)
11
12
S e rv e r
C us tom er
T o ta l
W
C os t
C o s t (W q )
C o s t (W q)
C u s to m e r
T o ta l
T otal
C o s t (W )
C o st( W )
C o st( W q)
13
S e rv e r s
P0
15
2
0.1 267
2.33 22
3.88 24
2.666 6
4 .4 3 8 9
$ 2 4 .0 0
$1 3.99
$3 7.99
$ 2 3 .2 9
$4 7.29
$ 3 7 .9 9
16
3
0.1 986
0.27 27
1.82 28
0.311 8
2 .0 8 4 1
$ 3 6 .0 0
$ 1.64
$3 7.64
$ 1 0 .9 4
$4 6.94
$ 3 7 .6 4
17
4
0.2 099
0.05 22
1.60 23
0.059 6
1 .8 3 2 0
$ 4 8 .0 0
$ 0.31
$4 8.31
$ 9 .6 1
$5 7.61
$ 4 8 .3 1
18
5
0.2 118
0.01 03
1.56 04
0.011 8
1 .7 8 4 1
$ 6 0 .0 0
$ 0.06
$6 0.06
$ 9 .3 6
$6 9.36
$ 6 0 .0 6
26
Lq
L
W
q
This portion of the spreadsheet calculates
optimal number of servers and the
corresponding minimum cost, Lq, and Wq
for the M/M/S model. This information is
used as input for a 100 trial simulation using
Excel’s Data Table option, shown next.
M/M/S Data Table Simulation
Template
Each time the F9 key is depressed a
new 100 trial simulation is obtained.
19
20
21
22
23
24
25
116
117
118
119
120
27
N
Trial
1
2
3
4
5
96
97
98
99
100
O

0.890
2.256
0.691
6.201
1.763
2.325
1.676
0.677
0.192
0.282
0.016
P
m
0.739
0.566
0.704
0.553
0.317
0.234
0.346
0.088
0.055
0.463
0.441
Q
Min Cost
$28.10
$72.87
$25.87
$175.35
$95.02
$156.45
$85.48
$127.03
$65.00
$17.70
$12.01
R
Opt S
2
5
2
13
7
12
6
10
5
1
1
S
Lq
0.68
2.146
0.311
3.225
1.836
2.075
2.246
1.172
0.833
0.949
0.001
T
Wq
0.77
0.951
0.450
0.520
1.042
0.892
1.340
1.730
4.345
3.369
0.086
Exponential Arrivals and Service
(Figure 18-19)
1. To redo the
barbershop
simulation in Fig. 187 with exponential
interarrival and
service times, the
formulas in B6 and
E6 are changed (as
shown) and copied
down to row 25 (trial
20).
2. Cell B36 in
the formula in
cell B6 contains
o the mean
interarrival time.
28
B
6 =-B$36*LN(RAND())/(24*60)
A
B
C
D
E
6 =-E$36*LN(RAND())/(24*60)
E
F
G
1
Sammy Lee's Barbershop Simulation Example
2
Expnential Arrivals and Service Times
3
Time
Clock
Clock time
Clock time
4
Between time at
at begin. Service
at end of Waiting
5 Trial Arrivals
arrival
of service
time
service
time
6
1
0:05
9:05 AM
9:05 AM
0:12
9:18 AM
0:00
7
2
0:17
9:23 AM
9:23 AM
0:23
9:46 AM
0:00
8
3
0:44 10:08 AM 10:08 AM
0:12 10:20 AM
0:00
9
4
0:17 10:25 AM 10:25 AM
0:14 10:39 AM
0:00
10
5
0:06 10:31 AM 10:39 AM
0:01 10:40 AM
0:07
11
6
0:02 10:34 AM 10:40 AM
0:02 10:43 AM
0:06
12
7
0:29 11:03 AM 11:03 AM
0:05 11:09 AM
0:00
13
8
0:15 11:19 AM 11:19 AM
0:17 11:37 AM
0:00
14
9
0:00 11:20 AM 11:37 AM
0:22 11:59 AM
0:17
15
10
0:09 11:29 AM 11:59 AM
0:05 12:05 PM
0:29
16
11
0:39 12:09 PM 12:09 PM
1:02
1:11 PM
0:00
17
12
0:31 12:40 PM
1:11 PM
0:00
1:11 PM
0:30
18
13
0:23
1:04 PM
1:11 PM
0:06
1:17 PM
0:06
19
14
0:24
1:28 PM
1:28 PM
0:06
1:34 PM
0:00
20
15
0:10
1:38 PM
1:38 PM
0:00
1:39 PM
0:00
21
16
0:07
1:45 PM
1:45 PM
0:31
2:17 PM
0:00
22
17
0:15
2:01 PM
2:17 PM
0:20
2:38 PM
0:16
23
18
0:12
2:14 PM
2:38 PM
0:24
3:02 PM
0:24
24
19
0:01
2:15 PM
3:02 PM
0:13
3:16 PM
0:46
25
20
0:45
3:01 PM
3:16 PM
0:37
3:54 PM
0:14
3. Cell E36 in
the formula in
cell E6 contains
o the mean
service time.
Four Seasons Villages
(Figure 18-21)
A
1. In financial
analysis a result,
such as a rate of
return or return
in investment, is
calculated
based on
estimates of all
the factors
involved.
2. In this
example,
estimates of
revenues and
costs lead to a
calculated return
on investment of
19.90%
29
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
B
C
D
E
F
Four Seasons Villages
Revenue
Hotel
Motel
Restaurants
Theaters
Bowling
Billards
Archery
Ice Skating
Retail Stores
Snack Shops
Total Revenues
$
$
$
$
$
$
$
$
$
$
$
20,400,000
5,740,000
12,770,000
14,640,000
1,960,000
850,000
345,000
1,544,000
18,345,000
950,000
77,544,000
$
$
$
$
$
$
$
$
13,100,000
5,400,000
1,100,000
5,100,000
2,870,000
8,530,000
16,000,000
52,100,000
$
$
$
$
4,186,400
47,913,600
22,998,528
24,915,072
19.93%
Expenses
Common Area
Advertising
Insurance
Security Guards
Parking Attendants
Real Estate Taxes
Land Lease
Total Expenses
Net Operating Profit
Depreciation
Profit Before Taxes
Taxes
Net Profit After Taxes
Return on Investment
D
14 =SUM(D4:D13)
D
23 =S UM (D16:D22)
26
27
28
29
D
= D23-D25
=D26*0.48
= D26-D27
=D28/125000000
3.Because this
analysis does
not take
possible
uncertainties in
revenues and
costs into
account, the
calculated return
on investment
might be
misleading.
Risk Analysis
Risk Analysis considers the uncertainty
in all the factors that affect a result. It uses
simulation to determines the result’s
probability distribution. Two ways of
performing repeated simulations are:
 Excel’s Data Table option
 Palisade Decision Tool’s @RISK
30
Four Seasons Villges Risk Analysis
(Figure 18-22)
1. Revenues and
some costs are
assumed to be
normally
distributed with the
means in column E
and standard
deviations in
column F. The
formulas are shown
next.
3. 100 simulations
using either Excel’s
data table option or
@RISK yields the
return on
investment
histogram shown
next.
31
A
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
B
C
D
E
F
Four Seasons Villages
Revenue
Hotel
Motel
Restaurants
Theaters
Bowling
Billards
Archery
Ice Skating
Retail Stores
Snack Shops
Total Revenues
$
$
$
$
$
$
$
$
$
$
Standard
Deviation
5,340,000
1,000,000
4,350,000
3,300,000
505,000
200,000
100,000
200,000
5,000,000
300,000
$
$
$
$
$
$
$
1,500,000
1,000,000
200,000
1,000,000
500,000
100,000
-
$ 4,186,400 $
$ 47,913,600
$ 22,998,528
$ 24,915,072
19.93%
200,000
$
$
$
$
$
$
$
$
$
$
$
Normal
25,344,365
5,686,791
10,941,615
10,964,598
1,801,710
820,415
275,917
1,673,893
14,321,316
868,605
72,699,225
Mean
20,400,000
5,740,000
12,770,000
14,640,000
1,960,000
850,000
345,000
1,544,000
18,345,000
950,000
77,544,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
14,781,892
6,221,008
1,301,882
4,617,922
2,998,575
8,457,940
16,000,000
54,379,219
$ 13,100,000
$ 5,400,000
$ 1,100,000
$ 5,100,000
$ 2,870,000
$ 8,530,000
$ 16,000,000
$ 52,100,000
$
$
$
$
4,354,205
50,025,014
24,012,007
26,013,007
20.81%
Expenses
Common Area
Advertising
Insurance
Security Guards
Parking Attendants
Real Estate Taxes
Land Lease
Total Expenses
Net Operating Profit
Depreciation
Profit Before Taxes
Taxes
Net Profit After Taxes
Return on Investment
Return on Investment Histogram
(Figure 18-23)
Frequency
Histogram
40
30
20
10
0
16% 17% 18% 19% 20% 21% 22% 23% 24% More
Return on Investment
This histogram indicates that the return on investment probably will be
some what higher that the 19.9% original estimate. It also indicates that
the chance of a negative return on investment is zero.
32
1. @RISK
provides several
analytical tools,
including
information on
the sensitivity of
each output
variable to the
input
distributions.
2. As an
illustration, this
Tornado graph
shows the
correlation
between each
input and the
return on
investment. The
higher the
correlation the
more significant
is the input in
determining the
output’s value.
33
Tornado Graph
(Figure 18-24)
Correlations for Return on Investment /D29
16
15
14
Security Guards /D19
13
Parking Attendants /D20
12
Depreciation /D25
11
Insurance/D18
10
Bowling /D8
9
Ice Skating /D11
8
Hotel /D4
7
Real Estate Taxes /D21
6
Archery /D10
5
Retail Stores /D12
4
Restaurants /D6
3
Billards /D9
2
Motel /D5
1
Theaters /D7
0
-1.00
Common Area /D16
Advertising /D17
.716
.456
.406
.211
-.135
.087
-.078
.065
-.049
.037
-.036
-.027
-.021
Corr Coeff
calculated at
end of bars
-.02
.008
-.006
-.50
.00
.50
Coefficient Value
3. Here, the common area cost is the most significant factor.
1.00
Four Seasons Villges Risk Analysis
Formulas
1. If Excel’s Data Table
option is used to do the
simulation use the
formula below in cell D4.
A
B
C
1
Four
2
3 Revenue
4
Hotel
5
Motel
6
Restaurants
D
7
Theaters
4 = N O R M I N V ( R A N D ( ), E 48, F 4 )
Bowling
9
Billards
2. If @RISK is used to do
10
Archery
11
Ice Skating
the simulation use the
12
Retail Stores
formula below in cell D4.
13
Snack Shops
14
Total Revenues
15 Expenses
16
Common Area
D
17
Advertising
Insurance
4 = R isk N o r m a l( E 4 , F 4 ) 18
19
Security Guards
20
Parking Attendants
21
Real Estate Taxes
3. The formula used in
22
Land Lease
cell D4 is copied down to
23
Total Expenses
dells D5:D14, D16:D21,
24 Net Operating Profit
25
Depreciation
and D25.
26 Profit Before Taxes
27
Taxes
28 Net Profit After Taxes
34
29 Return on Investment
D
E
F
Seasons Villages
$
$
$
$
$
$
$
$
$
$
Standard
Deviation
5,340,000
1,000,000
4,350,000
3,300,000
505,000
200,000
100,000
200,000
5,000,000
300,000
$
$
$
$
$
$
$
1,500,000
1,000,000
200,000
1,000,000
500,000
100,000
-
$ 4,186,400 $
$ 47,913,600
$ 22,998,528
$ 24,915,072
19.93%
200,000
$
$
$
$
$
$
$
$
$
$
$
Normal
25,344,365
5,686,791
10,941,615
10,964,598
1,801,710
820,415
275,917
1,673,893
14,321,316
868,605
72,699,225
Mean
20,400,000
5,740,000
12,770,000
14,640,000
1,960,000
850,000
345,000
1,544,000
18,345,000
950,000
77,544,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
14,781,892
6,221,008
1,301,882
4,617,922
2,998,575
8,457,940
16,000,000
54,379,219
$ 13,100,000
$ 5,400,000
$ 1,100,000
$ 5,100,000
$ 2,870,000
$ 8,530,000
$ 16,000,000
$ 52,100,000
$
$
$
$
4,354,205
50,025,014
24,012,007
26,013,007
20.81%
Hypothesis Testing Using Excel
Figure 18-25 contains customer waiting times for 10-trial
simulations of two alternative queuing organizations A and B.
4
5
6
7
8
9
10
11
12
13
A
26.3
28.6
25.4
29.2
27.6
25.6
26.4
27.7
28.2
29.0
B
28.5
30.0
28.8
25.3
28.4
26.5
27.2
29.3
26.2
27.5
Hypothesis testing helps determine if one alternative is better than
another. The null hypothesis is that the mean waiting times are
identical under the two alternatives, under the assumption that the
variances are unequal. A 5% significance level is used for the test.
35
Data Analysis Dialog Box
Click on tools on the menu bar, select the Data Analysis option, and the
Data Analysis dialog box appears. In it highlight t-Test: Two-Sample
Assuming Unequal Variances, and click the OK button to get the tTest:Two-Sample Assuming Unequal Variances dialog box shown next.
36
t-Test: Two Sample Assuming Unequal
1. Enter A4:A13 Variances Dialog Box (Figure 18-26)
in the Variable 1
Range line (or
$A$4:$A$13).
2. Enter B4:B13
in the Variable 1
Range line (or
$B$4:$B$13).
3. Leave the
Hypothesized
Mean Difference
line blank or put
a zero in it.
4. Enter 0.05 in
the Alpha box.
5. After selecting one of the options in the
Output options section, click on the OK button.
37
t-Test Results
(Figure 18-27)
The t value of -0.5744 is much smaller than the two-tailed
critical value of 2.1009. The null hypothesis that the means do
not differ must be accepted. There appears to be no
significant difference between the two alternatives.
Variable 1 Variable 2
Mean
27.4
27.77
Variance
1.94
2.209
Observations
10
10
Hypothesized Mean Difference
0
df
18
t Stat
-0.5744
P(T<=t) one-tail
0.2864
t Critical one-tail
1.7341
P(T<=t) two-tail
0.5728
t Critical two-tail
2.1009
38
Palisade Decision Tools
@RISK
The @RISK 4.0 software program on the
CD-ROM accompanying this book can be used to
perform simulations. The software permits the
use of more than 30 probability distributions, it
has options for analyzing results and it has the
capability to incorporate correlations between
input variables.
A few of the common distributions it
permits are beta, binomial, chi-square,
exponential, gamma, geometric, hypergeometric,
normal, Poisson, triangular, and uniform.
39
@RISK
To start @RISK, click on the Windows Start
button, select Programs, Palisade Decision
Tools, then @RISK 4.0.
40
Both Excel and @RISK will open. You will
see the normal Excel screen with two new tool
bars, one for Palisade Decision Tools and the
other for @RISK. The icons on these tool bars
that will be used will be explained in the
following slides.
Four Seasons Villges
with @RISK
1. The formula
is D4 is also in
D5:D14,
D16:D21, and
D25.
2. Add cell D29
to the list of
outputs by
highlighting the
cell and clicking
on the Add
Outputs icon.
A
D
4
= R isk N o r m a
3. Click on the
Simulation
Settings icon
and the
Simulation
Settings dialog
box opens as
shown next.
41
B
C
1
Four
2
3 Revenue
4
Hotel
5
Motel
6
Restaurants
7
Theaters
8
Bowling
9
Billards
10
Archery
11
Ice Skating
12
Retail Stores
Snack Shops
l( E 4 , F13
4)
14
Total Revenues
15 Expenses
16
Common Area
17
Advertising
18
Insurance
19
Security Guards
20
Parking Attendants
21
Real Estate Taxes
22
Land Lease
23
Total Expenses
24 Net Operating Profit
25
Depreciation
26 Profit Before Taxes
27
Taxes
28 Net Profit After Taxes
29 Return on Investment
D
E
F
Seasons Villages
$
$
$
$
$
$
$
$
$
$
Standard
Deviation
5,340,000
1,000,000
4,350,000
3,300,000
505,000
200,000
100,000
200,000
5,000,000
300,000
$
$
$
$
$
$
$
1,500,000
1,000,000
200,000
1,000,000
500,000
100,000
-
$ 4,186,400 $
$ 47,913,600
$ 22,998,528
$ 24,915,072
19.93%
200,000
$
$
$
$
$
$
$
$
$
$
$
Normal
25,344,365
5,686,791
10,941,615
10,964,598
1,801,710
820,415
275,917
1,673,893
14,321,316
868,605
72,699,225
Mean
20,400,000
5,740,000
12,770,000
14,640,000
1,960,000
850,000
345,000
1,544,000
18,345,000
950,000
77,544,000
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
14,781,892
6,221,008
1,301,882
4,617,922
2,998,575
8,457,940
16,000,000
54,379,219
$ 13,100,000
$ 5,400,000
$ 1,100,000
$ 5,100,000
$ 2,870,000
$ 8,530,000
$ 16,000,000
$ 52,100,000
$
$
$
$
4,354,205
50,025,014
24,012,007
26,013,007
20.81%
Simulation Settings Dialog Box
Iteration Tab
1. Enter 100 in
the # Iterations
line.
42
3. Clicking on
the Sampling tab
yields the dialog
box shown next.
2. Enter 1 in the
# Simulations
line.
Simulation Settings Dialog Box
Sampling Tab
Under Standard
Recalc click in
the Monte Carlo
button and then
click the OK
button.
43
Clicking on the
Start Simulation
icon gives the
Summary
Statistics in the
box within the
@RISK-Results
dialog box
44
Summary Statistics
The minimum, mean, and maximum of the return on investment
plus all the input variables is given in this dialog box.
@ RISK Reports
1. In the @RISK
dialog box, click
on Results on
the menu bar,
select Report
Settings, and the
@RISK Reports
dialog box
appears.
2. A variety of
reports and
options can be
selected.
45
3. Here a
Tornado Graph
in Excel is
selected (as
seen previously
in Fig- 18-24).