Earned Value Analysis with Microsoft Project

3

+++ Key Performance Indicators and Their Operational Value +++ Analyze Your Project Status in Microsoft Project +++

By Johann Strasser                                                                                                                Read article in German

Earned Value Analysis (EVA) is a tool for controlling your project progress. Key performance indicators (KPI) delineate the current schedule and cost situation. Earned value management (EVM) and earned value project/performance management (EVPM) are synonymous terms for EVA.

Regardless of the size and complexity of the projects earned value analysis provides:

  • a valid statement regarding the current status of the project
  • a forecast calculated to minimize the planning risk
  • a comprehensible format as a basis for stakeholder decisions

Find out how to Keep Track of Project Progression with Project Satus Reports.

In this article you will learn:

  1. How to determine earned value and the corresponding key performance indicators
  2. How to portray these in Microsoft Project in order to get a calculation of the current status of your project including forecasts.

Note: You are not currently planning the costs for work resources in your projects? You lack the real hourly rates of the resources or are not allowed to show them? Read on just the same.

  • You can assign all resources an hourly rate of EUR 1 without great effort and make EVA only relevant to the actual work. The real costs get left out.
  • Important note: This will only work if you are not planning real costs anywhere else. In that case, the ratio and the calculation of the earning rate would be incorrect.

 

There are many users who can employ earned value analysis very well in this way, as their project costs are entirely controlled via the resource work anyway.

General Information about Earned Value Analysis

When evaluating the current status of a project the following questions arise:

  • What is the current effort?
  • What has been delivered so far?
  • What should have been the corresponding effort?
  • When will the project end?
  • What will be the overall effort?

Simple Cost Variance

In order to determine the difference between planned and actual effort up until today you need a baseline as a reference. This baseline needs to be set after the planning has been completed but before the first change and update with actual data.

Indispensable for EVA: Set a baseline directly after planning has been completed.

The difference between the accumulated planned and actual values up until the current date adds up to a deficit or a surplus in the project. This reflects the current financial state of the project and is a very interesting figure for the payment plan. But it does not reflect how well the project is doing, as the stage of completion is not taken into consideration.

The simple cost variance is relevant for the payment plan but not for the actual state of the project.

In case of a surplus you ask yourself:

  • Have the costs really been lower than planned, or
  • has the project not progressed as far as it should have?

In case of a deficit the question arises whether

  • the effort has been higher so far or
  • the project has progressed further than planned.

And in both cases it is relevant what this means for

  • the overall costs that are to be expected and
  • the target date of the project.

Naturally, you will only be able to answer these questions reliably if you know what content has actually been delivered so far.

Special Download: Benefits of MS Project Server / Project Online to the MS Project Standard

Please click here to download the PDF and learn why you should prefer the server over the client version.

Earned Value

Earned value (EV) now establishes a relation between the stage of physical completion by the status date and the corresponding planned effort. This shows for the status date what effort was planned for the actual work performed.

The earned value is calculated from the stage of physical completion and the originally planned effort.

This is how it can determine the state of the project realistically and deduce a forecast for the future.

The illustration below shows the progression of planned costs and actual costs up to the status date as well as the stage of physical job completion from which the earned value is deduced.

Earned Value Analysis in Microsoft Project 1

Figure 1: Actual Delivery is lagging behind Planned Delivery

  • The blue curve shows the progression of costs in relation to the planned work (BCWS – Budgeted Cost of Work Scheduled) rising up to the level of the planned overall costs (BAC – Budget at Completion).
  • The yellow line represents actual costs accumulated up to the current date (ACWP – Actual Cost of Work Performed).
  • The ensuing broken yellow line illustrates the expected progression until the estimated end in terms of the expected cost (EAC – Estimated at Completion).

After the delivery items up to the current date (Actual Delivery) have been determined the result is transferred back to the original planning (Planned Delivery). This is where the real stage of physical job completion can be identified, which is significantly lagging behind expectations.

Thus, it is possible to read from the plan curve what value the actual delivery has according to the plan. This is the earned value or also BCWP – Budgeted Cost of Work Performed.

Earned value therefore represents the costs which according to the original planning would have been necessary to reach the stage of physical completion attained on the status date.

Unlike actual costs, earned value represents the project’s stage of physical completion.

