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.

  1. A project plan that clearly identifies the objectives to be accomplished
  2. A valuation of planned work known as a Planned Value (PV)
  3. 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.

CalculateEarnedValue
Create Procedure dbo.CalculateEarnedValue

@BudgetedCost numeric(18,2),
@LabourCost numeric(18,2),
@ResourceCost numeric(18,2),
@WorkCompleted numeric(18,2),
@ActualLabourCost numeric(18,2),
@ActualResourceSpent numeric(18,2),
@ACWS numeric(18,2),
@EarnedValue numeric(18,2) output,
@PerformanceRatio numeric(18,2) output
AS
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

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working