Critical Path Method (CPM) / Critical Path Analysis

Background

A project typically consists of a list of activities to be carried out. Each of the activities may require different amount of time to complete and some of the activities may be interdependent on one another. For example, a particular activity may require another activity to complete before it can start. When the number of interdependent activities increases, the entire project becomes complex and difficult to understand. This complex list of activities is also known as the "Work Breakdown Structure" in project planning.

Critical Path Method (CPM) / Critical Path Analysis

The Critical Path Method (CPM) or Critical Path Analysis is an important technique for effectively managing the complex list of activities of a project. It uses a mathematically based algorithm to calculate a network model known as the "critical path" to help a project manager determine the following:
  • How much time a project will require taking into account all the dependencies of the activities.
  • Which activities are "critical" or "on the critical path" that need to be completed on time to avoid delaying the whole project.
  • The slack time of the activities. An activity with slack time can be delayed without delaying the entire project. Activities not "critical" usually have slack time. One of the motivations of postponing an activity is the savings of labor and warehouse costs until it is necessary.
The Critical Path Method was developed by Du Pont in the 1950s and is now considered one of the most widely used technique in project planning and scheduling.

Critical Path Method Spreadsheet

Overview
This spreadsheet allows the project manager to perform project planning through the Critical Path Method. All the activities are first entered into the spreadsheet. This is followed by providing the amount of time required for each activity and specifying the dependencies. After which, a Gantt Chart will be automatically generated and the critical path of the project will be determined.

The critical path of the project is the sequence of project activities which add up to the longest duration. This is also known as the amount of time that the project will require. A delay in any of the activities on the critical path will result in a delay in the entire project.




In the diagram above, the darker shade of black on the Gantt Chart shows the critical path. All activities on this path will need to be completed on time to avoid delaying the project. Item E is shown with a lighter shade of black. This activity is not on the critical path and can be delayed without affecting the completion date of the project. Critical Path Calculation Overview In calculating the critical path, the spreadsheet automatically goes through a forward pass to determine the Early Start/Early Finish time of the project. After which, a backward pass is performed to determine the Late Start/Late Finish of the project. This process also determines the slack time allowed for each of the activities. The calculations also take into account weekends and holidays. This document will not go into the details of the calculation to determine the critical path.

Critical Path Method Spreadsheet Inputs
All the worksheets that require inputs to generate the Gantt Charts are marked with the word "(Input)". On the worksheet, fields that require inputs are marked with "*" for easy identification.




The diagram above shows the input worksheet "Month 1 (Input)". All the input fields required are marked with "*" and described below.
  • Project Start Date* - The start date of the project. The spreadsheet will be generating 12 output worksheets for tracking a project over a 12-months period. The start date allows the spreadsheet to determine which month to start generating the schedule.
  • ID* - A two digit number for identifying the item/task of the project.
  • Items* - The names of the items or tasks of the project.
  • DY* - The duration of the item/task in days. The start date and duration will be used to calculate the End Date as shown in the diagram above. It is important to note that the calculation of the End Date takes into account weekends and holidays like Christmas and New Year. Holidays can be defined in the "Holidays (Input)" worksheet.
  • DIDx* - The predecessor's ID. The predecessor item needs to be completed before the start of this item. A maximum of five predecessors can be specified.
Holiday Inputs
The Critical Path Method spreadsheet allows weekend and holidays to be defined and encompassed into the schedules.




Weekend
In the diagram below, the first section shows the definition of a weekend. A "Y" will indicate the particular day of the week is a weekend holiday.

Holidays
The second section allows holidays to be defined. It is important to define all the holidays within the 12-months schedule period. This will allow the Gantt Chart to incorporate all the holidays and generate an accurate schedule. The holiday's date is in "MM/DD/YY" format. The second column is used for describing the holidays and is optional. A maximum of 40 dates are supported, beyond which the holidays will be ignored.

Critical Path Method Outputs
This spreadsheet outputs a 12-months schedule in 12 different worksheets. Each worksheet will display the schedule for a particular month. The worksheets are named "Month 1", "Month 2" and "Month 3" etc. In each of the worksheet, a Gantt Chart is included to show the critical path of the project.




The diagram above shows the Gantt Chart generated in one of the output worksheets. The top left hand corner displays the month of this Gantt Chart. Each worksheet contains a unique month over a 12-months period. The next two rows show the days of week and days of the month of the Gantt Chart.

The following fields are automatically calculated using the Project Start Date, Items duration and dependencies.
  • Early Start Time - The earliest date that an activity can start.
  • Early End Time - The earliest date that an activity can be completed.
  • Late Start Time - The latest date that an activity has to start to avoid delaying the project.
  • Late End Time - The latest date that an activity has to complete to avoid delaying the project.
The darker shade of black shows activities on the critical path while the lighter shade of black shows activities not on the critical path. The "x" shows the holidays defined in the "Holidays (Input)" worksheet.

Printing

These spreadsheets can be printed quickly and easily in Landscape mode in Microsoft Excel. Simply set the "% to normal size" option to include all the fields in the worksheets.

To print several worksheets at once, simply click the tab for the first sheet in Excel, then hold down CTRL while you click the tabs of the other sheets that you want to select.

Get the Professional Project Management Package

 Benefits
  • Unlocked
  • Allows removal of copyright message in the template
  • Allows commercial use within the company
  • 12 months Critical Path Method Gantt Chart schedule
  • Independent Gantt Chart spreadsheet
  • Allows customization
  • Full source code
License PDF Specifications

Price

USD29.90 - Purchase

System Requirements

Microsoft® Windows 7, Windows 8, Windows 10 or Mac® OS X
512 MB RAM
5 MB of Hard Disk space
Excel 2007, 2010, 2013, 2016 or Office for Mac OS

See the Critical Path Method spreadsheet on Mac OS X


Back to Excel Templates and Add-Ins.