Chapter 13: Simulation

1
Chapter 13 Simulation

• Make sure that you have reviewed the EXCEL
  TUTORIAL provided in the Simulation folder on
  Blackboard. Even if you are very familiar with
  Excel, look over the entire tutorial. There are
  several helpful techniques that will assist you
  with completing this chapter.

2
Chapter 13Simulation

• Introduction
• Advantages and Disadvantages of Using Simulation
• Overall Simulation Modeling
• Random Variables and Generating Random Variables
• Examples

3
Introduction

• A simulation model is a computer model that
  imitates a real-life situation.
• Simulation models are useful for determining how
  sensitive a system is to changes in operating
  conditions.
• A benefit of computer simulations is that it
  enables us to answer what-if questions without
  actually changing a physical system.

4
Simulation

• Simulation is one of the most frequently employed
  management science techniques.
• Spreadsheet simulation modeling is quite similar
  to other modeling applications. The main
  difference is that simulation models decision
  making scenarios where at least one input
  parameter is a random variable.
• Example The demand for a product, the process of
  customers arriving to a store, the number of
  customers that will cancel reservations for an
  airline flight, etc.
• These random processes are typically too complex
  to be solved by analytical methods.

5
Simulation Programs

• The computer program that performs the simulation
  is called a simulator.
• Flowcharts can be useful in writing such a
  program.
• While this program can be written in any general
  purpose language (e.g. BASIC, FORTRAN, C, etc.)
  special languages which reduce the amount of code
  which must be written to perform the simulation
  have been developed.
• Special simulation languages include SIMSCRIPT,
  SPSS, DYNAMO, and SLAM.
• IN THIS CHAPTER WE WILL FOCUS ON SPREADSHEET
  MODELING USING EXCEL.

6
Advantages of Simulation

• Among the advantages of simulation is the ability
  to gain insights into the model solution which
  may be impossible to attain through other
  techniques.
• Also, once the simulation has been developed, it
  provides a convenient experimental laboratory to
  perform "what if" and sensitivity analysis.

7
Disadvantages of Simulation

• A large amount of time may be required to develop
  the simulation.
• There is no guarantee that the solution obtained
  will actually be optimal.
• Simulation is, in effect, a trial and error
  method of comparing different policy inputs.
• It does not determine if some input which was not
  considered could have provided a better solution
  for the model.

8
Simulation Modeling

• Simulation models use mathematical expressions
  and logical expressions that describe how to
  compute the value of output based upon input
  values.
• There are two categories of input
• Controllable Input (CI) Input values that can
  be established by the analyst
• Probabilistic Input (PI) Input based upon a
  random process
• Simulation conducts a series of experiments using
  a variety of values for CI to determine how
  changes in CI values affect output.
• The goal is to make recommendations for CI that
  will provide the best output values.

9
Simulation Modeling

• In order to build a simulation model you must
• Determine the output that you want to measure.
• Determine the CI and PI values that are needed to
  compute the output.
• Set the CI values.
• Determine the proper probability distributions
  that best models each PI value. Generate values
  for each PI parameter.
• Establish the precise mathematical formulas
  and/or logical expressions that express how to
  compute output based upon CI and generated PI
  values.
• Run the simulation model, generating PI, and
  computing output based upon CI and generated PI
  values.
• We will use Excel to simulate a variety of
  scenarios.

10
Simulation Modeling

• Ideally, the simulation should be run using
  actual past data.
• Predictions from the simulation model should be
  compared with historical results.
• For each policy (CI) under consideration by the
  decision maker, the simulation is run by
  considering a long sequence of potential PI input
  data values .
• Whenever possible, different policies should be
  compared by using the same sequence of input
  data.

11
Random Variables

• Random variable values are utilized in the model
  through a technique known as Monte Carlo
  simulation.
• Each random variable is mapped to a set of
  numbers so that each time one number in that set
  is generated, the corresponding value of the
  random variable is given as an input to the
  model.
• The mapping is done in such a way that the
  likelihood that a particular number is chosen is
  the same as the probability that the
  corresponding value of the random variable
  occurs.
• We will use Excel to generate PI using Monte
  Carlo simulation.

12
Generating PI

• We will review the process of generating random
  variables from5 common probability distributions
• Discrete random variables
• Normal random variables
• Discrete Uniform random variables
• Uniform random variables
• Exponential random variables

13
Generating Discrete Random Variables

• Excel has a RAND() function which randomly
  generates values greater than or equal to 0 but
  less than 1. In order to use this function to
  generate values following a discrete
  distribution, you must set up a lookup table.
• Example Suppose we want to generate a x
  according to the following discrete probability
• x p(x)
• 0 0.2
• 1 0.35
• 2 0.25
• 3 0.15
• 4 0.05

