Installing and Using the Monte Carlo Simulation with Solver Excel Add-in

Installing and Using the Monte Carlo Simulation with Solver Excel Add-in
Software for
Introductory Econometrics

By

Humberto Barreto and Frank M. Howland

[e-mail] and [e-mail]

[phone] and [phone]

WARNING:

This software was written and designed for teaching purposes. It has been tested on several examples, but not on a wide variety of datasets. For mission critical projects, always check the results with full-fledged statistical programs.
Excel 2000 or greater is needed for this add-in. Previous versions do not support the CalculateFull option. See www.decisionmodels.com/calcsecretsg.htm for more details on calculation in Excel.
The Monte Carlo Simulation with Solver add-in is MCSimSolver.xla (from the CD ROM, it is in BasicTools\ExcelAddIns\MCSimSolver).
This document assumes Excel’s Solver has been successfully installed and that the user is familiar with Solver. If not, please see the document Solver.doc before proceeding.

PURPOSE OF THIS DOCUMENT

This document describes how to install and use the Excel add-in MCSimSolver.xla in order to run a Monte Carlo simulation using Excel’s Solver in each repetition in an Excel workbook.

MCSimSolver.xla is not suitable for conventional Monte Carlo simulation. If you want to run a Monte Carlo without Solver, please use MCSim.xla, the Monte Carlo simulation add-in.

INSTALLING AND LOADING

Microsoft offers the following description of an Excel add-in:

Add-ins are programs that add optional commands and features to Microsoft Excel. Before you can use an add-in, you must install it on your computer and then load it in Microsoft Excel. Add-ins (*.xla files) are installed by default in the Library folder in the Microsoft Excel folder. Loading an add-in makes the feature available in Microsoft Excel and adds any associated commands to the appropriate menus. [Microsoft Excel Help, add-ins, overview]
Thus, to install an add-in is to have an add-in file (*.xla) in the Library folder of your hard drive. To load it, you must complete an additional step using the Add-In Manager. Fortunately, you need to do this only once.

Step 1: Installing the MCSimSolver.xla file

If you are accessing the MCSimSolver.xla add-in from a CD ROM, place the CD in your computer. If accessing from a network server, make sure you can read from the appropriate network drive.

If you do not have CD ROM or network access to the MCSimSolver.xla add-in, download it by visiting <www.wabash.edu/econometrics>. Download the MCSimSolver.xla file directly to the appropriate add-ins folder on your hard drive (usually, C:/Program Files/MicrosoftOffice/Office/Library) or move it there after downloading to your hard drive.

Step 2: Loading The MCSimSolver.xla add-in

Once the MCSimSolver.xla file is accessible, launch Excel and use the Add-In Manager to load the Monte Carlo Simulation add-in. First, open the Add-In Manager by clicking on the Tools menu item and selecting Add-Ins.

I
Mac Note: Some versions of OfficeX report, “Unable to Copy add-in to the Add-ins folder.” This is a bug. The add-in really is there. Simply quit Excel, then restart it, return to the Add-In Manager and continue following the instructions.
f the Monte Carlo Simulation with Solver add-in is not listed in the Add-Ins scroll box (as in the example above), click the Browse (or Select) button, navigate to the MCSimSolver.xla file on the CD ROM or network drive, select it, and click OK.

Click OK if you are asked to write the MCSim.xla file to the Addins (or Library) folder. The Add-In Manager dialog box will now list the Monte Carlo Simulation add-in.


The Add-In Manager lists all of the installed add-ins and those with checkmarks are also loaded. Microsoft offers the following advice, “To conserve memory, unload add-ins you do not use often. Unloading an add-in removes its features and commands from Microsoft Excel, but the add-in program remains on your computer so you can easily load it again.” [Microsoft Excel Help, add-ins, overview]
Make sure to select the check box next to the Monte Carlo Simulation add-in and click OK.

Excel will load the MCSim.xla file and notify you of successful installation with the following message:

Excel 2007 and greater
To access the Add-Ins manager dialog box in Excel 2007, follow these steps:

Once you have the Add-Ins manager dialog box, follow the instructions in step 2 above.

USING THE MCSIMSOLVER.XLA ADD-IN:

The Monte Carlo Simulation with Solver add-in uses a special, non-volatile cell formula, RANDOMNV(), to draw random numbers. After loading the MCSimSolver.xla add-in, you can enter RANDOMNV() as part of a cell formula. Use RANDOMNV() instead of RAND() or RANDOM() as you implement the optimization problem on a worksheet.
The functions RAND() and RANDOM() are volatile because any time any data changes on the sheet, cells that contain these functions are also recalculated. Cell A1 has formula “=RAND()” so when the sheet is recalculated (by hitting the F9 key), the value of the cell (the number displayed) will change.


