08 Apr Earned Value Shenanigans

<homebrew, tea, wine, coffee and several other beverages – this blog was written over several days!>

I’m currently helping manage a multiyear schedule and the client wishes to plot Planned Value vs Earned Value charts each month as a way of tracking “progress”.  This is a typical scenario in engineering projects, and is even mandatory in some publically funded projects which exceed a certain financial value.

I inherited the project schedule, and it’s the usual mixture of real milestones, some resource loaded activities, many more non-resource loaded activities, milestones with costs associated to represent payment milestones (and hence a cost to the project), and some long running activities to apportion expenses to.  So, not all tasks have work associated with them, and not all resources had costs either.  In many respects it is typical of the way we build schedules, they are built to represent a schedule, “normal” milestones and payment milestones, and if it’s built right, Earned Value “pops” out of the other end.  Alas, the schedule I inherited proved not the best basis to perform EV analysis on, and after spending some time on this, the old adage of preparation is key holds true.  Let me share with you my goal, and what I learnt along the way.

My Goal

This was a really simple goal, I wanted to update the project each month, and then produce the report by using the Earned Value Over Time Report; ultimately my customer wanted to produce a graph with EV and PV.  This is all provided out of the box as part of the Visual Reports functionality built into Project 2007/2010/2013.

So, what are the issues?

1. Long duration tasks.

So, what is a long duration?  In our instance we prorater a set of expenses (think of office and equipment costs) over the lifetime of the project, and because ours is a multi year project, the length of several tasks is nearly a thousand working days. We set this up as a manually scheduled task, and we end up with a cost per day.  Now, let’s say I take the status at the end of day 1, I should have 1 day of Actual Cost on my Project.  Let’s check this out.

1. I’ve set up Task ID1 with 967 working days, with a total cost of £48,350, which works out at £50 per day.  You can see this in the area(s) highlighted with (1) in the image below.  Note that I’ve split the screen so you can see both the Gantt chart and the Task Usage view.  I’ve added the relevant columns to both views.  Note that there is no work on this task, just cost.

2. I’ve setup Task ID2 with 200 working days, with a total cost of £10,000, which works out at £50 per day.  You can see this in the area(s) highlighted with (2) in the image below.

3. I setup the Status date to the end of the 1st working day.  This isn’t what you’d do in real life of course, you’d probably update the project every month, but I wanted to make sure that the EV works, and the simplest way to do this is on a daily basis.  You can see the Status Date marked with the red line on the Gantt Chart at (3) in the image.

4. The next job is to baseline the project, and then update the tasks.  For this I’ll just use the Mark on Track button.  For task ID 1, completing day 1 of a 967 day task equates to 0.15%, and this displays as 0% as the %complete (1), and the Actual Cost is displayed in the task column (2) as zero, but the timephased data is correct (3), displaying the Actual cost as £50.   Cost variance is £50 which is incorrect.

For task ID 2, completing day 1 of a 200 day task equates to 0.5, which rounded up is displayed as 1%, but the Actual Cost is displayed as £100 in the task column(2), but the timephased data is correct (3), displaying the Actual Cost and EV (BCWP) as £50.  Cost Variance is -£50 which is incorrect.  The Actual Cost is £100 because 1% of £10,000 is £100.

So, what to do, neither are correct!  We can edit the % complete directly and get the right cost value.  Try editing %complete to 0.5, it rounds to 1%, but notice what happens to the Gantt Chart.  CV is right though at £0.

Given that EV uses by default % complete, we need tasks that are <=100  days to get a sensible value.  Once we re-programme Task ID2 to 100 days, and use the Mark on Track button, everything works okay 🙂

Conclusion – review the task lengths and set appropriately.

2. Do not assign resources to milestones.

This makes sense, but not everyone follows the rule.  A milestone has zero work, and this messes up the EV calculation.  Have a look at the image below – I’m displaying two windows, the top one shows a resource (Ben), and the bottom window is split.  The cost for the Milestone is set to £1000, and the cost for the resource is ignored (because a milestone is zero hours long and therefore has zero work and zero resource cost). When the %complete is updated, the timephased BCWP (EV) data is not updated, and therefore CV is incorrect.

Conclusion – don’t assign resources to milestones.

3. Scheduling Milestones to start on Non-Working days.

Since the introduction of Manually Scheduled tasks, this is a possibility.  The image below shows this, notice again how the timephased EV data is incorrect when the milestone is scheduled during non-working time.  Fix this by moving the milestone (but see 4 below).

4. Milestones that finish at 17:00 (or the end of the calendar day).

For milestones that start and finish at the end of the day, timephased EV data is not calculated correctly.  This is an issue as milestones that recognise a cost are often linked with a FS relationship to other tasks – in the image below both M1 and M2 have costs associated with them, but M1 is dependent on T1 finishing; however you can see the relevant timephased data in the lower window of the Project image below.

What you can do instead is set a lead time on the dependency, in this case –1d, and everything works okay; the milestone still has the same date, it just starts at 08:00 instead of 17:00.  The image below shows this.

The issue with this is that anything that is driven by the milestone will also start at 08:00 on the same day, and so the trick is to set the duration of the milestone to be 1 day, and set it as a milestone manually.  See the image below.

5. Setting Mark on Track might not mark everything “correctly”.

During the testing of EV on my real multiyear project, I would set the status date to the very last day of the project, highlight all tasks, and click the Mark on Track button.  Several tasks only reached 99% complete, and not 100% as you would expect.  Using a selective filter to find these tasks was easy to do so that I could then manually set them to 100%.  The EV values then calculated correctly.

Exporting the tasks to Excel and checking everything works.

Before you start updating tasks, it’s worth setting the status date to the end of the project, then setting everything to “Mark on Track”.  Export the EV data (PV and EV at least) and check that both sets of values are the same.  To troubleshoot, make sure that you export down to a day level, and this way you can see which day the actual problem starts if EV and PV are not the same.  It’s worth setting up a specific EV Excel template for this (note you can use the Manage Templates button in the Visual Reports dialogue box to find out where these templates are stored, but by default it is %appdata%/microsoft/templates).  The default templates are .xlt so you may wish to convert them to .xltx to match the newer versions of Excel.

I would start by modifying and saving the default Earned Value Over Time Report, and setting the level of usage data to Days.

I’d then set the Time to use the Monthly Calendar (that way you can find out exactly which day is causing you issues when view the difference between the Earned Value and Planned Value.

Once EV and  PV are consistent for the whole of the project, then you know your project schedule is set correctly to calculate EV.  Now all you have to do is begin updating it!

Tip

In order to see the times as well as the dates in Project, change the date format in the Project Options dialogue box.

Conclusion

In truth, Earned Value calculations aren’t designed to work with milestones, as there isn’t a duration available for the calculation to work, and most of my issues were to do with the way contractual payment schedules were modelled using milestones in the schedule.  (EV calculations expect to have duration, cost and work associated with them in order to work seamlessly). However, all of the issues are easy to overcome, and once understood, the ability to pop out an EV report and chart each month easily outweighs the issues with setting it up correctly – that’s not something you can do in Primavera!

Enjoy,  Ben.