We want to generate values so that approx. 20 of
the time the value is 0, 35 of the time the
value is 1, 25 of the time the value is 2, 15
of the time the value is 3, and 5 of the time
the value is 4.
14
Generating Discrete Random Variables

• First we must set up a look up table as follows
• Lower Upper
• limit limit x
• 0 0.2 0
• 0.2 0.55 1
• 0.55 0.8 2
• 0.8 0.95 3
• 0.95 1 4

A lookup table is always set up so that the
difference between the upper and lower limit for
each x value equals the probability of the x
value occurring.
15
Generating Discrete Random Variables

• Then we generate a Random number between 0 and 1
  in Excel use RAND()
• If the random value Return the value
• Falls between 0 and 0.2 0
• Is greater than 0.2, but less than 0.55 1
• Is greater than 0.55, but less than 0.8 2
• Is greater than 0.8, but less than 0.95 3
• Is greater than 0.95, but less than 1 4

16
EXCEL Generating Discrete Random Variables
Enter the lookup table in cells C2E7 as shown
below. Then to generate a Random variable
according to this probability distribution, enter
the following formula in cell A10
VLOOKUP(RAND(),C3E7,3).

Column of the table return values
Value to lookup
Lookup table Location
You may get a different value in cell A10 since
your RAND() function returned a different value.
17
Excel Note

• Noticed we used when referencing the lookup
  table range. Recall (Excel tutorial) that this
  will ensure that the table location is correctly
  referenced when copying and pasting formula.
• Each time any cell is changed in Excel new RAND()
  values are generated, thus your x values will
  change. The only way to stop this from happening
  is to select Tools/Options on the menu bar, then
  click on the calculations tab. Under the
  calculation section, click Manual. Now you must
  manually press F9 if you want the computer to
  generate Random values using RAND() function. If
  you do this, in the future when copying and
  pasting formulas you must press F9 after pasting
  formula to generate different values.

See the file Generate Random Variables in the
Simulation formula for Excel file on generating
discrete random variables
18
Generating Normal Random Variables
In Excel enter the mean for the variable in cell
B4. In cell B5 enter the standard deviation.
To generate a normal random variable in cell A5
with this Mean and standard deviation, enter this
formula NORMINV(RAND(),B4,B5).
19
Generating Discrete Uniform Random Variables
In Excel enter the smallest value that the random
value can assume in cell D3. Enter the largest
value that the random variable can assume in cell
D4. To generate a uniform discrete random
variable in cell A8, enter this formula
RANDBETWEEN(D3,D4). UPDATE This function
does not work in Excel XP. If working in XP
environment use GENERATING UNIFORM R.V. And round
values to the nearest integer.
You may get a different value in cell A8 since
your RAND() function returned a different value.
20
Generating Uniform Random Variables
In Excel enter the smallest value that the random
value can assume in cell D3. Enter the largest
value that the random variable can assume in cell
D4. To generate a uniform random variable in
cell A8, enter this formula D3
RAND()(D4-D3)
You may get a different value in cell A8 since
your RAND() function returned a different value.
Discrete Uniform returns integers, while uniform
returns noninteger values.
21
Generating Exponential Random Variables

• In Excel enter your parameter in cell D5.
• To generate an exponential variable in cell A6,
  enter this formula
• -D5LN(RAND()). NOTE For Interarrival times,
  the parameter is the mean time
• between arrivals. For service time, the parameter
  is the average service time.

In the example in the spreadsheet, there were 15
arrivals per hour. This means an Arrival comes
(on average) once every 4 minutes. The parameter
is 4.
22
More Info Generating Values

• An Excel file is located in the Simulation Folder
  entitled gen_rand_num.xls. This file contains
  files for generating all of the random variables
  that we have discussed.

23
Example Dynogen, Inc.

• The price change of shares of Dynogen, Inc. has
  been observed over the past 50 trades. The
  frequency distribution is as follows
• Price Change Number of Trades
• -3/8
  4
• -1/4
  2
• -1/8
  8
• 0
  20
• 1/8
  10
• 1/4
  3
• 3/8
  2
• 1/2
  1
• Total 50
• Create a lookup chart to simulate the price
  changes of Dynogen shares.

Anderson, Sweeney, and Williams
24
Example Dynogen, Inc.

• Relative Frequency Distribution provides a
  discrete probability distribution.
• Price Change Relative Frequency
• -3/8 .08
  
• -1/4 .04
  
• -1/8 .16
  
• 0 .40
  
• 1/8 .20
  
• 1/4 .06
  
• 3/8 .04
  
• 1/2 .02
  
• TOTAL 1.00

25
Example Dynogen, Inc.

• Relative Frequency Lower Upper Price Change
• .08 0.00 0.08
  -3/8
