Earned Value Management – Free Excel Template

Posted on
Earned Value Management Excel Template

Earned Value Management (EVM) is a technique (Wikipedia) used in project management to measure progress of a project with respect to cost. In this article, we will cover the basics of EVM, why it is useful and also a free Excel template which will help calculate the metrics for us.

 

Why EVM?

Let’s take a simple project of painting our home. Before we begin the project, we estimate that it will take 10 days to complete painting and it will cost 1000. Then, we start the project. After 5 days, let’s say it has cost us 400 and we have completed 40% of the work. According to our original plan (assuming work is uniformly distributed over time), we should have completed 50% of the project by now and it should have cost us 500.

Now, if we want to assess how our project has gone so far, how would we do? Purely comparing the cost (actual 400 vs planned 500) would not give us the true picture. The actual cost is less than planned and it may appear that we are under budget. However, the work completed is only 40% compared to planned 50% and we are behind schedule. So, in order to have measures that truly reflect the progress made compared to plan, we introduce a term called ‘Earned Value’ to calculate the value of work performed so far. In our example, work performed so far is 40% of total work and so Earned Value is (40% * 1000) = 400.

 

 

EFFICIENCY INDICATORS

Cost Performance Index (CPI)

To calculate our Cost Efficiency (how we are progressing compared to our planned cost/budget), we can compare this Earned Value with Actual Cost (which is 400). 400/400 = 1.0. We are exactly on planned cost (neither over nor under).

A number above 1 means ‘Under Planned Cost’. A number below 1 means ‘Over Planned Cost’.

Schedule Performance Index (SPI)

Now, to calculate our Schedule Efficiency (how we are progressing compared to our planned schedule), we can compare Earned Value with Planned Value (which is 500). 400/500 = 0.8. So, we are behind schedule.

A number above 1 means ‘Ahead of Schedule’. A number below 1 means ‘Behind Schedule’.

Earned Value Management Excel Template - Earned Value
Earned Value Management Excel Template – Earned Value

 

Here are some other scenarios and we can see how SPI and CPI change based on % of Work Completed and Actual Cost.

Earned Value, SPI and CPI Calculations
Earned Value, SPI and CPI Calculations

 

VARIANCES

In addition to the indices, we can also calculate the variances which will provide the actual differences.

Cost variance (CV) is the difference between Earned Value and Actual Cost. CV = EV – AC

A positive value indicates Under Planned Cost while negative value will mean Over Planned Cost.

Schedule Variance (SV) is the difference between Earned value and Planned Value. SV = EV – PV

A positive value indicates Ahead of Schedule while negative value will mean Behind Schedule.

 

TERMS & DEFINITIONS

Let’s recap the terms we have covered this far. I have followed the terminology as presented in the PMBOK (Project Management Body of Knowledge) (Links: PMI, Wikipedia)

  1. PLANNED VALUE (PV): Authorized Budget assigned to the scheduled work 500
  2. EARNED VALUE (EV): Measure of actual work performed expressed as budget authorized for that work 400
  3. ACTUAL COST (AC): Actual cost incurred for the work performed 400
  4. SCHEDULE VARIANCE (SV): Amount by which project is ahead or behind plan = EV – PV = -100
  5. COST VARIANCE (CV): Amount by which actual cost is ahead or behind planned cost = EV – AC = 0
  6. BUDGET AT COMPLETION (BAC): Total Budget assigned to the entire plan 1000
  7. SCHEDULE PERFORMANCE INDEX (SPI): Measure of Schedule efficiency expressed as Earned Value to Planned Value  = EV/PV = 0.8
  8. COST PERFORMANCE INDEX (CPI): Measure of Cost efficiency expressed as Earned Value to Actual Cost = EV/AC = 1.0

 

FORECASTING

An extension of the Earned Value calculations is Forecasting, which deals with estimating how the rest of project will go.

Estimate at Completion (EAC) is the expected total cost by the end of the project. It is the sum of Actual Cost (AC) so far and Estimate To Complete (ETC). EAC can be calculated using different forecasting methods. The following are three common methods.

  • Budget Rate: If we assume the rest of the project will cost at the original planned budget rate, then EAC = AC + (BAC – EV)
  • CPI: If we assume the rest of the project will cost at the cost efficiency we have seen so far, then EAC = BAC/CPI
  • SPI & CPI: If we assume the rest of the project will cost based on the cost and schedule efficiencies we have seen so far, then EAC = AC + [(BAC-EV) /(CPI*SPI)]

