I was hired by a large mechanical contractor in 2002 and 03, mainly for my mechanical engineering gifts but I was also asked to create a digital system for analyzing mechanical contracting bids.  The vice president of the construction firm was already keen on use of the firms historical data to assist the bidding. I was very happy and excited to make this work for him and the team. we both believed that this business intelligence, made available during the estimating process, combined with the firms knowledge of mechanical construction, would make a difference.  

They already had a long established practice of using spreadsheets (Microsoft Excel). the estimators had a strong attachment to their own customized Excel spreadsheets on top of that.  Of course, there were other related tools for materials and equipment takeoff and bidding. One such tool was Quickpen, a subscription based price estimating system, used with a "lightpen" input.  Most of the time, the light pen was used on contract drawings, if they had to do an exacting takeoff and there was enough time.  Occasionally, with great effort, they could take Autocad drawings directly from an architect or  engineer, transform (beat it into submission)  and input it to Quickpen.    Not very automated, in my opinion.  Early 80s system IT was finally arriving at the mechanical contracting business.

When I first got into the design, the vice president wanted the spreadsheet templates to do nearly everything and anything.  He wanted  real time displays to tell him how this bid compared to the past bids.  To price projects with greater certainty, I knew we had to access as many relevant pieces of data as possible. We would use past bids in detail, as well as cost data from most recent databases.  They were already getting labor and equipment prices dynamically updated from subscription services and that helped. It had to be integrated somehow as well. 

They were doing all of the above in an ad hoc manner, while they were still preparing the bids. It was burdensome and sometimes looked like a chinese fire drill as the deadline approached.  It was sometimes reduced to rummaging around in the file cabinets for "last Aprils hospital bid that looked like this one".  I decided to explore "how the questions emerged" to get a concept. I had to get a handle on questions like :  "Show all the health care bids,  with the cost per square foot, broken down between plumbing, sheetmetal and hvac piping."  I knew that he was trying to see patterns in the companies experience as a data mining task.  We could also begin to find where our true advantages over the competition lay, and even perhaps, even quantify them!!!).

Anyway, I was also charged with creating a system for collecting and storing all of the companies mechanical bids from that point on. Without any migration away from Excel.  Not to even think about it.  To make things more interesting, a sister division of the firm (in a distant state) was using spreadsheets with all labor and equipment prices already included. Their fast bid turnaround time had become interesting to the VP and he now wanted to copy this too. They were mostly a design-build market however and we were mostly in the competitive and negotiated plans and specs. That fact was only marginally relevant. I found that fact to be a bit of a problem later. (A story for another time) In any event, the vice president and I agreed that we would try to better served by migrating toward more automation, like this sister firm.

The task was complicated by all the little 'parts' that go into mechanical projects, typical of mechanical construction. To look at the bid history, we needed 4 years ($900million worth) of bids to be input from paper or excel  into the same database for analyzing (We could also do some real data mining)  I was blessed to have the assistance of a graduate engineer and a computer science intern to work on this, and we rapidly created some usable collections of data. It grew gradually, as I was always busy designing and selling,  but eventually, the "Mechanical Sales database" was born.

The vice presidents concept was that the spreadsheet system was going to stand alone, but I knew that his demanding queries required a dependent connection to the database.   So it happened that the estimating spreadsheet was already their preferred method for inputting takeoff and cost details. and is ever more still tried and true, and anything else is suspect. The concept of linking the spreadsheets information to the database seemed daunting.  There was not much help among the estimators, they preferred to be left to their own devices .

Its an ingrained habit. Spreadsheets are the old standby, the one thing everyone can relate to. All this was still supplemented by physical files kept by the project estimator. This was used along with the Quickpen takeoff and pricing system to create accurate record of the material and labor quantities, costs and other details of the bid estimates. We have keyed in this data going back to the beginning of 2000. 

Filter screen ----- "Looking for patterns" page          

Input screen -----“Bid Summary” Tab (below)

BACK to main page

Using the Database

Two main screens for the users are the data input screen and the filter screen.  The “Input” screen is for data entry and the “Filter Select” screen is used to research and analyze and create reports with the data. Estimators will discover that the filter select screen is useful for their analysis.  

Microsoft Access Main screen -----“Mechanical  Sales Bid Database"