TM Software Reference

http://www.analycorp.com

XLSim is an Excel add-in that performs Monte Carlo simulation. It requires Excel 97 or higher.

Installing XLSim
Installation is accomplished by running Setup.exe. All examples and documentation are stored in the C:\XLSimFiles directory. NOTE: When too many add-ins are attached to Excel, they tend to interfere with each other, sometimes freezing Excel. We recommend that you minimize the number of add-ins used in conjunction with XLSim. Use the Tools Add-ins menu from Excel, and remove the check marks from those add-ins not needed. In particular, if you have previously used SIM.xla, eSIM or SIM25k, make sure to unload them before using XLSim. Then exit and restart Excel.

Running XLSim
Run Excel and open XLSim.xla from the File menu.

Auto Load Option
If you want XLSim to load every time you launch Excel

  1. Select Add-ins from the Tools menu in Excel.
  2. Select XLSim from list of add-ins and click "O.K."
  3. You can later go back and deselect XLSim from the Add-in menu to prevent Excel from loading it automatically.
What's New
XLSim.xla is quite similar to and upward compatible from SIM.xla. The primary enhancements are listed below.
  1. 1 Million-iteration limit.
  2. Over 100 Output Cells
  3. Additional Distributions. All generators now have minimal documentation within function wizard. Open gen_Functions.xls and use the Thaw command to view demonstrations of all random number generators. Gen_Resample may now be used as an array formula, removing the need for ResampleSync, which has been dropped. gen_Shuffle is an array formula that permutes an existing range of data into a new range. Gen_MVNormal is an array formula for generating multi-variate normals. Gen_Myerson is a generalization of the Lognormal distribution developed by Professor Roger B. Myerson of Northwestern University
  4. Freeze and Thaw commmands allow for easy shipment of models. Before sending a model to someone else, use the freeze command to store all gen functions in comments. To use a frozen model, simply Thaw it then save on your hard drive. NOTE: TO USE YOUR EXISTING SIM.xla models, do a Freeze followed by a Thaw.
  5. All graphs are now accessed from a menu, rather than buttons on the SimStat sheet.
  6. A new graph type available for common graphs and parameterized simulations allows for scrolling through distributions to view an animation.
  7. Data Range command analyzes spreadsheet data. Includes Histograms with variable numbers of bins.
Menu and Dialog Boxes
 
Once XLSim is loaded the Simulate menu will appear. Random input cells are specified with the Excel Function Wizard. 
  • Simulation initiates a simulation run of the specified output cells. Results are posted in a new workbook entitled SimStats.xls. 
  • Parameterized Sim repeatedly runs the same simulation of a single output cell with different parameter values. 
  • Data Range uses data from a worksheet instead of randomly generated numbers. Great for making Histograms of data. 
  • Graphs draws histograms and cumulative graphs of the simulation data. 
  • Common Graphs draws histograms and cumulative graphs of multiple series on the same axis. Be sure to try the interactive Histogram. 
  • Freeze is used before saving a spreadsheet model so that other XLSim users can read it on a different computer. 
  • Thaw is used to make a model live again after it has been frozen. Use Freeze and Thaw to upgrade your models from SIM and SIM25k. 
  • View Trials displays the actual simulation data and convergence-to-mean graphs. 
  • Remove XLSim closes XLSim. Optionally removes XLSim from Tools Add-Ins start up list.
 
 

 

Simulation   

The dialog box below will appear when you select Simulation.

Parameterized Sim   

The dialog box below will appear when you select Parameterized Sim.

Data Range   

