Monte Carlo Simulation

Monte Carlo

Monte Carlo

Simulation

Simulation

University of Texas at Dallas

 Aknowledgement

 Aknowledgement

 Wayne L. Winston,Wayne L. Winston, “Microsoft Excel Data Analysis“Microsoft Excel Data Analysis

and Business Modeling” 

 Aknowledgement

 Aknowledgement

 Wayne L. Winston,Wayne L. Winston, “Microsoft Excel Data Analysis“Microsoft Excel Data Analysis

and Business Modeling” 

Overview

Overview

 Part IPart I



 Questions answered with the help of Questions answered with the help of MCSMCS



 HistoryHistory



 Typical simulationsTypical simulations 

 Part II: Part II: Simulation examplesSimulation examples



 Part III: AdvanPart III: Advantages of tages of MCS over MCS over deterministicdeterministic

analysis

Challenges

Challenges

 We are constantly faced with uncertainty, ambiguity,We are constantly faced with uncertainty, ambiguity,

and

and variabilityvariability..



 Risk analysis is part of every decision we make.Risk analysis is part of every decision we make. 



We’d like to accurately predict (estimate) the

We’d like to accurately predict (estimate) the

probabilitie

probabilities s of uncertain events.of uncertain events.



 Monte Carlo simulation enables us to modelMonte Carlo simulation enables us to model

situations that present uncertainty and play them situations that present uncertainty and play them out thousands of times on a computer.

Questions answered

Questions answered

with the help of MCS

with the help of MCS

 How should a greeting card How should a greeting card company determinecompany determine

how many cards to produce?

how many cards to produce?



 How should a car How should a car dealership determine how manydealership determine how many

cars to order?

cars to order?





What is the probability that

What is the probability that

a new product’s cash

a new product’s cash

flows will have a positive net present

flows will have a positive net present value (NPV)?value (NPV)?



Modeling with MCS

 Monte Carlo Simulation (MCS) lets you see all the

possible outcomes of your decisions and assess the impact of risk, allowing for better decision making under uncertainty.

MCS: Where did the

Name Come From?

 During the 1930s and 1940s, many computer 

simulations were performed to estimate the probability that the chain reaction needed for the atom bomb would work successfully.

 The Monte Carlo method was coined then by the

physicists John von Neumann, Stanislaw Ulam and

Nicholas Metropolis, while they were working on this and other nuclear weapon projects (Manhattan Project) in the Los Alamos National Laboratory.

 It was named in homage to the Monte Carlo Casino, a

famous casino in the Monaco resort Monte Carlo where Ulam's uncle would often gamble away his money.

Who Uses MCS?

 Procter and Gamble (P&G)  Eli Lilly

 Wall Street firms

 Sears

 Financial planners

MCS Use

 General Motors (GM), Procter and Gamble (P&G),

and Eli Lilly use simulation to estimate both the average return and the riskiness of new products.

MCS Use: GM

 Predict structural costs and purchasing costs  Determine its susceptibility to different risks:

 Interest rate changes

MCS Use: Lilly

 Determine the optimal plant capacity that should be

MCS Use: Wall Street

 Determine the Value at Risk (VaR) of investment

portfolios.

 By definition, Value at Risk at security level p for a

random variable X is the number VaR_p(X) such that

Pr(X<VaR_p(X))=p

In practice, p is selected to be close to 1: 95%, 99%, 99.5%

MCS Use: Procter &

Gamble

MCS Use: Sears

 How many units of each product line should be

MCS Use: Financial

Planners

 Determine optimal investment strategies for their 

MCS Use: Others

Value “real options”:

 Value of an option to expand, contract, or postpone a

MCS Applications

 Design and visuals

 Finance and business (Actuarial Science)  Telecommunications

Part II



We’ll now discuss how Monte Carlo simulation

=RAND() function

 When you enter the formula =RAND() in a cell, you

get a number that is equally likely to assume any value between 0 and 1.

 Get a number less than or equal to 0.25 around 25% of 

the time

 Get a number that is at least 0.9 around 10% of the

xamp e : scre e

Random Variable

Simulation

 Demand for a calendar is governed by the following

discrete r.v.: DEMAND PROBABILITY 10,000 0.10 20,000 0.35 40,000 0.30 60,000 .25

