Calculate the Earned Value of a project on a weekly basis, using TFS, MSProject and MSExcel.
In this article we’ll explain how to calculate an Earned Value in days. It can be calculated in $ in a similar way… It is just a little more complicated.
It will work with TFS 2008, Office 2003 and Windows XP.
At the time of writing, it won’t work with Office 2007 and Windows Vista.
1- Define the tasks in TFS
First, the tasks from the work-breakdown-structure are entered in TFS, and assigned to team members as required.
A query will be needed to list all the tasks for your project, including the closed ones, so tasks don’t desappear as they get closed.
2- Use MSProject for the schedule
Though it is possible, it is unlikely that all the tasks of a project will be entered in TFS; typically Project Management or certain tasks performed by Consultants, for example.
Anyway, I like to have a semi-detailed schedule in MSProject, which will cover for all the tasks in the project. The tasks in TFS may be imported in MSProject automatically, using the TFS client tools, but I personally prefer to do this manually.
This is because I might have to prepare a project report as per Friday night on Friday afternoon, and TFS might not always be up-to-date. We have also experienced some problems with this interface.
I do however tend to group TFS tasks into a smaller number of tasks in MSProject, especially when there are hundreds or thousands of tasks, in order to make thinks easier. In this objective, the TFS tasks can be imported automatically in an MSExcel sheet (which works much better), using the query we mentioned earlier.
Then some calculations can be performed, in order to get Remaining time and %Complete values by group of tasks.
3- Export the schedule baseline into MSExcel
For the Earned Value chart, we need the Planned Effort values.
In this objective, we save the schedule baseline using the Resource Usage view in MSProject. Make sure that the values displayed go every 7 days (one week) and then copy and paste them in an MSExcel sheet, and replace the ‘d’ that comes with the numbers by nothing, so they are interpreted as numbers by MSExcel.
Now for each column, we need to add the week number, so it can be reference in the Earned Value table, in order to get the Planned Effort for each week. I personally like to use the format “2009w8” for the 8th week of 2009 for example.
We also need to calculate the sum of each column and the cumul of the sum for each column.
4- Get TFS Tasks updated
The Developers / Team menbers need to update the tasks that are assigned to them in TFS at least once a week. It is usually convenient to get them to do that at the same time they enter their timesheet. They need to update the Remaining Time and the status of the tasks they’ve been working on.
5- Update the schedule
The list of tasks in MSExcel can now be refreshed automatically with the latest values in TFS, and the schedule can be updated (manually), in order to reflect the progress on the project.
6- Update the Actuals from TFS Timesheet
In theory, TFS Timesheet can update the TFS Tasks Completed Time automatically, but at the time of writting we haven’t been able to get this to work properly.
So in order to obtain the actuals, we’ve been able to setup a pivot-table in MSExcel; Setup an external Data-Source pointing to a view, which is refering to the tfstimesheet table in SQL Server on the TFS Server.
We just needed the Work Item Id on the left, the sum of hours from timesheet entries in the middle and nothing on top, in order to get only one column with hours. Then a SUMIF formula allowed us to update the Completed time in the tasks listed in a different MSExcel sheet and to publish these values back in TFS.
7- Update the Earned Value data from MSProject
The Estimate At Completion (or EAC) for the project will be provided by the value in the Work column in MSProject.
The Earned Value is calculated from the %Complete of the project multiplicated by the baseline budget (in days) or Total Budget in the figure below.
The Total Budget is saved every week, because it can change over time, as the project needs to be re-baselined when significant changes happen. In this case if the history of Total Budget was not saved the values of the Earned Value would be impacted.
8- Draw the Earned Value Chart
See also an approach on how to calculate the tolerance for earned value on this post http://www.casoft.com.au/2010/03/earned-value-and-tolerance-using-critical-path.html
CASoft Blog