® 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 2.0 is quite similar to XLSim. The primary enhancements are listed below.
  1. XLSim 2.0 remembers the previous simulation, so you can easily repeat simulations.
  2. Simulation settings can be saved to and restored from cells on a worksheet.
  3. The Data Series command now works on rectangular arrays.

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 Ad-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 S imulate 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 S imulate 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 F reeze 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.

Saving and Loading Simulation Settings in XLSim

The Save buttons on the Simulation, Parameterized Simulation and Data Range dialog boxes will save the current settings of each simulation type. These saved settings are referred to as models. The Load buttons will ask you for the cell containing a saved model and then load the model into the appropriate dialog box.

Model Formats

The models are saved as array formulas containing the text from each text box on the simulation dialog box.

For examples of saved settings and model formats look at the file XLSim_VBA_Programming_Options.xls.

Running Simulations from a Macro

To run a model stored in a cell from a macro you create, follow these steps.
  1. Define a reference to XLSim from the workbook containing your macro.
    • Go to Tools-References in the VBA Editor when your project is active.
    • Check off the box labeled XLSim

  2. The subroutine RunModel can then be used to run a model stored in a cell.
    • The subroutine takes a range object referring to the model cell as its argument.
    • For example, to run a model in cell B1 of the active sheet the code would be
      RunModel Range("B1")

  3. Once a simulation has been run, you can also create graphs programmatically.
    • To create a histogram of a series, the syntax is:
      MakeHistogram SeriesNumber, NumBins, NumDec
    • To create a histogram of series which only takes on integer values:
      MakeIntHistogram SeriesNumber
    • To create a cumulative graph:
      MakeCumulative SeriesNumber

  4. You can also create graphs for a parameterized simulation programmatically.
    • To create an interactive histogram:
      FillCommonData OutputSeries, NumBins As Long, NumDec
      MakeInteractiveHistogram OutputSeries, NumBins, NumDec, IntegerHistogram (=True/False)

      NOTE:
      In this case the OutputSeries argument is an array of boolean values, telling whether or not to graph the corresponding output.

      For instance:

      FillCommonData Array(true, false, true), 100, 0
      MakeInteractiveHistogram Array(true, false, true), 100, 0, false

      This would graph the first and third simulated outputs.

      NOTE:
      It is necessary to precede the call to MakeInteractiveHistogram with the call to FillCommonData. If you do not call FillCommonData first, you will get a blank graph.


    • To create a common 3D bar:
      FillCommonData OutputSeries, NumBins, NumDec
      Make3DBarCommon OutputSeries, NumBins, NumDec, IntegerHistogram (=True/False)

      NOTE:
      The comments for the interactive histogram also apply to this graph.


    • To create a common 2D line graph:
      FillCommonData OutputSeries, NumBins As Long, NumDec
      Make2DLineCommon OutputSeries, NumBins, NumDec, Smoothing (=True/False)

      NOTE:
      The comments for the interactive histogram also apply to this graph.

  5. It is also possible to freeze and thaw a workbook programmatically using the commands
    Freeze
    Thaw
Example

The following subroutine would thaw the active workbook, run the simulation model stored in cell A1, then draw the histogram of the first output series with 10 bins and 2 decimal places.

Sub RunModelExample()
Thaw
RunModel Range("A1")
MakeHistogram 1, 10, 2
End Sub

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