Simulation

Chapter 14

Copyright © 2016 Pearson Education, Ltd. 14-1

Chapter Topics

■ The Monte Carlo Process

■ Computer Simulation with Excel Spreadsheets
■ Simulation of a Queuing System
■ Continuous Probability Distributions
■ Statistical Analysis of Simulation Results
■ Crystal Ball
■ Verification of the Simulation Model
■ Areas of Simulation Application

Copyright © 2016 Pearson Education, Ltd. 14-2

Overview

■ Analogue simulation replaces a physical system with an analogous

physical system that is easier to manipulate.

■ In computer mathematical simulation a system is replaced with a

mathematical model that is analyzed with the computer.

■ Simulation offers a means of analyzing very complex systems

that cannot be analyzed using the other management science
techniques in the text.

Copyright © 2016 Pearson Education, Ltd. 14-3

Monte Carlo Process

■ A large proportion of the applications of simulations are for

probabilistic models.

■ The Monte Carlo technique is defined as a technique for selecting

numbers randomly from a probability distribution for use in a trial
(computer run) of a simulation model.

■ The basic principle behind the process is the same as in the

operation of gambling devices in casinos (such as those in Monte
Carlo, Monaco).

Copyright © 2016 Pearson Education, Ltd. 14-4

Monte Carlo Process
Use of Random Numbers (1 of 10)
 In the Monte Carlo process, values for a random variable are
generated by sampling from a probability distribution.
 Example: ComputerWorld demand data for laptops selling for
$4,300 over a period of 100 weeks.

Table 14.1 Probability distribution of demand for laptop PCs

Copyright © 2016 Pearson Education, Ltd. 14-5
Monte Carlo Process
Use of Random Numbers (2 of 10)
 The purpose of the Monte Carlo process is to generate
the random variable, demand, by sampling from the
probability distribution P(x).

 The partitioned roulette wheel replicates the probability

distribution for demand if the values of demand occur in
a random manner.

 The segment at which the wheel stops indicates demand

for one week.
Copyright © 2016 Pearson Education, Ltd. 14-6
Monte Carlo Process
Use of Random Numbers (3 of 10)

Figure 14.1 A roulette wheel for demand

Copyright © 2016 Pearson Education, Ltd. 14-7
Monte Carlo Process
Use of Random Numbers (4 of 10)
When the
wheel is spun,
the actual
demand for
PCs is
determined by
a number at
rim of the
wheel.

Figure 14.2
Numbered roulette wheel
Copyright © 2016 Pearson Education, Ltd. 14-8
Monte Carlo Process
Use of Random Numbers (5 of 10)

Table 14.2 Generating demand from random numbers

Copyright © 2016 Pearson Education, Ltd. 14-9
Monte Carlo Process
Use of Random Numbers (6 of 10)
Select a number from a random number table:

Table 14.3 A table of delightfully random numbers

Copyright © 2016 Pearson Education, Ltd. 14-10
Monte Carlo Process
Use of Random Numbers (7 of 10)

 Repeating the selection of random numbers simulates

demand for a period of time. The next slide shows
demand for 15 consecutive weeks as drawn from the
random number table.

 Estimated average demand = 31/15 = 2.07 laptop PCs

per week.

 Estimated average revenue = $133,300/15 = $8,886.67.

Copyright © 2016 Pearson Education, Ltd. 14-11

Monte Carlo Process
Use of Random Numbers (8 of 10)

Table 14.4 Randomly generated demand for 15 weeks

Copyright © 2016 Pearson Education, Ltd. 14-12
Monte Carlo Process
Use of Random Numbers (9 of 10)
Average demand could have been calculated analytically:
n
E( x)   P( x ) x
i i

i1
where:
x  demand value i
i

P( x )  probability of demand
i

n  the number of different demand values

therefore:
E( x)  (.20)(0)  (.40)(1)  (.20)(2)  (.10)(3)  (.10)(4)
 1.5 PCs per week

