APPLIES TO

Microsoft Office Excel 2003

This article was adapted from Microsoft Excel Data Analysis and
Business Modeling by Wayne L. Winston. Visit Microsoft Learning to
learn more about this book.
This classroom-style book was developed from a series of
presentations by Wayne Winston, a well known statistician and
business professor who specializes in creative, practical applications
of Excel. So be prepared you may need to put your thinking cap on.
In this article
Who uses Monte Carlo simulation?
What happens when I enter =RAND() in a cell?
How can I simulate values of a discrete random variable?
How can I simulate values of a normal random variable?
How should a greeting card company determine how many cards to produce?
The impact of risk on our decision
Confidence interval for mean profit
Problems
Sample files You can download the sample files that relate to excerpts from
Microsoft Excel Data Analysis and Business Modeling from Microsoft Office Online.
This article uses the files RandDemo.xls, Discretesim.xls, NormalSim.xls, and
Valentine.xls.
We would like to be able to accurately estimate the probabilities of uncertain events.
For example, what is the probability that a new products cash flows will have a
positive net present value (NPV)? What is the riskiness of our investment portfolio?
Monte Carlo simulation enables us to model situations that present uncertainty and
play them out thousands of times on a computer.
NOTE The name Monte Carlo simulation comes from the fact that 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 physicists involved in this work were big fans of gambling, so they
gave the simulations the code name Monte Carlo.
Introduction to Monte Carlo simulation
Applies to: Microsoft Office Excel 2003
Support / Excel
Introduction to Monte Carlo si... Page 1
http://office.microsoft.com/en... 19/05/2011 13:59:44
Who uses Monte Carlo simulation?
Many companies use Monte Carlo simulation as an important tool for decision-
making. Here are some examples.
General Motors, Procter and Gamble, and Eli Lilly use simulation to estimate
both the average return and the riskiness of new products. At GM, this
information is used by CEO Rick Waggoner to determine the products that
come to market.

GM uses simulation for activities such as forecasting net income for the
corporation, predicting structural costs and purchasing costs, determining its
susceptibility to different kinds of risk (such as interest rate changes and
exchange rate fluctuations).

Lilly uses simulation to determine the optimal plant capacity that should be
built for each drug.

Wall Street firms use simulation to price complex financial derivatives and
determine the Value at RISK (VAR) of their investment portfolios.

Procter 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, how many pairs of Dockers
should be ordered this year.

Simulation can be used to value "real options," such as the value of an option
to expand, contract, or postpone a project.

Financial planners use Monte Carlo simulation to determine optimal

investment strategies for their clients retirement.