After hitting F9

The volatility of RAND() and RANDOM() works in our favor when doing conventional Monte Carlo simulation (with MCSim.xla) because we can easily recalculate the sheet, then track the results. However, a Monte Carlo based on running Solver each repetition (e.g., to find a NLLS fit) cannot be implemented with volatile random number formulas because each time Solver puts down a trial solution, the sheet recalculates and gets a new random number.
Consider, for example, the following optimization problem:

In other words, we want to maximize profits, but price, P, is a uniformly distributed random variable from zero to one. We want to know the distribution of q*, the optimal q. Since q*=P/2, each newly drawn P gives a new q*.

In cell A1, enter the formula for the profit equation, “=RAND()*A2-A2^2”, where we use Excel’s own RAND() function for the uniformly distributed price variable. Enter a “1” in cell A2. Hit F9 a few times to confirm that cell A1 bounces.
Next access Solver (Tools: Solver) to get the Solver dialog box.


Note that the Target Cell is A1 and the Changing Cells is A2.
Click Solve and watch cell A1 closely.

Solver failed because it works by placing trial solutions on the sheet. But each time it places a trial solution down, the sheet recalculates and we get a new P variable. Solver is hopelessly confused.

The workaround here is to use a non-volatile random number function, RANDOMNV() included in the MCSimSolver.xla add-in. You can easily test this function by entering it in a cell. To do this, in cell B1, enter the formula “=RANDOMNV()”. Hit F9. Cell A1 bounces, but B1 does not. This is because A1 has RAND(), a volatile function that recalculates whenever any cell changes, but B1 has RANDOMNV(), a non-volatile function (based on RANDOM()). Non-volatile functions change only if the data in that particular cell changes.
To recalculate non-volatile functions, you have to force a recalculation of the entire workbook. This is done via the keyboard shortcut CTRL-ALT-F9. Try it now.
As you can see, F9 recalculates only A1 while holding down the CTRL and ALT keys while hitting F9 recalculates both A1 and B1.
F9 recalculates volatile functions such as RAND() and RANDOM().¹

CTRL-ALT-F9 recalculates all cells.

The non-volatile RANDOMNV() function is the secret to using Excel’s Solver for an optimization problem with a stochastic objective function. Since the randomly drawn values don’t change unless there is a forced recalculation of the entire workbook, Solver can put down trial values and find the solution for a given random draw. To see this, enter the formula “=B1*C2-C2^2” in cell C1, then enter a 1 in cell C2. Since cell B1 has RANDOMNV(), the optimal solution is simply B1.Value/2. Execute Tools: Solver and make the Target Cell C1 and the Changing Cells C2. Click Solve. Solver finds an optimal q in cell C2 that is half of the value of cell B1.
From the active sheet, the Monte Carlo Simulation with Solver add-in will do the following:

1. 
   Replace all volatile RAND() or RANDOM() calls with the non-volatile RANDOMNV() function. If a change to the user’s data will be made, the user is warned before proceeding.
   
2. 
   Force an entire recalculation via the CTRL-ALT-F9 method.
   
3. 
   Run Solver, using the settings from the Solver dialog box.
   
4. 
   Repeat from steps 2 and 3 for as many repetitions as requested.
   
5. 
   Output the results of the tracked cells.
   

To run a Monte Carlo simulation, use RANDOMNV() as you set up the problem, execute Tools: Solver and configure the Solver dialog box to find an optimal solution, then click on the Tools menu item and select the MCSimSolver item to access a dialog box that controls the simulation.

The active cell (the last cell clicked by the user) appears by default in the Select a cell box. If this is not the cell you want to track, simply click in the Select a cell box and click on the desired cell.
To run a Monte Carlo simulation of two cells, click in the Select a second cell box and then click on a cell in the worksheet.

Click here to make the dialog box collapse so you can see the sheet.

Click Proceed to run the simulation.
If your sheet has cells that use either RAND() or RANDOM() in formulas, the following critical warning is displayed:

