# Generating Random Numbers using Excel

**Part 3**of "A Practical Guide to Monte Carlo Simulation",

*by Jon Wittwer, PhD*

[ Step 1: Sales Forecast Model ] [ Creating a Histogram in Excel ]

## Step 2: Generating Random Inputs

The key to Monte Carlo simulation is generating the set of random inputs. As with
any modeling and prediction method, the "garbage in equals garbage out" principle applies.
For now, I am going to avoid the questions "*How do I know what distribution to use for
my inputs?*" and "*How do I make sure I am using a good random number generator?*"
and get right to the details of how to implement the method in Excel.

For this example, we're going to use a **Uniform Distribution** to represent the
four uncertain parameters. The inputs are summarized in the table shown below.
(If you haven't already, **Download the example spreadsheet**).

The table above uses "Min" and "Max" to indicate the uncertainty in L, C, R, and P.
To **generate a random number between "Min" and "Max"**, we use the following formula in
Excel (Replacing "min" and "max" with cell references):

**=**

*min*+ RAND()*(*max*-*min*)You can also use the **Random Number Generation** tool in Excel's Analysis ToolPak Add-In to
kick out a bunch of static random numbers for a few distributions. However, in this example
we are going to make use of Excel's **RAND()** formula so that every time the worksheet
recalculates, a new random number is generated.

Let's say we want to **run n=5000 evaluations** of our model.
This is a fairly low number when it comes to Monte Carlo simulation, and
you will see why once we begin to analyze the results.

A very convenient way to organize the data in Excel is to make a column for each variable as shown in the screen capture below.

Cell **A2** contains the formula:

=Model!$F$14+RAND()*(Model!$G$14-Model!$F$14)

Note that the reference **Model!$F$14** refers to the corresponding **Min** value for the variable **L** on the **Model** worksheet, as shown in Figure 1. (Hopefully you have downloaded the example
spreadsheet and are following along.)

To generate 5000 random numbers for **L**, you simply copy the formula down 5000 rows. You repeat
the process for the other variables (except for **H**, which is constant).

## Step 3: Evaluating the Model

Our model is very simple, so to evaluate the output of our model (the **Profit**) for each run of the simulation, we just put the equation in another column next to the inputs, as shown in Figure 2.

Cell **G2** contains the formula:

=A2*C2*D2-(E2+A2*B2)

## Step 4: Running the Simulation

To iteratively evaluate our model, we don't need to write a fancy macro for this example. We simply copy the formula for profit down 5000 rows, making sure that we use relative references in the formula (no $ signs). Each row represents a single evaluation of the model, with columns A-E as inputs and the Profit as the output.

## Re-run the Simulation: F9

Although we still need to analyze the data, we have essentially completed a Monte Carlo simulation. Because we have used the volatile RAND() formula, to re-run the entire simulation all we have to do is recalculate the worksheet (**F9** is the shortcut).

This may seem like a strange way to implement Monte Carlo simulation, but think about what is going on behind the scenes every time the Worksheet recalculates: **(1)** 5000 sets of random inputs are generated **(2)** The model is evaluated for all 5000 sets. Excel is handling all of the iteration.

If your model is not simple enough to include in a single formula, you can create your own custom Excel function (see my article on user-defined functions), or you can create a macro to iteratively evaluate your model and dump the data into a worksheet in a similar format to this example (*Update 9/8/2014*: See my new Monte Carlo Simulation template).

**In practice**, it is usually more convenient to buy an add-on for Excel than to do a Monte Carlo analysis from scratch every time. But not everyone has the money to spend, and hopefully the skills you will learn from this example will aid in future data analysis and modeling.

[ Step 1: Sales Forecast Model ] [ Creating a Histogram in Excel ]

## A Few Other Distributions

My new **Monte Carlo Simulation template** includes a worksheet that calculates inputs sampled from a variety of distributions. Some of the formulas are listed below.

### Normal (Gaussian) distribution

To generate a random number from a Normal distribution you would use the following formula in Excel:

**=NORMINV(rand(),**

*mean*,*standard_dev*)Ex: =NORMINV(RAND(),$D$4,$D$5)

Excel 2010+: =NORM.INV(RAND(),$D$4,$D$5)

### Lognormal distribution

To generate a random number from a Lognormal distribution with median = exp(meanlog), and shape = sdlog, you would use the following formula in Excel:

**=LOGINV(RAND(),**

*meanlog*,*sdlog*)Ex: =LOGINV(RAND(),$D$6,$D$5)

Excel 2010+: =LOGNORM.INV(RAND(),$D$4,$D$5)

### Weibull distribution

There isn't an inverse Weibull function in Excel, but the formula is quite simple, so to generate a random number from a (2-parameter) Weibull distribution with **scale = c**, and

**shape =**, you would use the following formula in Excel:

*m***=**

*c**(-LN(1-RAND()))^(1/*m*)Ex: $C$5*(-LN(1-RAND()))^(1/$C$6)

### Beta distribution

This distribution can be used for variables with finite bounds (A,B). It uses two shape parameters, alpha and beta. When alpha=beta=1, you get a Uniform distribution. When alpha=beta=2, you get a dome-shaped distribution which is often used in place of the Triangular distribution. When alpha=beta=5 (or higher), you get a bell-shaped distribution. When alpha<>beta (not equal), you get a variety of skewed shapes.

**MORE Distribution Functions:** Dr. Roger Myerson provides a free downloadable
Excel add-in, **Simtools.xla**,
that includes many other distribution functions for generating random numbers in
Excel.