Key Results Summary

Automating Equipment Bid and Specification for Bulk Material Handling Systems

Situation:

A Midwestern firm specializing in the design-build of pneumatic conveying systems required a standalone program to perform the necessary calculations and specifications to bid the sale of bulk material transfer systems (i.e., all equipment required to move grain from a railcar into a processing facility). The engineers preparing the bids were using preprinted forms, numerous equipment catalogs with specifications, and reference books with tables of data about coefficients and other technical data. They used calculators to make computations as they filled in the forms.

Each configuration had a number of variables. How many feet of vertical lift? How many feet of horizontal run? How many bends in the pipe run? How large of a pipe? How much material was to be offloaded? How quickly did it need to be offloaded? Also, what material? Sugar for example cannot be moved too quickly or it will heat up and begin to melt.

Many of the calculations were iterative in that making a choice further into the process changed a value that preceded it. This in turn changed all subsequent calculations which had to be review or tested to make sure they fit the criteria or limits for the system configuration.

The major problem was that as an engineer got further into the configuration and specification some changes or additions required them to go back and revisit all of the calculations, equipment specs, data table look-ups, and any other variable up to that point. They were essentially starting over with a new sheet and repeating the process.

The client requested an automated solution and that the program run on a laptop so it could be used in the field and that all of the reference and specification data be kept current.

Action Plan:

Several engineers were used as subject matter experts. Each demonstrated and explained numerous configurations they were bidding. This review process provided the necessary understanding of where in the process it was necessary to go back and revisit prior choices and calculations. Feedback from the engineers indicated that they liked the pre-printed worksheets because they structured the steps so that nothing was missed. However, having to start over numerous times was frustrating.

The reference data and equipment specifications materials were identified and reviewed. The reference data was is large books. Each book had numerous tables of data that had to be used for calculations or as criteria in decision making. The table data was actually created from underlying formulas. The books of tables were simply a convenience. With the formula you could calculate what was needed rather than look it up in a table in a book.

Equipment vendors were contacted and asked to provide their product specifications in electronic format and to provide updated files as changes were made.

The client provided fifty old bids were to be used for development and acceptance testing.

Results:

The application was implemented as a spreadsheet workbook. Some workbook sheets contained tables used in the calculation process. Others contained equipment specifications. Macros were written for each vendor’s data file format to convert it into specification tables for use in the system. All of the reference data was created from underlying formulas. This reduced storage since values could be determined as needed.

Macros were used to move the user through the bid and specification creation process. Formula driven logic tests determined when recalculations were necessary and also when the user had to revisit earlier choices. The engineers could simply fill in the variables and as the choices, specifications, variables, and other data were developed the underlying formulas would direct the user to what they needed to do next. It situations where calculations were iterative, tests were coded to force enough recalculations to make sure that all values were “cycled” through to eliminate any discrepancies.

The final data was formatted on an output sheet that could be printed with client data, system parameters, equipment and specifications, as well as the system bid.

When the system was finished the fifty old bids were tested. Seven of them did not come up with the same results. Research uncovered that the old bids had calculation or look-up errors in them and that the new system was calculating correctly.

The master application file as placed on a server so engineers could download the current version each time they created a new bid. An administrator was assigned to maintain the vendor specification data as vendors provided updated files. Engineers reported they could complete bids and specifications in 60-70% less time.