Pert method – excel

3 – 1Copyright © 2017 Pearson Education, Inc.

► 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

3 – 2Copyright © 2017 Pearson Education, Inc.

► 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

3 – 3Copyright © 2017 Pearson Education, Inc.

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

3 – 4Copyright © 2017 Pearson Education, Inc.

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

3 – 5Copyright © 2017 Pearson Education, Inc.

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

3 – 6Copyright © 2017 Pearson Education, Inc.

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

3 – 7Copyright © 2017 Pearson Education, Inc.

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

3 – 8Copyright © 2017 Pearson Education, Inc.

Probability of Project

Completion

PERT makes two more assumptions:

► Total project completion times follow a
normal probability distribution

► Activity times are statistically independent

3 – 9Copyright © 2017 Pearson Education, Inc.

Probability of Project

Completion

Standard deviation = 1.76 weeks

15 Weeks

(Expected Completion Time)

Figure 3.12

3 – 10Copyright © 2017 Pearson Education, Inc.

Probability of Project

Completion

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

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

3 – 11Copyright © 2017 Pearson Education, Inc.

Probability of Project

Completion

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

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

3 – 12Copyright © 2017 Pearson Education, Inc.

Probability of Project

Completion

Time

Probability

(T ≤ 16 weeks)

is 71.57%

Figure 3.13

0.57 Standard deviations

15 16

Weeks Weeks

3 – 13Copyright © 2017 Pearson Education, Inc.

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

3 – 14Copyright © 2017 Pearson Education, Inc.

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

3 – 15Copyright © 2017 Pearson Education, Inc.

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