NEWS (2018 March 20):
I have updated the simulation package with 2 VBA functions:
RandShare() -- is for simulating finite discrete variables. It picks a results from a Results tables at random in proportion to the values (either a share or a probability) in a Shares tables.
Cholesky() -- supports generating correlated random variables. Details about how to use it are in the tutorial (requires some matrix operations in Excel).
The Sampler macro has been updated to generate examples of both of these (including an example 3 correlated normally distributed variables, and a way to generate arbitrary correlated random variables).
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Excel is a general purpose spreadsheet which is widely used & understood, but rarely used by itself for simulations. However, the Data Table function in MS Excel can be used to execute substantial simulations, without requiring cumbersome programming "tricks" or VBA coding. The result is an arbitrarily large results table in which each row is one iteration of the simulation, and each column is a random variable generated in the simulation.
A small number of additional probability functions are easily programmed using VBA to make Excel a general purpose simulation package. Because VBA is interpreted, use of VBA functions can greatly limit the speed of a simulation. However, for simulations of small size and complexity, the ease and familiarity of working in Excel, outweigh the disadvantages of speed. Examples from clinical trials will be used.
Finally, I discuss new methods to move simulations out of the black boxes and into the enterprise, based on work by Sam Savage. Simulation results (a “SIP”, or “Stochastic Information Packet”) from multiple platforms can be stored as XML strings(using the DIST standard) in a “SLURP” (“Stochastic Library Unit with Relationships Preserved”), and from there used for reports, planning, etc, or incorporated into other simulations.
The attached presentation is an introduction to doing simulation in MS Excel, using the Data Table function, covering:
* The basic method;
* Using inverse probability functions for validation, verification, and insight
* Some basic macros for setting up
* VBA code for some missing probability functions
* Examples (from clinical trials)
* Advantages, Disadvantages, and Limitations
* Additional Resources
Attached is:
(1) A power point tutorial on implementing basic simulations in Excel
(2) A presentation introducing the concept of Probability Management
(3) A presentation with some notes about non-introductory topics, such as validation, verification and sensitivity. (4) An Excel spreadsheet template containing macros which ease writing simulations, some missing probability functions written in VBA, and examples.
(5) Another Excel spreadsheet that contains a sampler of simulation functions, with a plot overlaying all random variables.