Excel template for earned value management
This article provides details of Excel template for earned value management that you can download now.
The acquired value of a project is the ratio between the budget and the percentage of completion of a project. For example, if your teams have completed 50% of the tasks of a project valued at $ 10,000, the acquired value of the project will amount to $ 5,000 or 50% of the budget provided for this project. It corresponds to the budgeted cost of the work done or CBTE.
Concerning the planned value, let's say that the total budget is $ 1,000, on a project that must last 5 days. On the 4th day of your term, your planned value will be $ 800. This amount corresponds to your overall budget divided by the number of days required, and multiplied by the progress of your project which corresponds here to 80% (4 days out of 5).
If we look at the actual costs, we can summarize it as the aggregation of all the expenses recorded to arrive at a time T of the project.
Microsoft Excel software under a Windows environment is required to use this template
These Excel templates for earned value management work on all versions of Excel since 2007.
Examples of a readytouse spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
The file to download presents four Excel template for earned value management
These Excel workbooks make it possible to rapidly consolidate all the necessary information and produce the associated summary.
Earned Value Management (EVM) is a project management technique that objectively tracks physical accomplishment of work
More Elaborately:
Good planning coupled with effective use of the EVM technique will reduce a large amount of issues arising out of schedule and cost overruns.
EVM emerged as a financial analysis specialty in United States Government programs in the 1960s, but it has since become a significant branch of project management.
In the late 1980s and early 1990s, EVM emerged as a project management methodology to be understood and used by managers and executives, not just EVM specialists. Today EVM has become an essential part of every project tracking.
There are following three bacis elements of EVM
All the three elements are captured on a regular basis as of a reporting date.
This is also referred to as Budgeted Cost of Work Scheduled ( BCWS ). Planned Value (PV) or BCWS is the total cost of the work scheduled /Planned as of a reporting date.
This is calculated as:
PV or BCWS = Hourly Rate * Total Hours Planned or Scheduled 
NOTE: Hourly Rate is the rate at which effort will be valued.
This is also referred to as Actual Cost of Work Performed (ACWP). Actual Cost (AC) or ACWP is the total cost taken to complete the work as of a reproting date.
This is calculated as:
AC or ACWP = Hourly Rate * Total Hours Spent 
This is also referred to as Budgeted Cost of Work Performed (BCWP). Earned Value (EV) or BCWP is the total cost of the work completed/performed as of a reporting date.
This is calculated as:
EV or BCWP = Baselined Cost * % Complete Actual 
All these three elements can be derived from Work Breakdown Structure by associating the costs to each of the tasks. For a big project it will be a tedious task to calculate these elements manually. Scheduling Softwares like Microsoft Project is used to calculate these three elements.
NOTE: % Completed Planned and % Compelted Actual are defined below
The percentage of work which was planned to be completed by the Reporting Date. This is calculated using the following formula
% Completed Planned = PV / BAC 
The percentage of work which was actually completed by the Reporting Date. This is calculated using the following formula
% Completed Actual = AC / EAC 
Cost Variance (CV) is very important factor to measure project performance. Cost Variance (CV) indicates how much over or under budget the project is.
Cost Variance can be calculated as using the following formula
Cost Variance (CV) = Earned Value (EV)  Actual Cost (AC) 
OR
Cost Variance (CV) = BCWP  ACWP 
Cost Variance % indicates how much over or under budget the project is in terms of percentage.
Cost Variance % can be calculated as using the following formula
CV % = Cost Variance (CV) / Earned Value (EV) 
OR
CV % = CV / BCWP 
Cost Performance Indicator is an index showing the efficiency of the utilization of the resources on the project. Cost Performance Indicator can be calculated using the following formula:
CPI = Earned Value (EV) /Actual Cost (AC) 

OR
CPI = BCWP / ACWP 
To complete Cost Performance Indicator is an index showing the efficiency at which the resources on the project should be utilized for the remainder of the project. This can be claulated using the following formula:
TCPI = ( Total Budget  EV ) / ( Total Budget  AC ) 

OR
TCPI = ( Total Budget  BCWP ) / ( Total Budget  ACWP ) 
Schedule Variance indicates how much ahead or behind schedule the project is.
Schedule Variance can be calculated as using the following formula
Schedule Variance (SV) = Earned Value (EV)  Planned Value (PV) 
OR
Schedule Variance (SV) = BCWP  BCWS 
Schedule Variance % indicates how much ahead or behind schedule the project is in terms of percentage.
Schedule Variance % can be calculated as using the following formula
SV % = Schedule Variance (SV) / Planned Value (PV) 
OR
SV % = SV / BCWS 
Schedule Performance Indicator is an index showing the efficiency of the time utilized on the project. Schedule Performance Indicator can be calculated using the following formula:
SPI = Earned Value (EV) /Planned Value (PV) 
OR
SPI = BCWP / BCWS 
To Complete Schedule Performance Indicator is an index showing the efficiency at which the remaining time on the project should be utilized. This can be claulated using the following formula:
TSPI = ( Total Budget  EV ) / ( Total Budget  PV ) 
OR
TSPI = ( Total Budget  BCWP ) / ( Total Budget  BCWS ) 
BAC is calculated using the following formula
BAC = Baselined Efforthours * Hourly Rate 
Variance At completion (VAC) is the variance on the total budget at the end of the project.
This is the difference between what the project was originally expected (baselined) to cost, versus what the it is now expected to cost.
VAC is calculated using the following formula
VAC = BAC  EAC 
The percentage of work which was planned to be completed by the Reporting Date. This is calculated using the following formula
% Completed Planned = PV / BAC 
The percentage of work which was actually completed by the Reporting Date. This is calculated using the following formula
% Completed Actual = AC / EAC 