EVM Overview

The earned value management is one of the very popular and standard approach to managing the value as part of the planned scope. It has three different elements namely the planned value (PV, also known as Budgeted Cost of Work Schedule or BCWS) at the beginning of the project, the earned value (EV, also known as Budgeted Cost of Work Performed or BCWP) at a snapshot in time during the project, and the actual costs (AC, also known as Actual Cost of Work Performed or ACWP) incurred in the project at the same time the EV is measured. 

From here, two sets of metrices can be created. 

In Project Management, these measures are used as follows:

1. If SV is zero or SPI is 1, the project is on schedule.
2. If SV is negative or SPI < 1, the project is behind schedule.
3. If SV is positive or SPI > 1, the project is ahead of schedule.
4. If CV is zero or CPI is 1, the project is on budget.
5. If CV is negative or CPI < 1, the project is over cost. 
6. If CV is positive or CPI > 1, the project is below costs.

Going beyond the Summary Costs

The cost and budget management is a specific focus area of project management. Even Agile projects have costs associated with them in terms of the resources required. These resources are split into two major categories, namely the human resources (such as employees, consultants, contractors, etc.) and non-human resources (such as facilities, equipment, infrastructure, materials, supplies, etc.). 

In small-to-medium scale projects where cost is tracked by the project manager against an allocated budget, sometimes the cost may involve non-human resource cost alone. This information is aggregated by the project manager at the release level and not necessarily at the individual task level to avoid the unnecessary burden on project team level tracking. In Part 2 of the EVM Metrics, we aggregated the actual costs at the release level.

However, in some projects the actual costs may be tracked at the individual tasks level. The tasks may be associated with a requirement which is mapped to the original release or the task may be directly associated with a release level,  such as time spent on product deployment environment preparation. When tasks are connected with a release, then,  the costs can be tracked at the individual task level and summarized to the release level for the cost variance (CV) and cost performance index (CPI). 

Please note that since the "Tasks" module is getting used in this final Part 3 of the EVM article, this module is applicable only for SpiraTeam and SpiraPlan.

Release Level

Need for Custom Properties - Release Level

There are two custom properties that must be setup for managing costs at the release level. The data type should be decimal. The first two are also discussed in the EVM Part 2.

1. Non-Human Costs: These costs represent the total cost budgeted from multiple areas. This is the anticipated cost required for continuing with the project phase or release. For instance, a total of $600 may be projected as non-human costs as follows. 
   • Software License costs is projected to be $300
   • Meals and Incidental Expenses is projected to be $200
   • Supplies and other expenses is projected to be $100
2. Blended Rate: Every company has an blended rate for a small-to-medium scale projects to make calculations simple for budgeting purposes.   For instance,
   • An email marketing campaign may have a Director of Marketing, Creative Designer, Front-End Developer, Back-End Developer, Campaign Manager, etc.
   • Some of these members may be contracted resources and others may be internal to the project and detailed resource management may be too involved for a small-to-medium scale project.
   • The finance department reviews these types of projects and comes up with a single blended rate for all the human resources used in this project for cost management purposes. 
   • It is possible that a business process does not track this blended rate. In such cases, this optional parameter can be dropped (see notes in the SQL Query explanation)

Set up of Custom Properties

The following screen shows the custom properties setup at the release level. In the following setup,

1. CUST-05 represents the non-human resources,
2. CUST-06 represents the blended rate

IMPORTANT:

1. It is expected that all these fields represents the same currency unit (Dollar, Rupee, etc.) throughout the project. If costs incurred have multiple currencies, convert them to the one currency against which comparisons will be made.
2. If you followed the EVM Part 2 logic, you do not setup Incurred Costs at the release level.

Data Setup - Release Level

See below the data set up in the releases particularly for the custom properties referred. 

Task Level

Need for Custom Properties - Task Level

1. Incurred Costs: These costs may represent a costs logged against the specific tasks.

Set up of Custom Properties - Task Level Level

The following screen shows the custom property setup at the task level for the incurred costs

1. CUST-03 represents the incurred costs

Data Setup - Task Level

See below the data set up in the tasks particularly for the custom properties referred (data is filtered here for one release).

IMPORTANT

1. As mentioned before, ensure the same currency (USD, INR) for tracking costs. As each task may be carried out by different geographical region, more granular discussions may emerge for tracking current at different levels and the currency rate applied on a specific date, etc. These thoughts make the query more complex and are out of scope.
2. The tasks only at the root level are factored in. While Spira allows task folders and organize the tasks in different hierarchies, iterating through every folder for tasks is more procedural that does not adequately lend for SQL query. So, tasks only at the root folder are picked in the Query.

SQL Query

