Setting Up a Project Critical Path Model

Determining project schedule risk through simulation can be achieved by constructing a critical path model in Excel.  This might sound tedious, especially for large projects, but we will walk through the process and show that it's pretty simple.  Simulating a critical path model is worth the effort and was discussed in a previous article.  We'll use the example project from that article to show the steps needed to simulate the critical path model.

A critical path model is simply an Excel spreadsheet that accounts for dependencies among project tasks and calculates the project completion time using the critical path.  Each task time is modeled as a random variable.  For each simulation trial, the task durations are randomly sampled and project completion time is calculated.  This is repeated numerous times, and we get a range of project duration and associated probabilities.

Critical Path Model

If you've already entered project data in your scheduling software you can save time by exporting the data to Excel.  For our example, the model was built from scratch.  The spreadsheet is shown below with a network diagram to make following the tasks easier.

Critical Path Model

The relevant columns for the purposes of simulation are columns AE, AG, and AH.

AE contains Simulation Master random variable functions for each task duration.  These variables are modeled using the PERT distribution where estimates of minimum, most likely, and maximum task duration are used as parameters.  These parameters are shown in columns AB through AD.

Using precedents, we enter formulas in column AG to calculate the start day for each task.

End day for each task is calculated in column AH.  The end day for a task is simply the start day plus the task duration.

The model with formulas is shown below.

Critical Path Model with Formulas Shown

Multiple Precedents

To handle multiple precedents, we make use of Excel's MAX function.  For example, task H cannot start until tasks E, F and G have been completed.  To calculate the start time of H (cell AG12) we use the latest end day of E, F or G which would be the maximum end day of the three.

What to Simulate

So we've set up the critical path model and we're ready to simulate.  What does it mean to simulate this model?  We want to know the range of outcomes for total project duration.  The project finishes when task H is complete.  Therefore, we would simulate task H end day (cell AH12).  When using Simulation Master, this cell would be the output cell.

Excel is a registered trademark of Microsoft Corporation.  Used with permission from Microsoft.