Monte Carlo Simulation with Microsoft Project

Project = uncertainty. Managing a project means dealing with uncertainties. Microsoft Project doesn’t really help you there. It applies Critical Path Method to determine, which tasks will delay the project if they are delayed and calculates how much slack time other tasks have.

If you can elaborate a bit on tasks durations and provide optimistic, pessimistic and expected durations, you can use Microsoft Project’s PERT Analysis tool to enter this data and calculate estimates for tasks.

But still, life is more complex than that. If you know optimistic, pessimistic and expected (most likely) durations, and assume that actual outcomes are triangularly distributed, you can run a Monte Carlo simulation and see how the overall project duration is distributed and what are the chances that you meet a certain deadline.

I found a neat VBA script to run the simulation right out of Microsoft Project. It works by using the same fields as Microsoft Project’s PERT Analysis Tool, that is Duration1, Duration2 and Duration3, assigns random, triangularly distributed duration to each task and at the end of each run, it exports durations to Microsoft Excel worksheet. As a result, you get a list of possible outcomes for each task, summary task as well as the whole project. Put it on chart and you’ll know how thing might turn out.

I rewrote the simulation from scratch to make it faster by storing all data in VB instead exporting it to Microsoft Excel and programed analytical charts like duration, work and cost sensitivity, criticality, project finish, duration, work and cost distribution right into the simulation.

You can find more on the project page on SourceForge.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Monte Carlo Simulation with Microsoft Project”

  1. Glen B. Alleman Says:

    Jacks MCS is a handy startin point to show how variance impacts the duration. The next step up is Risk+ which has now been reduced in price to be usable for commerical application ~$700. It provides Latin HyperCube random number generation which is critical for decoupling the durations and isolating the crutiality of the near critical path network.
    But Jack’s code is the best place to start to get a handle in the concepts of Monte Carlo.

  2. Mike Bradshaw Says:

    Per instruction, I downloaded/un-compressed files imported to MCP. Received this run-time Macro error: User-defined type not defined
    Sub SetupExcel(ByRef XlApp As Excel.Application, ByRef XlBook As Excel.Workbook

    Plz Help!

Leave a Reply