# Pert method – excel

Refer to the table 3-4 slide 3-5: Time estimates to the Project Paper.

Complete  the exercise using the provided formulas. Find the completion time  using PERT method as outlined on the slides. Use formulas NOT plugged-in numbers.

All necessary information and guidance are on attachments!

https://www.viddler.com/embed/ebd96754

► CPM assumes we know a fixed time
estimate for each activity and there is
no variability in activity times

► PERT uses a probability distribution for
activity times to allow for variability

Variability in Activity Times

► Three time estimates are required

► Optimistic time (a) – if everything goes
according to plan

► Pessimistic time (b) – assuming very
unfavorable conditions

► Most likely time (m) – most realistic
estimate

Variability in Activity Times

Estimate follows beta distribution

Variability in Activity Times

Expected activity time:

Variance of activity completion times:

t = (a + 4m + b)/6

v = [(b – a)/6]2

Expected activity time:

Variance of activity completion times:

t = (a + 4m + b)/6

v = [(b – a)/6]2

Estimate follows beta distribution

Variability in Activity Times

t = (a + 4m + b)/6

v = [(b − a)/6]2Probability of 1 in 100
of > b occurring

Probability of 1 in 100
of < a occurring

P
ro

b
a

b
ili

ty

Optimistic
Time (a)

Most Likely
Time (m)

Pessimistic
Time (b)

Activity
Time

Figure 3.11

Computing Variance

TABLE 3.4 Time Estimates (in weeks) for Milwaukee Paper’s Project

ACTIVITY
OPTIMISTIC

a

MOST

LIKELY
m

PESSIMISTIC
b

EXPECTED TIME
t = (a + 4m + b)/6

VARIANCE

[(b – a)/6]2

A 1 2 3 2 .11

B 2 3 4 3 .11

C 1 2 3 2 .11

D 2 4 6 4 .44

E 1 4 7 4 1.00

F 1 2 9 3 1.78

G 3 4 11 5 1.78

H 1 2 3 2 .11

Probability of Project

Completion

Project variance is computed by
summing the variances of critical
activities

s2 = Project variance

= (variances of activities
on critical path)

p

Probability of Project

Completion

Project variance is computed by
summing the variances of critical
activitiesProject variance

s2 = .11 + .11 + 1.00 + 1.78 + .11 = 3.11

Project standard deviation

sp = Project variance

= 3.11 = 1.76 weeks

p

Probability of Project

Completion

PERT makes two more assumptions:

► Total project completion times follow a
normal probability distribution

► Activity times are statistically independent

Probability of Project

Completion

Standard deviation = 1.76 weeks

15 Weeks

(Expected Completion Time)

Figure 3.12

Probability of Project

Completion

What is the probability this project can
be completed on or before the 16 week

Z = – /sp

= (16 weeks – 15 weeks)/1.76

= 0.57

Due Expected date
date of completion

Where Z is the number of
standard deviations the due

date or target date lies from the
mean or expected date

Probability of Project

Completion

What is the probability this project can
be completed on or before the 16 week

Z = − /sp

= (16 wks − 15 wks)/1.76

= 0.57

due expected date
date of completion

Where Z is the number of
standard deviations the due

date or target date lies from the
mean or expected date

.00 .01 .07 .08

.1 .50000 .50399 .52790 .53188

.2 .53983 .54380 .56749 .57142

.5 .69146 .69497 .71566 .71904

.6 .72575 .72907 .74857 .75175

From Appendix I

Probability of Project

Completion

Time

Probability

(T ≤ 16 weeks)

is 71.57%

Figure 3.13

0.57 Standard deviations

15 16

Weeks Weeks

Determining Project

Completion Time

Probability
of 0.01

Z

Figure 3.14

From Appendix I

Probability
of 0.99

2.33 Standard
deviations

0 2.33

Variability of Completion Time

for Noncritical Paths

► Variability of times for activities on
noncritical paths must be considered
when finding the probability of
finishing in a specified time

► Variation in noncritical activity may
cause change in critical path