Copyright © 2016 Pearson Education, Ltd. 14-13

Monte Carlo Process
Use of Random Numbers (10 of 10)
 The more periods simulated, the more accurate the results.

 Simulation results will not equal analytical results unless enough

trials have been conducted to reach steady state.

 It is often difficult to validate results of simulation - that true

steady state has been reached and that simulation model truly
replicates reality.

 When analytical analysis is not possible, there is no analytical

standard of comparison, thus making validation even more difficult.

Copyright © 2016 Pearson Education, Ltd. 14-14

Computer Simulation with Excel Spreadsheets
Generating Random Numbers (1 of 2)

 As simulation models get more complex they become impossible

to perform manually.

 In simulation modeling, random numbers are generated by a

mathematical process instead of a physical process (such as wheel
spinning).

 Random numbers are typically generated on the computer using a

numerical technique and thus are not true random numbers but
pseudorandom numbers.

Copyright © 2016 Pearson Education, Ltd. 14-15

Computer Simulation with Excel Spreadsheets
Generating Random Numbers (2 of 2)

Artificially created random numbers must have the following

characteristics:
1. The random numbers must be uniformly
distributed.
2. The numerical technique for generating the numbers
must be efficient.
3. The sequence of random numbers should reflect no
pattern.

Copyright © 2016 Pearson Education, Ltd. 14-16

Simulation with Excel Spreadsheets (1 of 3)

Exhibit 14.1
Copyright © 2016 Pearson Education, Ltd. 14-17
Simulation with Excel Spreadsheets (2 of 3)

Enter =VLOOKUP(F6,Lookup,2)
in G6 and copy it to G7:G20

Enter =4300*G6
in H6 and copy it
to H7:H20

= AVERAGE(G6:G20)

Generate random numbers for cells

F6:F20 with the formula =RAND( ) Exhibit 14.2
in F6 and copy it to F7:F20
Copyright © 2016 Pearson Education, Ltd. 14-18
Simulation with Excel Spreadsheets (3 of 3)

Click on the “View” tab, then on “Freeze Panes”

Spreadsheet is “frozen” at row 16 to show first 10 weeks and last 6

Copyright © 2016 Pearson Education, Ltd.

Exhibit 14.3 14-19
Computer Simulation with Excel Spreadsheets
Decision Making with Simulation (1 of 2)
Revised ComputerWorld example; order size of one laptop each week.
=1+MAX(G6-H6,0) is entered =VLOOKUP(F6,Lookup,2) is entered
in G7 and copied to G8:G105 in H6 and copied to H7:H105

=G6*50 is entered in cell

Shortages are computed by entering L6 and copied to L7:l105
=MIN(G6-H6,0) in I6 and copying to I7:I105
Copyright © 2016 Pearson Education, Ltd. Exhibit 14.4 14-20
Computer Simulation with Excel Spreadsheets
Decision Making with Simulation (2 of 2)
Order size of two laptops each week.
New formula for two
laptops ordered per week

Exhibit 14.5
Copyright © 2016 Pearson Education, Ltd. 14-21
Simulation of a Queuing System
Burlingham Mills Example (1 of 3)

Table 14.5 Distribution of arrival intervals

Table 14.6 Distribution of service times

Copyright © 2016 Pearson Education, Ltd. 14-22
Simulation of a Queuing System
Burlingham Mills Example (2 of 3)

Average waiting time = 12.5days/10 batches

= 1.25 days per batch
Average time in the system = 24.5 days/10 batches
= 2.45 days per batch
Copyright © 2016 Pearson Education, Ltd. 14-23
Simulation of a Queuing System
Burlingham Mills Example (3 of 3)
Caveats:
■ Results may be viewed with skepticism.
■ Ten trials do not ensure steady-state results.
■ Starting conditions can affect simulation results.
■ If no batches are in the system at the start, the
simulation must run until it replicates normal operating
system.
■ If the system starts with items already in the system,
the simulation must begin with items in the system.

