Version 3.0 User Guide
(Click here for older, Version 2.7 User Guide)
Jonathan Eckstein, with assistance from Sohum Sanghvi
June 2019 (Security Detail Updates October 2022)
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.
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.
Downloading and Installing Yasai
Downloading (All Browsers and Operating Systems)
- Download the YASAI.XLA file from
the "Downloads" page on the
YASAI website. Do not let your
browser open the file directly in Excel. Instead
simply save the file somewhere convenient (the
default folder is usually "Downloads")
- 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.
- If necessary, move the YASAI.XLA file to some location where
you will not inadvertently delete it.
Security Policy Adjustments
Excel has become increasingly security minded over the
years. Before attempting to install it, please make
the following adjustments. The following directions
are for Windows, but the procedure for Macs should be
similar.
- Launch Excel.
- Click the "FILE" tab above the ribbon.
- Click the "Options" button at the lower left of
the window.
- Click "Trust Center" on the left
of the resulting Options dialog box.
- Click the "Trust Center
Settings..." button.
- If this option is available,
select "Trusted Locations" on the left panel (if
this option is not available, skip to step 9)
- Click the "Add New
Location..." button.
- In the resulting dialog box,
enter or browse to the location of the folder
containing YASAI.XLA, and click "OK".
- Click "Macro Settings" on
the left panel.
- Check "Trust access to the
VBA project object model"
- Optionally, click the radio
button for "Enable VBA macros (not recommended...)
-- in principle, it should not be necessary, but
sometimes appears to be.
- Click "OK".
Installation for Windows Excel 2013/2016/365
- Launch Excel.
- Perform the security policy adjustments listed
above.
- Click the "FILE" tab above the ribbon.
- Click the "Options" button at the lower left of
the window.
- Click "Add-Ins" on the left
of the resulting Options dialog box.
- Select "Excel Add-Ins" in
the pull-down menu at the bottom of the dialog box,
and then push the adjacent "Go..." button.
- You will now be at the "Add-Ins" dialog box (the
add-in manager).
- Click the "Browse" in the resulting dialog box, locate the YASAI.XLA file you saved, select it, and click "OK".
- Select "OK" to exit the add-in manager.
YASAI should now be loaded. An "Add-Ins" pane should
appear in the ribbon interface. On this pane, there should be a button labeled
"YASAI". If you click it,several options should appear, starting with
"Simulation...". If the currently visible Excel object is a
chart rather than a worksheet, the first option in the YASAI
button should instead be "Charts..." and there will be only
two options.
Installation procedures for older
versions Windows Excel are generally similar. You find
the add-in manager and then browse to find YASAI.XLA.
Installation for Mac Excel 2011
- Launch Excel
- From the "Tools" menu in the menu bar, select
"Add-Ins"
- The add-in manager dialog box should appear.
Click "Select", locate the YASAI.XLA file you saved,
select it, and click "OK".
- Click "OK" to exit the
add-in manager.
YASAI should now be loaded. A new menu "YASAI"
should appear in the menu bar, with the options
"Simulation...", "Charts...", and so forth. If the
currently visible Excel object is a chart rather than a
worksheet, the first option in the YASAI button should
instead be "Charts..." and there will be only two
options.
Installation for Mac Excel 2016
- Launch Excel
- From the "Tools" menu in the menu bar, select
"Excel Add-Ins"
- The add-in manager dialog box should appear.
Click "Browse", locate the YASAI.XLA file you saved,
select it, and click "OK".
- Click "OK" to exit the
add-in manager.
YASAI should now be loaded. However, its
options may not appear until you open a new workbook.
The next time you open a workbook, you should see an
"Add-Ins" pane in its ribbon. Within this pane
should be five new popup buttons, the first of which
should be "YASAI_Simulation". If the currently
visible Excel object is a chart rather than a worksheet,
there should be only two new buttons, the first of which
should be "YASAI_Charts" and there will be only two
options.
Upgrading from Earlier versions of YASAI
If you are upgrading from an earlier version of
YASAI, just follow the same installation instructions above.
After selecting the YASAI.XLA file in the add-in manager and
clicking "OK", you may see a message that "a file with the
same already exists in the same location" and asking whether
you wish to replace it. Click "Yes".
YASAI Functions for Generating Random Variables
YASAI provides Excel functions that return random numbers with
specified distributions. They will generally return different, randomly chosen values
every time you recalculate a spreadsheet. The currently available random number generation functions are:
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. If
n = 0, then the return value is always 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 arguments
V and
P are
blocks of cells or lists (an example of a list is "{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 with the same group number vary in "lock
step" with one another: suppose the only two cells with
SIMPARAMETER functions contain
- SIMPARAMETER({10, 2, 6, 4}, "Bill", 1)
- SIMPARAMETER({1.2, 1.5, 1.8, 2.3}, "Nancy", 1)
Then YASAI will try the following four combinations of parameter values:
- Bill=10 Nancy=1.2
- Bill=2 Nancy=1.5
- Bill=6 Nancy=1.8
- Bill=4 Nancy=2.3
If two parameters have different group numbers, YASAI will
try all possible combinations of their values. For
example, suppose that the SIMPARAMETER calls are
- SIMPARAMETER({23.1, 24.2, 27.1}, "Bruce", 1)
- SIMPARAMETER({-10, 20}, "Adam", 2)
Then YASAI will try the six parameter combinations
- Bruce=23.1 Adam=-10
- Bruce=24.2 Adam=-10
- Bruce=27.1 Adam=-10
- Bruce=23.1 Adam=20
- Bruce=24.2 Adam=20
- Bruce=27.1 Adam=20
You can combine the two techniques: YASAI tries all
possible choices of the parameter selections that are in
different groups, but parameters within the same group
move in "lock step". For example, suppose that a model
has the following SIMPARAMETER functions:
- SIMPARAMETER({1, 2, 3}, "Fred", 1)
- SIMPARAMETER({100, 170, 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=170 Amy=1000
- Fred=3 George=200 Amy=1000
- Fred=1 George=100 Amy=2000
- Fred=2 George=170 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.
It is not recommended
that you 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.
If you omit the
name argument, YASAI uses the position of the current
cell (for example, "G12") as the output name. If you enable the "Simulate all
sheets" option (see the next section) and omit the
name argument, then
YASAI includes the sheet name in the output name (for example, "Sheet1!G12").
If you explicitly give two or more outputs the same name, then YASAI will
merge the sample data for those outputs into one larger sample.
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 the following 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, select 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.
The random number seed determines the sequence of
pseudorandom numbers that YASAI generates. Optionally,
you may indicate a fixed random number seed to use.
If you specify a random number seed, YASAI should
produce the same results every time you run a model,
so long as you do not make changes to its formulas.
If you leave the random number seed blank, YASAI will
construct a seed from the system clock, and your
simulation results will be different each time you run
a model. If you check the "Use same random number
seed for each scenario" option, YASAI resets the seed
to the same value at the beginning of each scenario
(this resetting is generally good practice because it
reduces the variability of results between different
scenarios, and is the default).
The "Simulate all sheets" option controls the extent
of recalculations that YASAI performs during the
simulation. If the option is unchecked (the default),
each sample point in the simulation will be obtained
by recalculating the currently active sheet of the
currently active workbook. If you check "Simulate all
sheets", then each sample point will be obtained
through a full recalculation of all sheets of all
currently open workbooks. Such recalculations could
be much slower than recalculating single sheets, so
you should only use this option if your model uses
multiple sheets. If you have multiple YASAI workbooks
open simultaneously and perform a simulation with
"Simulate all sheets" checked, all open workbooks will
effectively be merged into a single larger model, and
the simulation results will show combined results from
this merged model. Therefore, you should typically
have only one workbook open when selecting "Simulate
all sheets". A warning dialog box will appear if you
check "Simulate all sheets" when multiple workbooks
are open.
The "Dump internal simulation data" option (a new
feature as of version 3.0) which outputs the entire
sample data for every SIMOUTPU variable into a separate spreadsheet
ply. When this
option is checked, the simulation will run first,
and the simulated values for each variable and scenario
will be inserted into a a new worksheet.
If you use SIMPARAMETER on more than one sheet of your
currently open workbooks, the default number of
scenarios may change when you check or uncheck
"Simulate all sheets".
Once you have selected your simulation options, press
"Simulate" to run the simulation, or "Cancel" to return to Excel.
When you run the simulation, YASAI will recalculate the
results
S times for each combination of parameter
values, where
S
is the selected sample size. If there are
N
combinations of parameter values, the total number
of model recalculations will be
NS.
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. The
abort function may not work on Mac Excel 2016.
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 (but in a
predictable cyclic pattern). F9 is
equivalent to fully recalculating all open workbooks.
To recalculate only the currently active sheet, use
Shift-F9 (or Fn-F9 on Mac systems).
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 "Charts..." from the
YASAI menu (or the "YASAI_Charts" button in Mac Excel 2016), which produces a charting
dialog box):
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 may modify them to
suit your needs. The chart and its associated
data each become a new worksheet ply, with the names
"Chart Output
n" and "Chart Data
n",
repsectively. You may delete these sheets 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. Histograms are not available when using
Mac Excel 2011, due to a bug in that version of Excel.
Cumulative charts are available in all versions.
Show Min/Max Scenarios
As of version 3.0, YASAI can highlight minimum and
maximum scenarios in the simulation output sheet.
To use this functionality, select a cell of the
simulation report containing
the name of the simulation output variable for which you
want to find the minimum or maximum average value for and
then select the "Show Min Scenario" or "Show Max
Scenario" menu item, as appropriate (in Mac Excel 2016,
these respectively appear as "YASAI_Min_Highlight" or
"YASAI_Max_Highlight" buttons in the add-in pane; in other
versions they are under the "YASAI" popup in the add-ins
pane). This feature allows for quick identification of the
best scenario without having to manually scan through
the output report.
Hypothesis Testing
As of version 3.0, YASAI can run hypothesis tests to
evaluate whether the differences between sample means of
various outputs appears to be statisticaically significant.
After
running a simulation, select "Hypothesis Test..." from
the YASAI menu (in Mac Excel 2016, select the
"YASAI_Hypothesis_Test" popup in the add-ins pane). Select a variables and scenarios for
the pair of variables you wish to compare, as below.
The appropriate test is selected based on your preferences
for random number generation in your simulation. If you
opted to use the same random number seed for each
scenario (the default option) in the simulation menu, or
the two variables are from the same scenario, then
a paired
t-test will be run. If not, YASAI will
use a non-paired
t-test for
unequal variances. After running the hypothesis
test, a new sheet is created, which shows the 1-tailed and
2-tailed
p-values from running the appropriate hypothesis
test. Additionally, the sheet shows all the
observations for each the two variable-scenario pairs tested, which
may be convenient for performing further analysis.
Generally speaking, small
p-values mean that
the the differences between the observed means of the two
compared variables are very unlikely to have resulted from
pure chance, whereas large
p-lvalues means that the
observed outcome was more likely to have arisen by chance.
The smaller the
p-values, the more statistically
significant is the difference between the variables' sample
means.
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" or "ignore links" or "don't update links"
button if Excel asks you if you
want to "edit links" or "update 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. First, try select "Simulation..." from the
YASAI item in the add-ins menu: in recent versions of
Excel, simply activating
the simulation dialog box is often enough to
clear the "#NAME" errors. If not, you should modify
Excel's security settings to let YASAI run. If you
see an "Enable content" button between the worksheet
grid and the ribbon, try clicking it. If the problem
persists, use the Excel Trust
Center settings to enable macros.
After taking these steps, it may be necessary in
some cases to place the cursor in each cell displaying as
"#NAME" and hit the "enter" key to get Excel to properly
reevaluate the formula and display a proper value.
Simulating Only the Current Workbook
The "Simulate all sheets" option recalculates all
currently open workbooks. If it is not checked, each
simulation point is obtained by recalculating only the
currently active sheet. In some cases, it might be
preferable to recalculate all sheets of the currently
active workbook, but not sheets from other open workbooks.
Unfortunately, Excel does not provide the functionality
necessary to implement such recalculations; this is a
longstanding internal limitation of Excel.
If you have a single model that uses more than one sheet,
you should generally close all other workbooks before
running your simulation.
Known Problems Specific to Mac Systems
Developing VBA applications is much more difficult for
Mac Excel than for Windows Excel, and VBA does not
behave identically on Windows and Excel platforms.
Therefore there are a number of issues that affect only
Mac users:
Mac Excel 2008
Mac
Excel 2008 does not support Visual Basic, so it is
impossible to use YASAI with it.
Mac Excel Performance
While YASAI supports Mac Excel 2011 and 2016,
simulation performance can sometimes be poor.
Excel 2016 performance seems to have
improved as software updates have been released. If
you use Mac Excel 2016, make sure it is
recently updated.
Charts in Mac Excel 2011
Due to an apparent bug in Mac charting,
histogram charts are not available when using Mac Excel
2011. Cumulative charts are available on all platforms,
including Mac Excel 2016.
Dumping Data
Message in Mac Excel 2011When creating
the output report at the end fo the simulation in Mac
Excel 2011, a message saying "dumping internal data" may be
visible even if "dump internal simulation data" option was
not selected. This message is typically visible for
only a very brief time.
Aborting in Mac
Excel 2016The abort function may not work in
Mac Excel 2016.
Delayed Appearance of
Add-Ins Pane in Mac Excel 2016When you
install YASAI in Mac Excel 2016, the add-ins pane and YASAI
buttons may not appear for the current workbook. However,
they will appear for all subsequently opened workbooks.
Mac Excel 2016 Location of Opened Worksheets
During testing of Mac Excel 2016, it has been observed that
newly opened worksheets may appear with their windows at the
extreme edge of the screen, usually just barely visible the
extreme lower right corner. At present it is not clear
if this is due to YASAI or just a strange behavior of Mac
Excel 2016.