What is the Problem?
Most people think spreadsheets are easy: just enter a formula in a cell and keep adding more formulas until you get a result. However, research shows that about 90% of spreadsheets contain at least one error and that spreadsheet developers are overconfident in their assessment of the correctness of their own work. Errors in spreadsheets have led to many consequences such as financial losses, false declarations, public embarrassment, naming the wrong candidate as winner and extra audit costs.
Those mistakes were made by intelligent people, who just did not have the right training to develop and validate their spreadsheets. Spreadsheet errors are inevitable, like programming errors. While it is impossible to avoid them entirely, you can improve your chances by following the SSMI methodology, which is based on proven principles from Computer Science, Software Engineering and Information Systems. These news articles show that spreadsheets errors can have important monetary and human consequences.
Why Do Errors in Spreadsheets Occur?
Errors occur because developing a spreadsheet model is a highly creative activity that requires concentration. Doing this activity simultaneously with its implementation repeatedly interrupts the creative train of thought with spreadsheet manipulations such as writing formulas or formatting cells. The disruptions of this sort slow down and obstruct the most critical process, which leads to errors. There are two major types of errors: mechanical errors and logical errors. The mechanical errors are related to Excel manipulations, such as typing, pointing or copying a formula. Logical errors are much harder to detect: they are related to the variables and the formulas the developer has created or, worse, forgot to create.
What is the SSMI Solution?
Simply, we need to separate the creative process and the mechanical tasks. This is exactly what the our methodology is about. SSMI is a methodology that can be used by both individuals and organizations to create error-free spreadsheets. We believe that using a sound methodology can reduce the probability of making errors. This methodology also makes it easier to explain to your colleagues, bosses and clients how your spreadsheet works. Moreover, it is also easier to audit and test for errors, faster to modify in the future and easier to manage in an organizational setting.
The Conceptual and Logical Models are the Formula Diagram and the Formula List. The diagram lets you see the big picture at a glance, much better than Excel's Trace Dependants and Trace Precedent arrows tool. The Formula List can be handed to another developer for the implementation.
The Structured Implementation is based on proven concepts from Software Engineering and Computer Science, like 3-tier architecture and modular programming. The implementation follows easy steps that can produce complex, yet easy to understand spreadsheets. They are also very easy to modify and maintain over time.
The SSMI structured implementation makes an extensive use of Names. We use named references to bring close a value that can be defined far away. This makes it much easier to audit and validate the spreadsheet.
With SSMI, there is no limit to the size of your models.