Accordingly, unlike actual costs, earned value (EV) is a strictly financial factor representing the project’s current stage of physical completion. The EV number on its own is not really informative and cannot be compared to other projects.

But EV is a perfect basis from which to deduce the really interesting key performance indicators for forecasts. It can be employed in order to check the plausibility of the remaining plan for the items which are yet to be delivered. This is how you can minimize the risk of further planning mistakes.

Interested in reporting? Learn about the 5 Most Important PPM Reports.

Real Cost Variance

Real Cost Variance (CV = BCWP – ACWP) reveals the difference between actual costs and earned value. It is used to describe the efficiency of projects. It can also be calculated in percent (CV% = [BCWP-ACWP]/BCWP) and thus is a figure that can be compared across projects.

Real cost variance can moreover be calculated as a Cost Performance Index (CPI = BCWP/ACWP):

  • It equals 1 if everything is OK.
  • If it is below 1 the costs for the results obtained were too high, and
  • if it is above 1 the work performed cost less than planned.

A CPI of 0.8 therefore reveals a cost increase whereas a CPI of 1.2 indicates a cost reduction.

Real Schedule Variance

Real Schedule Variance (SV = BCWP – BCWS) shows the difference between planned costs and earned value. It clarifies the financial gap between results obtained and results planned. If calculated in percent (SV% = [BCWP-BCWS]/BCWP), it is also a figure that can be compared across projects. The schedule variance will at all events impact time.

It can also be calculated using the Schedule Performance Index (SPI = BCWP/BCWS):

  • It equals 1 if everything is OK.
  • If it is below 1 the costs for the results obtained are lagging behind schedule
  • if it is above 1 the work performed is ahead of schedule.

An SPI of 0.8 reveals a delay whereas an SPI of 1.2 indicates a speed-up.

Forecast

You have created an updated schedule and calculated new overall costs after updating the project plan by entering actual values, stages of completion and a new remaining effort.

You can compare your new costs with the estimated value (EAC – Estimated at Completion). Depending on the resulting difference you should reconsider and possibly also revise your planning.

But before you do this you have to make assumptions about further developments to enable you to calculate the forecast.

  • From now on according to plan: EAC = ACWP + (BAC – BCWP)
  • Carry on as before: EAC = ACWP + (BAC – BCWP) / (CPI * SPI)
  • No further schedule variance: EAC = ACWP + (BAC – BCWP) / CPI
    (note: this is the formula used by Microsoft Project)

The third option is the one calculated by Microsoft Project. The first two could be calculated using customized formulas.

The status update also includes the variance between the forecast and the planned overall cost (Variance at Completion VAC = BAC – EAC). This variance also needs to be calculated in percent if it is to be compared to other projects (VAC% = [BAC – EAC]/BAC).

Comparability of Projects

The hitherto mentioned indices and percentage values can be divided into stages and represented by traffic light indicators red/yellow/green. This allows users to quickly identify in a project overview which key performance indicators are not OK.

Traffic light indicators and division into stages make it easier to keep key performance indicators in view.

As a rule, actions are linked to the different colors that are meant to be taken in case the color changes:

  • In the case of unfavorable values, the linked action usually is to inform, or escalate to, the steering board.
  • Overly good values might prompt the revision of the plan or rather disclosure or removal of scheduled buffers.

It can happen that a CPI > 1 coincides with an SPI < 1 or vice versa. The meaning can also be graphically illustrated. The diameter of the individual projects can represent the planned costs in this case. This also allows for the appraisal of the absolute degree of variance.

Earned Value Analysis with Microsoft Project 2

Figure 2: Performance indices for several projects in possible traffic light colors

How Earned Value Analysis with Microsoft Project Works

To begin with, you should know: Microsoft Project calculates earned value for all tasks that have

  • planned costs (Baseline Cost in MS Project),
  • their beginning before the status date,
  • a stage of completion that is greater than zero.

To recap: This will only work if a baseline is saved once planning is completed and before the first update.

Read our other MS Project Tip telling you How to Use Resource Engagements in Project 2016!

Here is how you do it:

Under Options / Advanced / Earned Value options for this project you can select one of the eleven baselines available for earned value calculation.

In the same place you can also select which field you want to use for the completion in the EVA:

  • the field % Complete
  • the field Physical % Complete