This command is used when you want to read in data from a worksheet and analyze it with XLSim. It provides a useful Histogram in which you can adjust the number of bins (unlike Excel's histogram in the Analysis Tools). The following dialog box will appear when you select Data Range from the Simulate menu.

Simulation Results   

XLSim can produce the following outputs: statistics, histograms, cumulative graphs and series graphs. All outputs except for statistics are Excel graphs and can be edited with standard Excel commands.

Statistics
Upon completion of the simulation, statistics are displayed for each output cell.
 
In the case of Simulation, statistics appear as shown below. In the case of Parameterized Simulation, statistics will be displayed for each parameter value as shown below. In this case the parameter is Demand and the output is Revenue.

Histograms and Cumulative Graphs   

Histograms are available after a simulation and are created by selecting Graphs from the Simulate menu. The following dialog box will appear.

Once you have selected which outputs to graph, click on the Graphs tab at the top of the dialog box.

A separate histogram and cumulative graph are created for each of the selected outputs. The histogram appears on a sheet called Output Name H and the cumulative graph on a sheet called Output Name C.
 
Histogram with 5 bins and 2 decimal places
Cumulative frequency graph

Common Graphs   

Sometimes it is useful to compare the different outputs of a simulation side-by-side on the same set of axes. This is especially useful when you have run a parameterized simulation and would like to see the output for each of the parameters. Say we have run a parameterized simulation with parameter values 1, 2, 3 and 4. To compare the outputs, use the Common Graphs command on the Simulate menu.. The following dialog box will appear.

Once you have selected which outputs to include, click on the Graph tab at the top of the dialog box.

Common Histogram Types

Common Cumulative Graph
The Cumulative button on the Common Graph dialog box draws the cumulative frequency graphs of the selected series all on the same axis.

Series Graphs

Series graphs plot the confidence interval of the selected outputs on a common axis. They are accessed using the Series Graph tab on the Common Graph Dialog box.

A Series Graph is useful for assessing the risk associated with one parameter against versus another. 

Freeze and Thaw   

Use the Freeze command on the Simulation menu before you send an XLSim model to another user of XLSim. Freezing a workbook means the random numbers are replaced by their current value, and the formulas are stored in the cell comments. Once a workbook is frozen it can be given to another user of XLSim who can use the Thaw command to restore the random number formulas and recreate the model. To update a model written with SIM.xla or SIM25k.xla, first Freeze it, then Thaw it.

View Trials   

You can view the results of the simulation trials by selecting View Trials from the Simulation menu. The following dialog box will appear.

The trial data appears on a new sheet, along with the convergence data if the Draw convergence graph box is checked.

The convergence graphs appear on sheets named Output Name Conv.

Random Number Generators   

Make sure that XLSim is open before entering random number generating functions or opening an Excel workbook, which uses these functions. Be sure to look at gen_Functions.xls to see applications of the various functions.

Using the Function Wizard
To place a random number generating function in the spreadsheet, place the cursor in the desired cell, then click on the function icon . You should see a screen from which you select Statistical from the left menu and the desired function name from the right menu.

Click on OK and you will be prompted for the distribution's parameters. If, for example, you selected gen_Normal, you will be prompted for the Normal distribution's mean and standard deviation as shown below.

Fill in the requested parameters and click OK. Each time you press the calculate key - the F9 key in Windows - you should see a new random number generated in the cell.
 

Software Limitations
XLSim has been designed to provide an introduction to Monte Carlo Simulation for spreadsheet users. Although intended to solve real problems, the emphasis has been on ease of use rather than on extensive features. Two good commercial spreadsheet add-ins, @RISK and Crystal Ball provide greater Monte Carlo performance in several areas, albeit at higher costs, and steeper learning curves. In addition there are several stand-alone products that do what is known as Discrete Event simulation. These can simulate complex systems that evolve through time, such as workflow on a factory floor, or a telephone call center with waiting lines. A nice product in this category is Extend from Imagine That Inc.

Advice on Upgrading
If you believe that you have a spreadsheet model to which Monte Carlo simulation may be applied, it is recommended that you prototype it using XLSim. You may find that XLSim provides valuable insights and that nothing more is needed. On the other hand the XLSim model may convince you that further analysis will have a great impact, in which case you should consider upgrading to more powerful software.

The primary areas of increased performance available through upgrading to more powerful software are:

Monte Carlo Simulation Packages

Discrete Event Simulation Packages