Earned Value Analysis
& Pivot Tables

Not long ago, we featured an article on the power and versatility of Pivot Tables in Excel. Also, Earned Value Analysis is a powerful diagnostic tool that offers project managers the ability to zero in on problems by highlighting performance patterns across the tasks of their projects. In this article, we show how these two tools can be used in conjunction to produce revealing and informative results.

A brief summary of EVA

Among other variables Earned Value Analysis (EVA) features the computation of the following parameters which are ratios:

Note: API is not a standard EVA index but can be derived from the others.

These values are all measured against the baseline and hence a value of 1 is normal while values greater than one are indicators of good health. Quantities significantly less than one would require attention.

Now imagine you had a project with five different task categories and you recorded their progress over time.  Suppose further that you computed the indices shown above and saw the following:

Now we can employ the pivot table feature of Excel in order to analyse this data. For details on how pivot tables work, refer to our website for previous articles. For example we may want to examine the Schedule Performance of these tasks against type and can do so as follows:

From this it is clear that the Design type tasks are eperiencing problems in getting their work done on time. It is also possible to roll these results up to the project as a whole in order to get an impression of performance at that level.