Software Add-ins

About SIMTOOLS and FORMLIST

Simtools.xla and Formlist.xla are add-ins for Microsoft Excel (version 5 and later). Simtools adds statistical functions and procedures for doing Monte Carlo simulation and risk analysis in spreadsheets. Formlist is a simple auditing tool that adds procedures for displaying the formulas of any selected range. Also available here is Torndiag.xls, an Excel workbook for making "tornado" sensitivity-analysis diagrams. Simtools and Formlist were developed to facilitate applications of probability and statistics, and to help managers do complex decision analysis. These programs are distributed as freeware for individual use, and may be freely redistributed to students and faculty in an academic institution. All other rights are reserved.

You can learn more on how to use Simtools.xla from the book Probability Models for Economic Decisions, published by Cengage Learning (2005) [available at Amazon.com].

[Installation] [Simtools features] [Formlist features] [Torndiag.xls] [Links]


Installing SIMTOOLS and FORMLIST

To install Simtools and Formlist, download the Simtools.xla and Formlist.xla files from this site, and save these files in the macro library subfolder of your Microsoft Office folder. (The path for this library folder is often c:\Program Files\MSOffice\Office\Library.) When saving, make sure that each file's name has the correct xla extension that signifies an Excel add-in. (Internet Explorer sometimes tries to change xla extensions to xls.)

Then in Excel, select the Simulation Tools and Formula List options from the Tools>AddIns dialogue box. After installation, SimTools and Formula List should appear as options directly under the Tools menu in Excel.

Click here to download SIMTOOLS.XLA now (v3.31a, 108 kb).
For Excel 2007 and later versions: SIMTOOLS.XLAM.

Click here to download FORMLIST.XLA now (v1.5a, 34 kb).
For Excel 2007 and later versions: FORMLIST.XLAM.

Note: If your browser converts xlam files into zip files or you have other problems in downloading, then click here to download a zip file that contains both versions of the addins (simtools.xla, simtools.xlam, formlist.xla and formlist.xlam).

(Versions of Simtools.xla and Formlist.xla in the older Excel 5 format are also available.)

Solving download problems for XLA files: If your browser launches Excel instead of saving the files to disk, you should right-click on the above links and select the "Save as..." option from the pop-up menu. If your browser changes the filename extension of simtools.xla and formlist.xla to .xls, you should be able to use these files after renaming them to restore the correct .xla filename. If you have problems with add-in functions in an xls workbook that was made on a different computer, you should use Excel's Edit-Links procedure.


Features of SIMTOOLS 3.3:

SIMTOOLS.XLA adds to Excel the following 32 statistical functions, listed in six categories. Optional parameters are shown in italics.

