i-nth - iⁿ spreadsheet methodology

Our iⁿ spreadsheet methodology defines questions necessary for stakeholders to have confidence in their spreadsheets. The methodology specifies criteria for designing, building, and validating spreadsheets to ensure that they work correctly.

The iⁿ spreadsheet methodology consists of five parts:

Intent.
Instructions.
Instruments.
Implementation.
Immunity.

Each part consists of five questions designed to help you make better spreadsheets.

Overview of the methodology

Our iⁿ spreadsheet methodology is a framework for ensuring that spreadsheets are easy to use, work correctly, and are efficient. It can be used when designing, revising, or testing a spreadsheet.

The methodology is distilled from three primary sources:

Our experience in building, working with, and testing spreadsheets.
Observations of people struggling with their spreadsheets.
Review of the academic and practitioner literature about spreadsheet errors and good practices.

In developing the methodology, we've examined the problems that people often have with their spreadsheets. Those problems include fragile calculations, difficulty making changes, poorly documented processes, inefficient procedures, and incorrect results. The solutions to many of these problems are commonsense. However, it is remarkable how often they are not applied in practice.

The consequences of poorly designed and implemented spreadsheets include annoyance, frustration, and productivity losses. But spreadsheet risk can also have major, disastrous consequences, including loss of reputation, financial loss, and even the failure of an entire organisation. Examples of spreadsheet error consequences are described in the long list of EuSpRIG horror stories.

A central theme underpinning the methodology is that we are seeking to give a spreadsheet's stakeholders confidence that it does what it should – starting with having the right intent. It must also have a good design, be implemented correctly, be used correctly, and produce correct results. Every aspect of the methodology is a necessary condition for stakeholders to have confidence in their spreadsheets.

Our iⁿ spreadsheet methodology consists of five parts, each of which asks five questions.

Intent:

Is the spreadsheet fit-for-purpose?
Is the spreadsheet solving the right problem?
Do stakeholders understand the intent of the spreadsheet?
Does the spreadsheet answer all the necessary questions?
Is the scope limited to only the required tasks?

Instructions:

Are there clear instructions about how to use the spreadsheet?
Does the spreadsheet include instructions for developers?
Are all the instructions up-to-date?
Are all data sources defined?
Does all data have units specified?

Instruments:

Are the instruments (techniques, tools, and algorithms) appropriate?
Would other instruments be better?
Do stakeholders understand the spreadsheet's instruments?
Do the developers understand the instruments?
Are limitations and caveats clearly stated?

Implementation:

Are the instruments implemented correctly?
Is the design intuitive, easy to use, and robust?
Is there clear separation of data and calculations into modules?
Does the spreadsheet have access and version control?
Is recalculation time an issue?

Immunity:

Is the spreadsheet sufficiently immune to errors?
How well does the spreadsheet handle invalid data?
Is the spreadsheet easy to modify without creating errors?
Are the error messages adequate?
Does the spreadsheet fail gracefully when necessary?

Each part of the methodology is detailed in the following sections.

Intent

The keystone of every good spreadsheet is a clear intent. If the intent of the spreadsheet is muddled, or has drifted over time, then it is likely that the spreadsheet's implementation is confusing. There is also a high risk that the results are wrong.

Specific questions to consider when evaluating a spreadsheet's intent:

Is the spreadsheet fit-for-purpose? Fit-for-purpose means that the spreadsheet fulfils its intended purpose. It must also be designed in a way that is appropriate for the stakeholders.
Is the spreadsheet solving the right problem? The intent of the spreadsheet needs to be clearly articulated to ensure that it is solving the right problem. There is no point in finding the correct solution to the wrong problem. It is common for requirements to change over time – the intent may need to be updated to remain focussed.
Do stakeholders understand the intent of the spreadsheet? The users and other stakeholders need to understand the intent of the spreadsheet so that they can use it appropriately, including correctly interpreting the results. This also means understanding what the spreadsheet isn't intended to do.
Does the spreadsheet answer all the necessary questions? The spreadsheet needs to be sufficiently general that it represents all the essential aspects of the problem that it is solving. This will then enable the user to answer all the questions that the spreadsheet is intended to address.
Is the scope limited to only the required tasks? The scope of a spreadsheet needs to be limited to only those tasks that are required to fulfil its intent. A spreadsheet's scope should be as broad as it needs to be, but no more so. Over time, spreadsheets tend to accumulate peripheral data and calculations that may once have been relevant but aren’t any more – leading to a loss of focus and increased risk of error.

Instructions

Spreadsheets require clear instructions to ensure that they are used correctly. This includes instructions for the spreadsheet's users and developers.

