Indzara
Shop
Support
Login

Earned Value Management 2023 – Free 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 Earned Value Management (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.

What is Earned Value Management?

Video of Earned Value Management Terms & Definitions

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 the term Estimated Calculation Date (ECD), calculated as Project Start Date + (Planned Duration/SPI).

Earned Value Management in Excel

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 of Excel template

Step by Step tutorial on Earned Value Management in Excel

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.

75 Comments

  • Hi ,

    How do you calculate earn value at H_CALC .Can you please share the working .

    Reply

  • How do you obtain earned value? Because when I multiple work progresses to actual, the amount is different, as indicated in sheet H_CALC. can you pls show the working sheet for earn value?

    Reply

  • I need to Monitor 5 Project Sites for a Client, Monitor say 7 Items, on 15 days interval:

    Need the excel Sheet Format for monitoring the Data for Smal Medium Scale company- Small turnover of INR Rs 30 Million (1 USD = 84 INR).

    Data shall be fed by Site Supervisor from the Site and after review by manager Updated in SQL server.
    Compatible Excel Sheel Sheet required.
    1. Planned Budget and the amount of Budget earned for the Work achieved-=> Schedule Variance( Time)
    2. Comparison for Budget cost of work performed BCWP and Actual Cost ( ACWP)= > Cost Variance

    Reply

    • Thank you for sharing your requirement.

      Currently, we do not have a pre-built template solving your requirement. We can take it as a customization project to build on top of Earned Value Management template for a fee. Please write to us at the below link for estimation:
      https://support.indzara.com/support/tickets/new

      Best wishes.

      Reply

  • Hi,

    If i need a future value of EV curve to be plotted based on expert judgement fr eg: manpower availability, how will I do it.

    Reply

  • HI, I have copied the template so i can make an connection with a Ghant chart and critical chain.
    The formules are richt but the template is not calculating the earned value in H_calc anymore.
    earned value gives a 0.
    I have copied the sheets one on one. i see no changes in the formules

    Is there a solution?

    Kind regards,

    Patrick van der Tol

    Reply

    • Thank you for showing interest in our template.

      As per our email communication, on reviewing your sheet, we found that the number of rows in Plan, Actual and Actual cost table are different. The array formula used for the mentioned calculation refers to plan table and actual table and when there is two different list size, the formula will throw an error. Due to which it is showing as 0.

      Best wishes.

      Reply

  • Hello,
    I cannot find the cells that “H_CALC” refers to? I thought is a defined name, but when I generate a list of names “H_CALC” is not shown.

    Thanks

    Reply

    • Thank you for showing interest in our template.

      The H_CALC refers to a hidden sheet. Following are the steps to unhide the H_CALC sheet:

      1. Right-click any of the sheet name. For example: SETTINGS
      2. Select Unhide -> select H_CALC and press OK.

      Best wishes.

      Reply

  • Hi. This is a great sheet. But for some reason it is not auto-calculating the EV, SPI or CPI on the EVM sheet? Have you come across this before? I have input my data in the other sheets “Plan”, “Actual” and “Actual_Cost”.

    Reply

    • Thank you for showing interest in our template.

      We are unable to replicate the highlighted concern. Please share your sheet with sample data highlighting the concern to support@indzara.com to check further.

      Best wishes.

      Reply

  • This is one of the most simple and useful templates for EVM that I have found. I will definitely share with my colleagues.

    I find that SPI is not a very reliable indicator of schedule performance. I was able to do some research and found information on an extension of EVM called Earned Schedule. This extension is supported by the Project Management Institute (PMI). How can Earned Schedule be calculated in a spreadsheet?

    Reply

  • Hello!
    Thank you very much for your great support!
    Could you please tell me which meaning has your name or term “C_SNDT” in the formula-manager?

    Reply

    • Thank you for showing interest in our template and you are welcome.

      I understand that unreferenced named range might have concerned you but I would like to confirm you that C_SNDT is same as I_SNDT and C_SNDT is not used anywhere in the template’s calculation and we will delete the same from the named range when we build and release a new version of the template.

      Best wishes.

      Reply

  • Thanks for sharing. I teach project management and I will be using your sheet in class to practice EVM with my students. Hopefully they’ll be visiting your site to buy some of your products..

    Reply

    • Thank you for using our template and spreading words about our product and website.
      Best wishes.

      Reply

  • Greetings,
    I am using your template for a project management class. I have entered the data and the EV is 0. Can you help

    Reply

    • Thanks for using our template.

      Please ensure that the formulas and the links are not broken. In case there is still an issue, please share your file with the list of issues to contact@indzara.com

      Best wishes

      Reply

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

    Reply

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

    Reply

  • In the example you’ve provided … I’m having a difficult time understanding how the AC has been calculated on the EVM sheet. (Note that I’ve converted to US$, and have left the example as is in every other way.)

    Reply

  • Hi, could you email me the EVM template please?

    Best Regards,

    Reply

    • Thanks for your interest. Please download from the above post. Please let us know if there are any issues in downloading.
      Best wishes.

      Reply

  • I remember a very good and helpful EVM template if I can download but file not opening.

    Reply

  • If you allow, I want to help me , really need help in creating a EV report.
    Thanks for yor time

    Reply

  • hi can i ask how did you select the date and the PV, SV value and others automatically changes according to the date?

    Reply

      • It is not any one function. Please unhide the hidden sheet H_CALC and see the formula for details.

        Best wishes.

        Reply

    • There is a hidden sheet where we calculate PV which has formula using data including dates.

      Best wishes.

      Reply

  • Outstanding template!!! Just one question, in the Aggregate drop down, could i add bi-monthly and what is the formula for bi-monthly? I would really appreciate your help.

    –thank you so much…keep up the great work

    Reply

    • Thanks.
      Explaining formula here would be difficult. Sorry.
      Best wishes,

      Reply

    • The link to the file is in the FREE DOWNLOAD section just before the second video . Please search for FREE DOWNLOAD in the browser. Best wishes.

      Reply

  • Terrific for startups, small companies, and individual consultants! Cheers to good work!

    Reply

  • how earned value is calculated in the excel u have shared? Please describe the formula

    Reply

    • Earned Value represents the actual work completed as % of total planned work.

      It is SumProduct of
      Actual % Work Done (entered in ACTUAL sheet)
      &
      Total Planned work (column BC in PLAN sheet)

      Best wishes.

      Reply

  • Hi. Hw can i add to the droplists if required. And i Also want to use the earne value management template for a short project that requires being tracked daily. Hw can i get it done daily

    Reply

    • Modifying drop down lists would require editing the ‘Data Validation’ for the cell. DATA ribbon –> click on Data Validation.
      I am sorry that it is not easy to explain how to make it work for ‘daily’. It would require editing formulas in many places in the workbook. Please unlock sheets using indzara as password and edit the formulas accordingly.
      Best wishes.

      Reply

Leave a Reply

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