Discrete r.v.

Simulation(cont.)

 How can we have Excel play out, or simulate, this

demand for calendars many times?

 We associate each possible value of the RAND

Discr r.v. Sim (cont.)

 The following assignment ensures that a demand of 

10,000 will occur 10 percent of the time, and so on.

DEMAND RANDOM NUMBER ASSIGNED

10,000 Less than 0.10

20,000 Greater than or equal to 0.10 and less than

0.45

40,000 Greater than or equal to 0.45 and less than 0.75

Discr r.v. Sim (cont.)

 Creating the following cutoff table, we then use it to

look up the values “assigned” to each random

number: CUTOFF DEMAND 0 10,000 0.1 20,000 0.45 40,000 0.75 60,000

TRIAL RAND SIM

DEMAND 1 0.823097422 60,000 2 0.076074298 10,000 3 0.364201634 20,000 4 0.698116365 40,000

Discr r.v. Sim (cont.)

column of the second table is called the VLOOKUP function.

 Its syntax in Excel is:

 VLOOKUP( lookup_value, table_array,

Discr r.v. Sim (cont.)

 Thus, the VLOOKUP(0.823097422, LOOKUP, 2,

1)=60,000

 TRUE=1, FALSE=0

 If VLOOKUP can't find lookup value, and range

lookup is TRUE, it uses the largest value that is less than or equal to lookup value.

Discr r.v. Sim (cont.)

 If we simulate 400 values of calendar demand and

then calculate the fraction of time each demand

appears in the simulation, we’ll get a table similar to

the following: DEMAND FRACTION OF TIME 10,000 _0.10250 20,000 _0.35500 40,000 _0.29250 60,000 _0.25000 DEMAND PROBABILI TY 10,000 0.10 20,000 0.35 40,000 0.30 60,000 0.25

xamp e : orma

Random Variable

Simulation

 Suppose we want to simulate 400 trials or iterations

for a normal r.v. with a mean

μ

σ

 What is a normal random variable?

 Let us first define the standard normal random

Standard Normal

Random Variable



Its distribution has a form of a “bell” curve around

the zero.

 Standard Normal Distribution Table is a table that

shows probability that a standard normal random variable Z is less than a number z:

Φ(z

Connection between

any Normal r.v. and a

Standard Normal r.v.

If 

Z is N(0, 1) and is Y is N(μ, σ^2), then

Normal Random

Variable Simulation

 Suppose we want to simulate 400 trials or iterations

for a normal r.v. with a mean

μ

σ

 The formula NORMINV(RAND(),

μ

σ

a simulated value of a normal r.v. having a mean

Normal r.v. Sim (cont.)

33,518.16 = NORMINV(0.258433031, 40,000, 10,000)

 This value could also be looked up using the

Standard Normal Distribution table.

TRIAL RAND NORMAL RV

1 0.258433031 33,518.16 2 0.344835199 36,006.98 3 0.927522163 54,575.82 4 0.248403053 33,204.76

Example 3: How Many

Cards to Produce?

Suppose the demand for a Valentine’s Day card is

governed by the following discrete r.v.:

DEMAND PROBABILITY

10,000 0.10 20,000 0.35 40,000 0.30 60,000 .25

Cards to Produce?

(cont.)

 The greeting card sells for $4.00

 The variable cost of producing each card is $1.50  Leftover cards will be disposed at $0.20 per card

How many cards should be printed to get

the highest profit?

Cards to Produce?

(cont.)

 We simulate each possible production quantity

(10,000, 20,000, 40,000 or 60000) many times (e.g. 1,000 iterations)

 Then we determine which order quantity yields the

Cards to Produce?

(cont.)

1 produced 10,000 2 rand 0.400927091

3 demandcard 20,000

4 unit prod cost $1.50 5 unit price $4.00 6 unit disp cost $0.20 7 revenue $40,000.00 8 total var cost $15,000.00 9 total disposing cost

Cards to Produce?

(cont.)

 Our sales and cost parameters are in 4, 5, and 6  Enter a trial production quantity in 1

 Create a random number in 2 with =RAND()  Simulate demand for the card in 3 with

VLOOKUP(rand, lookup, 2)

 The number of unites sold is

Cards to Produce?

(cont.)

 Revenue in 7: MIN (Produced, Demand)*unit price  Total production cost in 8: produced*unit production

cost

 If we produce more cards than are demanded, the

number of units left over equals production minus demand

Cards to Produce?

(cont.)

 Disposal cost in 9:

unit disposal cost*MAX(produced-demand, 0)

 Total profit in 10:

Cards to Produce?

(cont.)

 We would like an efficient way to calculate profit for 

each production quantity



We’ll use a two

mean (ave profit) 24,985 45,984 57,311 44,218 st dev (risk) - 12,321.19 48,346.89 73,622.44 25,000 10,000 20,000 40,000 60,000 1 25000 50000 16000 -60000 2 25000 50000 100000 66000 3 25000 50000 16000 66000 4 25000 50000 100000 150000 5 25000 50000 100000 -18000

Cards to Produce?

(cont.)

 Enter 1-1000 on the left corresponding to our 1,000

trials

 Enter possible production quantities (third row)

 We want to calculate profit for each trial number and

each production quantity

 Refer to the formula for profit in the upper left cell of 

our data table by entering =B11

 We are now ready to trick Excel into simulating

1,000 iterations of demand for each production quantity.

Cards to Produce?

(cont.)

 Select the table range and then click Table on the

Data menu.

 Click on any blank cell (e.g. I14) as the column

input cell and choose production quantity (cell B1) as the row input cell.

 We calculate the average simulated profit for each

production quantity

 We calculate the standard deviation of simulated

Cards to Produce?

Conclusion

 Producing 40,000 cards always yields the largest

expected profit

 However, it also appear to have a large standard

The Impact of Risk in

Our Decision

 Producing 20,000 cards instead of 40,000, the

expected profits drop by about 22%, but the risk drops almost 73%.

 Therefore, if we are extremely risk averse,

producing 20,000 cards might be the right decision.

 Note that producing 10,000 cards always has a

std.dev. of zero cards because if we produce 10,000 cards we will always sell all of them and have none left over.

Confidence Interval for 

Mean Profit

 Into what interval are we 95% sure the true mean

will fall?

 This interval is called the 95% confidence interval 

for mean profit .



It’s computed by the following formula:

Mean Profit ±(1.96*profit std.dev

.)/√(number iterations)

Problems

1  A GMC dealer believes that demand for 2005

Envoys will normally be distributed with a mean of  200 and standard deviation of 30. His cost of 

receiving an Envoy is $25,000, and he sells an

Envoy for $40,000. Half of all leftover Envoys can be sold for $30,000. His is considering ordering 200, 220, 240, 260, 280, and 300 Envoys. How many should he order?

Problems (cont.)

2  A small supermarket is trying to determine how

many copies of Newsweek magazine they should order each week. They believe their demand for  Newsweek is governed by the following discrete random variable DEMAND PROBABILITY 15 0.10 20 0.20 25 0.30 30 0.25 35 0.15

Problems (cont.)

Newsweek and sells each copy for $1.95. They can return each unsold copy of Newsweek for $0.50.

How many copies of Newsweek should the store order to maximize its profit?

Part III: Advantages of 

MCS



In conclusion, we’ll discuss some advantages of 

MCS over deterministic,

or “single

point estimate”

 Advantages of MCS

MCS provides a number of advantages over 

deterministic,

or “single

point estimate” analysis:

 Probabilistic Results  Graphical Results  Sensitivity Analysis  Scenario Analysis

Probabilistic Results

 Results show not only what could happen, but how

Graphical Results

generates, it’s easy to create graphs of different

outcomes and their chances of occurrence.

 This is important for communicating findings to

Sensitivity Analysis

 With just a few cases, deterministic analysis makes

it difficult to see which variables impact the outcome the most.



In Monte Carlo simulation, it’s easy to see which

Scenario Analysis



In deterministic models, it’s very difficult to model

different combinations of values for different inputs to see the effects of truly different scenarios.

 Using Monte Carlo simulation, analysts can see

exactly which inputs had which values together  when certain outcomes occurred.

Correlation of Inputs

In Monte Carlo simulation, it’s possible to model

interdependent relationships between input variables.



It’s important for accuracy to represent how, in

reality, when some factors go up, others go up or  down accordingly.