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
Common Histogram Types
Common Cumulative Graph
This option produces histograms
on separate sheets for each of the selected outputs. The histograms all
have the same axes so that a meaningful comparison can be made between
them. The sheets are called Output Name I, and can be paged through
using Ctrl-PgUp and Ctrl-PgDn.
This option draws a 3D histogram
showing all of the outputs on one set of axes.
This option also draws all the outputs
on one axis, but represents them as 2D lines.
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 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
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.
Freeze and Thaw
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
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.
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
The primary areas
of increased performance available through upgrading to more powerful software
Monte Carlo Simulation
Discrete Event Simulation
Larger number of idealized
distributions. XLSim is limited to those shown under the function wizard
Latin hypercube sampling.
This sophisticated method of generating random numbers can speed up the
rate of convergence of a simulation.
Correlations may be
directly specified between random inputs for distributions other than Multivariate
Sensitivity of specified
output and input cells is provided in terms of R2 values.
for modeling your project
Time dimension through
which simulation evolves