Session 1-2

Simulation
 Modelling
• A simplified representation of reality that
reproduces (approximately) the behavior of the
system studied

• Simple example: profit from selling a product

– Price of product*number of units sold (price) –
number of units produced*variable unit costs – fixed
costs
At what price should you sell your product to maximize
profits?
 Example with random variables
• Unit price: set by you (e.g. 29€)
• Units sold: random variable following some distribution (e.g. normal with
mean and sd = functions of price)
• Unit Variable cost: random variable following some distribution (e.g.
uniform continuous distribution with min = 6 and max = 11)
• Fixed costs: know amount (e.g. 10.000€)

• At what price should you sell the product to maximize

(expected) profits?
• We can use simulation techniques to answer this type
of question
 Types of decision models

• Deterministic Models
Where all the inputs are known with complete
certainty

• Probabilistic/Stochastic Models
Where some inputs are random and uncertain
 What is simulation?
• Simulation is an imitation of reality. A number of
experiments is performed on simulated models
to determine the behavior of the real system.

• The basic idea is to build an experimental device,

or simulator, that will “act like” the system of
interest

• Simulation is one of the most widely used

decision modeling techniques
 What is simulation?
• Useful when analytical solutions are difficult
or impossible to find

• Warning: simulation does not generate an

optimal unique solution but can lead to the
most likely solution
 Monte Carlo Method
• Monte Carlo method is a technique that involves using
random numbers and probabilities to solve problems.
• The term Monte Carlo Method was coined in
reference to games of chance, a popular attraction in
Monte Carlo (Monaco).

• This method is often used when the model is complex,

nonlinear, or involves more than just a couple of
uncertain parameters.
Monte Carlo Method
 Who uses Monte Carlo simulation?
• GM uses simulation for activities such as forecasting net income for the
corporation, predicting structural and purchasing costs, and determining its
susceptibility to different kinds of risk (such as interest rate changes and
exchange rate fluctuations).

• Proctor and Gamble uses simulation to model and optimally hedge foreign
exchange risk.

• Sears uses simulation to determine how many units of each product line should
be ordered from suppliers—for example, the number of pairs of Dockers
trousers that should be ordered this year.

• Financial planners use Monte Carlo simulation to determine optimal investment

strategies for their clients’ retirement.

• … [Link]
simulation-in-Excel-64c0ba99-752a-4fa8-bbd3-4450d8db16f1
 The modelling and simulation process
1. Model creation
• Identify the fixed inputs (parameters), the decision
variables, the intermediate variables (equation), the
random variables (distributions), and the output
variable (equation).
• Create the model.
 Previous example
• Unit price: set by you

• Units sold: random variable following some distribution (e.g. normal with mean
and sd = functions of price)

• Unit Variable cost: random variable following a uniform continuous distribution

with min = 6 and max = 11)

• Fixed costs: 10.000€

• Profit: unit price*units sold – unit variable cost* units sold – fixed costs

Decision variables Fixed inputs (parameters)

Intermediate variables (equation) random variables (distributions)

Output variable (equation)

 The modelling and simulation process
1. Model creation
• Identify the fixed inputs (parameters), the decision variables,
the intermediate variables (equation), the random variables
(distributions), and the output variable (equation).
• Create the model.

2. Simulation
Generate random inputs and run the model several times (data
table).

3. Analysis of results
Determine summary statistics and confidence intervals, Plot
histograms, etc…
 Step 1: Determine the probability distribution of each RV

Random variables in real life means uncertainty,

such as:
– Product demand
– Lead time for orders
– Time between equipment breakdown
– Service time
– Etc…
Step 1: Determine the probability distribution of each RV

• Random variables could be discrete or

continuous

• There are many different probability

distributions (e.g. general discrete, normal,
Poisson, uniform, exponential, binomial, etc…)

• Usually, we use historical data to identify the

appropriate distribution
 Types of variables in simulation
• Discrete
– Used for simulating specific values: can only take on a
certain number of values
– Example: Number of people in a waiting line (queue)

• Continuous
– Used for simulating any value: can take on an infinite
number of values
– Example: The amount of time a person spends in a
queue
 Discrete uniform distribution

• All values occur with equal likelihood

• Excel function: RANDBETWEEN (min, max)
returns an integer between a minimum and a
maximum
 General discrete distribution
