Earned value management systems key document integration using Excel®
This paper discusses how to effectively integrate work authorization Earned Value Management Systems (EVMS) documentation using an enterprise spreadsheet software product. Given its wide level of adoption as a business productivity tool, Microsoft® Excel® will be the specific spreadsheet example referenced by this paper. However, certainly other software solutions, such as Google Apps and AppleWorks, can be considered as an EVMS document integration platform. Key approaches presented in this paper are based on industry guidelines found in A Guide to the Project Management Body of Knowledge (PMBOK® Guide) 5th Edition (Project Management Institute, 2013) and the American National Standards Institute/Electronic Industries Alliance (ANSI/EIA) – 748 that contains 32 guidelines for EVMS.
Documenting EVMS artifacts such as Work Authorization Documents (WAD), the Responsibility Assignment Matrix (RAM), and Control Account Plans (CAPs) are difficult challenges for many program managers and controllers. This paper suggests an integration example of EVMS documents utilizing Excel® spreadsheets.
The following topics will be addressed:
- Brief History of Earned Value Management (EVM)
- EVM Overview
- Integration of WADs, RAM, and CAPs with samples
This paper concludes with a recommendation of effective EVMS key document integration using Excel®.
What is an EVM? What is the difference between EVM and EVMS? EVM is a program management technique to objectively track progress and status a program and it also forecasts the most likely future performance. EVMS is a program management tool that integrates scope, cost, and schedule in order to plan and control a program.
Is an EVMS valuable for managing projects? Is an EVMS just another reporting requirement that program managers need to meet which adds an additional administrative burden? Why is it so difficult to properly document artifacts and prepare for internal and external EVMS audits, including those from U. S. government agencies? The answers to these questions can vary depending on each program’s circumstances and how its EVMS is structured.
The ANSI/EIA 748 32 Intent Guidelines serve as EVMS implementation standards for both Government and industry. According to the ANSI/EIA 748 Intent Guideline #9 (NDIA, 2005, p 19), “establish budgets for authorized work with identification of significant cost elements (labor, material, etc.) as needed for internal management and for control of subcontractors.” It also states, “…through a work authorization process, establish budgets for all authorized work to be done by the responsible organizational elements.” One of the biggest challenges and critical success factors is related to establishing a clear Work Breakdown Structure (WBS) which captures one hundred percent of Statement of Work (SOW), assigning accountability, and authorizing work. It is very important to properly document EVMS-related artifacts and each document should be clearly traceable and developed with a consistent level of rigor.
There are many automated program management (PM) applications with the capability to support broad EVMS requirements. In general, PM information system software has the capability to establish a program baseline (Budgeted Cost Work Scheduled) using labor category and rate, other direct cost (ODC), materials, Work Breakdown Structure (WBS), Organizational Breakdown Structure (OBS), etc.
Project schedules drive a baseline process in PM applications by integrating the schedule with the PM application. Moreover, based on periodic updates from schedule and cost updates from enterprise accounting systems, PM applications can produce the project performance metrics to include schedule and cost variances. They also support drill down to the root causes of those variances. The benefits to utilizing an integrated PM application are many. The ANSI/EIA 748 requires integration of scope, cost, and schedule and it also requires proper documentation of EVMS artifact integration. For ease of use purpose, many project managers and controllers can benefit from documenting EVMS artifacts such as the WAD, RAM, and CAP in Excel®. Utilizing PM systems in the areas of report generation and analysis can also be difficult for project stakeholders with non-technical backgrounds if they are not familiar with PM systems. To facilitate ease-of-use and accessibility, Excel® can be applied as an alternative for documenting EVMS artifacts such as WAD, RAM, and CAP. Using Excel® for this purpose also facilitates meeting the ANSI/EIA 748 EVMS criteria.
This paper puts forth a method for integrating required EVMS documentations such as WAD, RAM, and CAP to meet the Government and industry standards using Excel®. By integrating WAD/RAM/CAP into a single data repository in the Excel® workbook, one can easily trace EVM data from one document to another and provide easier ways of maintaining documentations which adds value to a program’s population of EVMS documentation.
Earned Value Management History
According to Quentin W. Fleming and Joel M. Koppelman (Earned Value Management 2nd edition, Chapter 3, © 2000), the Earned Value (EV) concept was conceived a hundred years ago in the latter part of the twentieth century. Industrial engineers working in early American factories utilized a “three-dimensional” approach to analyzing cost performance efficiency for work done in the factory. This approach became the fundamentals of EVM.
In 1958, the United States Navy introduced the Program Evaluation Review Technique (PERT) network-scheduling tool to industry and in 1962; PERT/cost was introduced to industry. However, the lifespan of PERT/cost did not last long. In 1967, the U.S. Department of Defense established the Cost/Schedule Control Systems Criteria (C/SCSC) and the subculture of C/SCSC grew from the 1970s through the mid-1990s.
Despite the successful use of EV within the C/SCSC, both private industry and the U.S. Government had concerns about “user friendliness” if the EVM concepts was to be applied beyond a U.S. government mandate. A formal meeting of the Management Systems Subcommittee of the National Defense Industrial Association (NDIA) was held at Phoenix, Arizona on 18 April 1995. Over the next few months, the subcommittee met to establish the industry version of EVMS criteria. In 1996, Dr. Paul Kaminski, accepted the 32 EVMS criteria. The NDIA subcommittee requested and obtained approval for 32 EVM criteria to be formally issued as an American National Standard Institute/Electronic Industry Association (ANSI/EIA) document. Finally, the ANSI/EIA 748 Guide was officially issued in July 1998. The PMBOK® Guide included an overview of EVM in 1987. Recently the United States Office of Management and Budget mandated the use of EVM across all U.S. government agencies to manage programs and projects. The mandate allowed for the EVM implementations to be altered based on contract type and dollar value.
Earned Value Overview
According to the PMBOK® Guide, “EVM is a methodology that combines scope, schedule, and resource measurements to assess project performance and progress. The principles of EVM can be applied to many projects in any industry.”
EVM is a project management technique for measuring project performance and progress. It has the ability to combine measurements of scope, schedule, and costs. In a single integrated system, EVMS is able to provide accurate forecasts of project performance problems, which is an important contribution for project management. Proper implementation and application of EVMS can provide significant benefits to include improved program/project performance, early risk indication / mediation, and clear / transparent accountability.
Depending on how one determines the genesis of EVM concepts, EVM existed over a hundred years or maybe just more than three decades since the implementation of C/SCSC in 1967. EVM terms had changed and evolved over the time. However, the emphasis of EVM has been consistent: accurate measurement of current and cumulative performance compared to the planned value permitting for accurate estimate of the final cost and schedule prediction. EVMS requires a fully defined project scope, and baseline plan developed initially using a bottoms-up estimating approach which integrates the scope, resources, costs, and schedule. EVM is an integrated management approach that provides both current and cumulative cost and schedule performance with analysis of determining variances and future cost and schedule forecasts.
In order to accomplish integration of scope, cost, and schedule, there are fundamental foundations of EVM, several of which are listed below.
- Integrated Master Schedule (IMS) – The IMS provides a logical sequence of work that the helps validate that contract requirements are met. Statement of Work (SOW) and WBS elements are captured within the schedule. In turn, the schedule is fully integrated, both horizontally and vertically, with the WBS elements.
- Work Breakdown Structure (WBS) – A product-oriented division of work which decomposes, organizes, displays, and defines the product to be developed. MIL-HDBK 881 provides guidance on the standard WBS approach.
- Organizational Breakdown Structure (OBS) – A functionally-oriented breakdown of the contractor’s organization established to perform the work on a specific project.
- Control Account (CA) – A management control point at which budgets and actual costs are accumulated and compared for the purpose of EVM reporting. They are natural management points since they represent the work assigned to a single responsible organizational element within the WBS structure.
According to the PMBOK® Guide, EVM develops and monitors three key dimensions for each work package and control account:
- Planned Value (PV) is the authorized budget assigned to schedule work.
- Earned Value (EV) is a measure of work performed expressed in terms of the budget authorized for that work.
- Actual Cost (AC) is the realized cost incurred for the work performed on an activity during a specific time period.
Earned Value Documentation and Government and Industry Guideline
According to the U.S. Office of Management and Budget (OMB) Circular number A-11 released in July 2003, the ANSI/EIA 748 compliance is required to obtain a portion of Congressional budget. Moreover, all major acquisitions with development effort are subject to the EVM policy in complying with the direction in OMB Circular number A-11. And in March 2005, the U.S. Department of Defense (DoD) announced that EVM would be required on any DoD contract greater than $20 million dollars and any DoD contract greater than $50 million dollars must have a validated EVMS.
For additional EVMS requirement details, supplemental information is provided below:
Exhibit 1: EVMS Requirement Details
(Source Data: Department of Defense Earned Value Management Implementation Guide, October 2006)
The ANSI/EIA 748 Intent Guideline #9 states “establish budgets for authorized work with identification of significant cost elements (labor, material, etc.) as needed for internal management and for control of subcontractors.” More so, it also states that “through a work authorization process, establish budgets for all authorized work to be done by the responsible organizational elements.” In order to fulfill and comply with the ANSI/EIA 748, at least three particular documentations are required as follows:
- Responsibility Assignment Matrix (RAM), according to PMBOK® Guide, is a grid that shows the project resources assigned to each work package. It is used to illustrate the connections between work packages or activities and project team members. Example is shown below:
Exhibit 2: Example of a RAM
- Work Authorization, according to PMBOK® Guide, is a permission and direction, typically written, to begin work on a specific schedule activity or work package or control account. It is a method for sanctioning project work to ensure that the work is done by the identified organization, at the right time, and in the proper sequence. Example is shown as below:
Exhibit 3: Example of a Work Authorization Document
- Control Account, according to PMBOK® Guide, is a management control point where scope, budget, actual cost, and schedule are integrated and compared to EV for performance measurement. CAP is a time-phased resource plan for planning and control since it represents the work assigned to one responsible organizational element for a single program. Example is shown as below:
Exhibit 4: Example of a CAP
Integration Methodology and Workbook Maintenance in Excel® with Examples
After development of WBS and OBS along with baseline establishment, as a supplemental to PM tools, how can RAM, WAD, and CAP be integrated in Excel®? One solution is to use the built-in features such as hyperlink function. Using hyperlinks within an Excel® workbook, EVMS-related documentation can be traced and be clearly organized. In implementing this solution, one recommendation would be to use a RAM as a summary. For example, if there is a change to a CAP, due to hyperlink usage in Excel®, the RAM will be automatically updated. Hyperlinks can be applied at the WBS column and will automatically guide a user to the designated WAD. More so, from a RAM, hyperlinks can be applied at the Budget at Complete (BAC) column and it will automatically lead to the designated CAP. An example is shown below:
Exhibit 5: Example of the use of hyperlinks in Excel®
There are many benefits utilizing project management information systems. However, documenting EVMS artifacts such as WAD, RAM, and CAP can be challenging for project managers or project controllers. For general usage, utilizing the project management information systems and generating reports and analysis from the tool can be difficult for many project stakeholders with non-technical backgrounds.
Excel® can be used as an alternative to document EVMS artifacts such as WAD, RAM, and CAP. In conjunction with using the program management information systems, by integrating WAD/RAM/CAP in a single data repository in the Excel® workbook, one can appropriately track EVM data from one document to another, which will provide transparency and integrity to the documentation package.
EVMS artifacts such as WAD/RAM/CAP can be effectively and efficiently documented using Excel® with ability to comply and meet the ANSI/EIA 748 EVMS implementation guideline. Using Excel® built in features such as hyperlinks and macros, EVMS integrated artifacts such as WADs, CAPs, and RAM can be traceable from one tab to another within a single repository workbook. This white paper suggests the integration of EVMS documents in Excel®.
Project Management Institute (2013). A guide to the project management body of knowledge (PMBOK®) – Fifth edition. Newtown Square, Pennsylvania: Project Management Institute.
Fleming, Q.W. and Kopppelman, J.M. (2000) Earned value management – second edition. Newtown Square, PA: Project Management Institute.
Defense Contract Management Agency (2006) Department of Defense Earned Value Management Implementation Guide, Retrieved on 17 July 2014 from https://acc.dau.mil/CommunityBrowser.aspx?id=19557/
National Defense Industrial Association (2005) Program Management Systems Committee (PMSC), ANSI/EIA-748-A Standard for Earned Value Management Systems Intent Guide January 2005 Edition, Retrieved on 17 July 2014 from http://www.srs.gov/general/EFCOG/02GovtReferences/03NDIAANSI/NDIAIntentGuide.pdf/
Department of Defense (1998) Department of Defense Handbook Work Breakdown Structure, Retrieved on 17 July 2014 from http://www.srs.gov/general/EFCOG/03OtherAgencies/MilHdbk881.pdf/
“Earned Value Management Systems Key Document Integration using Excel®” is an independent (publication) and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
Microsoft® and Excel® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
As used in this document, “Deloitte” means Deloitte Consulting LLP, a subsidiary of Deloitte LLP. Please see www.deloitte.com/us/about for a detailed description of the legal structure of Deloitte LLP and its subsidiaries. Certain services may not be available to attest clients under the rules and regulations of public accounting.
This publication contains general information only and is based on the experiences and research of Deloitte practitioners. Deloitte is not, by means of this publication, rendering business, financial, investment, or other professional advice or services. This publication is not a substitute for such professional advice or services, nor should it be used as a basis for any decision or action that may affect your business. Before making any decision or taking any action that may affect your business, you should consult a qualified professional advisor. Deloitte, its affiliates, and related entities shall not be responsible for any loss sustained by any person who relies on this publication.
Copyright © 2014 Deloitte Development LLC.
All rights reserved. Member of Deloitte Touche Tohmatsu Limited
©2014 Deloitte Development LLC
Originally published as a part of 2014 PMI Global Congress Proceedings – Phoenix, Arizona, USA
This paper should not be construed as and does not represent an endorsement on the part of
Deloitte for Excel® or any other Microsoft® product.