Guidelines:
- Manage the spreadsheet development life cycle
Beware that spreadsheets go through the life cycle whether or not the phases are explicit, so be deliberate about managing the life cycle of your spreadsheets.
Choose the degree of formality to apply to each spreadsheet, focussing most attention on the critical spreadsheets.
Informal vs formal management of the SDLC
For most spreadsheets, an informal SDLC management process is adequate. This means applying the life cycle with light management oversight and control. Managers just need to ensure that the people working with spreadsheets are aware of the SDLC and that they follow each of the phases in a deliberate manner.
For critical spreadsheets, formal management oversight and control of the SDLC is necessary – just like it is for other critical software. However, only a small percent of organizations implement and enforce formal control over their spreadsheets. Formal management includes ensuring that procedures exist, and are followed, to manage who accesses the spreadsheet, how the spreadsheet is used, how data is updated, maintenance of documentation, training of users, extensive testing of the spreadsheet, and formal version control.
In some cases, there may be regulatory requirements for the management of spreadsheets that organizations are required to adhere to.
Planning, Analysis, and Design phases
The Planning, Analysis, and Design phases are collectively just as important as the Construction phase. These phases set up a foundation upon which a robust and reliable spreadsheet can be constructed. The goal of these phases is to ensure that the spreadsheet is fit-for-purpose.
The first part of the Planning phase is researching and identifying what problem the spreadsheet is intended to solve. Often a spreadsheet will be built without clearly defining its purpose – a situation like finding the optimal solution to the wrong problem. Having identified the problem to be solved, we can consider potential solutions. Only then can we consider what resources may be needed to deliver those solutions.
A key purpose of the Analysis phase is to decide the best solution method for the problem. This involves analysing the specific needs and requirements of the situation. We should also consider whether a spreadsheet is the right tool to solve the problem. Having chosen a method, we can specify the detailed solution requirements and assign resources.
In the Design phase, we expand the requirements to create a detailed specification. For important spreadsheets, the specification should be a formal, written document. An essential part of this phase is incorporating stakeholder feedback about the design. Stakeholders should not be surprised by the spreadsheet that you deliver.
If the Planning, Analysis, and Design phases are inadequate, then the result is likely to be a lot of time and resource being wasted on reworking the spreadsheet to make it fit-for-purpose.
Often a spreadsheet is inherited from an earlier developer, having grown organically without any planning or analysis. The result is a spreadsheet that gives the appearance – and usually the reality – of never having been consciously designed. But even inherited spreadsheets can be improved via an iteration or two of the Planning, Analysis, and Design phases of the SDLC.
Construction phase
Spreadsheet developers typically focus almost solely on the Construction phase. The usual approach is to jump straight from problem identification to constructing the spreadsheet. That poor approach often does not properly define the problem, may use an inadequate method, and will generally result in a haphazard design.
But with the sound foundation created during the Planning, Analysis, and Design phases, construction of a spreadsheet is much more likely to be efficient and produce a robust and reliable spreadsheet.
Working from the detailed specification defined in the Design phase, the Construction phase is likely to be easier. Applying the good practices described in these guidelines will help make the Construction phase easier and will reduce later problems in the Testing and Implementation phases of the SDLC.
Testing phase
Software has bugs. Spreadsheets are software. Therefore, spreadsheets have bugs.
Despite the overwhelming evidence that almost all spreadsheets contain errors – a fact that has been known for decades – the Testing phase of the SDLC is often neglected. Failure to properly test a spreadsheet risks making decisions using incorrect analysis. Proper testing of a spreadsheet, for which there exist formal procedures, typically requires as much time and effort as the Construction phase.
Implementation phase
Once testing is complete, a spreadsheet is ready to be released to the users.
Immediately prior to the spreadsheet being put into operational use, a read-only backup copy must be taken and kept in a secure location. There is always a risk that users will break an operational spreadsheet, so you need to be able to restore the spreadsheet from a backup copy. If data is added to the spreadsheet over time, then additional backups may be necessary.
The Implementation phase is usually simple: the completed spreadsheet is made available to its users. The users may need training to use the spreadsheet correctly.
Observe how people use the spreadsheet, to ensure that it works as intended. At the very least, the users should be encouraged to report any issues that they find while using the spreadsheet. Contact details should be on the About worksheet. Feedback from the users supplies input to further iterations of the Spreadsheet Development Life Cycle.
Archive phase
When a new spreadsheet version is ready for Implementation, the earlier version must be withdrawn from use and archived to ensure that only the latest version is in use (a process called "version control"). Failure to properly control versions can lead to confusion and decisions being made using an out-of-date spreadsheet.
When a spreadsheet is no longer needed, it should be archived. It may be necessary to keep multiple archived versions of a spreadsheet, to create an audit trail.