Copyright © 2016 Pearson Education, Ltd. 14-24

Computer Simulation with Excel
Burlingham Mills Example
This formula is entered in
D15 and copied to D16:D23

Clock time is generated by

entering =MAX(E15,J14) in
F15 and copying to F16:F23

Copy
=VLOOKUP(H14,Lookup2,2)
to I14:I23
Arrival times are generated by
entering =E14+D15 in E15 =AVERAGE(G14:G23)
and copying to E16:E23 Exhibit 14.6
Copyright © 2016 Pearson Education, Ltd. 14-25
Machine Breakdown and Maintenance System
Simulation (1 of 6)
Bigelow Manufacturing Company must decide if it should
implement a machine maintenance program at a cost of $20,000 per
year that would reduce the frequency of breakdowns and thus time
for repair which is $2,000 per day in lost production.

A continuous probability distribution of the time between machine

breakdowns:
f(x) = x/8, 0  x  4 weeks, where x = weeks between
machine breakdowns
x = 4*sqrt(ri), value of x for a given value of ri.

Copyright © 2016 Pearson Education, Ltd. 14-26

Machine Breakdown and Maintenance System
Simulation (2 of 6)

Table 14.8 Probability distribution of machine repair time

Copyright © 2016 Pearson Education, Ltd. 14-27

Machine Breakdown and Maintenance System
Simulation (3 of 6)
Revised probability of time between machine breakdowns:
f(x) = x/18, 0  x6 weeks where x = weeks between machine
breakdowns
x  6 r1

Table 14.9 Revised probability distribution of machine repair time

with the maintenance program

Copyright © 2016 Pearson Education, Ltd. 14-28

Machine Breakdown and Maintenance System
Simulation (4 of 6)
Simulation of system without maintenance program (total
annual repair cost of $84,000):

Table 14.10 Simulation of machine breakdowns and repair times

Copyright © 2016 Pearson Education, Ltd. 14-29
Machine Breakdown and Maintenance System
Simulation (5 of 6)
Simulation of system with maintenance program (total annual
repair cost of $42,000):

Table 14.11 Simulation of machine breakdowns and repair with the

maintenance program
Copyright © 2016 Pearson Education, Ltd. 14-30
Machine Breakdown and Maintenance System
Simulation (6 of 6)
Results and caveats:
■ Implement the maintenance program since the cost savings
appear to be $42,000 per year and the maintenance program will
cost $20,000 per year.
■ However, there are potential problems caused by simulating
both systems only once.
■ Simulation results could exhibit significant variation since time
between breakdowns and repair times are probabilistic.
■ To be sure of accuracy of results, simulations of each system
must be run many times and the average results computed.
■ Efficient computer simulation is required to do this.

Copyright © 2016 Pearson Education, Ltd. 14-31

Statistical Analysis of Simulation Results (1 of 2)

 Outcomes of simulation modeling are statistical

measures such as averages.

 Statistical results are typically subjected to additional

statistical analysis to determine their degree of accuracy.

 Confidence limits are developed for the analysis of the

statistical validity of simulation results.

Copyright © 2016 Pearson Education, Ltd. 14-32

Statistical Analysis of Simulation Results (2 of 2)

Formulas for 95% confidence limits:

upper confidence limit  x  (1.96)(s / n )
lower confidence limit  x  (1.96)(s / n )
where x is the mean and s the standard deviation from a
sample of size n from any population.

We can be 95% confident that the true population mean will be

between the upper confidence limit and lower confidence limit.

Copyright © 2016 Pearson Education, Ltd. 14-33

Simulation Results
Statistical Analysis with Excel (1 of 2)
Simulation with maintenance program.

Confidence
limits

Copyright © 2016 Pearson Education, Ltd. Exhibit 14.9 14-34

Simulation Results
Statistical Analysis with Excel (2 of 2)

Click on “Data Analysis”

“Input Range” are

the cost ($) values
in column H

