I just read a good article from CFO.com that detailed the worst practices that people adopt when using spreadsheets – the most widely-used financial application out there. Most of us that have used spreadsheets at all in our jobs have seen poorly designed spreadsheets that were hard to follow, had incorrect formulas or were just plain sloppy looking. I study by PriceWaterhouseCoopers showed that over 90% off all spreadsheets contained errors. Adopting some sound practices around how spreadsheets are designed, handled, and updated can go a long way to cut down this error rate.
Among the worst practices cited by CFO.com, were: poor segregation of data, poor documentation of assumptions, poor documentation of constraints, and difficulties in making changes.
I will not go into a lengthy discussion about each of these offenses here, mainly because CFO.com already has, but one of the worst that I run into is poor segregation of data. Do not put multiple data points into a single cell and enter your calculation criteria in that cell. For instance, do not add four quarters of revenue by putting this in the cell: “=123544+127584+129848+153287”. Instead, create separate cells for this data and label each item appropriately. It is difficult for others, or even for yourself weeks later, to decipher what the numbers are that are arriving at the result. A properly designed spreadsheet will spell it out for the user.
Another practice that causes countless hours of rework for users is when spreadsheets are not originally designed to make changes easy. When formulas are entered as in the above example, it would be necessary to reenter all of the data if your estimates change for just one quarter. This vastly increases your likelihood for error.
By implementing these and other simple solutions mentioned in the CFO.com article, you can make your lives a lot easier by reducing the amount of time you spend tracking down discrepancies and rebuilding spreadsheets.