Prelim 2 – Module 7: Simulation

Question 1

What are possible benefits of simulation for a business? (5)

Answer 1

1. Try out scenarios that would be expensive/impossible to test in real life
2. Account for uncertainty/randomness/risk
3. Develop a deeper understanding of the process because you:
   - have to model it quantitatively
   - can visualize it in a new way
4. Estimate the performance of lots of different configurations or policies
5. Find the optimal solution for some problem

Question 2

What is a Monte Carlo experiment?

Answer 2

Use random numbers and operational rules to estimate performance

Question 3

Steps to a Monte Carlo Experiment (4)

Answer 3

1. (Optional) If testing a decision, choose the value of some decision variable
   Ex. when should we schedule our first reservation that needs a table to turn?
2. Generate random number inputs
   Ex. what is the dining duration for each party?
3. Insert the inputs into some system and apply operational logic
   Ex. Determine when the 1st table turns, 2nd table turns, 3rd table turns, etc.
4. Aggregate results
   Ex. What percentage of parties have to wait for a table? How long do they wait? How many wait longer than 5 minutes?

Question 4

How do we get random numbers?

Answer 4

In 1956, the RAND corporation published the book: A Million Random Digits with 100,000 Normal Deviates.

In Excel

Question 5

Rand ()

Answer 5

For a value on the interval between 0 and 1

Question 6

RandBetween(a,b)

Answer 6

For any integer between a and b (including a and b as possibilities)

Question 7

What are the three strategies for creating random numbers?

Answer 7

1. Expert opinion
2. Resample existing data
3. Fit to distribution

Question 8

Expert Opinion

Answer 8

Useful if no data exists about the random variable

Use very simple distributions (e.g., uniform)

Ex. An expert guesses that Establishment dining times are between 45 minutes and 180 minutes. We simulate dining duration as RandBetween(45,180).

Question 9

Resample Existing Data

Answer 9

Useful if a large amount of data exists about the random variable

Ex. Establishment has duration data from 1,000 recent visits. To build our simulation, each simulated customer’s dining duration is picked at random from those 1,000 data points.

Question 10

Fit to distribution

Answer 10

Useful if we have limited data

Simpler way to describe a random number distribution

Ex. We calculate that dining duration has a mean of 100.8 minutes and a standard deviation of 26.2 minutes. We simulate dining duration for each customer as Norm.Inv(Rand(),100.8,26.2).

Question 11

Uniform distribution

Answer 11

Any number (including non-integers) between a and b
= a + Rand()*(b-a)

Question 12

Normal distribution

Answer 12

A random variable from a normal distribution with mean 𝜇 and standard deviation 𝜎
= Norm.Inv(Rand(),𝜇,𝜎)

Question 13

Exponential distribution

Answer 13

A random variable from an exponential distribution with rate 𝜆
= -LN(Rand())/𝜆

Question 14

Binomial distribution

Answer 14

A random variable for the number of “hits” in n chances, each with a probability of success p
= Binom.Inv(n,p,Rand())

Question 15

Probability mass function

Answer 15

Probability that a discrete random variable takes some value

Question 16

Probability density function

Answer 16

Relative likelihood of some instantaneous value (continuous)

Question 17

Cumulative distribution function (CDF)

Answer 17

Probability that a random variable is less than or equal to some value

Question 18

We can use RAND() with the CDF to ___

Answer 18

Generate other random numbers

Connect [0,1] interval of RAND() to [0%,100%] CDF interval for our distribution
-on graph, draw arrow from percentage to graph from left to right

Question 19

CDF interval in Excel steps

Answer 19

1. Create a table
   - value
   - probability
   - CDF interval (max and min)
2. create a table with two columns
   - Column 1: Min of CDF interval for the value
   - Column 3: Demand value
   - Column 2 (the max of the CDF interval) is not necessary but helpful for seeing the interval

VLOOKUP(RAND(),my_table,3,TRUE)

Question 20

How to implement a Monte Carlo simulation in Excel

Answer 20

1. Set up your business model
   - Inputs
   - Outputs
   - Logic
   - Random Variables
2. Set up a data table
   - Create a column with numbers 1,2,3,…maximum number of times you want to regenerate random numbers
   - Each of these rows is called a replication.
   - For “Column Input Cell” when setting up your Data Table, either select an empty cell or set up a special cell to display the replication number
3. Collect results

Question 21

Excel functions and features to know for operational logic

Answer 21

Arithmetic (multiplication, division, addition, subtraction)
IF, MAX, MIN
COUNT and COUNTIF
SUM and SUMIF

Question 22

Excel functions and features to know for outputs

Answer 22

AVERAGE and STDEV.S
Creating a histogram
COUNT and COUNTIF and COUNTIFS
AVERAGE and AVERAGEIF and AVERAGEIFS
SUM and SUMIF and SUMIFS
PERCENTILE, SMALL, and LARGE

Question 23

Round Excel Function

Answer 23

Round(norm.inv.(rand(),100,50),0) gives us an integer value

Question 24

Max Excel Function

Answer 24

Max(norm.inv(rand()100,50),0) turns any negative value into 0

Question 25

Min Excel Function

Answer 25

Min(norm.inv(rand(),100,50),200) turns any value above 200 into 200