• Values occur with unequal likelihood
• Example: We expect that FC are equal to $10000
with 25% of chances; $12000 with 50% of chances
and $15000 with 25% of chances.
X P(X=x) CP
10000 0.25 0
12000 0.50 0.25
15000 0.25 0.75

• Excel function:
– Calculate the cumulative probabilities
– LOOKUP(RAND(),cumulative probabilities, results) where
RAND() returns a value between 0 and 1
 Binomial distribution
• Used to generate a number of successes in a sequence of n
independent yes/no experiments

• Example: suppose an airline company sells 170 tickets for a

flight and expects that 80% of the passengers with tickets
will actually show up for the flight. It does not mean that
exactly 136 (170*0.8) passengers will show up, but it will be
something around (it could be less or more).

• Excel function:
– [Link](number of trials, probability of success, RAND())
Where RAND() returns a value between 0 and 1
 Continuous Uniform distribution
• All values occur with equal likelihood between a
minimum and a maximum

• Example: A manager knows that a building cost is

uncertain, but based on his experience he can state
that the amount will be in between 20,000 € and
30,000 €

• Excel function:
– RAND()*(max-min)+min
 Normal distribution
• This distribution is characterized by two parameters: the mean and
the standard deviation

• Example: We believe that the demand of a certain product is

100,000 on average with a standard deviation equals to 25,000.

• Excel function:
– [Link](RAND(), mean, sd)
Where RAND() returns a value between 0 and 1

• Note: if n variables X follow a normal distribution with mean µ

and standard deviation σ, then the variable σ𝑛𝑖=1 𝑋𝑖 follows a
normal distribution with mean n*µ and standard deviation
σ*sqrt(n).
 Step 2: Simulation and replication

Run the model several times to cover a wide

range of values for the output variable and
obtain meaningful results Simulations
Profit
1
Excel function: Data -> 2
3
What-if Analysis -> Data
4
Table 5
6
7
8
 Step 3: Evaluating results
• Simulation is not expected to give the best possible
solution but the most likely solution.

• The higher the number of replications, the more accurate

the expected solution will be.

• Simulation is a decision tool because it provides the

probability that a certain event happens

• Simulation allows also to evaluate the precision of results

by considering the confidence interval of the simulated
variable: {µ-tαδ; µ+tαδ}
 Note on Random Numbers in Excel Spreadsheets

• Once entered in a spreadsheet, a random number

function remains “live.” A new random number is
generated each time the spreadsheet is modified.

• Press the F9 key to refresh the spreadsheet and

generate new random numbers.

• If you do not want the random number to change, you

can freeze it by selecting: tools, options, calculations,
and checking “manual.”
 Warmup : launching a new product
(deterministic model)

• Unit price: 29$

• Units sold: 700
• Unit Variable cost: 8 $
• Fixed costs: 12.000 $
• Net cash flow: ?

• Is the project profitable ?

 Exercise 1 : launching a new product
• Unit price: 29$

• Units sold: Normal distribution with mean =700 and sd = 100

• Unit Variable cost:

Uniform continuous distribution with min = 6 and max = 11

• Fixed costs:
The fixed costs are equal to $10000 with 25% of chances; $12000 with 50% of
chances and $15000 with 25% of chances.

• Net cash flow: ? Exercise:

1) Build a model taking into
• Is the project profitable ? consideration the randomness of
the variables above.
2) Calculate 500 simulations for
profits (Hint: use a simple entry
and select a random cell for the
column input)

You can work in pairs or groups of 3

 Example 2 : overbooking in a restaurant
• 75 available seats in a restaurant (i.e. no more than 75 guests can be served)
• Only 80% of customers show up
• Revenue/customer : Normal distribution (mean: 100 € and sd: 25 €).
• There will be a badwill of 500 € if a customer who has reserved is denied.

Note: If a variable X follows a normal distribution with mean m and standard

deviation sd, then the variable n * X follows a normal distribution with
mean n * m and standard deviation sd * sqr (n)
• How many reservations should we take ?
Exercise:
1) Build a model for profit taking into
consideration the randomness of the
variables above.
2) Simulate profits 100 times for all number of
reservations between 76 and 100. (Hint: use
a double entry table)