Instructions for Sample EVMS Calculations

Assistance with EVMS Reporting Requirements

The attached MS Excel workbook provides a template for project cost estimating and tracking. It also provides the capability to calculate and track the various reporting parameters required of an Earned Value Management System (EVMS).

The following conventions apply to the workbook and will serve to explain to the user how the template was developed. These conventions are not to be considered inviolable, but rather may be tailored as the individual user sees fit. The template posted here is locked so that it may not be changed, but the user may, if they choose, save a copy under another file name, and modify it as they see fit.

• Gray shaded areas are not intended to be updated.

• Only the white-colored cells are intended for user input.

• Labor estimates are input in terms of hours per month per labor category, but could be derived from other more complex calculations. (percentage of FTE per month per labor category, etc.)

• Labor costs on the "Incurred Costs" sheet are derived from calculations. Input to this sheet is only in terms of hours worked. Material and Other Direct Costs (ODC) are input as dollar amounts.

• Monthly updates to the "EVMS Calculations" and "EVMS Chart" worksheets will require "cut and paste" operations.

The workbook consists of seven worksheets labeled as follows:

Labor Rates

This worksheet is set up to allow inputting of both Government and contractor labor rates. The rates given for Government employees are the labor rates for step 5 of GS rates 1 through 15. The "Fringe Benefits" and "Overhead" factors are those currently called out by OMB Circular A-76, "Performance of Commercial Activities." To remain useful beyond the current fiscal year these numbers will need to be changed as OMB guidance changes.Contractor rates listed on this worksheet are intended to be used as an aid in constructing cost estimates. It is expected that the user will tailor these rates (and labor categories) to fit their particular situation.The "Overhead" and "G&A" rates listed are approximations of industry averages and will, of course, need to be tailored to the user's particular situation. Please note that only the "contractor labor category," "annual salary," "fringe benefits," "overhead," and "G&A" cells are intended for data input by the user. All of the gray-shaded cells are derived or calculated and are not intended to be changed.

Labor Estimates

This worksheet allows for the input of expected monthly labor hours, by GS grade and Contractor labor category, for each month of the project. Note that expected labor costs are derived through a calculation utilizing the input labor hours and the labor rates previously entered on the "Labor Rates" worksheet. Again, note that the gray-shaded areas are not intended for data input or change.

Material and ODC Estimates

Estimated costs for material and ODC items are entered into this worksheet. The worksheet is set up to allow input of estimates, by month, for the categories of hardware/equipment, software, vendor services, inter-agency services, and supplies/other.

Incurred Costs

This worksheet is used for the input of monthly costs incurred for labor (both Government and Contractor), materials, and other direct costs. Labor costs are entered only in the form of hours; actual costs are derived through calculations, based on the rates contained in the "Labor Rates" worksheet. Costs incurred for material and ODCs are entered directly as dollar figures.

% Complete

The percentage of project completion is entered into this worksheet. Note that for the purposes of this template, a total of four Work Breakdown Structure (WBS) subelements has been established. The percentage of completion for the entire WBS is calculated by summing the subelements on this sheet. For other, more complex WBSs, this could be expanded vertically (to reflect either more subelements per WBS category) and horizontally (to reflect more WBS categories. The yellow-shaded table on this worksheet, titled "Projected Completion Percentage by Month" is intended to provide guidance as to where the completion percentage is expected to be for each month. Actual percentage completion is input into the worksheet, and is expected to come from the expert opinion of the project manager.

EVMS Calculations

It is on this worksheet that the EVMS component of the template begins to take shape. Note that everything on this worksheet is derived from Earned Value Analysis (EVA) calculations. Nothing is input into this worksheet; rather, as the "Incurred Costs" and" % Complete" worksheets are populated each month, the Budgeted Cost of Work Produced column is populated automatically, and the remaining EVA calculations are performed in other worksheets. To complete the entire EVMS report, simply copy the calculations in rows 10 through 29 of the previous month's column and paste them into the current month's column.

Example: To complete the EVMS report for the month of July:

• Input July's expended labor hours in cells O5 through O19 (for Government labor) and O25 through O39 (for Contractor labor) in the "Incurred Costs" worksheet.

• Input material and other direct costs in cells H45 through H49 of the "Incurred Costs worksheet.

• Input the percentage of work completed in cells J4 through J7 of the "% Complete" worksheet.

• In the "EVMS Calculations" worksheet, select and copy cells H10 through H29 and paste into cells J10 through I29 of the same worksheet.

At this point, all of the EVMS calculations will be completed for the month of July.

EVMS Chart

This worksheet contains an imbedded chart to provide a graphic depiction of the EVMS elements Budgeted Cost of Work Scheduled (BCWS), Actual Cost of Work Performed (ACWP), and Budgeted Cost of Work Performed (BCWP). The data used to populate this chart is derived from calculations performed on other worksheets and is updated during the monthly reporting period.

The "EVMS Chart" is updated as follows:

• As the "Incurred Costs," "% Complete," and "EVMS Calculations" worksheets are updated, all the calculations required to update the chart will have been performed.

• Simply copy the "ACWP" and BCWP" rows (rows four and five) from the previous month's column and paste them into the current month's column.

• When the "ACWP" and "BCWP" are pasted into the current month, the chart will automatically be updated with the current month's data. As this process is repeated with each month's data, the chart will be kept current throughout the project's lifecycle.

Should you have questions regarding the use of the EVMS template, or require assistance with tailoring it to your specific project, please contact Jerry Harper at 202-482-0222 or jharper@doc.gov.