Posted by Enhancing Excel-Based Cost Models with PivotTable Reporting
Methods and Models I Track
MMT108_Presentation_EhancingExcelBasedCostModelswithPivotTableReporting_Mahmoud
Abstract:
A major challenge facing DoD cost estimators is developing Program Life Cycle Cost Estimates (PLCCEs) that serve as more than just an acquisition reporting “check-in-the-box” document. The ideal is to simultaneously create a dynamic, flexible tool that the program office can use to support their day-to-day costing needs, from answering a wide-range of cost-related data calls to integrating PLCCE outputs into their Program, Planning, Budgeting, and Execution (PPBE) processes. For DoD cost estimators that utilize Microsoft (MS) Excel to create their PLCCE models, a valuable tool that can be leveraged to achieve these goals is Excel’s PivotTable reporting capability.
Database and PivotTable:
In order to implement the PivotTable functionality in an Excel-based PLCCE, one must create a flat “database” that summarizes the PLCCE on one tab. This database will contain defined “fields”, comprised of rows and columns that categorize costs in a useful and meaningful way. Once the database is created, a cost estimator is able to utilize the PivotTable “Wizard” to create an initial PivotTable report and format it using the “Auto Format” capability. The PivotTable report(s) can be customized to fulfill any cost reporting requirement.
Risk/Uncertainty Analysis:
Cost risk/uncertainty analysis can also be performed using the MS Excel add-on tool “Crystal Ball”. After defining risk assumptions and forecasts, a run of the Crystal Ball tool will produce risk adjusted data to be extracted. The database table can now look-up risk adjusted forecasts from the Crystal Ball data extraction to provide a risk-adjusted database table, which can be used to apply a PivotTable report of risk-adjusted costs at various confidence intervals.
Real World Application:
The Space and Naval Warfare (SPAWAR) cost competency (SPAWAR 1.6) has utilized the risk adjusted database and PivotTable reporting tool to support programs within the Program Executive Officer (PEO) Command, Control, Communications, Computers, and Intelligence (C4I) organization for various data drills, including “what-if” scenarios for 5% and 10% budgetary cuts, Independent Government Cost Estimates that support contractor proposals during Source Selection, and many others.
Benefits:
The PivotTable tool and database structure provides a more flexible and dynamic cost reporting mechanism to support many cost-related data calls that would otherwise take days in a matter of minutes. It allows for detailed cost break-outs that can provide the basis for annual program spend plans or any other PPBE products. The PivotTable can perform a simplified cross-walking of Work Breakdown Structures (WBSs) / Cost Estimating Structure (CESs) and can serve as an additional cross-checking of existing cost summaries.
Disadvantages:
The PivotTable tool and database structure add additional file-size to Excel based models. In addition, the database structure takes time to implement correctly and cross check for accuracy.
Summary:
This paper intends to illustrate how PivotTable reporting provides cost estimators with an excellent tool to transform their Excel-based cost models into dynamic, flexible tools that will enable them to more effectively support the complex cost estimating needs of their programs.
Author(s):
Blaze Smallwood
Booz Allen Hamilton
Blaze Smallwood CCE/A graduated from Santa Clara University with a B.S. in Commerce in 2002 majoring in Economics and minoring in Mathematics, and is currently pursuing a M.S. in Information Systems from Illinois State University. He is employed by Booz Allen Hamilton as an Associate and serves as the co-chair of training committee within Booz Allen’s Applied Economic Analysis (AEA) Community of Practice (CoP). Mr. Smallwood specializes in cost estimating and economic analysis in support of Department of Defense (DoD) programs, mainly in Program Executive Officer, Command, Control, Communications, Computers, and Intelligence (PEO C4I) under the Space and Naval Warfare (SPAWAR) Enterprise. Mr. Smallwood has primarily supported Navy C4I programs, such as Global Command and Control System-Maritime (GCCS-M), developing cost estimating products such as Program Life Cycle Cost Estimates (PLCCEs), Cost Analysis Requirements Descriptions (CARDs), and Economic Analyses (EAs). He also provided cost analysis and modeling support to the Army Protected Communications-on-the-Move (PCOTM) satellite communications (SATCOM) terminal program, managed by PEO Command, Control, and Communications-Tactical (C3T).
Omar Mahmoud
Booz Allen Hamilton
Omar Mahmoud, CCEA, graduated from the University of California, San Diego with a Bachelors degree in Computer Science in 2000 and received his MBA from California State University, Long Beach in 2005. He is employed by Booz Allen Hamilton as an Associate on the Applied Economic Analysis team. Mr. Mahmoud is the lead cost estimator for a Major Automated Information System – Acquisition Category 1A program in the Program Executive Officer, Command, Control, Communications, Computers, and Intelligence under the Space and Naval Warfare Enterprise. Mr. Mahmoud has primarily supported Navy C4I programs, such as Global Command and Control System-Maritime, developing cost estimating products such as Program Life Cycle Cost Estimates (PLCCEs), and Economic Analyses. Mr. Mahmoud also provides training to Booz Allen Hamilton staff on subjects related to PLCCE Design and Modeling Techniques as well as Software Cost Estimation through the SCEA CeBOK training series.