Posted by

Time Phasing Cost Estimates in MS Excel Models

Models Track

Downloadable Files:



One of the most critical, and often difficult, elements of developing a cost model is the determination of how to phase costs across the life of an estimate. Agency Cost Positions (ACPs) and Independent Cost Estimates (ICEs) are often conducted early in a program’s lifecycle, before detailed technical specifications, let alone schedules, have been developed. Even in those cases where a decent schedule does exist, issues still arise concerning the best way to translate technical and programmatic milestones into executable cost/budget profiles. This task proves even more daunting as external factors, such as the needs of other programs, are introduced into the estimating and budgeting process.
A 2003 paper authored by Eric Burgess (Converting Time-phased Costs To Time-Phased Budgets) introduced another potential pitfall of phasing. Mr. Burgess proposed that many of the cost estimates developed for the Intelligence Community are based on program expenditures, resulting in annual estimates of expenditures. Before these estimates can be used as the basis for budgeting decisions, they should therefore be converted into budget profiles.
In 2005, Mr. Burgess created a Space/Ground Phasing model that incorporated the results of his 2003 paper. Using expenditure data collected from multiple systems, he developed two new schedule estimating relationships (SERs) for time-phasing the acquisition of space and ground systems. The results of these SERs are expenditure profiles, which Mr. Burgess’ model then converts into budget profiles by applying the published Air Force (AF) 3600 outlay rates.
This paper will continue to build on the work done by Mr. Burgess by expanding upon his Space/Ground Phasing model. One key area of interest is the impact of applying program- specific outlay rates versus the standard AF 3600 rates when developing budget profiles. While the standard rates provide a good basis for estimating new efforts, they can also artificially inflate budget estimates in the early years of a program.
The paper will also examine the possibility of further refining budget estimates by introducing a constraint mechanism into the model. Estimates based on schedule estimating relationships are often good starting points, but some amount of “tweaking” is often necessary to reflect real-world factors, such as budget constraints. For example, if an estimate of Program A indicates that $100M should be budgeted in 2010, but 2010 budget is fixed at $80M, what should happen to the remaining $20M that was estimated for 2010?
Finally, the paper will provide an enhanced time-phasing model. This model will incorporate the additional features mentioned above, as well as a new customized interface to improve integration with Excel-based cost models.


William Black
Scitor Corporation
Mr. Black began his career as a financial analyst with the Boeing Company in 2003, after graduating from Virginia Tech with degrees in Accounting and Psychology. He remained with Boeing for a little over a year, during which time he began developing tools using MS Excel and Access. In 2004, Mr. Black joined Northrop Grumman – TASC as a cost analyst, primarily supporting the National Geospatial-Intelligence Agency (NGA) and the Navy Engineering Logistics Office (NELO). He acted as the project lead for support to NGA’s Acquisition Directorate and the Office of the CIO, led the development of several Agency Cost Positions, and developed numerous tools and models in the MS Office suite. In 2009, Mr. Black left TASC to pursue opportunities with Scitor Corporation. He has since been involved in a business process re-engineering effort for the NRO, as well as several contract reconciliations and cost estimates. Currently, he is part of the cost estimating team that functions under the Office of the Director of National Intelligence (ODNI)’s Cost Analysis Improvement Group (CAIG).

Matt Reiley
Quaternion Consulting Inc.
Matt Reiley is a Senior Analyst at Quaternion Consulting Inc. (QCI) where he serves as the contractor cost analysis technical lead for the Office of the Director of National Intelligence
(ODNI) Cost Analysis Improvement Group (CAIG). He has ten years of experience performing cost and risk analysis, including Intelligence Community Major System Acquisition Independent Cost Estimates and Assessments, Architectural Trade Studies, Analysis of Alternatives, and methods and model developments. Matt has supported the ODNI CAIG since its inception in 2005 and prior to that time supported the Director of Central Intelligence (DCI) Community Management Staff (CMS) and the National Reconnaissance Office (NRO) CAIG. He received a B.S. in Mechanical Engineering from Pennsylvania State University

Jeffrey Jaekle
Scitor Corporation
Mr. Jaekle joined Scitor Corporation in January 2009 as a consultant, providing cost estimating and financial management support to both Intelligence Community (IC) and Department of Defense (DoD) customers. When he first joined Scitor, Mr. Jaekle provided cost estimating and source selection support to the National Reconnaissance Office (NRO) as well as provided business case analysis support to the Air Force. Currently he provides cost estimating and research support to the Office of the Director of National Intelligence (ODNI) Systems & Resource Analyses/Cost Analysis (SRA/CA) where he works on a variety of systems throughout the IC. Prior to Scitor, Mr. Jaekle worked for Northrop Grumman – TASC from June 2006 to January 2009. While there, he provided cost estimating, financial management, modeling, risk analysis, and business case analysis support to a variety of customers including: the National Geospatial Intelligence Agency (NGA), the Naval Engineering and Logistics Office (NELO), NAVAIR, and internal support to Northrop Grumman. Mr. Jaekle as authored or co-authored multiple SCEA papers and is a SCEA certified analyst. Mr. Jaekle graduated from the College of William and Mary with a degree in Economics.