Watch out: For better comprehension we will call the field % Physical Complete in this article. However, in Microsoft Project the field name actually is Physical % Complete.

The update of the field % Complete is usually linked to the update of resource work, i.e. to the field % Work Complete. This does not necessarily permit a statement regarding the stage of physical job completion.

In order to remove this link, you can switch off the corresponding property, Updating task status updates resource status, under File / Options / Schedule for this project.

Remove the link between resource work and the field % Complete in order to obtain a statement regarding the stage of physical job completion.

But watch out: Even then, you might not obtain appropriate results, as % Complete is inextricably linked to the current and remaining duration. Which again might not correspond to the actual work performed.

Tip: As a general rule, it is clearer to use the field % Physical Complete, as it is independent from all other values. This allows you to enter the actual stage of completion without affecting the update of duration, work or cost.

Updating the Project

First, it is advisable to set the status date in the project information and possibly have it also displayed as a vertical line in the Gantt chart via Format / Gridlines.

The correct update of all tasks is the second prerequisite for a correct calculation of the earned value analysis. To achieve this, you have to enter the current hours and costs as well as the remaining work and duration from which % Work Complete or % Complete are calculated.

Struggling to distinguish MS Project versions and their differences? Read article.

As a result, the progress lines of all running tasks have to reach up to the status date. The inaccuracy in calculating the earned value using % Complete is due to the circumstance that the use of time does not necessarily correspond to the physical job completion.

If you set the calculation method of the earned value to % Physical Complete, this field needs to be edited for every task started or completed.

In doing this, you should define a rule specifying exactly how these values are to be entered.

  • Single-figure percentage entries usually do not need to be entered this accurately and are likely to be scrutinized.
  • In this case, the inaccuracy furthermore depends on the number of running tasks.
  • Tasks which have not been started or completed are to be simply entered as 0% or 100%.

Calculation in Microsoft Project

The earned value calculation is basically the same for both methods.

The planned costs or baseline cost (field Base. Cost) are accumulated according to % Complete or % Physical Complete alongside the planned duration day by day.

Example: If a task has EUR 800 of baseline cost per day over 4 days and 50% completion, the baseline cost will be added up to 50% of the planned duration. In this case, it adds up to 2 days with EUR 800 each, i.e. EUR 1600.

The forecast in Microsoft Project is calculated according to the third case mentioned above:
EAC = ACWP + (BAC – BCWP) / CPI

In order to follow the calculation on the screen, select a Gantt chart in the top view and View / Task Usage for the bottom view and display the columns as shown in Figure 3.

Sticking with our example: You have four tasks with a duration of 4 days each whose baseline begins on Tuesday and costs EUR 800 per day – as you are using one resource each with an hourly rate of EUR 100 for 8 hours a day. Therefore, the planned overall costs of each task are EUR 3,200.

The status date is set to Wednesday evening and the tasks were updated in different ways in order to be able to explain the logic of the calculation using a real situation.

Calculation Using % Complete

All tasks have been set to the earned value method % Complete.

Earned Value Analysis with Microsoft Project 3

Figure 3: Earned value using % Complete with carry-over in Task B

Task A (“Vorgang A”) is implemented according to plan and reaches a degree of completion of 50% by the set status date.

  • This means the earned value is EUR 1,600 at 50% of the planned and actual duration.
  • CPI and SPI are both 1, as the actual costs match the baseline cost (Base. Cost).
  • The forecast matches both the baseline and the current planning at EUR 3,200.

Task B (“Vorgang B”) starts a day late and with only 4 hours – rather than the 8 hours planned. Still, 50% completion is reported for this task. This causes the progress line to jut out past the status date and the carry-over to be ignored in the calculation.

  • Only 25% of the planned duration are accumulated resulting in only one day at EUR 800.
  • The CPI equals 2, as this was achieved with a cost of only EUR 400.
  • The SPI is calculated as 0.5, as only half the planned performance of EUR 1,600 has been delivered.
  • The forecast is EUR 1,600, in accordance with the above formula. Accordingly, the real remaining work will be performed at half price, i.e. 400+(3,200-800)/2= EUR 1,600.
  • The costs of the current planning are now EUR 2,800 resulting from the 4 hours performed and the 8 hours on the remaining 3 days, which have not been rescheduled.

