Hi

Yet Another Simulation Add-In

Version 2.6 User Guide

(Click here for older, Version 2.1 User Guide)

Jonathan Eckstein
May 2016 

About Yasai

The YASAI.XLA add-in is intended is intended for teaching elementary Monte Carlo simulation. It does not provide the full functionality of @Risk, Crystal Ball, and other commercial products, but should be sufficient for elementary instruction. A key advantage is that YASAI.XLA consists of a single downloadable file that can be run on any PC with a recent version of Excel, without requiring administrator privileges or a special installation procedure. It is also designed to be very straightforward to use. In exchange for this simplicity, YASAI may run simulations slower than commercial products, since all the random number generation code is interpreted in Visual Basic.

Installing Yasai

We encountered problems with the automated installation procedure provided with some earlier versions; it is no longer supported.  Manual installation is not difficult.

Before starting the installation process, download the YASAI.XLA file from the YASAI website.  Do not open the file directly; simply save it somewhere convenient on the system on which you wish to run YASAI.

Manual Installation for Excel 2013
First, you must download the file YASAI.XLA from the YASAI download page, and if necessary move it to the system on which you wish to install YASAI.  
  1. Some browsers may rename the YASAI.XLA file to YASAI.XLS.  If this occurs, manually change the name back to YASAI.XLA before proceeding with the remainder of these instructions.
  2. Move the YASAI.XLA file to some location where you will not inadvertently delete it.
  3. Launch Excel. 
  4. Click the "FILE" tab above the ribbon.
  5. Click the "Options" button at the lower left of the window.
  6. Click "Add-Ins" on the left of the resulting Options dialog box.
  7. Select "Excel Add-Ins" in the pull-down menu at the bottom of the dialog box, and then push the adjecent "Go..." button.
  8. You will now be at the "Add-Ins" dialog box.  If YASAI already appears on the resulting list, uncheck it (it should only appear if you installed an earlier version of YASAI)
  9. Click the "Browse" in the resulting dialog box, locate the YASAI.XLA file you saved), select it, and click "OK".
YASAI should now be loaded.  A new "Add-Ins" pane should appear in the ribbon interface.  If you click it, the buttons "YASAI" should appear.  If you click it, two options should appear, "Simulation..." and "Charts...".  These choices should bring up the simulation and charting dialog boxes, respectively.

If Excel shows YASAI as installed but the random generation functions do not seem to be recognized, visit the "trust center" (also under the Options dialog box, followed by the "Trust Center Settings..." button) to make sure that Excel's security settings are allowing YASAI to run.

Installation procedures for other versions of Excel are similar.  Generally speaking, you need to locate the "Add-Ins" dialog box, click "Browse", and then select the YASAI.XLA file that you downloaded.

YASAI Functions for Generating Random Variables

YASAI provides Excel functions that return random numbers with specified distributions. They will generally return a different value each time they are called, depending on their arguments. Here are the functions that are currently implemented:

GENUNIFORM (a, b): Both arguments are numbers. Normally, it is expected that a < b. If so, a random number uniformly distributed over the interval [a, b)  -- that is, x such that a < x < b -- is returned. If a = b, then the value a (or equivalently b)is returned. If a > b, an error value is returned.

GENNORMAL (m, s): Both arguments are numbers. If s < 0, an error value is returned. If s is zero, the return value is m. Otherwise, a random value with a normal distribution with mean m and standard deviation s is returned.

GENBINOMIAL (n, p): The first argument n must be a nonnegative integer, and the second argument p must be a number in the range [0, 1]. Otherwise, an error value is returned. If these conditions are met, then the return value is an integer drawn randomly from a binomial distribution with n trials and probability p of success at each trial. Note that if n = 0, then the return value is 0. The implementation is efficient even when n is large.

GENPOISSON (m): The argument m is a nonnegative number. A negative argument causes an error value to be returned. A zero argument causes zero to be returned. Otherwise, the return value is randomly chosen from a Poisson distribution with mean value m. The implementation is efficient even when m is large.

GENTABLE (V, P): The argument V and P are blocks of cells or lists (for example, "{1,3,7}") having the same number of cells.  Essentially, the function returns each value in V with the probability specified by the corresponding element in P.  If the two arguments have the same number of cells but differing numbers of rows and columns, the correspondence is determined by scanning first across the first row, then across the second row, and so forth.  Non-numeric entries in P are treated as if they were zero.  If the two arguments do not have the same number of cells, or if P contains any negative numbers, or if P contains only zeroes, an error value is returned.  If the values in P do not sum to 1, they are rescaled proportionally so that they do.  For example, GENTABLE({1,2,3},{.2,.5,.3}) returns 1 with probability 0.2, 2 with probability 0.5, and 3 with probability 0.3.