“Output Range”
specifies the location
of the statistical
summary report on
the spreadsheet
Statistical summary report

Exhibit 14.10
Copyright © 2016 Pearson Education, Ltd. 14-35
Crystal Ball
Overview
 Many realistic simulation problems contain more complex
probability distributions than those used in the examples.

 However there are several simulation add-ins for Excel that

provide a capability to perform simulation analysis with a
variety of probability distributions in a spreadsheet format.

 Crystal Ball, published by Oracle, is one of these.

 Crystal Ball is a risk analysis and forecasting program that

uses Monte Carlo simulation to provide a statistical range of
results.
Copyright © 2016 Pearson Education, Ltd. 14-36
Crystal Ball
Simulation of Profit Analysis Model (1 of 13)

Recap of the Western Clothing Company break-even and

profit analysis:
Price (p) for jeans is $23
variable cost (cv) is $8
Fixed cost (cf ) is $10,000

Profit Z = vp - cf – vc
break-even volume v = cf/(p - cv)
= 10,000/(23-8)
= 666.7 pairs.

Copyright © 2016 Pearson Education, Ltd. 14-37

Crystal Ball
Simulation of Profit Analysis Model (2 of 13)
Modifications to demonstrate Crystal Ball
 Assume volume is now volume demanded and is defined by a
normal probability distribution with mean of 1,050 and
standard deviation of 410 pairs of jeans.
 The price is uncertain and defined by a uniform probability
distribution from $20 to $26.
 The variable cost is not constant but defined by a triangular
probability distribution.
 We can determine the average profit and profitability with the
given probabilistic variables.

Copyright © 2016 Pearson Education, Ltd. 14-38

Crystal Ball
Simulation of Profit Analysis Model (4 of 13)

2. Click on “Define Assumption”

1. Click on cell C4 to define

normal distribution parameters

=C4*C5-C6-(C4*C7)

Exhibit 14.11
Copyright © 2016 Pearson Education, Ltd. 14-39
Crystal Ball
Simulation of Profit Analysis Model (6 of 13)

Name pulled from

original spreadsheet

1. Enter mean and

standard deviation

3. Click on “OK” to return 2. Click “Enter” to configure

to the spreadsheet distribution in window

Copyright © 2016 Pearson Education, Ltd. Exhibit 14.12 14-40

Crystal Ball
Simulation of Profit Analysis Model (7 of 13)

Enter minimum and

maximum values for
the distribution

Exhibit 14.13
Copyright © 2016 Pearson Education, Ltd. 14-41
Crystal Ball
Simulation of Profit Analysis Model (8 of 13)

Enter three
estimates

Copyright © 2016 Pearson Education, Ltd.

Exhibit 14.14 14-42
Crystal Ball
Simulation of Profit Analysis Model (9 of 13)

1. Click on “Define Forecast”

2. Type in units

Exhibit 14.15
Copyright © 2016 Pearson Education, Ltd. 14-43
Crystal Ball
Simulation of Profit Analysis Model (10 of 13)

1. Click on “Run Preferences” to

2. Click on “Start” to indicate the number of trials and
begin the simulation seed number

Exhibit 14.16
Copyright © 2016 Pearson Education, Ltd. 14-44
Crystal Ball
Simulation of Profit Analysis Model (11 of 13)

2. Go to the “Sampling”
screen to enter the seed 1. Enter the number
number of trials

Exhibit 14.17
Copyright © 2016 Pearson Education, Ltd. 14-45
Crystal Ball
Simulation of Profit Analysis Model (12 of 13)

Click here to repeat

the same simulation

Exhibit 14.18
Copyright © 2016 Pearson Education, Ltd. 14-46
Crystal Ball
Simulation of Profit Analysis Model (13 of 13)
2. Click here to start 1. Click here to establish the number
the simulation of trials and the seed number

Click on “View”, then

“Statistics” to go to the Click here to reset
statistical summary Set new run the simulation and
screen (Exhibit 14.20) preferences run it again

