CASoft Blog

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

Taking over a new project, a new role or a new job… Restarting.

The objectives a project take over are (in this order):

1. Ensure long-term success of the Project Manager
2. Ensure success of the project

In order to take over a project successfully, the following steps shall be undertaken:

• Establish authority and leadership with confidence
• Establish relationships with every individual – learn about each one of them, what are their skills, experience, what type of management get the most of them, what are their motivators, personality and character.
• Assess Project – take ownership of the project plan and the schedule, and assume resposibility from there on – if plan does seem achievable, perform the foolwing actions in this order: 1. try to reduce scope, 2. try technical simplifications, 3. justify and ask for more resources, and 4. propose to replan the project.
• Assess and document Risks and Issues – perform a complete risk analysis, in order to confirm that the project objectives are achievable
• Document Vision and Action Plan – based on well know methodology
• Establish Project Management Master Mind

Spend a fair amount of your time improving your leadership ability:

• Provide navigation
• Establish solid ground
• Influence
• Process knowledge
• Get momentum
• Focus on victory
• Establish inner cycle
• Maintain priorities

Creating a shared vision consists of establishing and actively maintaining agreement and commitment about what is to be done and how it will be done.
A shared vision is a result of an ongoing dialog among all the people working on the project.

The objectives of a shared vision are:
-Facilitating people working together.
-Helping people to attain unity of purpose.
-Creating a common understanding.

Attention: unrealistic proclamations can transform the shared vision into a source of frustration

Define the focus, for example:

The main focus is the success of the project and the customer satisfaction, all along the project life.

Define the Project Structure:

The project structure describes the caps (or roles) and relationships (or interfaces) for a software development project:
-Several caps may be put on one head.
-One cap may be worn by several heads.
-The represented relationships would describe the official communication stream (or process).

Obtain Assignment Acceptance:

People and activities assignments result from an agreement between Project Manager and Assigned person.
When no solution can be found, or for important responsibilities, the acceptance of higher-level management and/or other stakeholders will be required.

Other Areas to be considered:

• Common Values
• Team Empowerment and Independence
• Decision Making
• Conflict Resolution
• Proposed Products and Tools
• Constraints
• Expectations