ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How To Compute Earned Value (EVM) using Transact-SQL

Updated on November 5, 2012

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.

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
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


    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article