ETC = EAC – AC

Once we know the EAC, we can calculate the Variance at Completion (VAC) which represents the cost difference between project’s planned budget and current estimate. VAC = BAC – EAC.

The final index we will calculate is the To-Complete Performance Index (TCPI) which represents the cost performance that is needed from now onwards to achieve the goal. It can be thought of as ‘Work Remaining / Funds available’. Work Remaining = BAC – EV. Funds available = BAC – AC
TCPI = (BAC – EV)/(BAC – AC)

If your new estimate has been approved, Funds Available will be EAC – AC. Then, the formula for TCPI will be
TCPI = (BAC – EV)/(EAC – AC)

If TCPI is >1, it is ‘Harder to Complete’ and if it is <1 it is ‘Easier to Complete’.

I have also added a term Estimated Calculation Date (ECD), calculated as Project Start Date + (Planned Duration/SPI).

 

EARNED VALUE MANAGEMENT EXCEL TEMPLATE

Now that we have covered the terms involved, let’s see how the template works in calculating these for us.

Overview of Steps:

  1. Enter basic project information in SETTINGS
  2. Enter Plan information in PLAN sheet
  3. Enter Actual work performed in ACTUAL sheet
  4. Enter Actual Cost in ACTUAL_COST sheet
  5. View EVM sheet for the output calculations

 

FREE DOWNLOAD

Earned Value Management Excel Template

 

VIDEO DEMO

 

We start by entering basic information in the SETTINGS sheet.

Earned Value Management Excel Template - Settings
Earned Value Management Excel Template – Settings

 

  • Enter Project Start Date and Project End Date.
  • Choose how you will aggregate data (Weekly vs Monthly)
  • Choose Planning Unit (Hours, Cost).
    • You can enter hours of planned work and cost per hour. The template will do the calculation for cost.
    • Or you can directly enter planned cost yourself.
  • Choose Cost Entry (Actual Cost, Cost Change)
    • You can enter actual cost or the change (compared to planned cost). If there are only few places where the actual cost is different from the planned, then you can enter only those and save time.
  • Choose Currency from the available choices. This setting will apply currency formatting to our output. You can choose OTHER if your currency is not listed, and then apply formatting manually.

 

Now, in the PLAN sheet, we can enter the planned hours for each task in each period. The period labels will auto-populate. You can add any number of tasks to the table.

Earned Value Management - Enter Plan Data
Earned Value Management – Enter Plan Data

 

Then, we move to the ACTUAL sheet. Here, we enter the actual work % performed for each task in each period. Task Names will auto-populate as you enter the % data.

Earned Value Management - Enter Actual Data
Earned Value Management – Enter Actual Data

 

For the final piece of data entry, we enter the actual cost for each task in each period in the ACTUAL_COST sheet.

Earned Value Management - Enter Actual Cost Data
Earned Value Management – Enter Actual Cost Data

 

We are ready to see the results in the EVM sheet. You can choose any snapshot date from the drop down.

Earned Value Management - Choose Snapshot dates
Earned Value Management – Choose Snapshot dates

 

You will see all the terms that we discussed earlier.

Earned Value Management - Metrics
Earned Value Management – Metrics

 

Earned Value Management - Indices
Earned Value Management – Indices

 

Earned Value Management - EVM Chart
Earned Value Management – EVM Chart

 

Earned Value Management - Indices Chart
Earned Value Management – Indices Chart

 

Forecasting Calculations: Choose the forecasting method.

Earned Value Management - Forecasting Methods
Earned Value Management – Forecasting Methods

 

Earned Value Management - Forecasting
Earned Value Management – Forecasting

 

The EVM sheet is protected with password indzara to prevent accidental modification of formulas. Please feel free to unprotect and make changes as needed.

All the terms used in this template are listed along with definitions in the TERMS sheet.

Please leave your feedback in the comments below. If you find the template and the post useful, please share.

 

Related Project Management Templates

All Project Management Templates

45 thoughts on “Earned Value Management – Free Excel Template

  1. great site and very helpful information
    great also that you havent locked the templates down
    thank you for your efforts

    1. Thanks for your feedback!!!

  2. Hi, could you please share the excel template for the EVM?
    Best regards!

    1. Thanks for your interest in our template.

      Please download it from the free download section of https://indzara.com/2016/02/earned-value-management-free-excel-template/

      Best wishes

Leave a Reply

Your email address will not be published. Required fields are marked *