Finance – Earned value calculation excel
Earned value calculation excel
How to do earned value calculations in excel
Calculating earned value is an essential part of project management, as it's one of the most accurate and objective ways to assess project performance during project delivery - which enables a project manager or other management to keep projects on budget and on schedule or course-correct to get it back on track.
Different companies do earned value management in slightly different ways, but all use the same set of EVM metrics. These metrics are designed to make a project performance assessment as clear and objective performance, and different earned value formulas can be used and leant on for diving deeper into certain aspects of performance including schedule and cost - as well as for projecting out what we have found during our calculations to better predict future outcomes.
In terms of how these calculations are conducted, many companies use excel or another spreadsheet tool or software.
Earned value calculations are relatively straightforward, which makes doing earned value calculations in excel relatively straightforward too.
There are only two components to the powerful measure of earned value:
- Percent of project (or phase of works) complete
- Project (or phase of works) budget, also known as budget at completion
These two components are simply multiplied together to give us our current earned value:
Earned value = % of project completed (actual) x Budget at completion (BAC)
So when thinking about our earned value calculation for excel, there's really only one thing we need to be tracking and inputting into our simple equation: % of the project completed.
We know our budget at completion at the start of the project, so that remains the same throughout the life of the project and can simply be one excel cell which is constantly referenced.
The best way to approach your % of project completed in excel is to setup the measurement intervals you will use to measure earned value - whether that be at the end of each month or end of each week - and leave some cells where you can enter your actual numbers. This way you can see the progression over time and of course reference the new number in your excel calculation.
The makeup of these numbers will of course depend on how you and your projects choose to measure % complete, as well as what tools and systems you use to get these numbers.
How to organise your other earned value calculations in excel
The real power of earned value comes from the fact that earned value enables a project manager or company to understand project performance on the basis of scope, time and cost.
The real benefit of your earned value 'number' is when you compare this number to your actual cost and schedule so that you can gauge performance.
Earned value calculations in excel almost always feature this other variables or equations, and are closely linked to the earned value.
To understand our performance on a cost-basis, we only need to track and reference one other metric or number in our excel sheet: our actual cost.
It's best if this actual cost is formatted similarly to your % of project complete, and it can even follow the same cadence to keep things simple.
Once we have this row of numbers, we can reference this number into our cost variance formula:
Cost variance = Earned value - Actual cost
In our cost variance excel table or cell, we need to reference our actual cost - which is the data being collected and reconciled on the project as well as reference our earned value number which we worked out above. This is where keeping your excel sheet organised comes in handy.
Once organised, you can simply enter your most recent % of project complete and your actual costs. Your earned value and your cost variance will then be automatically updated in real-time in your excel sheet.
The major last piece of the puzzle for a standard earned value calculation excel sheet is your schedule variance.
Schedule variance is found with this equation:
Schedule variance = Earned value - Planned value
Once again, we can reference our earned value cell, but what about our planned value?
For planned value, we need to be able to reference the planned % of project completion at this stage of the project.
So we want to setup a separate table which mirrors our earned value analysis cadence. If we want to calculate earned value once per month, then setup a table which has the planned % of project completion during each month x by our static BAC, then we can reference this number in our planned value equation:
PV = % of project completed (planned) x Budget at completion (BAC)
Then we simply forward this number into our schedule variance formula, and subtract that number from our referenced earned value number.
We can of course build out even more earned value formulas and detail if required, such as our estimate at completion, or we can keep it simple.
How you organise your earned value calculations in excel is up to you, but it should be in such a way as to make referencing tables and sequential equations easy.
Why you maybe shouldn't use excel for your earned value calculations...
The purpose of this article was to help you explain how to do earned value calculations in excel, and you can find some great and useful templates online to do exactly this - or build your own format from scratch using some of the information above.
But I'm also going to tell you why you maybe shouldn't use excel for earned value. While excel is a great data reconciliation tool and great for working with formulas and numbers, for many companies, excel isn't connected to the way that work is captured.
Great examples of companies who maybe shouldn't manage their earned value calculations in excel are companies who work on site or in the field like companies in construction, oil and gas and mining.
The problem with these companies using excel is that excel isn't how information is captured on site. Maybe these companies use a digital solution to capture work on site or maybe that still use paper and PDFs. But if they aren't using excel (which they probably won't be because it doesn't work very well on site) then there's a few real issues which arise very quickly:
- The information being collected on site is in a different format, so it needs to be manually reconciled and moved to something like an excel sheet
- The information being collected on site isn't directly connected to the earned value calculation excel spreadsheet, there is a big chance that some information gets lost, misplaced or entered incorrectly
- The information being collected on site doesn't get to the spreadsheet quick enough, so the most recent project % complete numbers aren't up-to-date and this impacts the integrity of your earned value numbers
Most companies in these project-based industries can feel these problems every day. They deal with moving information manually, re-formatting documents and losing critical information on site or in the office.
So instead of highlighting problems, let's talk about solutions.
The best way to eliminate these issues is by implementing an end-to-end digital solution. These digital solutions enable workers to document progress and other important information using mobile technology on site, and all of this information is then piped straight into a consolidated system. All of this information is standardised through forms and form logic, and all of this data is available to the project manager or earned value manager in real-time.
This accurate and up-to-date information is pushed straight into registers, and you can also transfer it to your earned value calculation excel sheet - or you can see some earned value and planned vs. actual data within the same system too.
Earned value calculations are one of the most powerful project management tools, so you should be constantly looking for ways to improve and refine how you manage it.
People in 70+ countries use this software to understand earned value more easily and more accurately.
See how you can easily streamline your systems and processes with Sitemate today
English (United States) 
English (Australia) 
English (UK) 
English (New Zealand) 
English (South Africa) 
German