Specific questions to consider when evaluating a spreadsheet's instructions:

Are there clear instructions about how to use the spreadsheet? Clear instructions ensure that the user understands what the spreadsheet is for, how to use it, and how to interpret the results. Without clear instructions, the users may make incorrect assumptions and hence make incorrect decisions. The user instructions may be included within the spreadsheet and/or in separate documentation.
Does the spreadsheet include instructions for developers? Instructions for developers must describe complex and unusual features, including special cases and potential traps for the unwary. Cell comments and VBA code comments should be used liberally. The developer instructions may be included within the spreadsheet and/or in separate documentation, though should be distinct from the user instructions.
Are all the instructions up-to-date? As a spreadsheet evolves, it is easy for the instructions to become obsolete. However, it is important that the instructions are updated as spreadsheet changes are made, to ensure that the instructions are still correct. Change control or version management of the documentation may be applicable in some cases.
Are all data sources defined? When updating a spreadsheet, the user needs to know where to get the data. Both manual and automated data sources need to be fully specified.
Does all data have units specified? For example, a cell may be called "Cost", with a value of "10" – but is that dollars per month, thousands of dollars per annum, a percentage, or what exactly? A related issue can occur when the basis of a piece of data is inadequately defined. For example, when a spreadsheet uses a time-value-of-money discount rate without saying whether the rate is before or after tax, or whether it is in real or nominal terms. In all cases the units should be defined close to the data input.

Instruments

The choice of techniques, tools, and algorithms – collectively known as instruments – is crucial 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 evaluating 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, as it allows developers a great deal of latitude in choosing how to solve problems. The issue is that developers don't always choose the best tools, techniques, and algorithms for the task at hand.
Would other instruments be better? A common issue is that developers tend to choose instruments they're familiar with, rather than choosing the best instruments for the current task. An alternative approach may lead to better 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. 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 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 for them to have confidence in the spreadsheet.

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 best for the task at hand.

Specific questions to consider when evaluating 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. Being 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? Use a modular structure. 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 highlight data in contrast to formulae. 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. But note that spreadsheets are not a secure environment. Similarly, a spreadsheet that anyone can change is likely to soon become unmanageable. At the very least, a spreadsheet should have a log of changes made to it, so that versions can be controlled.
Is recalculation time an issue? There are often many 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.

Immunity

No spreadsheet can be guaranteed to be 100% free of errors under all circumstances. But the way a spreadsheet handles errors has a major impact on its usability. In addition, error handling has a major impact on user confidence.

Specific questions to consider when evaluating a spreadsheet's immunity:

To what extent is the spreadsheet immune to errors? This question recognises that it is unrealistic to expect a spreadsheet to be completely immune to all potential sources of error. Nonetheless, it is important for a spreadsheet to deal with the most common types of user and data errors. Instead of failing, the spreadsheet should recognise when errors have occurred and respond appropriately.
How well does the spreadsheet handle invalid data? The user needs to get helpful and constructive feedback when they enter invalid data. Having the spreadsheet filled with cell values such as #DIV/0!, #VALUE!, or #N/A is not an elegant or helpful way to handle invalid data.
Is the spreadsheet easy to modify without creating errors? The spreadsheet should be constructed in a way that is easy to change while minimising the potential for creating errors. For example, a Table structure automatically adjusts when new rows are added. Similarly, using named ranges rather than cell addresses in VBA avoids the errors created when a row or column is inserted.
Are the error messages adequate? When an error occurs, the spreadsheet should show meaningful information that enables the user to recognise that an error has occurred and provides guidance about how to correct the error.
Does the spreadsheet fail gracefully when necessary? For situations not directly handled by the spreadsheet, it should still fail in a way that causes minimal problems. Having a spreadsheet report an incorrect result, but giving no indication that something went wrong, can be disastrous.

Conclusion

The iⁿ spreadsheet methodology specifies criteria for designing, building, and validating spreadsheets to ensure that they work correctly. It consists of five parts: Intent, Instructions, Instruments, Implementation, and Immunity – each of which asks five key questions. The methodology is designed to ensure that a spreadsheet does what it is supposed to do, while being easy to use and producing reliable results.

By ensuring that all the methodology's questions are answered satisfactorily, spreadsheet users and other stakeholders can have confidence in the spreadsheet. An inadequate answer to any of the questions suggests that decisions based on the spreadsheet may be wrong, and hence the spreadsheet poses a risk to your organisation.

To find out more about how the iⁿ spreadsheet methodology can be applied to your spreadsheets, please contact us.

iⁿ spreadsheet methodology