Exhibit 14.19
Copyright © 2016 Pearson Education, Ltd. 14-47
Crystal Ball
Simulation of Profit Analysis Model (13 of 13)

Click on “View” to
return to the
Frequency window

Mean profit value

Exhibit 14.20
Copyright © 2016 Pearson Education, Ltd. 14-48
Crystal Ball
Simulation of Profit Analysis Model (14 of 14)

Exhibit 14.21

Move arrow to
“0.00” or set lower
limit equal to 0.00

Probability (.8136) that the

company will break even
Copyright © 2016 Pearson Education, Ltd. 14-49
Verification of the Simulation Model (1 of 2)

■ The analyst wants to be certain that the model is internally

correct and that all operations are logical and mathematically
correct.
■ Testing procedures for validity:
 Run a small number of trials of the model and compare
with manually derived solutions.
 Divide the model into parts and run parts separately to
reduce the complexity of checking.
 Simplify mathematical relationships (if possible) for
easier testing.
 Compare results with actual real-world data.

Copyright © 2016 Pearson Education, Ltd. 14-50

Verification of the Simulation Model (2 of 2)

■ The analyst must determine if the model’s starting conditions are

correct (system empty, etc.).

■ Must determine how long model should run to insure steady-state

conditions.

■ A standard, fool-proof procedure for validation is not available.

■ Validity of the model rests ultimately on the expertise and

experience of the model developer.

Copyright © 2016 Pearson Education, Ltd. 14-51

Some Areas of Simulation Application

■ Queuing
■ Inventory Control
■ Production and Manufacturing
■ Finance
■ Marketing
■ Public Service Operations
■ Environmental and Resource Analysis

Copyright © 2016 Pearson Education, Ltd. 14-52

Example Problem Solution (1 of 6)

Willow Creek Emergency Rescue Squad

Minor emergency requires two-person crew

Regular emergency requires a three-person crew
Major emergency requires a five-person crew

Copyright © 2016 Pearson Education, Ltd. 14-53

Example Problem Solution (2 of 6)

Distribution of the number of calls per night and emergency type:

Calls Probability Emergency Type Probability
Minor .30
0 .05 Regular .56
1 .12 Major .14
1.00
2 .15
3 .25
4 .22 1. Manually simulate 10 nights of calls
5 .15 2. Determine the average number of
6 .06 calls each night
3. Determine the maximum number of
1.00
crew members that might be needed
on any given night.

Copyright © 2016 Pearson Education, Ltd. 14-54

Example Problem Solution (3 of 6)

Step 1: Develop random number ranges for the probability distributions.

Cumulative Random Number

Calls Probability
Probability Range, r1
0 .05 .05 1–5
1 .12 .17 6 – 17
2 .15 .32 18 – 32
3 .25 .57 33 – 57
4 .22 .79 58 – 79
5 .15 .94 80 – 94
6 .06 1.00 95 – 99, 00
1.00

Emergency Cumulative Random Number

Probability
Type Probability Range, r1
Minor .30 .30 1 – 30
Regular .56 .86 31 – 86
Major .14 1.00 87 – 99, 00
1.00
Copyright © 2016 Pearson Education, Ltd. 14-55
Example Problem Solution (4 of 6)

Step 2: Set up a tabular simulation (use the second column of random

numbers in Table 14.3).

Copyright © 2016 Pearson Education, Ltd. 14-56

Example Problem Solution (5 of 6)

Step 2 continued:

Copyright © 2016 Pearson Education, Ltd. 14-57

Example Problem Solution (6 of 6)

Step 3: Compute Results:

average number of minor emergency calls per night = 10/10 =1.0

average number of regular emergency calls per night =14/10 = 1.4
average number of major emergency calls per night = 3/10 = 0.30

If calls of all types occurred on same night, the maximum number

of squad members required would be 14.

Copyright © 2016 Pearson Education, Ltd. 14-58