2013-ET04

Utilization of Visual Basic in Cost Estimating and Analysis Tools – Anyone Can Cook

eTrack

ET-4_Presentation_UtilizationOfVisualBasicInCostEstimating&AnalysisTools_Eden

ET-4_Paper_UtilizationOfVisualBasicInCostEstimating&AnalysisTools_Eden

Abstract:

As collaborative environments become more prevalent in all industries, the cost estimating and analysis industry is no exception to this movement. Increasing amounts of pressure are put upon cost estimators and analysts to develop tools that are low cost, fast, robust in design, have long standing methodologies, do not require proprietary software or licenses, and are and easy to use by both the advanced cost estimator looking for maximum control and the novice simply trying to diligently support the early stages of a development program.

The effort to improve the flexibility and usability of cost estimating tools is particularly challenging because many novice end users do not understand the principles needed to correctly develop and document a cost estimate, do not have the advanced computer skills to execute such an effort, or both . While most non-cost estimators can provide critical information associated with the development, procurement, and sustainment of a program, many are not trained nor have the time to develop methodologies, conduct cost research, properly document assumptions, keep organized records, and provide meaningful up to the minute reporting. Additionally, when cost estimating tools are developed, they must take into account the advanced user who may have extensive cost estimating experience, currently use cost estimating and analytical tools, provide advanced reporting, and require direct access to the methodology of a tool. The utilization of Visual Basic for Applications (VBA) in cost estimating tools, particularly in Microsoft Excel, is a rich resource to address the situation. By providing access to features like customized user interfaces, specialized tool outputs, selectable methodologies, tiered user access, and dynamic help menus a user can produce surprisingly comprehensive and accurate results with minimal training, time, and experience. In the last few years these tools have become incredibly popular and continue to be requested by clients.

As a result of numerous inquiries by ICEAA members, this paper will explain many of the challenges and advantages of utilizing VBA in the construction of Excel-based, parametric modeling centric, cost estimating, and analysis tools. This paper also will provide an introduction to best practices in the design of user interfaces; help files; inclusion of supplemental tools; data archiving; coordination with existing cost estimating tools and those under development; and provides examples of current tools developed with extensive VBA resources.

Author:

Jeremy Eden
Booz Allen Hamilton
Mr. Eden has been with Booz Allen Hamilton for 6 years, assigned to the Defense Business Segment’s Analytics practice. Mr. Eden provides project, business, analysis, and cost estimating, support primarily for Department of Defense (DoD) clients including the United States Marine Corps (USMC). Mr. Eden has over 10 years of experience performing feasibility studies, intelligence analysis, design, implementation, maintenance, and reporting of engineering and economic projects. For the last 4 years, Mr. Eden has supported The USMC Logistics Requirements Funding Summary Cost Estimating Tool (LRFS CET) task. Mr. Eden co-authored the USMC LRFS CET for non-cost estimator logisticians to use in the completion of an LRFS. This tool utilizes heavy usage of Visual Basic, an interface that “interviews” the user, interactive help feature, and an extensive model database. Mr. Eden has also recently participated in the development of the Defense Threat Reduction Agency (DTRA) Travel Highly Optimized Reporting (THOR) Tool, DTRA Country Risk Assessment Tool, Joint Light Tactical Vehicle (JLTV) Affordability Cost Tool, and many others analytical tools. Mr. Eden is a graduate of Virginia Tech and is a certified Project Management Professional.