|
I created the Critical Path Method (CPM) spreadsheet below mainly as a challenge to see whether the CPM / PERT algorithm could be implemented in Excel and not be too terribly difficult to use. I'm happy to say that it is indeed possible. Although Microsoft Project may be easier to use for Critical Path Analysis, if you follow the guidelines below, you may find our CPM spreadsheet quite handy. I was unable to come up with a good way to create a PERT Chart, but I did add a gantt chart to the CPM spreadsheet to highlight the critical path tasks and show the slack time for the flexible tasks.
The gantt chart in the Critical Path Method spreadsheet is very similar to what you'd see using Microsoft Project, except that I was unable to figure out a good way to add the lines with arrows pointing from the end of one task to the start of a successor. Unless you know how to do it, please don't ask me to add that feature.
Critical Path Method Spreadsheet
Screenshot
|
This spreadsheet implements the PERT algorithm and Critical Path Method to aid in project planning and scheduling.
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. Events are shown as diamonds.
Task dependencies are defined by simply listing the predecessors for each task.
"No Installation, No Macros - Just a simple spreadsheet"
|
 Download
Cost: $0.00
License: Personal and Company Use (Not for distribution or resale)
File Type: .xls
Size: ~100 KB
Version: 1.0
Required: Microsoft Excel® 2002(XP), 2003, or 2007
|
Disclaimer: This spreadsheet and the information on this page is for illustrative and educational purposes only. We do not guarantee the results.
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.
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 instead.
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 50 tasks (including the Start and Finish events).
- 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.
- 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).
- If you have problems using this spreadsheet, try Microsoft Project instead.
- You can list up to 6 predecessors by listing one ID per column.
More Project Management Tools
- Gantt Chart - Create a detailed project schedule.
- Timeline - Create a project timeline, showing events along an axis with leaderlines.
Critical Path Method & PERT Resources
- PERT at wikipedia.com - A very detailed explanation of how to implement the Program Evaluation and Review Technique, using the Critical Path Method.
- Triangular Distribution at wikipedia.com.
- Beta Distribution at wikipedia.com.
Sharing Our Critical Path Method Spreadsheet
If you like our CPM spreadsheet, you are welcome to link to this page from your website or blog. Just use the following URL:
http://www.vertex42.com/ExcelTemplates/critical-path-method.html
|