Task C (“Vorgang C”) shows the correctly updated Task B. If 50% completion were achieved in 4 hours on only one day, the duration needs to be reduced from 4 to 2 days. That is why the progress line only reaches exactly up to the status date.

  • This results in an earned value of EUR 1,600.
  • The CPI arrives at a value of 4, as in 4 out of 16 hours only a quarter of the costs was accumulated.
  • The SPI amounts to 1, as still only the planned work representing EUR 1,600 was performed by the status date.
  • The forecast is EUR 800, as the second day could also be achieved with the performance of the first day.
  • The costs of the current planning are now EUR 1,200 resulting from the 4 hours performed on the first day and the remaining 8 hours of the second day, which have not been rescheduled.

Task D (“Vorgang D”) begins a day earlier than planned, but with 8 hours per day only 50% completion are achieved.

  • This results in an earned value of EUR 1,600.
  • The CPI only arrives at 0.67 due to the third of additional effort.
  • The SPI is 1 due to the expected EUR 1,600.
  • The forecast is EUR 4,800, as a third of additional effort is to be expected.
  • The current baseline cost also amounts to EUR 4,800, as the duration has already been extended to 6 days with 8 hours each.

In the Summary Task (“Sammelvorgang”), the planned value and the earned value are calculated from the sum of the subtasks whereas % Complete is calculated from the actual position of the current start and the current duration. The values for CPI, SPI and EAC are calculated anew in the summary task, so that EAC is not the sum of the subtasks here.

If you want to calculate this yourself, take care not to use the displayed value of the CPI, as great rounding differences can result with only two decimal places. Calculate with the values BCWP/ACWP instead. In this case, that would be 4,800+(12,800-5,600)/(5,600/4,800)=10,971.4.

The sum of the current planning amounts to EUR 12,000 compared to the EUR 10,972 from the total in the Summary Task and the EUR 10,400, which is the sum of the EACs of all tasks. It could therefore make sense to revise the effort planning for the tasks that have so far gone well a little downward so that the delay of one day can be remedied – possibly by a reallocation of resources.

Calculation Using % Physical Complete

Using the same data after switching to % Physical Complete and entering “50% Physical Complete” for all tasks, you will get almost the same result as with the previous method.

Earned Value Analysis with Microsoft Project 4

Figure 4: Earned value using % Physical Complete with carry-over in Task B

Task B (“Vorgang B”) now has different results for earned value, indices and forecast, as the carry-over of 50% past the status date is not truncated. In this case, irrespective of the position, 50% is half the planned performance, i.e. EUR 1,600. This is how the SPI arrives at 1 and the CPI at 4 as in Task C – regardless of further planning for the remainder of these tasks.

In the Summary Task (“Sammelvorgang”), you can now find a forecast corresponding to the sum of the subtasks.

The earned value itself can only be interpreted if you know the budget of the project. What is more, it cannot be compared across several projects. It therefore makes more sense to pass on the indices and percentage variance of the forecast as project status information to others.

These values are easily classifiable and independent of the projects’ timeframes and budgets.

No matter how long the project takes – a CPI of 0.5 will make your name just as prominent in the company as a CPI of 1.5.

Conclusion – Earned Value Analysis with Microsoft Project

In this article, you have learned that earned value itself can only be interpreted if you know the budget of the project. This is why it cannot be compared across several projects.

Therefore, it makes more sense to pass on the resulting indices and the percentage variance of the forecast as project status information to others. These values are easily classifiable and independent of the projects’ timeframes and budgets.

Tip: The skill of understanding earned value and also the indices is not very common. Make sure you always check whether the recipient of the data can deduce the relevant information from them. It is always better to pass on the illustrations shown above than mere figures.

In your position as a project manager, we recommend you to use earned value analysis in order to check the plausibility of your own planning – it will minimize your planning risk.

Do you use Earned Value Analysis with Microsoft Project? What are your experiences? Please leave a comment below.

Final tip: Subscribe to the blog newsletter now and never miss another blog post.

print this article

Share.

3 Comments

  1. Mostafa AbdelRazik on

    Where did you get that the used formula by project is: EAC = ACWP + (BAC – BCWP) / CPI?

    Which version is this? I have always known project to calculate EAC = BAC/CPI

Leave A Reply