What Project Management Has

Provided So Far

1. The project’s expected completion time is
15 weeks

2. There is a 71.57% chance the equipment
will be in place by the 16 week deadline

3. Five activities (A, C, E, G, and H) are on
the critical path

4. Three activities (B, D, F) are not on the
critical path and have slack time

5. A detailed schedule is available

## Sheet1

 Sample ex. Example Time Estimates (in weeks) for a Project / Cost estimates in \$ ACTIVITY OPTIMISTIC MOST LIKELY PESSIMISTIC EXPECTED TIME VARIANCE a m b t = (a + 4m + b)/6 [(b – a)/6]2 A 1 2 3 2 0.1111111111

## Sheet2

 3 pt estimates PERT Method Budget average variance O M P (O+4*M+P)/6 (p-o)/6 ((p-o)/6)^2 rent A 1000 1300 2000 1366.6666666667 166.6666666667 27777.7777777778 utilities B 100 125 170 128.3333333333 11.6666666667 136.1111111111 parking spot C 40 50 75 52.5 5.8333333333 34.0277777778 1547.5 27947.9166666667 Var 167.1763041423 St dev σ estimated cost 1547.5 27947.9166666667 Variance Sum of the errrors from the mean Average 167.1763041423 Std Dev z = x- u/sigma Z value z = x-u/sigma X z*sigma = x- u z= 1.64 2.7388 x- 1547.5 1550.2388 X

## Sheet4

 Probabilities 2200 5 0.1351351351 A 1200 7 0.1891891892 B 1500 10 0.2702702703 C 1800 15 0.4054054054 37 1 1

## Sheet3

Rent Rent Rent
1500
2300 Mean 2193.75 Mean 2068.75
1850 Standard Error 200.5433467572 Standard Error 194.3251830971
3200 Median 2100 Median 1875
1700 Mode ERROR:#N/A Mode 1700
1900 Standard Deviation 567.2222416554 Standard Deviation 549.6346188931
2400 Sample Variance 321741.071428571 Sample Variance 302098.214285714
1700 Kurtosis -0.2454172284 Kurtosis 1.8521411061
Skewness 0.6662596684 Skewness 1.3729851424
Range 1700 Range 1700
Minimum 1500 Minimum 1500
Maximum 3200 Maximum 3200
Sum 17550 Sum 16550
Count 8 Count 8
Confidence Level(95.0%) 474.2096612376 Confidence Level(95.0%) 459.5060406466

1

PERT Tutorial:

Suppose that you are a given the responsibility to manage a project and need to develop a budget
forecast for the project, with the intention of submitting a budget request to your supervisor for
approval. You have many line items in your forecast. These include deterministic items such as
building rent, insurance, etc., and many probabilistic line items such as payroll, bonus payments, travel
expenses, etc. Therefore, you must develop a budget based on a probabilistic approach and use
statistics. Suppose your budget forecast is normally distributed, with a mean of \$300,000, and a
standard deviation of \$10,000.

Figure 4-21

If you wanted to submit a budget for which you were 95% confident you would be able to make that

budget, your calculations would be something like below.

zσ = x – μ

zσ + μ = x
(1.645) (\$10,000) + \$300,000 = x

\$16,450 + \$300,000 = x
\$316,450 = x

Therefore, the budget submission would be for \$316,450 – because there is a 95% probability that the
project team will spend no more than that amount.

The question now becomes, “how did we get a normal distribution with a mean of \$300,000 and a
standard deviation of \$10,000 in the first place?

Refer to the following spreadsheet. The first four columns show eight-line items of expenses (“Team

2

salaries,’ “Office rent,” “Travel expenses,” etc.). For each item, three forecasts are made for those
expenses. The first, the “Optimistic” is the best-case scenario in terms of favorability of expenses (i.e.,
the least cost forecast if that situation arises). The second, “Most Likely,” is the realistic case scenario.
The third, “Pessimistic,” represents the worst-case cost scenario. For “Team salaries,” the project
manager believes that the size of the project team is probabilistic as some people may quit, the team
may remain intact for the duration of the project, or unanticipated needs may arise and additional
people may be hired. So, the least possible salary expense would be \$222 thousand, the most probable
expense would be \$242 thousand, and the worst case situation would be \$247 thousand.

