Monte-Carlo analysis is a common technique used within science and mathematics. Within my career I have frequently used it to help understand the effect of uncertainty and risk within cost and time of large procurement projects.
The purpose of this add-in is to provide a way to undertake simple monte carlo analysis without relying on complex and costly software while still having the basic functionality needed.
The tool is written in VBA as a .xlam add-in within Excel. While not the most performant of implementations it does offer a simple interface and an implementation of the common distributions used within the project management sphere.
Correlation has been implemented into the tool to allow the random sampling of correlated distributions. The implementation is based on Iman and Conover's 1982 paper.1 The tool uses a Cholesky decomposition of the correlation matrix to check for consistency and then proceeds with Iman and Conover's method if consistent.
I have mirrored the functionality of @Risk so if you have used that before it should be familiar. The tool has the following functions implemented in Excel.
There are two types of function Input and Output the input functions generate Monte Carlo data for the relevant distribution which is stored in memory and the output functions perform that operation on the underlying slice data.
This function Generates a triangular distribution with given minimum, most likely and max.
Generates a Bernoulli distribution with a given probability.
Calculates the arithmetic mean of the given distribution
Returns the percentile of the given distribution
To install the add-in follow these "Add or Remove COM add-in" instructions.
You may need to unblock the add-in depending on how you downloaded it as shown in these instructions