• .04 0.08 0.12
  -1/4
• .16 0.12 0.28
  -1/8
• .40 0.28 0.68
  0
• .20 0.68 0.88
  1/8
• .06 0.88 0.94
  1/4
• .04 0.94 0.98
  3/8
• .02 0.98 1.0
  1/2

26
Example Dynogen, Inc.

• If the current price per share of Dynogen is 23,
  use the random numbers below to
• simulate the price per share over the next 10
  trades.
• Use the following stream of random numbers
• 0.21, 0.84, 0.07, 0.30, 0.94, 0.57,
  0.57, 0.19, 0.84, 0.84

27
Example Dynogen, Inc.

• Simulation Worksheet (Start at 23)
• Trade Random Price
  Stock
• Number Number Change Price
• 1 0.21
  -1/8 22 7/8
• 2 0.84
  1/8 23
• 3 0.07
  -3/8 22 5/8
• 4 0.30
  0 22 5/8
• 5 0.94
  3/8 23
• 6 0.57
  0 23
• 7 0.57
  0 23
• 8 0.19
  -1/8 22 7/8
• 9 0.84
  1/8 23
• 10 0.84
  1/8 23 1/8

28
Set up A Formal Simulation Model

• We can simulate the stock price of Dynogen in
  Excel by following the steps outlined on slide 9.
• Step 1 We want to measure the stock price.
• Step 2 There are no Controllable Inputs (CI).
  The Price change is our only Probabilistic Input
  (PI).
• Step 4 Our PI follows the discrete distribution
  on slide 24. We can create the lookup table on
  slide 25 in Excel and generate this discrete
  distribution as shown on slide 19.
• Step 5 The only math formula that we need to
  compute our output based upon our PI is
• Ending Stock Price Starting Stock Price
  Price change
• On the next slide we simulate this problem in
  Excel.

29
Example Dynogen, Inc.

• Spreadsheet for Stock Price Simulation

30
Creating the Dynogen INC Spreadsheet in Excel

• Our Lookup table was entered in cells A4 through
  C11.
• In cell E3 we generated a random variable (price
  change) from this distribution using the formula
• VLOOKUP(RAND(),A4C11,3).
• Copy this formula into cells E4 through E12 to
  generate price changes for all ten trades.
• In cell F3 we compute the stock price
  incorporating the change and the original price
  using the formula
• 23 E3
• In cell F4 compute the stock price based upon the
  change and the current stock price (based upon
  the last trade) using the following formula
• F3 E4
• Copy the formula in cell F4 into cells F5 through
  F12 to compute the ending stock price for the
  remaining trades.

31
Example Dynogen, Inc.

• Theoretical Results and Observed Results
• Based on the probability distribution, the
  expected price change per trade can be calculated
  by
• (.08)(-3/8) (.04)(-1/4) (.16)(-1/8)
  (.40)(0)
• (.20)(1/8) (.06)(1/4) (.04)(3/8)
  (.02)(1/2) .005
• The expected price change for 10 trades is
  (10)(.005) .05. Hence, the expected stock
  price after 10 trades is 23 .05 23.05.
• Compare this ending price with the spreadsheet
  simulation and manual simulation results on the
  previous slides. We can see that both are
  relatively close to the expected stock price
  after 10 trades above.

32
Example Wayne International Airport

• Wayne International Airport primarily serves
  domestic air traffic. Occasionally, however, a
  chartered plane from abroad will arrive with
  passengers bound for Wayne's two great amusement
  parks, Algorithmland and Gram's Cherry Preserve.
• Whenever an international plane arrives at the
  airport the two customs inspectors on duty set up
  operations to process the passengers.
• Incoming passengers must first have their
  passports and visas checked. This is handled by
  one inspector. The time required to check a
  passenger's passports and visas can be described
  by the probability distribution on the next
  slide.

Anderson, Sweeney, and Williams
33
Example Wayne International Airport

• Time Required to
• Check a Passenger's
• Passport and Visa
  Probability
• 20 seconds
  .20
• 40 seconds
  .40
• 60 seconds
  .30
• 80 seconds
  .10

34
Example Wayne International Airport

• After having their passports and visas checked,
  the passengers next proceed to the second customs
  official who does baggage inspections.
  Passengers form a single waiting line with the
  official inspecting baggage on a first come,
  first served basis. The time required for
  baggage inspection is uniformly distributed
  between 0 to 3 minutes.
• Simulate this process for 20 passengers and
  determine the average time that it takes customs
  to clear the first 20 customers?

35
Set Up Simulation Model

• Step 1 We want to determine the average time
  that it takes customs to clear the first 20
  customers.
• Step 2 We have no CI and the Time required to
  check passports/visas, and the time required for
  baggage check are our 2 PI.
