FREE Earned Value Calculator

Posted by Kevin Brady on Fri 5th September 2008 at 04:01 PM, Filed in Document Templates & ToolsProject ManagementEarned Value

Please click the link to download your free “Excel 2007 Earned Value Calculator”
I have actually used this calculator on a number of projects in place of using Microsoft Projects Earned Value Analysis (EVA) capability because of its greater flexibility and use as project reporting tool.

This calculator uses ‘Traffic Light’ indicators for each time segment of the project and the performance outlook for the project. These simple indicators, with an appropriate traffic light key attached, could be easily added to Clarety’s Standard Status Report Template.

If you need information on why EVA is important please click on the post “Why Should I Use Earn Value Analysis ?” and then select “EVA Simplified” to get a detailed view of what all the metrics on this important artifact mean and how they can be used to look under the bonnet of your project and get a real understanding of your projects real as against perceived performance.

The calculator contains three sheets.

Sheet 1. Definitions – This sheet explains what all the metrics mean, and how they are calculated. This section is especially important to read because the next sheet uses a RED, GREEN and AMBER indicator to describe the example projects performance and this definitions sheet explains how these indicators are calculated and what the thresholds are for each colour change.

Sheet 2. Analysis - The second sheet works out EVA performance metrics on a monthly basis and provides a RED, GREEN, AMBER status for each time slice (in this case each calendar month).  The last column indicates predictive performance metrics and associated traffic light indicators in terms of the project’s final outcome. The indicators are worked out by taking an average between the two most important EVA performance index numbers Cost Performance Index (CPI) and the Schedule Performance Index (SPI). This average gives a combined view of how much cost and schedule variance the project is suffering. If this average is 1 or greater then the project is most likely on budget and running to schedule. If the project is less then 0.65 then the project is in serious trouble with the project is seriously behind schedule and earning 65pence of value for every pound spent and in such a situation the project should either be axed or a serious attempt at project recovery undertaken. Remember EVA is a powerful indicator of future project performance and such indicators cannot be ignored when you bear in mind the following statistically proven statements:-

• Once a project is 10% complete, the over-run at completion will not be less than the current over-run.

• Once a project is 20% complete, the CPI (Cost Performance Index) will not vary from its current value by more than 10%.

Sheet 3. Trends – This sheet puts all the key EVA metrics into graphs showing performance overtime and the trend to be expected going forward.

This entry has been viewed 13258 times.

READER COMMENTS:

Great excel work sheet, useful for replacing MSFT project on simple projects.
But event the Excel worksheet contains the critical flaw found in many understandings of EV.
The “earned value” (BCWP) must be calculated from some measure of Physical Percent Complete.
BCWP = BCWS * Physical Percent Complete (at the period of measurement). Having BCWP be entered from an “opinion” creates the opportunity to game the system.
The physical evidence of Physical Percent Complete closes the loop-hole for projects using EV.
Glen B. Alleman
VP, Program Planning and Controls
Aerospace and Defense
Denver, Colorado USA

Posted by Glen Alleman  on Tue 9th September 2008 at 03:37 AM | #

POST A COMMENT:

Please feel free to submit relevant comments to this entry but note: inappropriate or purely promotional comments may be removed as will be personal abuse and defamatory remarks. Reasoned debate and substantiated critique on the topic in hand is encouraged and welcomed. Email addresses are never displayed, but they are required to confirm your comments.

Name:

Email address is required but will not appear publicly:

Add your comments below:

Remember my personal information for next time

Submit the word you see below:

20 + 2 =