# Pert method – excel

## If you are looking for affordable, custom-written, high-quality, and non-plagiarized papers, your student life just became easier with us. We are the ideal place for all your writing needs.

Order a Similar Paper Order a Different Paper

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!

tutorial link

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

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

## Are you stuck with another assignment? Use our paper writing service to score better grades and meet your deadlines. We are here to help!

Order a Similar Paper Order a Different Paper