GENEXPON (a): The argument must be a positive number, or an error value is returned. If so, the return value is randomly chosen from an exponential distribution with mean value 1/a.

GENGEOMETRIC (p):  Returns a geometric random variables with a probability p of being 1.  This variable is equal to the number of trials of a mean p Bernoulli (or equivalently, GENBINOMIAL(1,p)) variable until the value 1 is obtained.  The value of p must be greater than 0, and less than or equal to 1, or an error value is returned.

GENTRIANGULAR (a, b, c):  Returns a value from a triangular distribution with minimum a, mode b, and maximum c.  The arguments must be numbers with the property a < b < c, or an error value is returned.

GENLOGNORMAL (m, s): Generates a lognormal random variable, and is equivalent to exp(GenNormal(m, s)).  The restrictions on the arguments are the same as for GENNORMAL.  

Specifying Scenarios

In YASAI, decision variables are called parameters.  For each possible value combination for the parameters, YASAI obtains a sample, recording the values of all the output variables.   YASAI provides the function SIMPARAMETER(L, name, group) to specify each parameter:
  • The first argument, L, is a block of cells or a list specifying the possible return values.
  • The name argument is a character string describing the parameter, and is used only in the output reports.  If it is omitted, its value is taken from the cell containing the formula -- for example, if the SIMPARAMETER function is in cell C12, the default name of the parameter is "C12"
  • The group argument is optional and defaults to 1 if omitted.  It may be any whole number between 1 and 20 (values outside this range produce an error value).  For simplicity, all parameters within the same group should have the same number of values in L. 

Parameters in the same group vary in "lock step" with one another, whereas YASAI tries all possible combinations of values between different groups.  For example, suppose that a model has the following SIMPARAMETER functions"

  • SIMPARAMETER({1, 2, 3}, "Fred", 1)
  • SIMPARAMETER({100, 150, 200}, "George", 1)
  • SIMPARAMETER({1000, 2000}, "Amy", 2)

Then YASAI will test the following parameter combinations:

  • Fred=1   George=100   Amy=1000
  • Fred=2   George=150   Amy=1000
  • Fred=3   George=200   Amy=1000
  • Fred=1   George=100   Amy=2000
  • Fred=2   George=150   Amy=2000
  • Fred=3   George=200   Amy=2000

Older versions of YASAI provided a different function, called PARAMETER, for specifying parameters.  This function was similar to SIMPARAMETER, but it made testing combinations of parameter values from multiple lists more complicated.  You may find a description of the PARAMETER function in earlier versions of the user guide.

We recommend that you not mix SIMPARAMETER and PARAMETER specifications in the same model.  If you do, however, all parameters specified with PARAMETER are treated as being in group 1.

 

Specifying Output

To specify an output of the simulation, use the formula SIMOUTPUT(x, name): This function returns the value x. During simulation runs, the values of x encountered are saved for later analysis, as described below. The argument name is a character string to describe the output in the simulation reports. For example a cell containing =SIMOUTPUT(A4+B7,"profit") defines an output called "profit" whose value is A4+B7.

Running the Simulation

Once you have built your model, specified scenarios (if any), and specified outputs, you can run your simulation. To do so, select "YASAI Simulation" from the Tools menu. This will cause a dialog box to appear.

YASAI analyzes your spreadsheet to determine how many scenarios appear to be needed.  It places this number in the default number of scenarios box.  If this number is satisfactory, click on the "Default" button.  If you would like a different number of scenarios, click on the "set to" button and enter the number of scenarios you want.

The "Sample Size" box is the number of times YASAI will recalculate your model for each scenario.  It defaults to 1000, but you can enter any positive whole number.

Optionally, you may indicate a fixed random number seed to use, and whether the seed should be reset for each scenario (resetting is good practice, and is the default). Press "Simulate" to start the simulation, or "Cancel" to return to Excel.

The simulation involves only the current sheet of the current workbook. If the number of scenarios" is N, and the "Sample Size" is S, then YASAI will recalculate the current sheet NS times. These recalculations are divided into N blocks of S recalculations, each block constituting a scenario. Output data are collected separately for each scenario, and the values returned by any PARAMETER functions in the sheet will vary from scenario to scenario, as described above.

