Project Management

Three-point estimating for work values

MS Project offers three-point estimating for Duration values through PERT. This short VBA routine lets you do three-point estimating on Work values.


Three-point estimating helps project managers make better estimates. Instead of merely coming up with a ballpark figure, managers using three-point estimating gain more granular control of how the end value is calculated. With three-point estimating, the end value is the weighted average of the estimates.

Project managers have been exposed to this technique through PERT (Program, Evaluation, and Review Technique) three-point estimating for Duration values. Microsoft Project gives us access to PERT estimating via the Analysis toolbar.

PERT uses three estimates—Optimistic, Pessimistic, and Most Likely—to derive the duration of a task. PERT asks a project manager to give each of these estimates a weight, which when taken together must add up to 6. You can think of the weight as a measure of confidence in that estimate. Figure A contains three estimates and a weight for each.
Figure A

Op
Work

Most Likely Work
Pess Work
Op Weight
Most Likely Weight
Pess Weight
2
10
25
0
3
3

This particular weighting shows one of the advantages of three-point estimates. This project manager has decided to compensate for some uncertainty or risk in the estimates by giving no weight to the Optimistic estimate and nearly equal weight to the Most Likely and Pessimistic values. Because the ML and Pess weights are nearly equal, the end value will be close to the average of the Most Likely and Pessimistic values. An even more pessimistic assessment might give the Most Likely value a weight of 1 or 2 and the Pessimistic value a weight of 4 or 5.

Using the weights, the project manager can have more control over how the estimates are used to get the value. A very confident estimate might have a weight of 5 on the Optimistic estimate and 1 on Most Likely. Such optimism might be appropriate for a task that has been completed several times before and has always taken the same amount of time to complete.

The basic formula for such a weighted average is as follows:

((Op * Op Weight) + (Pess * Pess Weight) + (ML * ML Weight)) / 6

Estimating work values
You can see the benefits of using three-point estimates in your scheduling. But what if you tend to estimate work values instead of durations? Microsoft Project does not have built-in functionality for supporting this kind of “Work PERT” estimating. But one of the cool things about Project is that with a little work in VBA (Visual Basic for Applications), you can make it do almost anything. A very short VBA routine can provide “Work PERT” functionality. It uses six custom number fields (Number1 – Number6) and a custom Text field (Text1). Figure B shows how the fields should appear.
Figure B
Custom field Field title
Number 1 Op. Work
Number 2 ML Work
Number 3 Pess. Work
Number 4 Op Weight
Number 5 ML Weight
Number 6 Pess. Weight
Text 1 Work PERT Status

You can choose to enter these fields in an existing view or create a new one for them. You can arrange them as you like, but remember which weight goes with which estimate.

One of the advantages of this tool is that it lets you set different weights for each task. In Project’s normal PERT tool, you must pick one set of weights for the entire plan. But sometimes you might have different levels of confidence in the estimates for different tasks. With the “Work PERT” tool you can set weights on a task-by-task basis.

Once you’ve set the weights and entered the estimates, all that’s left to do is run the macro shown in Listing A. It will run only for tasks that haven’t yet started. For each task it runs through, it will place a status in the Text1 field. For tasks where the Work value is calculated, it will enter the date the value was set. For completed or in-progress tasks, the macro will enter “Not Calc'd: Task In Progress or Complete.”

If the weights don’t add up to six, the macro will return a message in the Text1 field stating that there was a problem with the weights. If you see this note, you should fix the weights and re-run the macro.

 

Editor's Picks