Ten Common Estimating Spreadsheet Errors
Don’t underestimate spreadsheet errors!
Underestimating a project or losing bids by overestimating can seriously erode profits. Too often these problems are caused by errors in spreadsheets. A PricewaterhouseCoopers study once showed that up to 91 percent of sophisticated spreadsheets contain errors. Another study concludes the error rate is 94%. Regardless, errors are prevalent, so let’s look at ten common estimating spreadsheet errors.
According to research done by ZDNet, errors can be extremely costly. Horror stories abound, but here some of the better-known classics:
- Hiding cells — instead of deleting them — cost Barclay’s bank millions during the 2008 meltdown.
- A cut and paste error cost TransAlta $24 million.
- Another cut and paste error cost JP Morgan $6 billion when a Value at Risk model was miscalculated.
10 common Estimating spreadsheet errors:
- Hardcoding your values in the spreadsheet formulas resulting in right this month and wrong forever
- Adding rows or columns that don’t get included in the range of the sum, subtotal and average functions
- Losing unsaved changes due to interruptions
- Data entry errors such as skipped entries or transposed numbers
- Saving “what if” changes over the Excel worksheet you want to keep
- Formula errors like parentheses errors in a mathematical formula; formulas pasted over with constants; or formulas no longer calculating properly when you delete an item
- A bad query copied into a spreadsheet
- A bad sort that destroys the integrity of a row
- Old spreadsheet errors copied over and over again
- Maintaining a number of templates for specific types of projects, which multiplies the risks of data entry errors, omissions, transposed numbers, and outdated pricing
One way to reduce the number of spreadsheet errors is to limit access to files or locking certain cells. You may also want to implement quality-control procedures for catching data entry errors in spreadsheets, including input controls, validation, and cross-footing.
An example of an input control is reconciling the data entered into the spreadsheet to the source records. A common-sense suggestion is to separate the data entry and formula areas on a spreadsheet.
Finally, it’s a good idea to have the logic of the formulas within the spreadsheet inspected by someone other than the user or developer of the spreadsheet.
Prevent errors with true estimating software!
Reduce your risks by moving to the industry-standard — Sage Estimating software. Win more, bid more with industry-specific cost databases. Learn more here.
Worried about pricing? This article will help: 10 Ways Your Construction Software Can Pay for Itself.
- Accounting and Taxes
- Business Advice for Construction
- COVID-19 Help
- Lean Management for Construction
- Press Releases
- Professional Services
- Sage 100 Contractor
- Sage 300 CRE
- Sage Customer Relationship Management
- Sage Estimating
- Sage Intacct
- Thought Leadership
- Year-End help Sage CRE
- Sales Tax Processing within Sage Intacct #MakeYourForemanHappy
- Sage Intacct Real Estate: Interview with a Sage Expert #MakeYourForemanHappy
- Summer Sage Intacct Free Training Dates- RSVP today! #MakeYourForemanHappy
- Sage Intacct and Procore Integration #MakeYourForemanHappy
- ETHOSystems named one of Bob Scott’s Top 100 VAR Stars in 2022!