i spreadsheet testing methodology — Instruments

The choice of appropriate techniques, tools, and algorithms — collectively known as instruments — is critical to the success and accuracy of every spreadsheet. The instruments must be well understood, including the limitations and caveats of the instruments when applied to the spreadsheet's intent.

Specific questions to consider when testing a spreadsheet's instruments:

  • Are the instruments (techniques, tools, and algorithms) appropriate? A key strength of spreadsheets is that they are incredibly versatile. However, that versatility can also be a weakness, in that it allows developers a great deal of latitude in choosing how to solve problems. The issue is that developers don't always choose appropriate tools, techniques, and algorithms for the task at hand.
  • Would other instruments be better? A common issue is that developers tend to choose instruments with which they are familiar, rather than choosing the best instruments for the current task. An alternative approach may lead to more accurate solutions, a more robust spreadsheet, or a spreadsheet that is easier to use and understand.
  • Do stakeholders understand the spreadsheet's instruments? A spreadsheet that is a "black box" to its users and other stakeholders invites trouble, because they do not know how it works, what its limitations may be, and may not be able to identify when it fails to produce correct results.
  • Do the developers understand the instruments? Subtle errors can arise from incorrect application of the techniques, tools, and algorithms used in a spreadsheet.
    It is critical that stakeholders understand the instruments used in the spreadsheet.
    A common example is the incorrect timing of cashflows — Excel's financial functions make specific assumptions about timing that may not match the (usually implicit) assumptions of the spreadsheet's builder.
  • Are limitations and caveats clearly stated? It is important to understand when a spreadsheet's results are valid, but perhaps even more important to understand when they are not valid. Every instrument has limitations, either in terms of problems that it cannot solve, or in circumstances in which its results are inaccurate or even simply wrong. The limitations of the chosen instruments need to be understood by the developers, the users, and other stakeholders in order for them to have confidence in the spreadsheet.

Next we discuss the spreadsheet's implementation.

Previous: Instructions

Next: Implementation

Return to: Overview