PERT/CPM CALCULATIONS

1
PERT/CPM CALCULATIONS
URBS 609 PERT, Unit 2

• Basic Techniques Using MS Excel
• And Manual Calculation

2
About This Training Module

• This training module was crafted using
  PowerPoint by Microsoft Corporation. It has been
  packaged with PowerPoint Viewer, a standalone
  Microsoft product that allows a user to view this
  module without use of PowerPoint.

• Left mouse-click or enter to go to next slide
• Right mouse-click or backspace to go to previous
  slide
• ESC to exit this module

This Unit of Instruction was crafted by Robert
Hugg For Minnesota State University, Mankato
Urban and Regional Studies Institute - 2004
3
Training Module Preview

• This module will provide
• Introduction to manually calculating key Project
  Management functions (both PERT and CPM)
• Introduction to using MS Excel to calculate key
  functions (PERT and Risk analysis)
• Step-by step instruction on building a PERT risk
  analysis calculator using MS Excel
• Use of PERT and CPM traditional techniques to
  manually lay out a project
• This module is constructed as the second block in
  a building block approach

4
PERT Calculations - Simplicity

• Simple steps in a logical order
• Step 1 Define tasks
• Step 2 Place Tasks in a logical order, find the
  critical path
• The longest time path through the task network.
  The series of tasks (or even a single task) that
  dictates the calculated finish date
• Step 3 Generate estimates
• Optimistic, pessimistic, likely and PERT-
  expected
• Standard Deviation and variance
• Step 4 Determine earliest and latest dates
• Step 5Determine probability of meeting expected
  date
• Steps 1 and 2 are logic and legwork, not
  calculation these require a clear goal

5
PERT Calculations Step 3

• Assuming steps 1 and 2 have been completed begin
  calculations use a table to organize your
  calculations
• Simple calculations to estimate project durations
• Based on input of 3 estimated durations per task
• Most Optimistic (TO) best case scenario
• Most Likely (TL) normal scenario
• Most Pessimistic (TP) Worst case scenario
• Formula derives a probability-based expected
  duration (TE)
• (TO x 1 TL x 4 TP x 1) / 6 TE
• Read this formula as the sum of (optimistic x 1
  likely x 4 pessimistic x 1) divided by 6
  expected task duration
• Complete this calculation for all tasks

6
PERT Calculations Step 3

• Standard deviation and variance
• Standard deviation (SD) is the average deviation
  from the estimated time
• SD(TP-T0)/6 read as (pessimistic-optimistic)/6
• As a general rule, the higher the standard
  deviation the greater the amount of uncertainty
• Variance (V) reflects the spread of a value over
  a normal distribution
• VSD2 (Standard deviation squared)

7
PERT Calculations Step 3

• When doing manual PERT Calculations it is helpful
  to construct a table to stay organized
• Consider the sample project in Unit 1 planting
  trees and flowers, set up using a list
• Rough estimates and no risk analysis
• No Range, simply rough estimates - unreliable?
• PERT Analysis will better refine estimates
• Start by setting up a table to organize data

8
Our Project A Refresher
TASK ID Description Duration (Days)
1 Mark Utilities ?
2 Dig Holes ?
3 Buy Trees ?
4 Buy Flowers ?
5 Plant Trees ?
6 Plant Flowers ?
7 Buy Edging ?
8 Install Edging ?
Set up in tabular form, it might look like this
Set up in visual form it might look like this
9
PERT Step 3 First Get Organized
In considering all tasks on the previous slide, a
table might look like this
CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration)
TASK TO TL TP TE
1
2
5
6
8
TOTAL
OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS
TASK TO TL TP TE
3
4
7
TOTAL
TO-Optimistic TM-Likely TP-Pessimistic
TE-Expected (Derived by PERT)
Remember tasks 3, 4 and 7 are concurrent and do
not add to the timeline
10
PERT Step 3 Durations

• After generating estimates using the formula, the
  table might look like this

CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration)
TASK TO TL TP TE SD V
1 1 3 5 3 .67 .44
2 2 4 7 4.17 .83 .69
5 1 3 6 3.17 .83 .69
6 1 3 5 3 .67 .44
8 1 2 4 2.17 .5 .25
TOTAL 7 15 28 15.6 3.5 2.51
OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS
TASK TO TL TP TE SD V
3 .5 1 3 1.25 .42 .17
4 .5 1 3 1.25 .42 .17
7 .5 1 3 1.25 .42 .17
TOTAL 1.5 3 9 3.75 1.26 .51
TO-Optimistic TM-Likely TP-Pessimistic
TE-Expected (Derived by PERT) SDStandard
Deviation VVariance
11
PERT Step 4 Dates
For each task, determine the latest allowable
time for moving to the next task The difference
between latest time and expected time is called
slack time Tasks with zero slack time are on the
critical path
CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration) CRITICAL PATH TASKS (Longest Duration)
TASK TO TL TP TE TE ES EF LS LF LF LF Slack SD V V
1 1 3 5 3 3 0 3 0 3 3 3 0 .67 .444 .444
2 2 4 7 4.17 4.17 3 7.17 3 7.17 7.17 7.17 0 .83 .694 .694
5 1 3 6 3.17 3.17 7 10.17 7 10.17 10.17 10.17 0 .83 .694 .694
6 1 3 5 3 3 10 13 10 13 13 13 0 .67 .444 .444
8 1 2 4 2.17 2.17 13 15.17 13 15.17 15.17 15.17 0 .5 .254 .254
TOTAL 7 15 28 15.51 15.51 3.5 2.530 2.530
OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS OTHER PROJECT TASKS
TASK TO TL TP TE ES ES EF LS LS LF FLOAT FLOAT SD SD V
3 .5 1 3 1.25 0 0 1.25 3 3 4.25 3 3 .42 .42 .17
4 .5 1 3 1.25 0 0 1.25 3 3 4.25 3 3 .42 .42 .17
7 .5 1 3 1.25 1.25 1.25 2.50 4.25 4.25 5.50 3 3 .42 .42 .17
TOTAL 1.5 3 9 3.75 1.26 1.26 .51
ESEarliest Start EF Earliest Finish
LSLatest Start LFLatest Finish
12
PERT Step 5 Probabilities
Manually computing probability using data
compiled in your table

• Determine probability of meeting a date by using
  the table data
• Denote the sum of all expected durations on the
  critical path as S
• Denote the sum of all variances on the critical
  path as V
• Select a desired completion time, denote this as
  D
• COMPUTE (D-S)/square root (V) Z ( the number
  of std. deviations that the due date is away from
  the expected date))
• Enter a standard normal table to find a
  probability that corresponds with Z or go online
  to
• http//math.uc.edu/statistics/statbook/tables.htm
  l) to enter a z number - the application will
  retrieve the probability from the lengthy table
• For our project, figure a probability based on
  the most likely time, 15 days (15-15.51)/square
  root(2.53) (15-15.51)/1.59-.3207 (Z)
• A corresponding probability is 37.7 (Rounded)
• This process can be repeated for any date desired

13
PERT Step 5 Probabilities Computing
probability in Excel using data compiled in your
table

• Excel has normal distribution functions built in
  and can compute PERT probabilities
• By creating a table as a spreadsheet, the
  addition of a few simple formulae will do the
  rest of the work
• Create a table as a template that can be used
  over and over again simply change the input

14
PERT Step 5 Probabilities Computing
probability in Excel using data compiled in your
table
15
Constructing the Spreadsheet
Step 1 - Create a spreadsheet that resembles the
table used earlier
16
Constructing the Spreadsheet
Step 2 Use formulae as shown to calculate PERT
Expectations
17
Constructing the Spreadsheet Cell Formulae used
for PERT Analysis- expected durations

• Computing PERT Expected duration
• For each task cell (Optimistic 4x Typical
  Pessimistic)/6
• Adjust cell address for each task

18
Constructing the Spreadsheet
Step 3 Use formulae as shown to calculate
variances
19
Constructing the Spreadsheet Cell Formulae used
for PERT Analysis Variances

• Computing Variances
• For each task cell
• ((Pessimistic-Optimistic)/6)2
• Adjust cell address for each task

20
Constructing the Spreadsheet
Step 4 Use formulae as shown to calculate STD.
Deviations
21
Constructing the Spreadsheet Cell Formulae used
for PERT Analysis Standard Deviations

• Computing Standard Deviations
• For each task cell
• Square root of the variance for that task
• Adjust cell address for each task

22
Constructing the Spreadsheet
Step 5 Use formula as shown to sum PERT
expectations
23
Constructing the Spreadsheet Cell Formula used
for PERT Analysis Summing PERT Expectations

• Sum Pert Expectations using either auto-sum
  feature or sum formula

24
Constructing the Spreadsheet
Step 6 Use formula as shown to sum variances
25
Constructing the Spreadsheet Cell Formula used
for PERT Analysis Summing Variances

• Sum Variances using either auto-sum feature or
  sum formula

26
Constructing the Spreadsheet
Step 6 Use formula as shown to compute
probability
27
Constructing the Spreadsheet Cell Formula used
for PERT Analysis Completion Probability

• Excel uses a formula designed to compute the
  probability of placement of a combination of
  elements in a normal distribution very accurate
• NORMDIST(x,mean,standard_dev,cumulative)
• X   is the value for which you want the
  distribution (desired date)
• Mean  is the arithmetic mean of the distribution
  (summed PERT expected durations)
• Standard_dev   is the standard deviation of the
  distribution (square root of the summed
  variances)
• Cumulative  is a logical value that determines
  the form of the function. If cumulative is TRUE,
  NORMDIST returns the cumulative distribution
  function (probability of completion on the date
  entered)

28
Constructing the Spreadsheet Cell Formula used
for PERT Analysis Hints and Tips

• Be sure to adjust formulae as necessary when
  adding additional tasks
• If a error message shows up check cell addresses
  in the formulae first formulae must reflect
  intent
• This set of formulae mirrors the manual
  calculations but takes less time for the user
• Because PERT is a probabilistic approach, these
  formulae can deliver a 100 probability but no
  plan is perfect these are always estimates
• Never feel there is a 100 probability of a
  project completing on the estimated date

29
PERT Analysis Thoughts, Philosophy and Lessons
Learned

• All Plans are estimates and are only as good as
  the task estimates unrealistic estimates equal
  unrealistic plans
• If the scope of a plan changes, all estimates
  must change adding tasks equals added time and
  cost
• PERT Analysis is a good way to what if before a
  project is launched helps determine if it is
  needed at all
• What tasks will it take to do the project?
• What is the optimum order of the project tasks?
• How long will it take to do the project?
• How likely is the project to succeed?
• What if The Boss wants it earlier, what is the
  likelihood then?
• A great way to get organized and stay organized

30
CPM Analysis

• In comparison to PERT, CPM analysis is simple
• CPM Analysis is a series of easy steps
• Develop time and cost data ("normal" and
  "crashed") for all tasks
• Develop cost-per-week for crashing (crashed costs
  divided by time saved)
• Develop project network (PERT)
• Crash the activity on the critical path with the
  lowest cost-for-crashing
• Recalculate the project network (the critical
  path might change!)
• Repeat steps 4 5 until all the paths have been
  crashed.
• Ease up on all non-critical paths, just to the
  point that all paths are critical.

31
CPM Analysis

• A typical CPM table might have the following
  structure

Activity Begin End Time (Crashed) Time (Normal) Cost (Crashed) Cost (Normal) Time Saved Cost Increase Cost / Week
Foundation 1 2 1 2 4000 3000 1 1000 1000
Frame 2 3 1 4 8000 4000 3 4000 1333
cost-per-week for crashing crashed costs
divided by time saved
32
CPM Analysis Thoughts, Philosophy and Lessons
Learned

• All Plans are estimates and are only as good as
  the task estimates unrealistic estimates equal
  unrealistic plans
• If the scope of a plan changes, all estimates
  must change adding tasks equals added time and
  cost
• CPM Analysis is a good way to what if before a
  project is launched helps control expectations
• How much will it cost?
• How long will it take?
• How long will it take if it needs to be done
  sooner?
• How much will it cost if it needs to be done
  sooner?
• A great way to get organized and stay organized

33
Use PERT and CPM Together

• PERT CPM are totally complementary - both
  require the same preparation

1. Define the Project and all of its significant
   activities or tasks. The Project should have only
   a single start activity and a single finish
   activity.
2. Develop the relationships among the activities
   decide which activities must precede and which
   must follow others.
3. Draw a Network Diagram connecting all the
   activities (each activity should have a unique
   number).
4. Assign time and/or cost estimates to each
   activity.
5. Compute the longest time path through the
   network. (The critical path)
6. Use the Network to help plan, schedule, monitor
   control the project.

34
PM Calculations Overview

• PERT and CPM can be used together
• Calculations are based on a few simple formulae
• PERT Derived duration estimates
• Standard Deviation
• Variance
• Probability of meeting expectation
• Crash costs and time normal costs and time
• Calculations can be done manually or using Excel
  same formulae, different tools

35
Resources Used in This Unit

• Bonini, Charles, et al, Quantitative Analysis for
  Management, Columbus McGraw Hill, 1997
• Dr. Anthony Filipovitch
• Goldratt, Eli, Dr., The Goal A Process of
  Ongoing Improvement, Great Barrington New River
  Press, 1996
• Mednick, Barry, PERT-CPM on Excel,Fullerton Cal
  State, 2000
• MS Project, by Microsoft Corporation
• MS Excel, by Microsoft Corporation
• PM Body of Knowledge (PMBOK), Philadelphia PMI,
  2000

• Project Management Institute (PMI) Resource
  Center
• Project Management Institute Website
• ProjeX, by WAA, Inc
• Systema, Sid, Probabilistic Solutions to Project
  Scheduling, Ferris State, 1999
• US National Performance Survey, The Standish
  Group, 1998
• Verma, Vijay K., Managing the Project Team The
  Human Aspects of Project Management,
  Philadelphia PMI, 1997
• Wiest, Jerome D., and Levy, Ferdinand K., A
  Management Guide to PERT/CPM, New Delhi
  Prentice-Hall of India Private Limited, 1974

36

• You have completed
• URBS 609 PERT Unit 2
• Please proceed to
• URBS 609 Project Management Using MS Project
  Block

This Unit of Instruction was crafted by Robert
Hugg For Minnesota State University, Mankato
Urban and Regional Studies Institute - 2004