Critical Path Method - CPM & PERT
The Critical Path Method (CPM) provides a way for project managers to determine which tasks are critical (zero slack time) and which tasks can be delayed (and for how long). Normally, you'd need specialized software such as MS Project. Not any more. Now you can just download my CPM spreadsheet that implements the CPM / PERT algorithm in Excel. If you can copy and paste, enter data into cells, and follow a few simple guidelines, you can do a critical path analysis with almost no learning curve.
The CPM spreadsheet produces a Gantt Chart showing the critical path tasks as well as the potential delay times for the flexible tasks. You may need to be familiar with some of the basic terms used in the CPM method, but the template is very easy to use and does not require you to enter formulas. Unlike my other gantt chart template, the gantt chart in the CPM spreadsheet is an actual bar chart object, and you can resize it and change font sizes to get it to display the way you want.
The gantt chart in the Critical Path Method spreadsheet is very similar to what you'd see using Microsoft Project, except that it doesn't add the lines with arrows pointing from the end of one task to the start of a successor. But, that is an extremely minor limitation compared to the benefit you'll get from the lower cost and ease of use.
Critical Path Method Spreadsheetfor Excel
- 25 tasks (you can insert more rows, but the algorithm only works for up to 25 tasks)
- 6 predecessors per task (the pro version lets you include up to 10)
- all tasks must have successors, but tasks without successors are not highlighted (pro version highlights them yellow)
- milestones in the chart do not align well when the chart is modified (pro version works better)
CPM Spreadsheet (Pro Version)
This spreadsheet implements the PERT algorithm and Critical Path Method to aid in project planning and scheduling.
The .XLS file is still limited to 100 tasks (because of the column limit in Excel). But, the .XLSX version lets you include up to 200 or 500 tasks.
Unlike a normal project schedule or gantt chart, this spreadsheet lets you define the duration of a task using O-M-P times (Optimistic, Most Likely, and Pessimistic). You can choose to calculate the expected time (Duration) using either the Triangular or Beta distribution.
The Gantt Chart shows the tasks on the Critical Path as red bars, the flexible tasks as blue bars, and the slack time as gray bar extensions. Milestones (tasks with zero duration) are shown as short black bars (rather than diamonds like you might normally see).
Task dependencies are defined by simply listing the predecessors for each task.
Using the Critical Path Method Spreadsheet
It isn't my intention here to explain the details of PERT and the Critical Path Method. I created the spreadsheet based on the information in Project Management for Dummies and the very detailed article on wikipedia. You are encouraged to read through these or other resources if you have questions about terminology or how to use what the analysis will tell you.
Although the CPM spreadsheet contains a gantt chart, if you are needing to create a detailed project schedule, I would recommend using my Gantt Chart template as well.
Assuming you know how the Critical Path Method works, you can perform a critical path analysis on your project using the above spreadsheet, provided that you follow the guidelines below:
- Read the comments (little red triangles) within the critical path method spreadsheet for additional help if you have a question about what the column labels mean.
- The PERT table is limited to 100 tasks (including the Start and Finish events).
- You can list up to 6 predecessors by listing one ID per column.
- Each task needs to have at least one successor (a task that lists it as a predecessor).
- Do not mess with the Start or Finish rows.
- Edit only the cells with the light-gray border (The ID, Task Name, Predecessors, and Times).
- To insert a task, copy one of the rows between the start and finish events and insert the copied row where you need it to be.
- To remove a task, delete the entire row.
- You can move a row by cutting and inserting the cut row above another row.
- If you get an error, it is likely due to a circular reference error. Check your dependency relationships (predecessors).
- If a task has a duration of 0, it is defined as an Event or Milestone (shown as a diamond in the gantt chart).
- In the Gantt Chart, you will probably have to align the upper and lower axes by playing with the maximum value via Format Axis > Scale. The chart is set up to show the number of days at the top and the number of weeks (5 days per week) at the bottom. The events (start, finish, etc) use the lower axis. The bars use the upper axis.
- If copying WBS numbers and task descriptions from another file, make sure to only paste values, because the tasks column uses conditional formatting to highlight the critical tasks and the tasks that do not yet have successors.
While you are in the process of adding tasks: If you are wondering why tasks are marked as critical, check to see if there are any tasks highlighted yellow. All tasks need to have successors before the algorithm will correctly predict the critical path.
Interpreting the Gantt Chart
Even though the gantt chart does not show lines connecting dependent tasks, it is fairly easy to figure out if you remember that dependencies relate the START of one task to the END of its predecessor.
For critical tasks: Look at the start of the task and see if it lines up with the END of other tasks, or possibly the end of the delay period.
The end of the Delay period will usually line up with the beginning of a critical task. If it doesn't, the end of the Flexible task may line up with the beginning of another Flexible task.
Displaying all Bar Chart Labels
If you have more than 100 tasks, the bar chart may show only every other task label (or fewer if you have > 200 tasks). In that case, you will need to right-click on the y-axis and go to Format Axis. Then set the "Interval Between Labels" to "Automatic." You will then need to stretch the chart taller until you see all the labels.
Hours instead of Days
Except for the calculation of the Finish Date, there are no calculations in the worksheet that are based on dates. So, if you want the times to represent hours rather than days, then you can just change all labels to say "Hours" instead of "Days" and delete the Finish Date cell. When you enter the O, M, and P values, just consider them to be hours.
The template is set up initially to fit the entire analysis on a single page. If you want to do something different, then all of the Excel print settings are open to you. You can print the gantt chart separately by first selecting the chart and then pressing CTRL+p.
Behind the Scenes
If you want to see everything that is going on, you can unhide all of the columns. There is no VBA and there are no other hidden worksheets. The highlighting in the Task Name column is done using conditional formatting.
More Project Management Tools
- Gantt Chart - Create a detailed project schedule.
- Excel Timeline - Create a project timeline, showing events along an axis with leaderlines.