What happens when I enter =RAND()in a cell?

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. Thus, around 25 percent of the time, you
should get a number less than or equal to .25; around 10 percent of the time you
should get a number that is at least .90, and so on. To see how the RAND function
works, take a look at the file RandDemo.xls, shown in the following figure.
Introduction to Monte Carlo si... Page 2
http://office.microsoft.com/en... 19/05/2011 13:59:44
NOTE When you open the file RandDemo.xls, you will not see the same random
numbers shown in the previous figure. The RAND function always recalculates the
numbers it generates when a spreadsheet is opened or new information is entered in
the spreadsheet.
I copied from cell C3 to C4:C402 the formula =RAND(). I named the range C3:C402
Data. Then, in column F, I tracked the average of the 400 random numbers (cell F2)
and used the COUNTIF function to determine the fractions that are between 0
and .25, .25 and .50, .50 and .75 and .75 and 1. When you press the F9 key, the
random numbers are recalculated. Notice that the average of the 400 numbers is
always near .5 and that around 25 percent of the results are in each interval of .25.
These results are consistent with the definition of a random number. Also note that
the values generated by RAND in different cells are independent. For example, if the
random number generated in cell C3 is a large number (say .99), this tells us nothing
about the values of the other random numbers generated.
How can I simulate values of a discrete random
variable?
Suppose the demand for a calendar is governed by the following discrete random
variable.
DEMAND PROBABILITY
10,000 .10
20,000 .35
40,000 .3
60,000 .25
How can we have Excel play out, or simulate, this demand for calendars many
times? The trick is to associate each possible value of the RAND function with a
possible demand for calendars. 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 .10
Introduction to Monte Carlo si... Page 3
http://office.microsoft.com/en... 19/05/2011 13:59:44
20,000 Greater than or equal to .10, and less than .45
40,000 Greater than or equal to .45, and less than .75.
60,000 Greater than or equal to .75.
To see a simulation of demand, look at the file Discretesim.xls, shown in the
following figure.
The key to our simulation is to use a random number to key a lookup from the table
range F2:G5 (named lookup). Random numbers greater than or equal to 0 and less
than .10 will yield a demand of 10,000; random numbers greater than or equal to .10
and less than .45 will yield a demand of 20,000; random numbers greater than or
equal to .45 and less than .75 will yield a demand of 40,000; and random numbers
greater than or equal to .75 will yield a demand of 60,000. I generated 400 random
numbers by copying from C3 to C4:C402 the formula RAND(). Then I generated 400
trials or iterations of calendar demand by copying from B3 to B4:B402 the formula
VLOOKUP(C3,lookup,2). This formula ensures that any random number less
than .10 generates a demand of 10,000; any random number between .10 and .45
generates a demand of 20,000, and so on. In the cell range F8:F11, I used the
COUNTIF function to determine the fraction of our 400 iterations yielding each
demand. Note that whenever you press F9 to recalculate the random numbers, the
simulated probabilities are close to our assumed demand probabilities.
How can I simulate values of a normal random
variable?
If you enter into any cell the formula NORMINV(rand(), mu , sigma), you will generate
a simulated value of a normal random variable having a mean mu and standard
deviation sigma. Ive illustrated this procedure in the file NormalSim.xls, shown in the
following figure.
Introduction to Monte Carlo si... Page 4
http://office.microsoft.com/en... 19/05/2011 13:59:44
Lets suppose we want to simulate 400 trials or iterations for a normal random
variable with a mean of 40,000 and a standard deviation of 10,000. (I entered these
values in cells E1 and E2 and named these cells mean and sigma, respectively.)
Copying the formula =RAND() from C4 to C5:C403 generates 400 different random
numbers. Copying from B4 to B5:B403 the formula NORMINV(C4,mean,sigma)
generates 400 different trial values from a normal random variable with a mean of
40,000 and a standard deviation of 10,000. When we press the F9 key to recalculate
the random numbers, the mean remains close to 40,000 and the standard deviation
close to 10,000.
Essentially, for a random number x, the formula NORMINV(p, mu , sigma) generates
the p
th
percentile of a normal random variable with a mean mu and a standard
deviation sigma. For example, the random number .8466 in cell C13 generates in cel
B13 approximately the 85th percentile of a normal random variable with a mean of
40,000 and a standard deviation of 10,000.
How should a greeting card company determine
how many cards to produce?
In this section, Ill show how Monte Carlo simulation can be used as a tool to help
businesses make better decisions. Suppose that the demand for a Valentines Day
card is governed by the following discrete random variable:
DEMAND PROBABILITY
10,000 .10
20,000 .35
40,000 .3
60,000 .25
The greeting card sells for $4.00, and the variable cost of producing each card is
$1.50. Leftover cards must be disposed of at a cost of $0.20 per card. How many
cards should be printed?
Basically, we simulate each possible production quantity (10,000, 20,000, 40,000 or
60,000) many times (say, 1,000 iterations). Then we determine which order quantity
Introduction to Monte Carlo si... Page 5
http://office.microsoft.com/en... 19/05/2011 13:59:44
yields the maximum average profit over the 1,000 iterations. You can find the work
for this section in the file Valentine.xls, shown in the following figure. Ive assigned
the range names in cells B1:B11 to cells C1:C11. Ive assigned the cell range G3:H6
the name lookup. Our sales price and cost parameters are entered in cells C4:C6.
I then enter a trial production quantity (40,000 in this example) in cell C1. Next I
create a random number in cell C2 with the formula =RAND(). As previously
described, I simulate demand for the card in cell C3 with the formula VLOOKUP
(rand,lookup,2). (In the VLOOKUP formula, rand is the cell name assigned to cell C3
not the RAND function.)
The number of units sold is the smaller of our production quantity and demand. In
cell C8, I compute our revenue with the formula MIN(produced,demand)*unit_price.
In cell C9, I compute total production cost with the formula produced*unit_prod_cost.
If we produce more cards than are demanded, the number of units left over equals
production minus demand; otherwise no units are left over. We compute our disposal
cost in cell C10 with the formula unit_disp_cost*IF(produced>demand,produced-
demand,0). Finally, in cell C11, we compute our profit as revenue-total_var_cost-
total_disposing_cost.
We would like an efficient way to press F9 many (say 1,000 times) for each
production quantity and tally up our expected profit for each production quantity. This
situation is one in which a two-way data table comes to our rescue. The data table I
used in this example is shown in the following figure.
Introduction to Monte Carlo si... Page 6
http://office.microsoft.com/en... 19/05/2011 13:59:44
In the cell range A16:A1015, I entered the numbers 1-1000 (corresponding to our
1,000 trials). One easy way to create these values is to enter a 1 in cell A16, select
the cell, and then, on the Edit menu, click Fill Series. In the Series dialog box,
shown in the following figure, enter a step value of 1 and a stop value of 1000. Under
Series in, click Columns, and then click OK. The numbers 1 through 1000 will be
entered automatically in column A, starting in cell A16.

