ADVANCED EXCEL

1 PERT Tutorial:

Suppose that you are a given the responsibility to manage a project an d 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 L ikely,” 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 deviation s 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