I wrote a series of articles on spreadsheets for beginners starting in the July 2018 issue of ELECTRICAL CONTRACTOR. Now, I would like to present to you some ideas for everyday uses of this powerful electronic tool.
As estimators, we spend much of our workday manipulating numbers. Most of us use a dedicated electrical estimating program that completes our calculations with speed and reliability. However, there always seem to be a few things the estimating program cannot do. This is the time to break out a tool that can do almost anything with numbers and text. The following are some examples of how I use spreadsheets.
Some readers may think it is overkill to use a spreadsheet for simple addition and subtraction. However, I like being able to see that the column of numbers I just added are correct. Yes, I know Microsoft Windows has a nice calculator, but you cannot make corrections to a completed calculation. You must start over if you make a typo. If you notice an incorrect number on a spreadsheet, you can retype the entry and the math is recalculated instantly. The simplicity of editing on a spreadsheet also makes for a great what-if analysis, since you can change numbers back and forth so easily.
I do plenty of estimates that include complex duct banks, but I have never had an estimating program that could calculate the trench size required for a particular duct bank. When I got fed up with drawing out duct banks and making manual calculations, I made a spreadsheet. One portion of the spreadsheet is a graphic representation of the duct bank. Simply enter the number of inches for each conduit, spacing between each conduit and inches of concrete cover for one row and one column of the duct bank. The other portion of the spreadsheet performs the math to give totals on trench and duct bank sizes. I also added a section to deduct the voids created by the conduit from the total amount of concrete needed.
For various reasons, I don’t always take advantage of the fixture assemblies in the estimating software I use. One reason is that some projects have many fixtures that are so new they are not in the software’s database. If I am not using the fixture assemblies, I export the fixture counts to a spreadsheet. I then add columns for items such as installation and support type. Finally, I copy the fixture quantities to the appropriate columns for installation and support. After adding a totaling formula for each column, I can use the totals to enter the fixtures into the estimating system. A new casino would be an extreme example of a project I would use this spreadsheet for, because they usually have dozens of custom fixtures to analyze.
Keeping a record of your bids is time consuming, but essential. You can make it easier and more efficient by using a spreadsheet. For each bid, record the project name and type, the price you bid to each general contractor, the material-to-labor ratio, the square-foot price and the price per unit, if applicable. You can also include notes about the project. If you are able to get the information, include who was awarded the project and what the price was. For most public projects, this information is readily available, but will be harder to get for private projects. Of course, feel free to add any other information to the record you deem important.
Once this information has been gathered for a while, you can analyze the data for competitive information, such as what kind of projects you are most successfully bidding, which general contractors you have the best success with, who your strongest competitors are and which markups are working. These records may even provide insight into general contractors’ bid shopping practices. If you are up for learning some more advanced spreadsheet features, you can prepare all this analysis automatically by using the spreadsheet’s data tools.
Let’s get personal
You can also extend the power of the spreadsheet into your personal life. I have built many tools for myself, including mortgage refinance decision matrices, lists of streaming service pricing and features, area calculations for getting new flooring, an inventory of all the books I own and a list of food losses for an insurance claim when my freezer died.
There is no end to the value of spreadsheets. When it comes to organizing data and calculating numbers, they are a must-have.