• Step 4 Passport/Visa check time follows a
  discrete distribution (slide 33) and baggage
  check times follow a uniform distribution.

You Must use the Same units for the entire
problem, so we converted Passport Check Times to
minutes.
36
Example Wayne International Airport

• Establish math formulas expressing the
  relationship between the average wait time
  (output) and the PI inputs.
• In order to determine this output, we must record
  the following information for each passenger
• When his service begins at the passport control
  inspection
• The length of time for this service (our 1st PI)
• When his service begins at the baggage inspection
• The length of time for this service (our 2nd PI)
• The time baggage inspection ends.

37
Example Wayne International Airport

• Time Relationships
• Time a passenger begins service
• by the passport inspector
• (Time the previous passenger started passport
  service)
• (Time of previous passenger's passport
  service)
• The time a passenger begins service by passport
  inspector 0 for the 1st customer.

38
Example Wayne International Airport

• Time Relationships
• Time a passenger begins service
• by the baggage inspector
• ( If passenger does not wait in line for baggage
  inspection)
• (Time passenger completes service
• with the passport control
  inspector)
• (If the passenger does wait in line for baggage
  inspection)
• (Time previous passenger completes
• service with the baggage inspector)
• If the time a passenger finishes passport service
  (passport start time passport
• service time) is before the previous passengers
  baggage service end time, then the
• customer has to wait in line for baggage
  inspection.

39
Example Wayne International Airport

• Time Relationships
• Time a customer completes service
• at the baggage inspector
• (Time customer begins service with baggage
  inspector) (Time required for baggage
  inspection)

40
Setting up Excel Simulation

• Following the outline in slide 36, in excel for
  each passenger we must
• 1st Establish the time that a passenger begins
  passport service.
• 2nd Generate the passport service time for a
  passenger.
• 3rd Compute the passport service end time.
• 4th Determine the baggage service start time.
• 5th Generate the baggage service time.
• 6th Compute the baggage service end time

41
Wayne Int. Airport Excel Simulation
42
Wayne Int. Airport Creating Excel Spreadsheet

• To generate the 1st Passenger
• The passport service start time in cell B10 is 0,
  (They enter service immediately to begin
  simulation).
• Generate Passport service time in cell C10 by
  entering the formula
• VLOOKUP(RAND(), A3C6,3)
• Compute the passport service end time in cell D10
  by entering the formula
• B10 C10
• The baggage start time begins at the end of the
  passport service time (since this is the first
  customer. So in cell E10 enter
• D10
• Generate the baggage service time in cell F10 by
  entering the formula
• I2 RAND()(I3 - I2)
• Compute the baggage service end time in cell G10
  by entering the formula
• E10 F10

43
Wayne Int. Airport Excel Spreadsheet
RECALL THAT ACTUAL CELL VALUES FOR SIMULATIONS
MAY VARY FROM ONE SLIDE TO THE NEXT BECAUSE OF
THE RAND FUNCTION (REFER TO YOUR SUGGESTED EXCEL
MANUAL)
44
Wayne Int. Airport Creating Excel Spreadsheet

• To generate the 2nd Passenger
• The passport service start time for the second
  customer in cell B11 equals the Passport Service
  end time of the previous customer. Thus enter
  the following formula in this cell
• D9
• Generate Passport service time for Passenger 2 in
  cell C11 by entering the formula
• VLOOKUP(RAND(), A3C6,3)
• Compute the passport service end time for
  Passenger 2 in cell D11 by entering the formula
• B11 C11
• Determine the baggage start time of Passenger 2
  as follows
• If Passenger 2 finishes passport Services before
  the previous passenger ends baggage service,
  passenger 2 must wait and begin service when the
  previous passenger ends baggage service.
  Otherwise Passenger 2 can begin baggage service
  when they end passport service. So in cell E11
  enter
• IF(D11ltG10, G10, D11)
• Generate the baggage service time in cell F11 by
  entering the formula
• I2 RAND()(I3 - I2)
• Compute the baggage service end time in cell G11
  by entering the formula
• E11 F11

45
Wayne Int. Airport Creating Excel Spreadsheet

• The simulation for passenger 2 should be imitated
  for the remaining passengers. Thus we should copy
  cells B11 through G11 into the corresponding
  columns for each of the remaining passengers.

46
Wayne International Airport

• As shown on the previous slide, if customs at
  Wayne International Airport utilize one
  passport/visa check person and one baggage check
  person, if will take them 32.22 minutes to
  service the first 20 passengers.

47
More Simulation Practice

• For more practice simulating problems (including
  incorporating CI) Try the following problems in
  your textbook (Chapter 13 Simulation)
• 5, 9, 13, 14, 16, 21
• The answer to these exercises are in our
  Simulation Folder in Course Documents.

48
End of Chapter 13