Hi

Yet Another Simulation Add-In

Version 1.15 User Guide

(Click here for older, Version 1.0 User Guide)

Jonathan Eckstein and Steven Riedmueller,
Revision of March 14, 2002


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

Option 1: Using the Installer
A new, experimental installer is available as of March 2005.  It is still under development.  It is safe, but does not always succeed.   If it fails, you can try the manual procedure below.
  1. Start Excel, and make sure that the security level is set to "medium" or "low" ("medium" is recommended).  The security level can be found under "Tools->Macro->Security...".
  2. Exit Excel.
  3. Download the file "Yasai-1.15-install.exe" from the YASAI website and double click it to execute.
  4. Click "Next", "Start", "Enable Macros", "Enable Macros", "Next", and "Exit".
At this point, the choice "YASAI Simulation..." should appear under the "Tools" menu in Excel.  If it does not, try the following:
  1. Select "Add-ins..." under the "Tools" menu.
  2. Click "Browse."
  3. Browse to the directory C:\Program Files\Microsoft Office\Office\Library
  4. If YASAI.xla is visible, double-click it.
Again, at this point, "YASAI Simulation..." should appear under the "Tools" menu in Excel.  If it does not, try the manual installation procedure below:

Option 2: Manual Installation
  1. Check for and disable any old version of YASAI: Launch Excel.  Select "Add-Ins..." from the Tools menu. Scroll to the bottom, and if "YASAI" appears, uncheck it. Then click the "OK" button. Exit Excel.  If YASAI was not present, skip to step 3 (Download). 
  2. Remove older versions of YASAI from your hard drive:  Using Find->Files or Folders from the Start menu, locate all files named "YASAI.XLA" on your hard drive(s) and delete them.  
  3. Download:  Download the file YASAI.XLA from the YASAI website. If the download placed the file in a temporary ("Temp") directory, move it to another, permanent directory. If your browser renamed the file to "YASAI.XLS", it is preferable to change it back to "YASAI.XLA".
  4. Configure Excel security: Start Excel, and select "Macros->Security..." from the "Tools" menu. Select "Medium" security, then switch to the "Trusted Sources" pane and make sure "Trust all installed add-ins and templates" is checked. Click OK.
  5. Install the YASAI add-in: Select "Add-Ins..." from the Tools menu. Click "Browse...", locate YASAI.XLA, and click "Open". If Excel asks if it can copy the file, click "Yes". If Excel asks if it can overwrite another file of the same name, click "Yes". (The exact questions Excel asks depend various factors, including which version of Excel you have.)


YASAI Functions for Generating Random Variables

These functions 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.

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.


Specifying Scenarios

To specify different trial values for decision variables, YASAI provides the function PARAMETER(L, k, name). 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. The k argument specifies the number of scenarios between changes in the parameter. For example, a cell containing =PARAMETER({1,2,3},1, "woof") specifies a parameter called "woof" that will change every scenario, first taking the value 1, then 2, and then 3. A cell containing =PARAMETER({1,8,2.3,-2},2, "snort") defines a parameter called "snort" that takes the value 1 for the first two scenarios, then 8 for the next two, 2.3 for the next two, and -2 for the last two. The argument k makes it possible to try combinations of values of various parameters without having to write every combination out in a list.


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.


Known Problems

Updating Links
When you open a workbook containing YASAI functions, Excel may display the message that it contains "automatic links to another workbook", and asks if you want to update the links. As of version 1.15, it should not matter what you answer here -- YASAI should automatically repair the links.  Link updating generally occurs when you move a YASAI-based spreadsheet between two computers where YASAI is installed in different places in the file system.

If link updating fails, your spreadsheet formulas may contain weird strings like "!'C:Documents and Settings\JoeUser\...\YASAI.xla':".  If you delete these strings manually or with Excel's "Replace" function, things shoudl start working normally again.

"YASAI Simulation..." appears on the Tools menu, 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 immediately see a dialog box asking if you wish to enable macros. Click "Yes" or "Enable Macros". The YASAI dialog box should appear. Click "Cancel". For each cell containing a YASAI spreadsheet function, click the cell, place the cursor at the end of the formula in the formula bar, click, and then hit the enter key.


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.