Inverse cumulative-probability functions. These functions, along with Excel's NORMINV function, can be used for generating random variables when the first parameter (called "probability" or "randprob") is generated by a RAND() or CORAND function:

  • BETINV(probability, mean, stdevn, lowerbound, upperbound) returns inverse cumulative values for a beta random variable, parameterized by its mean and standard deviation. When the first parameter is a RAND, BETINV yields a bounded random variable. (Default lower and upper bounds are 0 and 1.)
  • BINOMINV(probability, n, p) returns inverse cumulative values for a binomial random variable. When the first parameter is a RAND, BINOMINV yields a bounded integer random variable between 0 and n, with mean n*p.
  • DISCRINV(randprob, values, probabilities) returns inverse cumulative values for a discrete random variable. When the first parameter is a RAND, DISCRINV returns a discrete random variable with possible values and corresponding probabilities in the given ranges.
  • EXPOINV(probability, mean) returns inverse cumulative values for an exponential random variable. When the first parameter is a RAND, EXPOINV yields a nonnegative random variable (often used for random waiting times).
  • GAMINV(probability, mean, stdevn) returns inverse cumulative values for a gamma random variable, parameterized by its mean and standard deviation. When the first parameter is a RAND, GAMINV yields a nonnegative random variable.
  • GENLINV(probability, quart1, quart2, quart3, lowest, highest) returns inverse cumulative values for a generalized-lognormal random variable that has 25% probability below the quart1 value (the top of the first quartile), 50% probability below quart2, and 75% probability below quart3. A generalized-lognormal random variable is a constant plus or minus a lognormal random variable. When the first parameter is a RAND(), GENLINV yields a random variable which could be positive or negative, but is bounded on the side of the narrower quartile range. If optional lowest and highest values are specified (satisfying lowest < quart1 < quart2 < quart3 < highest), then values of the generalized-lognormal random variable are adjusted as necessary to keep GENLINV within these bounds (increasing to the lowest value from below it, decreasing to the highest value from above it).
  • LNORMINV(probability, mean, stdevn) returns inverse cumulative values for a lognormal random variable, parameterized by its mean and standard deviation. When the first parameter is a RAND, LNORMINV yields a nonnegative random variable.
  • POISINV(probability, mean) returns inverse cumulative values for a Poisson random variable. When the first parameter is a RAND, POISINV yields a nonnegative integer random variable.
  • TRIANINV(probability, lowerbound, mostlikely, upperbound) returns inverse cumulative values for a random variable with a triangular probability density. When the first parameter is a RAND, TRIANINV yields a bounded random variable.
  • XTREMINV(probability, mean, stdevn) returns inverse cumulative values for an extreme-value (or Gumbel) random variable, parameterized by its mean and standard deviation. When the first parameter is a RAND, XTREMINV yields a random variable that may be positive or negative. (If W is a Weibull random variable then -LN(W) has this extreme-value distribution.)

Functions for working with correlations among random variables:

  • CORAND(CorrelArray, RandSource), entered as an array formula in a range of cells in a row, returns RANDom values for making random variables that have correlations as in the given CorrelArray. (See also NORMIZE.) Each value in an array returned by CORAND is like a RAND in that it is generated uniformly between 0 and 1, but values in a CORAND array are not independent. For any integer n greater than 1, the CorrelArray parameter may be a square n-by-n array of correlations for n random variables (as returned by MCORRELS), and then CORAND returns an array of n correspondingly correlated values. Such an n-by-n correlation array should be symmetric and must have ones on the diagonal. The CorrelArray parameter can also be a single number, in which case CORAND functions as if the CorrelArray parameter were a 2-by-2 array, returning two random values with the given correlation. When CorrelArray is a number and the optional RandSource parameter is a reference to another cell that contains a RAND or CORAND formula, the CORAND function in one cell returns a uniform random value that is correlated with the RandSource cell according to the CorrelArray number. (For more about CORAND, see notes below.)
  • MCORRELS(dataRange) returns the matrix of correlation coefficients among the columns of the data range. If the data range has n columns, then MCORRELS should be entered as an array formula in a square n-by-n range.
  • MIDRAND(correlation, givenCoValue) returns the conditional median of a CORAND given another CORAND's value and the correlation between them. Used for subjective assessment of correlations.
  • MSQRT(squarearray) returns a lower-triangular matrix square root (or Cholesky factor) of a given square array. An array function.
  • NORMIZE(datacolumn) returns an array of normalized rank values, sampled from the standard normal distribution (at fractile medians), and rank-ordered as the data column. When CORANDs are used to make continuous random variables that are not normal, the correlation parameters of CORAND should be normalized rank correlations, which can be estimated from data by applying NORMIZE to each data series and then computing the correlations among these normalized arrays.
    (Note: NORMIZE may return an error message when it is entered into a range that is not the same size as the data column. But a calculation bug in Excel 97 may also cause this error message when a NORMIZE array is recalculated. In this case, the spreadsheet should be recalculated using the Ctrl+Alt+F9 keystroke, which tells Excel to recalculate all cells.)
  • PRODS(values) multiplies each pair of values in the given range and returns the products as a square array. The values must be given in one row or one column. To illustrate the use of this function, suppose that a range named "correls" contains a square symmetric array that lists the correlations of the random returns-per-share of various stocks, a range named "stdevns" lists the standard deviations of these stock returns, and a range named "shares" lists the numbers of shares of these stocks in some investment portfolio; then the standard deviation of the total returns of the portfolio is
    SUMPRODUCT(PRODS(shares),PRODS(stdevns),correls)^0.5

