How I Calculate Earned Value for Status Reporting

If you've seen my earlier post on Status reports, I showed you an Earned Value chart that I include on my status reports. This post talks about how I create that chart.

Step 1

Create you work breakdown structure. I bet this term was made up by an engineer. I have a lot of respect for engineers - after inventing the wheel back in the old days, they did many other good things for humanity; some of them invented the wheel again, while others built machines, computers, bridges, and space aircraft. The fella who invented the term definitely falls in the latter category; however, he must have slept through his arts classes during his undergrad, because otherwise he would've come up with a sexier or at least a more common name for a list of work packages. Now, work package is a unique animal in its own class. I am sure the fella's English teacher cried when he heard the term. Don't get me wrong - I've tried hard to use the term, but I keep getting blank looks from everybody on it. Even some PMs! (different story here). I stick to Task. It is something people understand, and everybody knows what a task is.

So, in plain English, get a list of what you are going to do on your project and break high-level tasks into more granular tasks as needed.

Step 2

Enter the tasks into MS Project, Excel, some fancy EPM software, or carve them in stone (the transfer to Excel later on will require some retyping on this one). I use Excel because it is easy, and I know a lot of really good PMs who use Excel.

You'll want to have your tasks as row headings; x-axis is where you'd want to put your time periods (I wanted to say dates, but them remembered that that's part of my dating blog - don't mix pleasure with business). It'll looks something like this:

Step 3

Make estimates. Earned value requires that you convince yourself that you know how much effort or money each task will take. There is a whole bunch of estimating techniques out there so making good estimates is a different topic. However, if there was only one thing you should know about estimating is that you need to get the numbers from people who will actually be doing the work. There are exceptions to this rule, but I would say it stands in 80% of the cases.

Once you make the estimates, you can calculate the budget for each task and add this information to the spreadsheet:

Step 4

Take a break - you deserve it.

Step 5

Add formulas to calculate the Earned Value for every period as the project progresses. The formula I am using in Excel is SUMPRODUCT - it allows you to multiple the values in one column by corresponding values in another column and then calculate the total sum. You'll also notice I use a formula for calculating the ending date of the time periods.

If you were doing these calculations on paper, the Total Earned Value for a given period would look as follows:

(Task 1 % Complete * Task 1 Budget) + ( Task 2 % Complete * Task 2 Budget) + ...

The % complete for every task goes into the cross cells of the table. I use the 0%-50%-100% approach for % complete, because for all intensive purposes, most tasks have little value until they are completed, and calculating a more accurate number in the middle is not worth the effort, unless you have some automated time tracking software.

Step 6

Add rows for Actual Cost and Budget. These will be used on the Earned Value chart so you can visually compare your actual cost with earned value and have a budget line. You already know the budget so you can enter it into the Budget line:

Step 7

Create a chart. I use a Line with Markers chart, but feel free to be more creative. Just remember that this chart type should not be cumulative. For the data, I use the Earned Value, Actual Cost, and Budget rows, and for the X-axis labels, I use the Date row.

Some formatting tips:

1. Pick custom colors for the trend lines and marker, as the default palette doesn't work so well.
2. You might want to tweak the increment on the Y- and/or X-axis to optimize the chart's look.

Step 8

Start tracking. Every week, I update the % complete values for each task, enter the actual cost incurred during that week, and Excel does the rest for me:

In addition to the chart, my status report includes a copy of the table with % complete numbers into my status report. This spares me the effort of writing a section on the work completed and planned.

Simple and easy, kids!