I was recently following a forum discussion about issues related to using spreadsheet software for estimating. It turned into a spreadsheet–versus–database argument and got a little heated. The truth is both systems have a place in your estimating toolbox.
Spreadsheet opponents felt these programs are prone to user errors, such as incorrectly designed formulas. The main argument against database programs was they lack flexibility, forcing estimators to do things one particular way. Neither argument is necessarily true, so let’s do some analysis.
Spreadsheet software is extremely powerful, and the basic functions can be learned in a few hours. Dig a little deeper, and you’ll be amazed by all spreadsheets can do. In fact, a spreadsheet can also be a database. Let me explain.
A database is simply a collection of data and can be visualized as a bunch of rows and columns. Each column is a field or category of data, such as description, price, discount or labor unit. Each row is a record, such as a 4S box with its price and labor unit. Once you have entered thorough records for all of your materials, you have a completed database. Some electrical contractors have built fully functional estimating systems with a database, using spreadsheet software.
Spreadsheets have several advantages over database systems. The biggest is flexibility. Almost any calculation you can imagine can be automated in a spreadsheet. During the beginning of my tenure at my second estimating job, we were doing estimates by hand. We usually had dozens of standard preprinted pricing sheets and recapitulation (recap) sheets to process. It was a madhouse on bid day. The totals on the price sheets for material and labor had to be transferred to the recap. Then, all of the labor rates, quotes, subcontractors, rentals and direct job expenses had to be written on the form. Next, the markups for sales tax, overhead and profit had to be written in. Finally, the math for the whole recap had to be done, usually on a paper-tape calculator. Just when you thought you were done, a new lower quote for lighting fixtures comes in, and the math had to be done again. Many times, I erased all the way through the paper.
One of the greatest highlights of my working life was the day the boss purchased an estimating system, which came with an Apple II computer. Unfortunately, the program did not have a recap feature, so we still had to do those by hand. The computer also came with a spreadsheet program called Visi-Calc. Once I understood its capabilities, it did not take me long to create my first spreadsheet template, an exact replica of our paper recap. Now, all of the math could be done almost instantly, and every time there was a change, the program could recalculate the math in seconds. Bid day was no longer such a madhouse.
Spreadsheets have amazing analysis capabilities. One of the most noted is creating “what-if” scenarios. The simplest of these is the ability to see what happens if you change one or two numbers. The software immediately recalculates the entire template so you can see the result. If you don’t like it, you can change it back, and it is recalculated again. Compared to a manual system or even the computerized estimating systems of the day, which required you to create a new printout every time you made a change, that was simply amazing to me.
There are a couple of downsides to using spreadsheets. One is the security of your data, since spreadsheets save only on command or with auto-save. Any data entered between saves could be lost if your computer shuts down for any reason.
Another downside is data creation and management. Creating and maintaining a large spreadsheet estimating database is a huge task. Imagine the time it would take to create 100,000 items, including, at a minimum, entering size, description, price and labor for each item. Then, you would need to create at least a few thousand assemblies that tie back to the items you created.
The main downside, as I mentioned above, is inaccurate formulas. When you create a spreadsheet template, it must be audited, or tested, thoroughly. Fill in the template with some actual numbers from another bid, and compare the results to your original bid. You should also use a calculator to check your results. Another method I use is to create check figures. It is often possible to calculate a total using two different methods. If the results don’t match, there is a problem.
Spreadsheets have a place in your office, either as your entire estimating system or a supplement to others. Next month, I will discuss databases.