Functions for decision analysis:

  • ARGMAX(labels, values, testCells, criterion) returns a label corresponding to a maximal value, where the corresponding test cells (if any) match the criterion.
  • CE(incomes, RiskTolConst, RiskTolSlope) returns the certainty equivalent of a random draw from the incomes range, for a decision-maker with constant risk tolerance (or linear risk tolerance when the optional RiskTolSlope parameter is used). When the risk tolerance is positive number, the CE value is between the minimum and the average of the income values, and the CE becomes closer to the average as the risk tolerance becomes larger. If the risk tolerance is a negative constant (denoting risk-seeking behavior), then the CE value is between the average and the maximum of the income values. If the RiskTolConst parameter is exactly equal to 0 and the RiskTolSlope is omitted, then the CE function returns the average of the income values. (The CE is computed by converting the incomes to utility values by UTIL, averaging these utility values, and converting this average utility back into monetary units by UINV. Nonnumerical entries in the incomes range are ignored. The CE's dependence on the risk tolerance is discontinuous when the risk tolerance is zero, because a slightly lower risk tolerance yields a CE near the maximal income, and a slightly higher risk tolerance yields a CE near the minimal income. If the RiskTolSlope parameter is used then a zero or negative risk tolerance generates an error.)
  • RISKTOL(HighIncome, LowIncome, CertainEquiv) returns the constant risk tolerance such that a lottery paying either the high or low income, each with probability 1/2, has the assessed certainty equivalent value.
  • UTIL(income, RiskTolConst, RiskTolSlope) returns a utility value of monetary income, for a decision-maker with constant or linear risk tolerance.
  • UINV(utility, RiskTolConst, RiskTolSlope) returns the monetary certainty equivalent of an expected utility from the UTIL function with the same risk-tolerance parameters. 

Functions for analyzing discrete probability distributions:

  • CEPR(values, probabilities, RiskTolConst, testCells, criterion) returns the certainty equivalent, for a decision-maker with constant risk tolerance, of a random income drawn from the specified values according to the corresponding probabilities, conditional on the event where the corresponding test cells (if any) match the criterion. When the RiskTolConst parameter is 0, the CEPR function returns the conditional expected value.
  • CORRELPR(values1, values2, probabilities) returns the correlation coefficient for a discrete probability distribution with corresponding values of two random variables.
  • COVARPR(values1, values2, probabilities) returns the covariance for a discrete probability distribution with corresponding values of two random variables.
  • STDEVPR(values, probabilities) returns the standard deviation for a discrete probability distribution with corresponding values of a random variable.

Functions for regression analysis:

  • REGRESSN(XDataRange, YDataRange) actively returns multiple regression output, for predicting Y as a random variable that depends linearly on the explanatory X variables. REGRESSN should be entered as an array formula in a range with 7 rows and as many columns as the X data range.
  • YHATSTE(XDataRange, NewXRow, RegressnStdErr) returns the standard error of the estimated conditional mean of Y (often called Y-hat) at the given new X row in a multiple regression.

Functions for randomly generating discrete distributions:

  • DIRICH(alphaArray, RandSource), entered as an array formula in a range that has the same size as the alpha array, returns Dirichlet random fractions that sum to 1. These random fractions have means that are proportional to the corresponding values in the alpha array. Higher alpha-array values yield less variance. (RANDom inputs to drive the random DIRICH values can be provided externally in an optional RandSource range that is the same size as the alpha-array. Otherwise, DIRICH gets random inputs automatically by implicit calls to Excel-VBA's random number generator.)
  • DIRALPHA(dataRange), entered as an array formula in a range of cells in one row, returns estimated alpha parameters for a Dirichlet distribution. Each row of the data range should contain nonnegative numbers that sum to 1.
  • LGT(x) is the logistic distribution. It transforms random variables from a logit model to fractions between 0 and 1 or (as an array formula) to discrete probability distributions proportional to the EXP(x(i)) values. In the array-formula usage, x must be an array of numbers in a row, and LGT must be entered into a similar array.
  • LGTINV(p) is the inverse of the logistic or logit function. Applied to a fraction or (as an array formula) to a discrete probability distribution, LGTINV returns log-odds ratios for a logit model. In the array-formula usage, p must be an array of probability values in a row, and LGTINV must be entered into a similar array.
  • SHUFFLE(n, RandSource), entered as an array formula in a range of n cells in one row, returns a random ordering of the numbers from 1 to n. When entered into a row range of fewer than n cells, this function generates random samples from {1,...,n} without replacement. The values in a given range of n cells in a row can be shuffled by entering the array formula =INDEX(givenrange,1,SHUFFLE(n)) into another n cells in a row. (An optional RandSource cell containing a RAND value can be used to determine the outcome of SHUFFLE. When RandSource is omitted, SHUFFLE uses instead an implicit call to Excel-VBA's random number generator.)

SIMTOOLS.XLA also adds three macro procedures to the Excel Tools menu:

  • SIMULATION TABLE, in a selected range, tabulates outputs from repeated recalculations of a Monte Carlo simulation model. The outputs to be tabulated should be in the top row of the selected range, but the top-left cell of this selected range should be unused. Recalculated values of the simulation outputs will fill the lower rows of the selected range, with each row containing the output values from an independent recalculation of the simulation model. The left column of the selected range is used for a percentile index, which can be useful for making cumulative-distribution charts after the output data is sorted (but the Simulation Table procedure itself does not sort the output data).
  • ITERATIVE PROCESS iteratively copies values to a state range from an update range, while tabulating output.
  • COMBINE ROWS makes all combinations of rows from selected ranges. It can be used to make a table of possible event-sequences that is equivalent to a decision tree.

Using Simtools functions in VBA macros: If you want to use Simtools functions in a VBA macro program, it is necessary to attach Simtools.xla as a reference in your VBA module, by applying the Tools:References menu command in the Visual Basic Editor and checking Simtools.xla as an available reference.

More technicalnotes about CORAND: With an n-by-n CorrelArray parameter, CORAND's optional RandSource parameter can be a reference either to a single cell or to n cells in a row. RandSource cells should contain independent uniform random values, as generated by RAND or CORAND. When RandSource is a single cell, this cell's value is returned in CORAND's output array as the last value on the right. When RandSource is a range of n cells in a row, CORAND looks in these cells for all the random inputs that it needs to generate its n correlated outputs, preserving the rightmost value but transforming the others to generate appropriately correlated random outputs. This usage (along with a similar RandSource usage for the DIRICH and SHUFFLE function) can be applied when you want to work with another simulation add-in's random number generator. When RandSource is omitted, CORAND automatically generates its n random values by transforming the results of n implicit calls to Excel-VBA's random number generator. The seed for this VBA random-number generator is randomly reset (with a reference to the computer's clock) whenever the SimulationTable or IterativeProcess macro procedure is run (from version 3.31).

What's new in version 3: In version 3.0 (3/99), the NORMIZE, PRODS, and XTREMINV functions have been added, an optional RiskTolSlope parameter has been added for the CE function, and the former CONDEXP function has been dropped (subsumed now by CEPR). The menu item formerly that was formerly called MarkovProcess has been renamed IterativeProcess, and the former SensitivityTable menu item has been dropped (subsumed now by Torndiag.xls). In version 3.1 (4/99), the percentile numbering in the left column of a Simulation Table has been changed to run from 0 to 1 with step 1/(k-1) (instead of from 0 to (k-1)/k with step 1/k), for compatibility with Excel's PERCENTILE function. LGT and LGTINV have been modified to avoid Excel97 recalculation bugs (v3.11). DISCRINV has been revised to improve speed (v3.13). To handle nonsymmetric correlation arrays generated by Excel's data-analysis tools, CORAND has been changed to use only the lower-left half of the correlArray (v3.14). In version 3.2 (12/99), the MIDRAND function has been added, and values of the NORMIZE function have been changed to fractile medians in the standard normal distribution (having mean 0 and standard deviation 1, instead of matching the mean and standard deviation of the data column). The SHUFFLE function has been added in version 3.3 (3/00). The current version of Simtools has been tested in Excel 5, Excel 97, and Excel 2000 before release.

Notes on earlier versions: Changes in versions 2.x include the CE function (v2.2), the DIRICH function (v2.3), the DISCRINV function (v2.4), the optional RandSource parameter for CORAND (single cell usage in v2.5, range usage in v2.95), the optional RiskTolSlope parameter for UTIL and UINV (v2.6), the RISKTOL function (v2.7), the DIRALPHA function and the optional RandSource parameter of DIRICH (v2.8), the MCORRELS function and the array-function usage of LGT and LGTINV (v2.9), the optional Lowest and Highest parameters for GENLINV (v2.96), the CEPR function (v2.98). Other second-decimal version updates have been modifications to improve speed and numerical accuracy. Some function names were changed in version 2 of Simtools. The version-1 functions BINV, GINV, TRNGLINV, and EXPLINV have been renamed BETINV, GAMINV, TRIANINV, and EXPOINV. The version-1 function EXPLUTIL function was replaced by the UTIL function, with inverse UINV. See also simtools.doc.


Features of FORMLIST 1.5:

FORMLIST.XLA adds one macro procedure to the Excel tools menu:

  • FORMULA LIST is a procedure for auditing the formulas that are used in a selected range. Under this procedure, the formulas from all cells in the audit range can be listed as text in any selected column. If a cell is part of an array formula then the range of this array is listed with the formula (new in v1.5). The list can also include the names that are defined in the workbook (new in v1.4).
    If the selected output range is the same as the audit range, then FORMULA LIST instead does a formula/text toggle, inserting a single quote ['] before the initial equals sign [=] in each formula, to display formulas in place as text. A second application of this formula/text toggle removes these quotes, to restore the formulas. Array formulas are not affected by a formula/text toggle.

FORMLIST.XLA also adds two Lookup-Reference functions:

  • FORMULAS(auditRange) actively displays the current formulas in the audit range, as an array in a column. To display all formulas, the FORMULAS function should be entered as an array formula into a range in one column that includes more cells than there are formulas in the audit range. The words "FORMULAS FROM RANGE..." should appear below the last displayed formula.
  • FORMRC(auditCell) returns the formula of the designated cell in R1C1-style notation (with braces on array formulas). If the cell does not contain a formula, then FORMRC returns the cell's value (or a zero-length string for a blank cell)

Features of TORNDIAG.XLS:

TORNDIAG.XLS is a normal Excel workbook file, not an add-in. It can be stored in any disk or folder where you keep Excel workbook (.xls) files. When you open this file in Excel, it adds a TornadoDiagram procedure to the Excel Tools menu. This procedure can then be used to make a tornado-style sensitivity-analysis diagram in any open workbook. (Tornado diagrams show how an output value would change as various input parameters are changed, one at a time, from a given best estimate to a given low estimate and a given high estimate.) Instructions for using this TornadoDiagram procedure, along with an illustrative example, can be found in the TORNDIAG.XLS workbook itself.

Click here to download TORNDIAG.XLS now (v1.1, 10/21/1998, 30 kb).


Links:

You can learn more on how to use Simtools.xla from the book Probability Models for Economic Decisions, published by Cengage Learning (2005) [available at Amazon.com].
The Decision Analysis Society offers information about other software products for decision analysis, such as TreePlan. Commercial simulation add-ins comparable to Simtools include @Risk, Crystal Ball, and xlSim.

Simtools and Formlist are open code software. Comments to myerson @ uchicago.edu. 

Microsoft Office Marketplace logo