Inspired by the work of Jack Dahlgren on Free Monte Carlo Simulator for Microsoft Project and with a help of insightfull posts from Glen Alleman on Herding Cats as well as a lot of Googling around I started an open source project Monte Carlo Simulation for MS Project on SourceForge.net.
The purpose of the project is to explore the possibilities of Monte Carlo simulation in the area of project management.
It is not the purpose of the project to compete with professional quantitative risk assesment tools like @Risk (by Palisade), Risk+ (by Deltek) or Crystall Ball (by Oracle, formerly by Decisioneering).
What can Monte Carlo Simulation for MS Project (MCS) do for you?
There are many risks on projects and one of the obvious is that tasks will take more or less time than planned. As theory goes, duration/work on tasks is a random variable with certain probability distribution.
As there are extremely rare cases where we can actually measure cumulative distribution function, which would completely describe probability distribution and even more rare cases where we simply know which probability distribution is the right one and what are its parameters, we have to assume certain distribution. Since triangular distribution looks just as good as any other skewed distribution (beta, betapert, lognormal) from far enough distance and due to practical and performance reasons I started with triangular distribution, which is defined with minimum, maximum and most likely (mode) values.
Since it is very unrealistic to actually define a minimum and a maximum value, MCS offers you an option to enter which percentiles are you estimating. I suggest to use 10th and 90th percentile, which means that 9 out of 10 tasks will complete inside min-max range and one will complete outside of its min-max range, which is much more realistic than saying all my tasks will complete inside estimated range.
MCS uses Duration1 and Duration3 fields to store minimum and maximum estimates plus Duration or Work for most likely estimate. If task type is set to Fixed Duration, then Work field is used as most likely value and during simulation run, Work field is substituted with a random value. If task type is set to a value, other than Fixed Duration, then Duration field is used as most likley value and during simulation run, Work field is substituted with a random value.
There may be other sources of variance on the project, but currently, they’re not taken into account during simulation. Such sources of variance might be:
- resource availability
- resource rates (for cost simulations)
- lag times
- time constraints
MCS works by going through all non summary tasks and assigning random triangularly distributed value to Work or Duration, depending on its Type and taking Duration1 and Duration3 as input parameters for triangular distribution. At the end of each run, project is recalculated and these tasks variables are stored for all project’s tasks:
- finish time
Same variables are stored for the entire project as well.
At the end of the simulation, various reports are produced in Microsoft Excel:
- sensitivity analysis for duration, work and cost, based on Spearman’s rank correlation between tasks value and project’s value,
- criticality analysis based on calculating the percentage of runs where certain task was on a critical path,
- project finish, duration, work and cost distribution charts with relevant descriptive statistics and calculated percentiles.
- milestones finish dates distribution charts with relevant descriptive statistics and calculated percentiles.
- resources workload distribution charts with relevant descriptive statistics and calculated percentiles.
- Download the package from SourceForge,
- Unzip it to some folder.
- Run Microsoft Project.
- Press Alt-F11 to run Visual Basic Editor.
- Import all .bas and .cls files from the package into Global.MPT project, so that the application is available to all projects. Warning: Some files in ALGLIB libraries are duplicates. Import them only once!
- Select Tools and References and check Microsoft Excel Object Library.
- From Microsoft Project select Tools, Macros and run MCS macro.
- Type in the number of iterations to perform. You might want to experiment with 100 and going beyond 1000 doesn’t make much sense.
- Type in percentile at which you’ve estimated lower and upper bounds. 0,1 means 10th and 90th percentile.