Explore the intricacies of spreadsheet-based estimations and their vulnerabilities, as well as the importance of double-checking your calculations to ensure accuracy. Understand the responsibility of the estimator in identifying true costs and validating data through cross-check methods.
Key Insights
- Spreadsheets are highly flexible for various estimating requirements but are prone to errors if not used or modified correctly, with open formulas that can potentially be corrupted or disrupted.
- Database-driven estimation software offers protections against these issues, but it is ultimately the responsibility of the estimator to apply appropriate quantities and understand the true costs.
- It is essential to use multiple methods to validate cost estimates, such as the 100% check or cross-check method, which involves comparing row and column totals to identify any calculation errors.
This lesson is a preview from our Construction Professional Course Online (includes software). Enroll in this course for detailed lessons, live instructor support, and project-based training.
So two of the offsetting factors of estimating with a spreadsheet are that spreadsheets are extremely flexible to meet any specific estimating requirement, and also that spreadsheets are extremely vulnerable to errors when not used or modified correctly. So it's important to keep in mind that spreadsheets that are commonly or frequently used for estimating have open formulas that can actually be corrupted, or rows could be deleted, affecting other costs. Database-driven estimates or real estimating programs that take a lot of this into account have many protections to prevent this from happening, but nothing is bulletproof; if you don't apply the appropriate quantities, you'll still end up with incorrect costs.
So no matter what you use—whether it’s a piece of software, database-driven software, or a spreadsheet—it is truly the responsibility of the estimator to understand what the costs actually are and provide the correct cost regardless of the application being used. Let’s look at another example of validating the estimate cost, which I refer to as a 100% check. There are a number of ways to do this, but be sure to use more than one method to double-check your math and ensure that it adds up.
In this particular case, you can see that I actually use calculations to compare row totals versus the column totals. So this 100% check that I use is considered a cross-check method for validation, and you can see that the math actually calculated as a 100% check, meaning that the numbers are accurate; however, in the example below, the calculation for the total amount was overwritten, and therefore it shows that there’s a calculation error. In other words, the dollar amounts don’t line up.
This is not uncommon. Again, it’s a spreadsheet, and a lot of things can happen to a spreadsheet where valid information can be disrupted, replaced, or accidentally overwritten. The formula may be lost, but this is one way of verifying and validating that something took place—something actually changed that’s giving you an error in your total cost.
Compare the combined column subtotals and the row totals of the same group of items that you see below. If the two totals don’t match, then it’s defined as a calculation error that needs to be checked. There are a number of ways to do this.
Learn Construction Estimating
- Nationally accredited
- Create your own portfolio
- Free student software
- Learn at your convenience
- Authorized Autodesk training center
I’m just showing you one example that I use to quickly identify any errors that may be in the spreadsheet. Go ahead and take a look at your spreadsheet and see if you can break it, and once you do, you’ll be able to see the calculation error. Then just go ahead and hit your undo button to bring it back to the correct calculation.
Incorrect calculations are typically the result of adding and deleting rows and columns. This is what makes spreadsheets vulnerable. When adding or deleting rows or columns in an estimate spreadsheet, always check to see if the related formulas are correctly calculating the changes.
And as mentioned earlier in the lessons, never hide rows or columns. And if you have to delete a row or column, double-check all the math associated with it.