select EVM.NAME, EVM.PLANNED_VALUE, EVM.EARNED_VALUE, EVM.ACTUAL_COSTS,
 EVM.EARNED_VALUE - EVM.PLANNED_VALUE as SCHEDULE_VARIANCE,
 Round( (EVM.EARNED_VALUE / EVM.PLANNED_VALUE),4) as SCHEDULE_PERFORMANCE_INDEX,
 EVM.EARNED_VALUE - EVM.ACTUAL_COSTS as COST_VARIANCE,
 Round( (EVM.EARNED_VALUE / EVM.ACTUAL_COSTS),4) as COST_PERFORMANCE_INDEX
from
 (select
   R.RELEASE_ID, R.NAME, Cast(R.TASK_ESTIMATED_EFFORT as double) as ESPV, Cast(R.TASK_PROJECTED_EFFORT as double) as PFEV, AC.COSTS, Cast(R.CUST_05 as double) as NHCosts, Cast(R.CUST_06 as double) as BlendedRate, 
   ((Cast(R.TASK_ESTIMATED_EFFORT as double) * Cast(R.CUST_06 as double)) + Cast(R.CUST_05 as double)) as PLANNED_VALUE, 
   ((Cast(R.TASK_PROJECTED_EFFORT as double) * Cast(R.CUST_06 as double)) + Cast(R.CUST_05 as double)) as EARNED_VALUE,
   ((Cast(R.TASK_PROJECTED_EFFORT as double) * Cast(R.CUST_06 as double)) + AC.COSTS) as ACTUAL_COSTS
  from 
    SpiraTestEntities.R_Releases as R 
  inner join 
   (select TK.PROJECT_ID, 
       TK.RELEASE_ID,
       sum(Cast(TK.CUST_03 as double)) as COSTS 
     from SpiraTestEntities.R_Tasks as TK 
       where TK.PROJECT_ID = ${ProjectId} and 
         TK.RELEASE_ID is not null and 
         TK.CUST_03 is not null
     group by TK.PROJECT_ID, TK.RELEASE_ID) as AC on
   AC.PROJECT_ID = R.PROJECT_ID and R.RELEASE_ID = AC.RELEASE_ID
where 
 R.PROJECT_ID = ${ProjectId} and R.IS_DELETED = False and
 R.TASK_ESTIMATED_EFFORT IS NOT NULL and 
 R.TASK_PROJECTED_EFFORT IS NOT NULL and
 R.RELEASE_TYPE_NAME in {"Major Release", "Sprint", "Phase", "Minor Release"} and
 R.RELEASE_STATUS_NAME = "In Progress") as EVM

Explanation

• PLANNED VALUE = (TASK_ESTIMATED_EFFORT * BLENDED RATE) + NON-HUMAN RESOURCES
  • NOTE: If NON-HUMAN RESOURCES are dropped from PLANNED VALUE, drop them from EARNED VALUE also.
• EARNED VALUE = ( TASK_PROJECTED_EFFORT * BLENDED RATE) + NON-HUMAN RESOURCES
  • NOTE: Incurred Costs are not part of the  EARNED_VALUE
• ACTUAL COSTS = This is the sum of the costs tracked as INCURRED_COSTS by RELEASE. These are handled as a run-time inline subquery used in the INNER JOIN.
  • NOTE: For EVM calculations, the type of task does not matter. But, you can create a separate Spira Custom Widget to evaluate COSTS by TASK_TYPE for deeper evaluation of what types of tasks cost more!
• The inner TASK query excludes any tasks that does not have costs associated or release is not mapped and is filtering for the current project
• NOTE: If your business process does not track the blended rate, then, the SQL query containing " * (Cast(R.CUST_05 as double) must be dropped in all the PV, EV, and AC computation.
• The Schedule Variance (SV) is computed as a difference (EV - PV)
• The Cost Variance (CV) is computed as  difference (EV - AC)
• The Schedule Performance Index (SPI) is computed as a ratio (EV / PV)
• The Cost Performance Index (CPI) is computed as a ration (EV / AC) 
• To facilitate the division correct, the EV and PV must be cast to be decimal and rounded off to 4 digits (just to differentiate it from the previous queries)
• If more precision is required, update as necessary in the second parameter of Round function
• The information is filtered for the release types (Sprint", "Phase", "Minor Release", & "Major Release")
• The information is filtered for "In Progress" releases for the current only where the EVM matters
• The information also excludes where there is not a valid data for PV or EV

Guidelines

• This approach requires three custom properties as explained above (2 at the release and 1 at the task)
• This approach depends on tasks being properly estimated and actuals being entered
• This approach depends on the non-human costs and blended rate captured one time at the release level
• This approach depends on the incurred costs being tracked at the task level
• This approach assumes that tasks are mapped to the release
• This approach assumes that only tasks in the root level folder are included

The Output

Here is the output that can be part of the custom component of a standard or custom report.  Depending upon what one may want to report (Variance or Index), this query can be modified and represented graphically on the reporting portal.