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.
Horror Stories
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.
Most common spreadsheet development cycle: your creative work is constantly interrupted by keyboard manipulations.
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 SSMI development cycle: your creative process (Formula Diagram and List) is not interrupted by keyboard manipulations,
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.
Example of a Formula DIagram
Example of the corresponding Formula List
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.
Simple definition blocks make it easy to validate a spreadsheet, even when you only have a printed version.
When you look at the Formula View, you see that the definition formulas only use the cells just above them: no more hunting around to find the meaning of a far reference, like G$36.
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.
This is a 110-variable model. You can print it full-size on multiple pages and cut and tape the pages so you can work on it with clients or colleagues.