Having worked as a business analyst I always have this fear of undiscovered errors when I present my Excel reports to my management. Recently, while working on an Excel project, I uncovered about 10 cells of calculation errors in a forecasting spreadsheet. The executive manually compiles this forecast for a logistic oriented company. He needs to manually calculate 30-50 values using pivot tables and copy them into an Excel report template.
Studies have shown that Excel spreadsheet errors are extremely common. Humans appear to have an error rate floor (ERF) that exists even when they are working very carefully. Everyone has a similar error rate floor, and working more carefully can decrease one’s error rate only modestly. Not a very comforting thought when you are working on an important spreadsheet.
My strongest advice to clients is to automate the most tedious and error prone parts of your Excel spreadsheets to reduce such calculation errors. I always argued that such revamped Excel application can be proven to give productivity gains. The calculated savings will be a key decision factor in whether you find it worthwhile to go forward in this project. Key variables in estimating cost of your present reporting process can include: (reporting freq per month) * (time needed) * (no of people involved) * (potential costs in reporting errors). The future gains will certainly exceed the present costs.