Transcript Monte Carlo Simulation - The University of Texas at Dallas

Monte Carlo
Simulation
Natalia A. Humphreys
April 6, 2012
University of Texas at Dallas
Aknowledgement
 Wayne L. Winston, “Microsoft Excel Data Analysis and
Business Modeling”, 2004
Overview
 Part I
 Questions answered with the help of MCS
 History
 Typical simulations
 Part II: Simulation examples
 Part III: Advantages of MCS over deterministic
analysis
Challenges
 We are constantly faced with uncertainty, ambiguity,
and variability.
 Risk analysis is part of every decision we make.
 We’d like to accurately predict (estimate) the
probabilities of uncertain events.
 Monte Carlo simulation enables us to model situations
that present uncertainty and play them out thousands
of times on a computer.
Questions answered with
the help of MCS
 How should a greeting card company determine how
many cards to produce?
 How should a car dealership determine how many
cars to order?
 What is the probability that a new product’s cash
flows will have a positive net present value (NPV)?
 What is the riskiness of an investment portfolio?
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?
 General Motors (GM)
 Procter and Gamble (P&G)
 Eli Lilly
 Wall Street firms
 Sears
 Financial planners
 Other companies, organizations and individuals
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
 Forecast net income for the corporation
 Predict structural costs and purchasing costs

Determine its susceptibility to different risks:
 Interest rate changes
 Exchange rate fluctuations
MCS Use: Lilly
 Determine the optimal plant capacity that should be
built for each drug
MCS Use: Wall Street
 Price complex financial derivatives
 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
 Model and optimally hedge foreign exchange risk
MCS Use: Sears
 How many units of each product line should be
ordered from suppliers
MCS Use: Financial
Planners
 Determine optimal investment strategies for their
clients’ retirement.
MCS Use: Others
 Value “real options”:
 Value of an option to expand, contract, or postpone a
project
MCS Applications

Physical Sciences

Engineering

Computational Biology

Applied Statistics

Games

Design and visuals

Finance and business (Actuarial Science)

Telecommunications

Mathematics
Part II
 We’ll now discuss how Monte Carlo simulation works
by looking at a few simulation examples
=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 time
Example 1: Discrete
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
function with a possible demand for calendars.
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
60,000
Greater than or equal to 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
60,000
0.823097422
2
10,000
0.076074298
3
20,000
0.364201634
4
40,000
0.698116365
Discr r.v. Sim (cont.)
 The function used to create the values in the third
column of the second table is called the VLOOKUP
function.
 Its syntax in Excel is:
 VLOOKUP( lookup_value, table_array,
col_index_num, range_lookup )
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
DEMAND
PROBABILI
TY
10,000
0.10
10,000
0.10250
20,000
0.35500
20,000
0.35
40,000
0.29250
40,000
0.30
60,000
0.25000
60,000
0.25
Example 2: Normal
Random Variable
Simulation
 Suppose we want to simulate 400 trials or iterations
for a normal r.v. with a mean μ=40,000 and standard
deviation σ=10,000
 What is a normal random variable?
 Let us first define the standard normal random variable.
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)=Pr(Z<z)
 A standard normal r.v. Z is a r.v. with μ=0 and σ=1
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
Y=σZ+μ
Normal Random
Variable Simulation
 Suppose we want to simulate 400 trials or iterations
for a normal r.v. with a mean μ=40,000 and standard
deviation σ=10,000
 The formula NORMINV(RAND(), μ, σ) will generate
a simulated value of a normal r.v. having a mean μand
standard deviation σ.
Normal r.v. Sim (cont.)
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
33,518.16 = NORMINV(0.258433031, 40,000, 10,000)
 This value could also be looked up using the Standard
Normal Distribution table.
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
maximum average profit over the 1,000 iterations
Cards to Produce? (cont.)
1
2
3
produced
rand
demandcard
10,000
0.400927091
20,000
4
5
unit prod cost
unit price
$1.50
$4.00
6
7
8
unit disp cost
revenue
total var cost
$0.20
$40,000.00
$15,000.00
9
10
total disposing cost
profit
$$25,000.00
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
MIN (Production Quantity, Demand)
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:
Revenue – total var cost – total disposing cost
Cards to Produce? (cont.)
 We would like an efficient way to calculate profit for
each production quantity
 We’ll use a two-way data table
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
profits for each production quantity
Cards to Produce?
Conclusion
 Producing 40,000 cards always yields the largest
expected profit
 However, it also appear to have a large standard
deviation (risk)
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)
 In our example: (53,650.46 59,628.26 )
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.)
2
The supermarket pays $1.00 for each copy of
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” analysis.
Advantages of MCS
MCS provides a number of advantages over deterministic,
or “single-point estimate” analysis:
 Probabilistic Results
 Graphical Results
 Sensitivity Analysis
 Scenario Analysis
 Correlation of Inputs
Probabilistic Results
 Results show not only what could happen, but how
likely each outcome is.
Graphical Results
 Because of the data a Monte Carlo simulation
generates, it’s easy to create graphs of different
outcomes and their chances of occurrence.
 This is important for communicating findings to other
stakeholders.
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
inputs had the biggest effect on bottom-line results.
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.
 This is invaluable for pursuing further analysis.
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.
References
 Wayne L. Winston, “Microsoft Excel Data Analysis and
Business Modeling”, 2004
http://office.microsoft.com/en-us/excelhelp/introduction-to-monte-carlo-simulationHA001111893.aspx
 Monte Carlo Simulation
http://www.palisade.com/risk/monte_carlo_simulati
on.asp