Creating a Standard Indicator based on the Baseline

Some organizations know what Earned Value is and use it to calculate the performance of a project. Others do not. Default calculations (such as the Schedule Performance Index or SPI) depend on many other fields that not too many organizations use.

We’re often asked about a different (and easier) approach to measure time deviations that can also help Project Managers to get used to being measured in terms of the Baseline which is often used in a project approval process.

When creating a program schedule, one of the main tasks is to set the Baseline, which is “take a picture” of the project’s plan and use it as a framework of what the project should advance like. By setting the baseline, we’re copying some information like the start and finish dates of the tasks, their duration and some other information.

Let’s use a simple example of a project schedule. An implementation of Project Server in this case:

First of all, we need to set the baseline. Make sure that the start date for the entire project is in the past so you see tasks that already should have been finished. Then:

Go to Tools -> Tracking -> Set baseline

And set the first baseline for the entire project (default settings).

Just to make sure we have correctly established it, use the Tracking Gantt view (View -> Tracking Gantt) and we will see the Gantt Chart representing the actual tasks (colored tasks) and the baseline (gray tasks):

Now, we need to calculate a percentage of completion that uses the baseline to tell us what percentage of completion we should have achieved at the time of the consult.

When we saved the baseline, we copied the Start and Finish fields into Baseline Start and Baseline Finish fields so they don’t get updated while we’re working in our project plan. Those are the fields we’re going to use in our custom calculation.

Go to Tools -> Customize -> Fields in order to create the custom calculations and you’ll see the next dialog box:

As you can see, we can create custom calculations for Tasks, Resources and Projects (this last only if we have Project Server). The calculations we’re going to create are for tasks so make sure the task option is selected at the top.

We will call % planned the amount of work we should have achieved for each tasks and the result of the calculation is a number, so we will add a task number field.

Click on the type dropdown menu at the top right and select Number:

Then click on the first field (Number1) and click on Rename. Set the new name as % Planned and click Ok.

See that the list of custom fields has changed and % Planned has been added as custom name for field Number1:

Now we need to create a formula so that % Planned is calculated automatically but first we need to understand what we need to do:

If the current date (today) is less than the baseline start date, that means that the task should not have started yet. Therefore, % Planned should be 0. If the current date is more than the baseline finish date that means that the task should have been completed by now. Therefore, % Planned should be 100. That’s the easy part.

The complicated part is what happens in between. For that, we need to calculate how much time has elapsed from the beginning of the task and today and calculate how much time the task lasts.

For the previous calculations, we need to calculate the difference between dates and for that, we use the ProjDateDiff function which uses the project calendar (so we’re applying weekends and the labor time to the calculations) in addition to the two dates we need to calculate the difference from.

With that in mind, we can create the following formula:

IIf([Current Date]<[Baseline Start],0,IIf([Current Date]>[Baseline Finish],100,(ProjDateDiff([Baseline Start],[Current Date],[Project Calendar]))/(ProjDateDiff([Baseline Start],[Baseline Finish],[Project Calendar]))))

This formula determines where in time we should be and if we’re between 0 and 100%, it will calculate how much time has elapsed from the beginning of the task and divide it by the duration of the task.

As you can see, we’re depending on the baseline data so that the project manager has to set the baseline in order for this calculation to happen. You can enter the formula by typing everything or by selecting the fields and functions using the buttons for Field and Formula so you can click on them instead of typing.

Back on the Custom Fields dialog box, set the Calculation for task and group summary rows to Use formula, so we make sure every summary task use the same formula to calculate its % planned:

Now we will create a new field that shows us a graphical indicator depending on the difference between the % complete of the tasks and our % planned.

For that, create a new task field, but this time choose it to be a text field. Name it Custom Status:

Before we actually enter the formula for Custom Status, let’s determine the thresholds for the graphical indicators.

Let’s set, for example, that we want a green indicator when our tasks are doing fine or we are ahead the schedule. We want it to be yellow if the task has a delay up to 10% and red if the delay is over 10%. And let’s assume that the PMO has established that the names for those statuses are: On Schedule, Delayed and Risk respectively.

So our formula should be something like:

IIf([% complete]>=[% Planned],”On Schedule”,IIf([% Planned]-[% complete]<=10,”Delayed”,”Risk”))

This tells Project to calculate the difference between % complete and % Planned and set it to three different states: On Schedule, Delayed or Risk.

If you click on the Formula button again to see or verify the calculation, you will see that % Planned has been changed into Number1:

Why? Simply because Number1 is the internal name for that field, while % Planned is a custom name field we use to create the calculations.

Set the calculation for task and group summary rows to use the formula we have just created and now click on the Graphical Indicators button so we can tell Project that we want to see a picture instead the text of “On Schedule”, “Delayed” or “Risk”.

Here we will set the test, values and images like this:

Now select that for summary rows and project summary will inherit the same criteria as non summary rows. Click on Yes when Project asks if you want to remove all criteria from the list and replace it with the inherited criteria.

You should see this:

Now click Ok and back on the Custom Fields dialog box click Ok again. We’re almost done.

The last step is to view those fields and see how they work.

Add the two calculations we’ve just made into your view:

Right click the column that appears to the right of Finish and select Insert Column. From the Column Definition, select % Planned (Number1) from the Field name dropdown list. And click Ok. Do the same for adding % complete and Custom status.

You should view something like this.

As you can see, now there’s a graphical indicator that shows the schedule status of each task based on the baseline we established and you can see how good you’re doing in your project. Update the % complete column for some tasks and see when the indicator changes its color.

Posted By: René Alvarez