Creating a Standard Indicator based on the Baseline

Avatar

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:

Project Schedule

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_

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):

Tracking
Gantt Chart

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:

Custom
Fields

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_:

Create a
New Number Field

Then click on the first field (_Number1_) and click on _Rename_. Set the new
name as **% Planned** and click _Ok_.

Percent
Planned

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

List
Number Fields

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.

Percent Planned Formula

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:

Percent
Planned Custom Field

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**:

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**. Custom Status Forumla

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

Custom Status Formula

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”.

Creating Graphical Indicators

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

Graphical Indicators Created

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:

Graphical Indicators Dimmed

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.

New View with
Indicators

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.

About
Showing 5 comments
  • Avatar
    Barbara Moreira

    Hello René,

    Can you please explain to me what is the diference between this formula:

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

    and this one:

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

    Thanks,

    Bárbara

  • Avatar
    Rene Alvarez

    Barbara,

    Actually, there’s no difference between them since Baseline Duration is calculated as the difference beteween Baseline finish and Baseline start. However, if you need to set another calendar for this calculation, it’s easier to do so with the first one.

    Hope it helps.

  • Avatar
    Laurence Vanlede

    Hello,

    Thank you for the formula to enhance % complete (or at least to give more indications about what the progress should be).

    Nevertheless, I’m a bit bummed because of the results… I give you an example:

    ‘Works’ has
    – start date 25/04/2014
    – finish date 9/07/2014
    – baseline finish: 9/07/2014
    –> the task is late because only ‘35%’ completed at status date 31/07/2014.

    And it still shows ‘at risk’?? It should be ‘delayed’ or ‘late’, no??

    How can I improve the formula?
    Is there also a way of including the status ‘completed’?

    Thanks a lot for your help and I hope to hear from you very soon.

    Laurence

  • Avatar
    Dave Bush

    Great post Rene! Thanks! Beware there is a typo is in the text version of the “% Planned” formula. The formula in the screen cap is correct, but in the text version the second “less-than” should be a “greater-than”

  • Avatar
    Jeff Jacobson-Swartfager

    Thanks, Dave! We’ve updated the text formula.

Leave a Comment

Advisicon is a Project, Program & Portfolio Management Company. We transform your organization's project management with a mix of methodology and technology that delivers results. Our team specializes in technology implementations, application and workflow development, training and consulting.
5411 NE 107th Ave, Suite 200
Vancouver
WA
98662
United States