Global Manufacturing Company Needed a Flexible Estimating System.
European manufacturing company.
This custom equipment manufacturer had too many possible permutations for an estimating system with a fixed logic.
Develop an application which would allow the user to modify the estimating logic without having to request programming changes.
- Visual Basic
- Microsoft JET (Access) Database
This company designs and manufactures large scale industrial equipment, which it sells worldwide. Their machines are customised to match each customer's requirements, which resulted in their own requirements for a flexible estimating system.
Their existing system had a somewhat lengthy legacy - starting in the 1980's when it was initially developed on a Burroughs mini-computer.
Some of that data was later extracted and the application extended by running it firstly on the Lotus 1-2-3 spreadsheet system and then moving it into the somewhat radical Microsoft Windows environment using Microsoft Excel v1.0.
It continued to be enhanced in Excel until late 1989 when the decision was taken to move it into a database environment to allow for further expansion plus the ability to have a storage mechanism for keeping all estimates online.
The chosen application was DataEase (DOS based). This development was handled internally with some external assistance. The spreadsheet layout/cell references were retained which helped with the conversion of the Excel formulas and macro logic. This resulted in a major step forward and remained the primary estimating tool for the next six years.
It started to become apparent that the program logic in the DOS based system was constantly being modified as the requirement to constantly refine the estimating process was ongoing. Internally the application was becoming messy and the skills to be able to maintain it were becoming less available. Response times for generating estimates were also starting to increase (due to all the additional logic) and the ability to move data into the various Windows application was cumbersome.
The decision was taken to start a new evolution of the estimating system - this time as a native Windows application.
One of the our key design decisions was to take all the hardcoded, database specific, logic and make it much more generic. At the same time, we stored that logic as meta-data (i.e. data about data stored in the database). This allowed us to design and develop a database logic-processing engine.
This engine would read the estimating logic, process all the calculations/conditions and generate the estimate data. This allowed anyone familiar with the estimating system application to modify the estimating logic (rules) using another custom written Logic Editor application. Having also retained the Excel cell-naming nomenclature, little more than basic Excel skills are required to make these adjustments. Any changes made using the Logic Editor are logged/audited, so a full history of how the estimating rules have changed is available.
The system is fully multi-user, allows estimate locking (once complete), estimate copying and generates estimates in a few seconds.
In the 10+ years this system has been in place, literally no changes to the application have been made in terms of how estimates are generated. The logic undergoes constant tweaks however, allowing the estimators to continually refine and improve their estimating accuracy.
Many changes have been made to the application in terms of enhancing it to interface to Excel. This creates a fully functional spreadsheet for the sales people. From this spreadsheet they are able to record all the customer details plus their various selling prices, margins etc. All of this is backed by the detailed estimate items, allowing the sales people to change quantities while at the customer premises.
The customer benefits from having an estimating system with a reasonable amount of flexibility while at the same time, retaining rigidity where rigidity is required. A tool like Excel leaves the system too open to significant "tinkering" and over the years it becomes impossible to reliably re-use estimates or the data. Sometimes they may copy an estimate from a machine made a number of years ago and re-generate it at today's costs and using their current logic/rules. This is where some rigidity is paramount but with the flexibility to easily lift that old data and utilise it in the current environment.
Further plans are afoot to further develop this application. Some of the ideas on the table include a forms editor (to allow the estimators to add new fields to their various input screens) plus interfacing it directly with a new equipment sizing/design tool. This would allow the designers to see the cost impact of the various design configurations as they try them out. This would then be passed on to the estimators for further refinement and conversion into a fully fledged estimate. From this, we would then have the basic data to generate a template proposal (bearing in mind that every solution offered is bespoke to the customer). Along with this development would be a move into the SQL Server environment for the database. This would open up the possibility to then retrieve current costing data from the company's ERP system.