As the warning states, the add-in will destroy data on the sheet, inserting the non-volatile RANDOMNV() function in place of the RAND() (or RANDOM()) functions. Running Solver with volatile functions makes no sense. Click No to leave your data untouched.
Use the Progress Bar to gauge how long it will take to finish the simulation. You may use other programs while the simulation is running, but that may slow Excel down. You can always hit the Escape (ESC) key (on the top left corner of most keyboards) to kill the simulation. Click End when prompted. The add-in runs faster if no other Excel workbooks are open.
Excel performs a full recalculation of your worksheet (the equivalent of CTRL-ALT-F9), runs Solver, tracks the results for as many repetitions as indicated and stores the value of the cell (or both cells) after each calculation. The results are then presented in a new worksheet in your workbook.

Histogram bins include the left endpoint, but not the right.

The new spreadsheet in your workbook is alive—you can change the scale, title, and legends on the graphs, change labels and colors on the cells in the spreadsheets, and add descriptive information as needed. The data underlying the graph are available by scrolling right.

You can run as many Monte Carlo simulations as you want by simply returning to your original worksheet and executing Tools: MCSimSolver. Delete unwanted results by simply deleting the sheet.
The Record All Selected Cells Option
You might want to track more than two cells or see all of the simulation results (instead of just the first 100). The Monte Carlo Simulation with Solver add-in allows you to track up to 256 variables (including one or two you selected for histogram display) and see results for up to 65,000 repetitions.
To take advantage of this, you must first select the cells you want to record (using the CTRL key, as usual, to select non-contiguous cells), then execute Tools: MCSimSolver and, finally, check the Record All Selected Cells option. By checking the Record All Selected Cells option, the add-in will track all cells that were selected before you executed Tools: MCSimSolver and brought up the Monte Carlo Simulation dialog box. The add-in inserts a new worksheet in your workbook and shows ALL of the values generated by the Monte Carlo simulation. You can use this information to sort the results in order to find percentiles (e.g., to approximate the chances of values falling in a particular interval) and to track more than just one or two cells.
The results generated by the Record All Selected Cells option are in “raw” form. You will need to compute averages, SDs, and draw histograms on your own. You can see the full set of simulation results for any cell (including one that was chosen as a tracked cell) by simply selecting that cell before executing Tools: MCSimSolver.
To practice using this option, select cells B1, C1, and C2, by clicking on cell A1, then holding down the CTRL key while clicking on cell C1, then clicking on cell C2. Next, execute Tools: MCSimSolver. Make one of the tracking cells B1 and the other C1. Make sure to check the Record All Selected Cells option. Click Proceed.
The Monte Carlo simulation add-in inserts two sheets in your workbook. One reports summary statistics and draws a superimposed histogram of cells C1 and C2, while the other simply lists all of the simulation results for all three variables. You can confirm that the average, SD, max, and min are the same for cells C1 and C2 by computing these statistics in your MCRaw sheet. You can use the Histogram add-in to generate summary statistics and a histogram of your MCRaw results.

The Output to Existing Sheet Option

If you are doing repeated simulations and have set up calculations based on the simulation results, you may want to output the results to an existing MCSim sheet rather than creating a new sheet each time a Monte Carlo is run. Check the Output to Existing MCSim Sheet option to do this. Instead of creating a new MCSim sheet, output is placed in the usual way (i.e., first 100 in column B, summary stats in range J5:J8 (and L5:L8, if two cells are tracked), and a histogram) in a previously created MCSim sheet.
Be careful in using this option because existing simulation results are overwritten and cannot be recovered. You can copy the existing MCSim sheet (Edit: Move or Copy Sheet…) if you want to preserve results from a previous simulation.
ERRORS AND TROUBLESHOOTING
The MCSimSolver.xla add-in has few rudimentary error checks. Unlike the MCSim.xla add-in, it does not test to see if cells change on recalculation.
Since the add-in relies on Solver, this must be properly configured for the add-in to work. If Solver bombs, giving miserable or disastrous results, the add-in’s output is totally compromised. Solver is not perfect and should be used with caution.

THIS VERSION

The latest MCSimSolver.xla version is 12 July 2012
To check the date of your installed add-in, execute Tools: Add-ins and then highlight the add-in. The Add-Ins dialog box displays the date at the bottom.

To install this for the first time, please follow the instructions on the first page of this document.
To install over a previous version that is already installed, please see InstallingAddinOverPreviousVersion.doc

ADDITIONAL HELP AND FEEDBACK:

If something goes wrong in the installation or loading process, an unexpected error keeps recurring, or you have other problems, please contact us. We are interested in your comments, suggestions, or criticisms of the MCSimSolver.xla software.
www.wabash.edu/econometrics

Humberto Barreto Wabash College [e-mail] [phone]

Frank Howland Wabash College [e-mail] [phone]

1 Other volatile functions in Excel are

AREAS()
INDEX()

OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
RAND()

Page of