Business Technology Article 02 - Double Level Monte Carlo Simulation

Using Monte Carlo Simulation (MCS), we generally model a situation where a process has several inputs. We model the process by a formulation that results in one or more outputs. Some of the parameters in the formulation will be sampled from specific statistical distributions. When we replicate this formulation (in thousands of rows or runs), we will get thousands of outputs which are then subjected to analysis.

This is a single level simulation since each row represents one run through the process with a single set of outputs. I will consider a very simple example we will modify to show how we can improve the realism of the simulation by introducing double level runs. 

A) One Level Simulation: Summer Sales

Suppose we wish to simulate summer sales of some item (not widgets). We start with one row showing the starting quantity on hand (possibly from the previous summer or from an initial order in May). Assume this is a constant. We then sample some distribution that will generate a value equal to the sales in that summer. We sample another distribution to get a value for the returns in that summer. We can then calculate the net sales for that summer.

This table shows 6 out of the projected 10,000 runs (or summers or replications):
 
Sales and Returns are shown in green to indicate they are input variables (and hence are randomly generated). Net Sales are shown in blue to indicate the output we wish to analyze. (Green is change and blue is an objective, the sky). 

B) Two Level Simulation

What happens if we feel that a single randomly generated sample for the sales (per run) is not realistic? We have several show rooms and the sales of each one behave differently: one is just being launched, another is right smack in the middle of a shopping area, the third is in a low income area, etc. We cannot use the same distribution nor the same parameters for all our showrooms. To get a single value (net sales in one summer), it is preferable to conduct a “sub-simulation” whereby we can create hundreds of rows. For each row, we will extract a random sample from a specific distribution for that specific showroom. So in any row, we will have 5 individual sales, one for each showroom. We will call such rows sub-runs:

Let us call this the secondary table (sub-runs) while calling the initial table, the primary table.
Notice that we have a total of 302 which is the value of the sales in Run 1 in the primary table. (For simplicity, we are not including returns).

This looks too simple. Why are we raising this issue? Why not simply pick up the value (in blue) which represents the sales for that summer in the 5 showrooms and plug it into its respective row in the primary table?

There are several reasons standing in the way of simplicity:

Reason 1: Excel does not have a native method for looping over N rows. We cannot simply go from one row in the primary table to the next, branching out to another table to simulate the sub-runs and then return. But, we can use VBA to do that.

Reason 2: most models will have restrictions on the secondary simulation. These cannot be built without a separate secondary simulation. For example, we have a limited starting quantity. We can only have a number of sub-runs that will result in a net sale not greater than the starting quanity. (We assume that returns are damaged and not re-sellable). Another restriction may be that the actual number of individual sales in each showroom are not the same. We can model the sub-runs, say, that Showroom A has 20 sales this summer (totaling 180) while Showroom B has 14 sales, etc. In other words, we do not have the same count of values in the showroom columns.

The primary level simulates thousands of summers. The secondary level simulates as many  individual sales in 5 showrooms for one summer as are needed to use up the starting quantity and then plugs the result in the primary run. 

C) The Solution

A chance reading of a problem posed in Christian Albright and Wayne Winston's wonderful book: “Management Science Modeling” was the solution. The problem is presented using an Excel Add On called @RISK. However, its logic forms the basis of primary and secondary runs that resolves the above problems. The problem is called “Bidding for a Government Contract” and is in Chapter 12, Example 12.1 on page 653 (revised 3rd edition).

I have adapted the procedure using a VBA module and used it extensively in the two Monte Carlo Simulation books. You would need to specify the required number of runs somewhere in the worksheet. The VBA module will then loop as many times as that value. For each run, the VBA module pushes intoa specific cell in the worksheete the value of the current ID (or loop index). That’s all it does. The ID is used to locate a row in the primary table. Suitable formulas are used to pick up the result of the simulation in the secondary table and plug it into the specific row for that ID.

Things are a bit more complex when we realize we might have circular referencing, which is only allowed under specific conditions in Excel. (In fact, when you open the Excel Workbook in the download below, you will get that warning). 

Download a Fully Worked Out Example

Rather than go through the full cycle in this article, I have prepared a zipped file containing:
1) Workout 14 from the Monte Carlo Simulation eBook (Part 1)
2) Chapter 18 which is an appendix that shows how to use the VBA module
3) An Excel Workbook containing the fully solved workout
Click Here to download the zipped file. 

To comment, no need to register or log in with Disqus. Enter any name and a check box will enable you to comment as a guest. Your email will be required but Disqus will not show it in the thread. I am committed to your privacy and confidentiality and will not use it or distribute it. Click Here to review guest privacy and commenting policies in Disqus.