Project critical path analysis can be performed using Monte Carlo simulation as a way to determine project completion time probabilities. In this article, we will create a network diagram in Excel and a table to perform necessary logic operations.
We will assume that each task follows the PERT distribution (also called Beta-PERT). Using past experience, bottom-up analysis, or expert estimates we give each task a minimum, most likely, and maximum task duration.
For nodes where paths merge, i.e. a node with more than one predecessor, logic must be added to the spreadsheet to determine task start time. For example, task D has tasks B and C as predecessors. The start time for task D will be the maximum of task B or C completion day.
The spreadsheet containing the network diagram and a table of tasks is shown below. The formulas are shown to see how the logic is performed. For a more detailed description of the spreadsheet set-up, refer to this article.
The same spreadsheet with values is shown below. Since each task duration is a random variable (PERT), each time the spreadsheet is calculated, a new value will be randomly sampled from each distribution.
While the network diagram is not necessary to perform the simulation, having a visual representation of the network makes constructing the table much easier.
With the network model created, a Monte Carlo simulation can be run. Again, the random variable inputs are task durations (column AE) and the output is the completion day of task H (cell AH12).
The results of the simulation is shown above. With information from the simulation we can do several things.
- Find the probability of completing the project in X number of days or less. See below for example.
- Give completion time with a given level of certainty. For example, we could give a project duration of 45.7 days with 95% probability.
- Look at the range of possible outcomes to see the risk associated with the project schedule. In our example project, the minimum outcome is 33.28 days and the maximum is 50.75 days.
It's interesting to note that if we gave a project completion estimate using the most likely estimates, the critical path project duration would be 40 days. The critical paths in this case are A-B-D-E-H and A-B-D-G-H. From the probability analysis below, we see that the project only has a 28% chance of being completed in 40 days or less!
The simulation was performed using Simulation Master and the network diagram was created with Diagram Master.
Related article: Exposing Project Schedule Risk Hidden in Best Estimates
Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.
Hello Chris,
I like this article very much.
My attempts to create a network with Excel have not been satisfactory.
If you like, look at the follow article.
https://clevercalcul.wordpress.com/2015/04/28/
I created the network manually.
Best regards
Gerhard
Thanks Gerhard!