Spreadsheets Don't Lie

Spreadsheets are everywhere as the application of choice for model building and data analysis so they must be correct. Is this true?

Many finance professionals acquired their first IBM PC back in the 1980s simply to help with budgets and forecasts and reduce their dependence on accounting paper. Since that time, Office has taken over most desktops as the standard commercial package.

After all this time and so many reports of expensive mistakes, why is it that practitioners are still over confident that their spreadsheets are correct, do not see the need for formal audits and blindly use their results? Poor spreadsheet discipline can cost money. These links show that even the largest firms can make some basic mistakes:

Spreadsheet Mistake Costs Tibco Shareholders $100 Million
How The London Whale Debacle Is Partly The Result Of An Error Using Excel

Similarly, poor spreadsheet security can also have consequences:

University of East Anglia leaks confidential and highly sensitive student data in mass email

Practitioners should understand that, although spreadsheets are simple at a basic level, they need to adopt a structured approach with some basic development rules. Here are some elements of the approach Systematic uses for developing commercial applications:

• Keep it simple and don't over complicate.
• Separate inputs, calculations and outputs into distinct areas.
• Use styles and apply standard colours, formats and units.
• Input data once and then link to it many times.
• Control inputs with data validation.
• Use limited range names for clarity.
• Don't use long formulas.
• Keep the flow of information logical – top to bottom, left to right.
• Use one formula per line for consistent blocks of code.
• Make the column headings consistent on all worksheets.
• Use model checks and error traps to ensure that the model is working correctly.
• Explain the application with comments, user instructions, maps and diagrams.
• Produce clear reports and graphics to summarize the output.

Above all, you must adopt standards for spreadsheets, accept that most workbooks have errors and make sure you audit and review them before relying on their output. In this business, you are guilty until proven innocent.