Next we enter our possible production quantities (10,000, 20,000, 40,000, 60,000) in
cells B15:E15. We want to calculate profit for each trial number (1 through 1,000)
and each production quantity. We refer to the formula for profit (calculated in cell
C11) in the upper left cell of our data table (A15) by entering =C11.
We are now ready to trick Excel into simulating 1,000 iterations of demand for each
production quantity. Select the table range (A15:E1014), and then click Table on the
Data menu. To set up a two-way data table, we select any blank cell (we chose cell
I14) as our column input cell and choose our production quantity (cell C1) as the row
Introduction to Monte Carlo si... Page 7
http://office.microsoft.com/en... 19/05/2011 13:59:44
input cell. When you click OK, Excel simulates 1,000 demand values for each order
quantity.
To illustrate why this works, consider the values placed by the data table in the cell
range C16:C1015. For each of these cells, Excel will use a value of 20,000 in cell C1
In C16, the column input cell value of 1 is placed in a blank cell and the random
number in cell C2 recalculates. The corresponding profit is then recorded in cell C16.
Then the column cell input value of 2 is placed in a blank cell, and the random
number in C2 again recalculates. The corresponding profit is entered in cell C17.
By copying from cell B13 to C13:E13 the formula AVERAGE(B16:B1015), we
compute average simulated profit for each production quantity. By copying the
formula STDEV (B16:B1015) from cell B14 to C14:E14, we compute the standard
deviation of our simulated profits for each order quantity. Each time we press F9,
1,000 iterations of demand are simulated for each order quantity. Producing 40,000
cards always yields the largest expected profit. Therefore, it appears as if producing
40,000 cards is the proper decision.
THE IMPACT OF RISK ON OUR DECISION
If we produce 20,000 cards instead of 40,000 cards, our expected profit drops
approximately 22 percent, but our risk (as measured by the standard deviation of
profit) drops almost 73 percent. Therefore, if we are extremely risk averse, producing
20,000 cards might be the right decision. By the way, producing 10,000 cards always
has a standard deviation of zero cards because if we produce 10,000 cards, we will
always sell all of them and have none left over.
NOTE In this worksheet, I set the Calculation option to Automatic Except For
Tables. (See the Calculation tab on the Options dialog box.) This setting ensures
that our data table will not recalculate unless we press F9, which is a good idea
because a large data table will slow down your work if it recalculates every time you
type something into your spreadsheet. Note that in this example, whenever you
press F9, the mean profit will change. This happens because each time you press F9
a different sequence of 1,000 random numbers is used to generate demands for
each order quantity.
CONFIDENCE INTERVAL FOR MEAN PROFIT
A natural question to ask in this situation is "Into what interval are we 95 percent sure
the true mean profit will fall?" This interval is called the 95 percent confidence interva
for mean profit. A 95 percent confidence interval for the mean of any simulation
output is computed by the following formula.

In cell J 11, I computed the lower limit for the 95 percent confidence interval on mean
profit when 40,000 calendars are produced with the formula D13-1.96*D14/SQRT
(1000). In cell J 12, I computed the upper limit for our 95 percent confidence interval
Introduction to Monte Carlo si... Page 8
http://office.microsoft.com/en... 19/05/2011 13:59:44
with the formula D13+1.96*D14/SQRT(1000). These calculations are shown in the
following figure.
We are 95 percent sure that our mean profit when 40,000 calendars are ordered is
between $53,860 and $59,934.
Problems
A GMC dealer believes that demand for 2005 Envoys will be normally
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. He is considering ordering 200, 220,
240, 260, 280, or 300 Envoys. How many should he order?
1.
A small supermarket is trying to determine how many copies of People
magazine they should order each week. They believe their demand for
People is governed by the following discrete random variable.
2.
DEMAND PROBABILITY
15 .10
20 .20
25 .30
30 .25
35 .15
The supermarket pays $1.00 for each copy of People and sells each copy for
$1.95. They can return each unsold copy of People for $0.50. How many
copies of People should the store order?
3.
2011 Microsoft Corporation. All rights reserved.
Introduction to Monte Carlo si... Page 9
http://office.microsoft.com/en... 19/05/2011 13:59:44