How To Compute Earned Value (EVM) using Transact-SQL
EV is a measurement in Earned Value Management (EVM) which is a project management technique to determine if a project is going off course in terms of performance. It has the ability to measure scope, schedule and cost in an integrated way. EVM provides insight into a project’s progress.
You need three types of information, as input, to properly measure EVM for a basic project. Complex projects would include other metrics like cost and scheduling performance.
- A project plan that clearly identifies the objectives to be accomplished
- A valuation of planned work known as a Planned Value (PV)
- A Metric to quantify the measurement of work accomplished called Earned Value (EV)
This article is describes how to calculate the Earned Value (EV) in Transact-SQL. This information can be combined with other data as part of ongoing project reporting system.
Create Procedure dbo.CalculateEarnedValue
@EarnedValue numeric(18,2) output,
@PerformanceRatio numeric(18,2) output
Set @BudgetedCost = @LabourCost + @ResourceCost
The @BudgetedCost is the Budgeted Cost of Work Scheduled (BCWS). This is an alternate name for the valuation of planned work or Planned Value (PV). The PV is the sum of the Labour Cost (@LabourCost) and the Resource Cost (@ResourceCost).
Next the PV or BCWS is multiplied by the percentage of the project that is completed (@WorkCompleted).
@EarnedValue = @WorkCompleted * @BudgetedCost
This is the Earned Value.
The EV could be used to calculate the Actual Cost of Work Scheduled (ACWS) which is the total amount of labour and resources that were spent on a project to date divided by the Earned Value which would give you the Performance Ratio.
Set @ACWS = @ActualLabourCost + @ActualResourceSpent
Select @PerformanceRatio = (@ACWS/@EarnedValue )*100
This will determine if the project is on, over or below budget