iⁿ spreadsheet testing methodology — Implementation

The spreadsheet's implementation is where theory meets application. Unfortunately, spreadsheet developers usually start with implementation, skipping essential steps like defining the spreadsheet's intent and considering which instruments would be appropriate for the task at hand.

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

• Are the instruments implemented correctly? There are two main types of implementation errors. The first, and by far the most common, involves the developer simply making a mistake — for example, not including all required cells in a SUM formula. The second type arises from a misunderstanding of the instruments — for example, not knowing that the IRR function can produce multiple solutions.
• Is the design intuitive, easy to use, and robust? Intuitive designs make instructions almost (but not quite) unnecessary. Easy to use means that the user is guided through using the spreadsheet in an obvious and logical manner. A robust spreadsheet is tolerant of user error, has a flexible design that adapts to change, and it is difficult to break. Ideally the spreadsheet should follow design standards that are applied throughout the organisation.
• Is there separation between data and calculations? All data, including assumptions, switches, and controls should be separated from the calculations and clearly identified as being something the user can change. For example, a spreadsheet may use different coloured text to indicate data in contrast to formulae. In particular, data must not be embedded within formulae — a common, but high risk, practice.

• Does the spreadsheet have access and version control? Spreadsheets that contain sensitive data must have access limited to only the people who have the appropriate authority to access that data. Similarly, a spreadsheet that anyone can change is likely to soon become unmanageable. At the very least, a spreadsheet should contain a log of changes made to it, so that versions can be controlled.
• Is recalculation time an issue? There are often many different ways of achieving the same result in a spreadsheet, though some ways are more efficient than others. Implementing a more efficient approach can substantially improve run time performance, making the spreadsheet easier to use and improving the timeliness of results.

Next we discuss the spreadsheet's immunity to errors.

Previous: Instruments

Next: Immunity

Return to: Overview