To error or not: consequences of spreadsheet errors and how to avoid them.

• Author: Moreno, Frances

Most financial professionals understand the potential consequences of spreadsheet errors, but the horror stories continue to pile up. For example: One company (source: European Spreadsheet Risk Interest Group: www.eusprig.org/horrorstories.htm) saw its stock fall by 15 percent when, following its report of a 3 percent rise in revenue, it turned out that revenue fell 5 percent. The error occurred when data was transferred from an accounting system to a spreadsheet used to produce trading statements. Another corporation had to halt trading on its stock alter details of its almost S3 billion profit were embedded in a template of the previous year's results and became widely accessible. It turned out that someone thought a black cell background fill would hide the text. It did not.

A major corporation almost gave an employee an $11 million severance package due to a faulty spreadsheet with too many zeros.

And another company lost. 25 percent of its value after inaccurately reporting its fourth quarter earnings due to a misrecorded number in one cell of a spreadsheet.

Fortunately there are methods that CPAs can use to avoid these types of problems that lead to financial loss and embarrassment--and avoid the nightmares. These methods require only incorporating some practical tips for building a better spreadsheet model. The following tips apply to Excel 2003 and 2007. Note to Excel 2010 users: menus are nearly identical to Excel 2007.

The 80 Percent Planning/20 Percent Design Rule

Spreadsheet problems arise when Excel users create or recycle massive workbooks without thoughtful planning. Experts note that a good spreadsheet typically follows an 80 percent planning/20 percent design rule. Considerations involved in the planning process include determining the type of spreadsheet to use. the intended audience, the purpose and the stakes involved in each spreadsheet. This is particularly important for high-risk spreadsheets or those determined to have "higher slakes." Companies can significantly reduce risks by introducing best practices for designing. auditing and sharing Excel spreadsheets.

Designing: Separate Inputs from Outputs Using Your Own Conventions

Every spreadsheet model has inputs and outputs. Inputs are the variables that can change from time to rime, such as interest rate or principal payment amount on a loan. Outputs are the formulas/calculations whose results depend on the inputs, like the resulting monthly payment in the 12-month...