Ten Common Estimating Spreadsheet Errors
“I’ve got good news and bad news,” your estimator says. “The bad news is we got the bid.” “Then, what’s the good news?” you say. “We’ll only lose $10,000 on it,” says the estimator.
Underestimating a project or losing bids by overestimating can seriously erode profits. Too often these problems are caused by errors in spreadsheets. A 2004 PricewaterhouseCoopers study shows that up to 91 percent of sophisticated spreadsheets contain errors. A 2007 study concludes the error rate is 94%.
That sounds about right considering how often I find errors in my own spreadsheets. We’d all like to be error free. Knowing the sources of spreadsheet errors may be helpful in eliminating them.
You’re not alone! These happen to even the most experienced estimators:
- 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.
What do you do in your company to prevent spreadsheet errors?