We are continuing our column on the different tools that will help you improve your project management practices. Today, we are going to focus on calculating the earned value of a project.
This article will help you understand what the earned value of a project is, why project managers should calculate it and how to apply this methodology in Excel.
Analyse Your Earned Value with Excel
In order to understand how earned value is calculated, we created a simple tool, which you can freely adjust according to your needs.
First, you have to enter the data needed to calculate the earned value:
- In the PV tab (planned value) you have to enter the planned value for each deliverable. You can divide this value linearly (for example, $ 500 per week for a deliverable with a value of $ 1,500 for 3 weeks) or however you like.
- In the AC tab (actual cost) you have to enter the costs corresponding to the work completed for each deliverable.
- In the EV tab (earned value) you have to enter for each week the percentage of progress for each of your deliverables.
Once you have entered the data, you can analyze the earned value of your project in the Final Report Tab.
We'll find two main categories of indicators: Variance Indicators & Performance Indicators.
Cost Variance = Earned Value - Actual Cost
The Cost Variance tells a project manager whether you've exceeded your budget or you're under budget.
- If this number is negative, it shows that you have exceeded your budget.
- If this number is equal to zero, the project is on budget
- If this number is positive , it shows that you are under budget
Schedule Variance = Earned Value - Planned Value
The Schedule Variance will help a project manager determine whether your task is ahead or behind schedule.
- If this number is negative, then your task is running late
- If this number is equal to zero, then the task is on schedule
- If this number is positive, then your task is ahead of schedule
Something to keep in mind, a task can be ahead of schedule but can be over budget. This implies that you've spent too much money compared to the amount of work that has been actually completed.
2. Performance Index
Cost Performance Index = Earned Value/ Actual Costs
The CPI also indicates whether you've exceeded your budget or you're under budget however it gives more information about the degree of variation.
- If the CPI is less than 1, then the task is over budget
- If the CPI is equal to 1, then the task is on budget
- If the CPI is greater than 1, then the task is under budget
For example, if your EV = $5,000 and the AC= $3,700
CPI = 5,000/3,700 = 1.35, therefore your project is 35% under budget.
Schedule Performance Index = Earned Value/ Planned Value
The SPI also helps a project manager determine whether the project is ahead of schedule or behind schedule.
- If the SPI is less than 1, then the project is behind schedule
- If the SPI is equal to 1, then the project is on schedule
- If the SPI is greater than 1, then the project is ahead of schedule
For example is your EV= $14,000 and the PV= $7,000:
SPI = $14,000/$9,000= 1,56, this means that your project is ahead of schedule by 56%.
If your CPI is greater than 1 and your SPI greater than 1 then your project is under budget and ahead of schedule. Hurray! However, if your CPI is greater than 1 but your SPI is less than 1, then your project is under budget but it's behind schedule, this means that you should have completed more tasks by now.
These performance indices provide insight into time and cost, they will give you more information on what will happen if the project continues at the same pace. Essentially, they will help you find out in which direction your project is heading and which corrective actions you should take to ensure the project is delivered on time and within budget.