Earned Value Management is quite popular and useful tool to objectively track of how a project is progressing.

Earned value management is also an excellent tool to track actual costs Vs actual earned value (based on progress). Whenever actual costs exceed the earned value, it indicates that project is not in good financial health.

Many construction project managers often ask us, how to go about using earned value management in their live projects.

So we have created a basic Excel template to help them easily use earned value management to monitor their construction projects.

Why excel?

Because of its ease of use and flexibility, Excel spreadsheets seem to have become the most preferred tools for many project management applications.

When thinking of earned value management, different kinds of numbers need to be considered –

• Different tasks
• Their duration
• Budgeted costs, billing values
• Scope of work
• Actual progress, costs etc.

Spreadsheets are ideal for tracking and displaying all these numbers to present a comprehensive picture.

So here is a simplified Excel template for ‘Earned Value Management’ in construction projects.

Our consultants at WorkPack have helped construction project managers from different industries to streamline construction progress tracking and cost control.

This excel template is based on the best practices followed by industry leaders.

How to use excel template for project cost control?

The excel sheet is already populated with some sample data. It represents earned value tracking scenario for an imaginary construction project.

Several tasks are listed in the sheet with details of their duration, budgeted, scope of work, actual work done etc.

Based on these numbers, the excel sheet calculates –

• Weight factor for each task
• Actual % progress
• Actual costs (based on time spent)
• Earned Value – as weighted average of % progress
• Schedule and Cost Variance values

Weightage for each task

Different tasks in a construction project are not same as each other. They differ with respect to – efforts, material, costs, duration etc.

Task weight factor helps to capture the importance or value of a task in a single number. For example a task (A) with weightage of 8 is four times more valuable than another task (B) with weightage of 2.

Correspondingly, 50% progress of task A means more work than 100% of task B.

But how to calculate these weight factor numbers is a subjective decision. The important requirement is those numbers should reflect the actual value or importance of related tasks.

A regularly used approach is to use the actual billing value of a task – which is what we have followed here in the earned value Excel template.

Actual % Progress

% progress of a construction task is best calculated based on the actual physical work done on the site.

Many project managers use actual costs or mandays spent to determine the actual progress. But this a very faulty methodology, which assumes 100% efficiency.

In our Excel template for earned value calculation, we have used the actual work done to determine the actual % progress.

The actual progress for overall project is calculated as a ‘weighted average’ of % progress of individual tasks.

Overall Actual % Progress = ∑ (task %progress × task weightage) / ∑ (task weightage)

Actual Costs

Actual costs in a construction project could be a combination of the labor costs and material costs – depending on who is doing the material procurement.

But here we have only considered the labor costs for simplicity.

Correspondingly, actual mandays spent on a task will reflect the actual cost, when multiplied by labor rate for that task (labor rate = cost/estimated duration).

Sum of actual cost values for all the construction tasks represents the actual cost for overall project.

Earned Value of a Task

Earned value is the value of work that has been actually done so far.

So it can be easily calculated by multiplying the total task value (billing value) with % progress of that task.

Sum of earned values for all the construction tasks will represent the earned value at project level.

Schedule Variance

Schedule variance represents the % variation between planned and actual progress values for overall project.

Schedule Variance = 100 × (Actual Progress – Planned Progress) / Planned Progress

Cost Variance

Cost variance represents the % variation between earned value and actual costs values for overall project.

Cost Variance = 100 × (Actual Costs – Earned Value) / Earned Value

Automating Earned Value Management

This Excel template for earned value management in construction projects was derived from our exhaustive project management solution – WorkPack.

Our team has already helped multiple construction project managers to digitize and streamline their project management practices – including earned value management, cost control, schedule tracking etc.

Do you think your team can benefit from WorkPack as well? Find out by setting up a free trial account.

Let us know in case you need any help or if you would like to schedule a free consulting session to understand how it works.