Office rent is determined by contract, so it is deterministic. Each case of the three scenarios will be
forecast at \$9 thousand – the contracted amount.

All other line items are probabilistic, and their forecasts are so entered (in \$thousands).

3

1 2 3 4 5 6 7

Expense Optimistic Most Likely Pessimistic (a + 4m + b)/6 (b-a)/6 Variance

Team salaries 222 242 247 239.50 4.17 17.36

Office rent 9 9 9 9.00 0.00 0.00

Travel expenses 8.4 18 17.9 16.38 1.58 2.51

Training expenses 0 6 9 5.50 1.50 2.25

IT maintenance share 3 4 4.7 3.95 0.28 0.08

Performance awards 0 19.6 49 21.23 8.17 66.69

Office supplies 0.1 1.27 1.4 1.10 0.22 0.05

Unplanned software 0 0 20 3.33 3.33 11.11

300.00

100.05 sum of the variances
10.00 square root of (sum of the variances)

Figure 4-22

PERT says that if the number of forecast line items is large (probably at least 30), then we are in the
process of building a normal distribution curve of forecast costs. For this academic example, simulate
that with just eight line items.

The next step is to calculate the mean of this curve. PERT says that the mean of each line item is the
weighted average of the sum of the Optimistic value, 4 times the Most Likely value, and the Pessimistic
value. This represents six weights, so divide that sum by 6 to obtain the weighted average. The
formula is below.

(1*Optimistic + 4* Most Likely + 1 * Pessimistic)
6

This is often abbreviated as follows.
(a + 4m + b)

6

For the first line item, “Team salaries,” the formula calculates as follows.

(1*222 + 4* 242 + 1 * 247)
6

or, 239.5. This is shown in the fifth column of Figure 4-22. All the rest of the calculations for the line
items are in the fifth column. The total of these means is the mean of the budget normal distribution
curve, or 300.00 – shown at the bottom of the fifth column.

To calculate the standard deviation of this curve, several steps are necessary. When forecasting the
Optimistic and Pessimistic values for each line item, ensure that you are at least 99% sure that the final
value will be within this range. In the “Team salaries” line item example, the forecaster is over 99%

4

certain that the actual amount spent on “Team salaries” will be within the range of \$222 and \$247
thousand. Put another way, PERT says that there must be 6 standard deviations between the
Optimistic and Pessimistic values. Therefore, one standard deviation is 1/6th of the distance between
the Optimistic and Pessimistic values, or,

Pessimistic – Optimistic

6

which is often formulated as follows.

b – a

6

5

The standard deviation for “Team salaries” is therefore

247 – 222
6

or, \$4.17 thousand. This is entered in Figure 4-22 in column 6.

The standard deviations of each of the other line items are also calculated this way
and are entered in 4-22 also.

Statistical theory says that one cannot add standard deviations to obtain the
standard deviation of the budget, but one can use a procedure which incorporates
adding variances. Convert each standard deviation to as associated variance by
squaring its value, and place that value in column 7. For example, the square of
the standard deviation for “Team salaries” (here rounded to 4.17) is (rounded to)
17.36.

The total sum of the variances is shown as 100.05. Compute the square root of
100.05, 10.00, which is the standard deviation for the budget – or put another way,
is the standard deviation of this normal distribution curve.

From here, perform z-calculations as appropriate.

6

0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09

0.0 0.0000 0.0040 0.0080 0.0120 0.0160 0.0199 0.0239 0.0279 0.0319 0.0359

0.1 0.0398 0.0438 0.0478 0.0517 0.0557 0.0596 0.0636 0.0675 0.0714 0.0753

0.2 0.0793 0.0832 0.0871 0.0910 0.0948 0.0987 0.1026 0.1064 0.1103 0.1141