A "progress" display indicates how quickly the simulation is progressing.  When the simulation is over, there will be a short delay while the outputs are processed. The output report is automatically placed in a new sheet named "Simulation Output n", which YASAI inserts into the current workbook. YASAI makes this report the current sheet and then returns control to Excel. For each output-scenario combination, the report contains the mean, standard deviation, minimum, maximum, and percentiles in 5% intervals. Currently there are no graphics or other output data, although improvements are planned for later versions.

Aborting a Simulation

You can abort a YASAI simulation while it is running by clicking on the "Abort" button, or simply by pressing the escape key on your keyboard. It may take up to 5 seconds for the simulation to abort.

Testing a Model Interactively

To test a model interactively, simply press the F9 key. Excel will perform a single recalculation, drawing new values for all the random variable generation functions. New values will also be generated for each PARAMETER function call.

Charting Simulation Output

As of version 2.0, YASAI can produce charts of simulation outputs.  You must run simulation before trying to produce charts.  To make a chart, select "YASAI Charts..." from the "Tools..." menu, which produces a charting window:

You may chart up to five blocks of outputs, each corresponding to one row in the window.  The first column selects which variables to chart.  The second and third columns allow you to specify a range of scenarios for the selected variable, for example scenarios 1 through 5; a graph for each scenario in the range will appear in the output.  The last column selects the kind of graph desired for the block of variables: "Histogram" is a standard bar chart, and "Cumulative plot" produces empirical cumulative distributions.  You may mix the two kinds of graphs on a single chart.  With the "automatic" button set under "Chart Range" YASAI chooses the horizontal axis range and subdivisions to attempt to produce an attractive chart.  "Manual" lets you specify a range from "Min" to "Max", with "Buckets" subdivisions.

YASAI charts are regular Excel charts.  Once they have been created, you modify them to suit your needs.  The chart and its associate data each become a new worksheet ply.  You may delete them if they are no longer needed.  YASAI charts are also "static" -- they are based on the simulation immediately preceding their creation; if you run another simulation of the same model, they will not automatically update.  You must run a simulation again and make new charts if you want your charts to reflect a change to your simulation model.

Due to internal limitations in Excel, cumulative graphs are not possible for outputs with more than 32,760 observations. Histograms are possible for any sample size.

Known Problems

Updating Links
Excel uses a system of "links" to match user-defined functions (like those defined by YASAI) to their source files.  These links contain absolute filename paths, which often creates problems when moving files from one computer to another.  Generally speaking, you should just press the "continue" button if Excel asks you if you want to "edit links".  Link-related problems have become less severe in recent years, but are still an occasional irritant.  If link updating fails, your spreadsheet formulas may contain strange-looking strings like "!'C:Users\JoeUser\...\YASAI.xla':".  If you delete these strings manually or with Excel's "Replace" function, YASAI should start working normally again.

YASAI appears in the menu bar or ribbon, but the all YASAI spreadsheet functions evaluate to "#NAME!"
Excel's security settings may be preventing YASAI from running. Select "YASAI Simulation..." from the Tools menu.  You should modify Excel's security settings to let YASAI run. You may see an "Enable content" button between the worksheet grid and the ribbon; clicking it should enable YASAI to run.

Mac Excel 2008
Mac Excel 2008 does not support Visual Basic, so it is impossible to use YASAI with it.

Mac Excel 2016 Performance
Mac Excel 2016 has a "ribbon" closely resembling recent Windows versions of Excel, but seems to have been released prematurely.  Its Visual Basic Editor is rudimentary and the run-time behavior of its user interface does not match either Mac Excel 2011 or any version of Windows Excel.  We have managed to make YASAI compatible with Mac Excel 2016, but simulations appear to run very slowly.  There may also be some residual reliability issues with Mac Excel 2016, but the main issue we have observed is simulation performance.  We welcome any suggestions that would result in improved performance.  The best Mac version of Excel for YASAI remains 2011.  YASAI appear to run well with Windows Excel 2016.

Charts in Mac Excel
Due to an apparent bug in Mac Excel 2011 charting, histogram charts are not available on Mac platforms.  Cumulative charts are available on all platforms.  In future, we hope to investigate whether the charting bug persists in Mac Excel 2016 and later. 

Disclaimer: Since this Software is provided free of charge, the Software is provided on an "AS IS" basis, without warranty of any kind. The authors assume no responsibility whatsoever for its use by other parties, and make no guarantees, expressed or implied, about its quality, reliability, or any other characteristic.