advertisement

URBS 609 PERT, Unit 2

Basic Techniques Using MS Excel

And Manual Calculation

T his 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

• 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

• 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

• 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 (T**

**O**

**) – best case scenario**

– **Most Likely (T**

**L**

**) “normal” scenario**

– **Most Pessimistic (T**

**P**

**) Worst case scenario**

• Formula derives a probability-based expected duration (T

E

**– ( T**

**O x 1 + T**

**L x 4 + T**

P x 1 **) / 6 = **T

E

– 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

• Standard deviation and variance

– Standard deviation (SD) is the average deviation from the estimated time

• SD=(T

P

-T

0

)/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=SD 2 (Standard deviation squared)

Urban and Regional Studies

Institute 6

• 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

**Start**

**Set up in tabular form, it might look like this…**

**7**

Buy Edging

**TASK ID**

1

**Description**

**Mark Utilities**

2

3

**Dig Holes**

**Buy Trees **

4

5

**Buy Flowers**

**Plant Trees**

6

7

8

**Plant Flowers**

**Buy Edging**

**Install Edging**

**Duration (Days)**

?

?

?

?

?

?

?

?

**3**

Buy Trees

**1 2**

Mark Utilities Dig Holes

**5**

Plant Trees

**6**

Plant Flowers

**8**

**Install Edging Finish**

**4**

Buy Flowers

**Set up in visual form it might look like this…**

Urban and Regional Studies

Institute 8

**In considering all tasks on the previous slide, a table might look like this**

**T**

**O**

**T**

**L**

**CRITICAL PATH TASKS (Longest Duration)**

**T**

**P**

**T**

**E**

**TASK**

**1**

**2**

**5**

**6**

**8**

**TOTAL**

**T**

**O**

**T**

**L**

**T**

**P**

**OTHER PROJECT TASKS**

**T**

**E**

**TASK**

**3**

**4**

**7**

**TOTAL**

T

O

-Optimistic T

M

-Likely T

P

**-Pessimistic T**

**E**

**-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

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

**TASK**

**1**

**2**

**5**

**6**

**8**

**TOTAL**

**TASK**

**3**

**4**

**7**

**TOTAL**

**1**

**1**

**7**

**T**

**O**

**1**

**2**

**1**

**T**

**O**

**.5**

**.5**

**.5**

**1.5**

**T**

**L**

**1**

**1**

**1**

**3**

**T**

**L**

**3**

**4**

**3**

**3**

**2**

**15**

**CRITICAL PATH TASKS (Longest Duration)**

**T**

**P**

**5**

**T**

**E**

**3**

**7 4.17**

**6 3.17**

**5 3**

**4 2.17**

**28 15.6**

**OTHER PROJECT TASKS**

**T**

**P**

**T**

**E**

**3 1.25**

**3 1.25**

**3 1.25**

**9 3.75**

**SD**

**.67**

**.83**

**.83**

**.67**

**.5**

**3.5**

**SD**

**.42**

**.42**

**.42**

**1.26**

**V**

**.44**

**.69**

**.69**

**.44**

**.25**

**2.51**

**V**

**.17**

**.17**

**.17**

**.51**

T

O

-Optimistic T

M

-Likely T

P

**-Pessimistic T**

**E**

**-Expected (Derived by PERT)**

SD=Standard Deviation V=Variance

Urban and Regional Studies

Institute 10

**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**

**T**

**O**

**1**

**2**

**1**

**1**

**1**

**7**

**T**

**L**

**3**

**4**

**3**

**3**

**2**

**15**

**CRITICAL PATH TASKS (Longest Duration)**

**T**

**P**

**5**

**7**

**6**

**5**

**T**

**3**

**E**

**4.17**

**3.17**

**3**

**ES**

**0**

**3**

**7**

**10**

**EF**

**3**

**7.17**

**10.17**

**13**

**LS**

**0**

**3**

**7**

**10**

**LF**

**3**

**7.17**

**10.17**

**13**

**4 2.17**

**13 15.17 13 15.17**

**28 15.51**

**OTHER PROJECT TASKS**

**Slack SD**

**0**

**0**

**0**

**0**

**0**

**.67**

**.83**

**.83**

**.67**

**.5**

**3.5**

**V**

**.444**

**.694**

**.694**

**.444**

**.254**

**2.530**

**TASK**

**3**

**4**

**7**

**TOTAL**

**T**

**O**

**.5**

**.5**

**.5**

**1.5**

**T**

**L**

**1**

**1**

**1**

**3**

**T**

**P**

**T**

**E**

**3 1.25**

**3 1.25**

**ES**

**0**

**0**

**EF**

**1.25**

**1.25**

**LS**

**3**

**3**

**LF**

**4.25**

**4.25**

**3 1.25**

**1.25**

**2.50 4.25 5.50**

**9 3.75**

**FLOAT**

**3**

**3**

**3**

**SD**

**.42**

**.42**

**.42**

**1.26**

**V**

**.17**

**.17**

**.17**

**.51**

ES=Earliest Start EF= Earliest Finish LS=Latest Start LF=Latest Finish

Urban and Regional Studies

Institute 11

**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

**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

**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

**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

**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

**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

**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

**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

**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

**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

**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

**• 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 crashing on the critical path with the lowest cost-for-

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**

**Frame**

**1**

**2**

**2**

**3**

**1**

**1**

**2**

**4**

**4000**

**8000**

**3000**

**4000**

**1**

**3**

**1000**

**4000**

**1000**

**1333 cost-per-week for crashing = crashed costs divided by time saved**

Urban and Regional Studies

Institute 31

**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

Urban and Regional Studies

Institute 32

• 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

• 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

• 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