0.3 0.1179 0.1217 0.1255 0.1293 0.1331 0.1368 0.1406 0.1443 0.1480 0.1517

0.4 0.1554 0.1591 0.1628 0.1664 0.1700 0.1736 0.1772 0.1808 0.1844 0.1879

0.5 0.1915 0.1950 0.1985 0.2019 0.2054 0.2088 0.2123 0.2157 0.2190 0.2224

0.6 0.2257 0.2291 0.2324 0.2357 0.2389 0.2422 0.2454 0.2486 0.2517 0.2549

0.7 0.2580 0.2611 0.2642 0.2673 0.2704 0.2734 0.2764 0.2794 0.2823 0.2852

0.8 0.2881 0.2910 0.2939 0.2967 0.2995 0.3023 0.3051 0.3078 0.3106 0.3133

0.9 0.3159 0.3186 0.3212 0.3238 0.3264 0.3289 0.3315 0.3340 0.3365 0.3389
1.0 0.3413 0.3438 0.3461 0.3485 0.3508 0.3531 0.3554 0.3577 0.3599 0.3621

1.1 0.3643 0.3665 0.3686 0.3708 0.3729 0.3749 0.3770 0.3790 0.3810 0.3830

1.2 0.3849 0.3869 0.3888 0.3907 0.3925 0.3944 0.3962 0.3980 0.3997 0.4015

1.3 0.4032 0.4049 0.4066 0.4082 0.4099 0.4115 0.4131 0.4147 0.4162 0.4177

1.4 0.4192 0.4207 0.4222 0.4236 0.4251 0.4265 0.4279 0.4292 0.4306 0.4319

1.5 0.4332 0.4345 0.4357 0.4370 0.4382 0.4394 0.4406 0.4418 0.4429 0.4441

1.6 0.4452 0.4463 0.4474 0.4484 0.4495 0.4505 0.4515 0.4525 0.4535 0.4545

1.7 0.4554 0.4564 0.4573 0.4582 0.4591 0.4599 0.4608 0.4616 0.4625 0.4633

1.8 0.4641 0.4649 0.4656 0.4664 0.4671 0.4678 0.4686 0.4693 0.4699 0.4706

1.9 0.4713 0.4719 0.4726 0.4732 0.4738 0.4744 0.4750 0.4756 0.4761 0.4767

2.0 0.4772 0.4778 0.4783 0.4788 0.4793 0.4798 0.4803 0.4808 0.4812 0.4817

2.1 0.4821 0.4826 0.4830 0.4834 0.4838 0.4842 0.4846 0.4850 0.4854 0.4857
2.2 0.4861 0.4864 0.4868 0.4871 0.4875 0.4878 0.4881 0.4884 0.4887 0.4890

2.3 0.4893 0.4896 0.4898 0.4901 0.4904 0.4906 0.4909 0.4911 0.4913 0.4916

2.4 0.4918 0.4920 0.4922 0.4925 0.4927 0.4929 0.4931 0.4932 0.4934 0.4936

2.5 0.4938 0.4940 0.4941 0.4943 0.4945 0.4946 0.4948 0.4949 0.4951 0.4952

2.6 0.4953 0.4955 0.4956 0.4957 0.4959 0.4960 0.4961 0.4962 0.4963 0.4964

2.7 0.4965 0.4966 0.4967 0.4968 0.4969 0.4970 0.4971 0.4972 0.4973 0.4974

2.8 0.4974 0.4975 0.4976 0.4977 0.4977 0.4978 0.4979 0.4979 0.4980 0.4981

2.9 0.4981 0.4982 0.4982 0.4983 0.4984 0.4984 0.4985 0.4985 0.4986 0.4986

3.0 0.4987 0.4987 0.4987 0.4988 0.4988 0.4989 0.4989 0.4989 0.4990 0.4990

These are some commonly used z-values.

One Tail Two Tail

90% 1.282 1.645
95% 1.645 1.96

99% 2.325 2.575 