Posted by

Utilization of Visual Basic in Cost Estimating Tools

Software/Hardware Track



As collaborative computing environments become more prevalent in all industries, the cost estimating industry is no exception to this movement. Increasing amounts of pressure are put upon cost estimators to develop tools that are 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 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 novice users can provide critical information associated with the development, procurement, and sustainment of a program, many are not trained to develop methodologies, conduct cost research, and properly document assumptions. Additionally, when cost estimating tools are developed, they must take into account the advanced user who has extensive experience in cost estimating, using cost estimating tools, and requires 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 to get a development program off to a good start.

This paper will explain the challenges and advantages of utilizing VBA in the construction of Excel-based, parametric modeling centric, cost estimating tools. This paper also addresses 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.


Jeremy Eden
Booz Allen Hamilton
Mr. Eden has been with Booz Allen Hamilton for 5 years, assigned to the Defense Business Segments Analytics practice. Mr. Eden provides cost estimating, project and business analysis support. Mr. Eden has over 9 years of experience performing feasibility studies, intelligence analysis, design, implementation, maintenance, and reporting of engineering and economic projects. For the last 3 years, Mr. Eden has supported a task for the United States Marine Corps Market: The Logistics Requirements Funding Summary Cost Estimating Tool. Mr. Eden co-authored a cost tool 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. Previous work has included supporting the United States Defense Information Systems Agency (DISA) Defense Information Systems Network (DISN) Diversity Study. Mr. Eden co-authored a cost tool for assessing DISA’s worldwide fiber-optic network diversity effort. This tool utilizes regression analysis of previous diversity actions, as well as current market and industry parameters to provide high confidence estimates of the impact of the program on current and future agency budgets. Mr. Eden also supported the United States Marine Corps Joint Light Tactical Vehicle (JLTV) Cost Analysis Requirements Description (CARD). This task included the editing, updating, and modifying of a CARD document on a weekly basis as customers requirements and objectives changed. Other previous work has included support for the United States Marine Corps Amphibious Assault Vehicle (AAV). Mr. Eden provided support in editing and updating the cost model analysis for the AAV program. This support included the utilization of Visual Basic for Applications (VBA) script modification, specifically for Microsoft Excel to create a final tool that was complete, but available for further utilization and modification by the client as requirements changed.