Generating Random Numbers using Excel

Sales Forecast Example - Part II

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 applys. 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).

Figure 1: Screen capture from the example sales forecast 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.

Figure 2: Screen capture from the example sales forecast spreadsheet.

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

Since our model is very simple, all we need to do to evaluate the model for each run of the simulation is put the equation in another column next to the inputs, as shown in Figure 2 (the Profit column).

Cell G2 contains the formula:
=A2*C2*D2-(E2+A2*B2)

Step 4: Run the Simulation

We don't need to write a fancy macro for this example in order to iteratively evaluate our model. We simply copy the formula for profit down 5000 rows, making sure that we use relative references in the formula (no $ signs).

Rerun 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 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.

Until I get around to providing another example that uses macros, let me just say that 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.

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.

---

[ Sales Forecast ]         [ Step 5: Creating a Histogram in Excel ]

---

A Few Other Distributions

---

CITE THIS PAGE AS: