Transcript PERT / CPM Calculations - Krypton

URBS 609 PERT, Unit 2
PERT/CPM CALCULATIONS
Basic Techniques Using MS Excel
And Manual Calculation
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
Urban and Regional Studies
Institute
2
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
Urban and Regional Studies
Institute
3
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 5:Determine probability of meeting expected date
• Steps 1 and 2 are logic and legwork, not calculation –
these require a clear goal
Urban and Regional Studies
Institute
4
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
Urban and Regional Studies
Institute
5
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
• V=SD2 (Standard deviation squared)
Urban and Regional Studies
Institute
6
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
Urban and Regional Studies
Institute
7
Our Project – A Refresher
TASK ID
Set up in tabular form, it
might look like this…
7
Description
1
Mark Utilities
2
Dig Holes
3
Buy Trees
4
Buy Flowers
5
Plant Trees
6
Plant Flowers
7
Buy Edging
8
Install Edging
Buy Edging
Duration (Days)
?
?
?
?
?
?
?
?
3
Buy Trees
1
Start
Mark Utilities
2
5
Dig Holes
Plant Trees
4
Buy Flowers
6
Plant Flowers
8
Install Edging
Finish
Set up in visual form it might
look like this…
Urban and Regional Studies
Institute
8
PERT Step 3– First Get Organized
In considering all tasks on the previous slide, a table might look like this
TASK
1
2
5
6
8
TOTAL
TASK
3
4
7
TOTAL
TO
TO
TO-Optimistic
CRITICAL PATH TASKS (Longest Duration)
TL
TP
TE
TL
TP
TM-Likely
OTHER PROJECT TASKS
TE
TP-Pessimistic TE-Expected (Derived by PERT)
Remember – tasks 3, 4 and 7 are concurrent and do not add to the timeline
Urban and Regional Studies
Institute
9
PERT Step 3– Durations
After generating estimates using the formula, the table might look like this
TASK
1
2
5
6
8
TOTAL
TO
1
2
1
1
1
7
TASK
3
4
7
TOTAL
TO
.5
.5
.5
1.5
TO-Optimistic
CRITICAL PATH TASKS (Longest Duration)
TL
TP
TE
3
5
3
4
7
4.17
3
6
3.17
3
5
3
2
4
2.17
15
28 15.6
OTHER PROJECT TASKS
TL
TP
TE
1
3
1.25
1
3
1.25
1
3
1.25
3
9
3.75
TM-Likely
SD=Standard Deviation
SD
.67
.83
.83
.67
.5
3.5
V
.44
.69
.69
.44
.25
2.51
SD
.42
.42
.42
1.26
V
.17
.17
.17
.51
TP-Pessimistic TE-Expected (Derived by PERT)
V=Variance
Urban and Regional Studies
Institute
10
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
TASK
1
2
5
6
8
TOTAL
TO
1
2
1
1
1
7
TL
3
4
3
3
2
15
TASK
3
4
7
TOTAL
TO
.5
.5
.5
1.5
TL
1
1
1
3
ES=Earliest Start EF=
CRITICAL PATH TASKS (Longest Duration)
TP
TE
ES
EF
LS
LF
Slack
5
3
0
3
0
3
0
7
4.17
3
7.17
3
7.17
0
6
3.17
7
10.17 7
10.17
0
5
3
10
13
10
13
0
4
2.17
13 15.17 13 15.17
0
28 15.51
OTHER PROJECT TASKS
TP
TE
ES
EF
LS
LF
FLOAT
3
1.25
0
1.25
3
4.25
3
3
1.25
0
1.25
3
4.25
3
3
1.25 1.25 2.50 4.25 5.50
3
9
3.75
Earliest Finish
LS=Latest Start
Urban and Regional Studies
Institute
SD
.67
.83
.83
.67
.5
3.5
V
.444
.694
.694
.444
.254
2.530
SD
.42
.42
.42
1.26
V
.17
.17
.17
.51
LF=Latest Finish
11
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.html) 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
Urban and Regional Studies
Institute
12
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
Urban and Regional Studies
Institute
13
PERT Step 5 – Probabilities
Computing probability in Excel using data compiled in your table
Urban and Regional Studies
Institute
14
Constructing the Spreadsheet
Step 1 - Create a spreadsheet that resembles the table used earlier
Urban and Regional Studies
Institute
15
Constructing the Spreadsheet
Step 2 – Use formulae as shown to calculate PERT Expectations
Urban and Regional Studies
Institute
16
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
Urban and Regional Studies
Institute
17
Constructing the Spreadsheet
Step 3 – Use formulae as shown to calculate variances
Urban and Regional Studies
Institute
18
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
Urban and Regional Studies
Institute
19
Constructing the Spreadsheet
Step 4 – Use formulae as shown to calculate STD. Deviations
Urban and Regional Studies
Institute
20
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
Urban and Regional Studies
Institute
21
Constructing the Spreadsheet
Step 5 – Use formula as shown to sum PERT expectations
Urban and Regional Studies
Institute
22
Constructing the Spreadsheet
Cell Formula used for PERT Analysis – Summing PERT Expectations
• Sum Pert Expectations using either autosum feature or sum formula
Urban and Regional Studies
Institute
23
Constructing the Spreadsheet
Step 6 – Use formula as shown to sum variances
Urban and Regional Studies
Institute
24
Constructing the Spreadsheet
Cell Formula used for PERT Analysis – Summing Variances
• Sum Variances using either auto-sum
feature or sum formula
Urban and Regional Studies
Institute
25
Constructing the Spreadsheet
Step 6 – Use formula as shown to compute probability
Urban and Regional Studies
Institute
26
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)
Urban and Regional Studies
Institute
27
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
Urban and Regional Studies
Institute
28
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
Urban and Regional Studies
Institute
29
CPM Analysis
• In comparison to PERT, CPM analysis is simple
• CPM Analysis is a series of easy steps
1. Develop time and cost data ("normal" and "crashed") for all
tasks
2. Develop cost-per-week for crashing (crashed costs divided by
time saved)
3. Develop project network (PERT)
4. Crash the activity on the critical path with the lowest cost-forcrashing
5. 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.
Urban and Regional Studies
Institute
30
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
Urban and Regional Studies
Institute
31
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
How
How
How
much will it cost?
long will it take?
long will it take if it needs to be done sooner?
much will it cost if it needs to be done sooner?
• A great way to get organized and stay organized
Urban and Regional Studies
Institute
32
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.
Urban and Regional Studies
Institute
33
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
Urban and Regional Studies
Institute
34
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
Urban and Regional Studies
Institute
35
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
Urban and Regional Studies
Institute
36