Authors
John S. Edwards, Paul N. Finlay, & John M. Wilson
Abstract
The implementation of 'do it yourself' spreadsheets (DIY-SS) has become widespread in recent years. Although the interest of the non-specialist in creating SS is to be welcomed, this has led to concerns that many of these systems may not be 'fit-for-purpose'.
The principal reasons for this are the inappropriate scope and focus of the SS and the lack of detailed verification that is carried out. With an appropriate framework to guide them, the OR professional therefore has an important new role to play in supporting DIY-SS by guiding the DIY developer.
A set of 20 guidelines is presented to help the OR specialist guide the DIYer to 'scope' any proposed SS. Then a set of 21 'points of best practice' is presented to support verification as the resultant SS is developed. The paper concludes by briefly reflecting on the new role for the OR specialist.
Sample
The 21 points of best practice are:
- Keep all the major components of each area of organisational responsibility together and make each component a different module in the spreadsheet.
- Within each module segregate (the values in) the data model from (the formulae of) the logic model.
- Incorporate information about the major assumptions and modelling conventions underpinning the whole of the spreadsheet explicitly in the spreadsheet.
- Specify the range of application of the spreadsheet - either of the whole spreadsheet or of parts of it.
- Put the name of the file that holds the spreadsheet permanently into the summary model section of the spreadsheet.
- Arrange for the time and date when the results were produced to be automatically printed with the results, linked to the current date of the underlying model.
- Write an information flow diagram to support the spreadsheet development.
- Subdivide the logic model modules into logically integrated sub-units.
- For each relationship, check its 'shape', its dimensions, the units used, and its range of application.
- Use factor names rather than cell references.
- Assign titles to the top left hand corner of each of the spreadsheet modules.
- Protect or lock some of the entries in a spreadsheet.
- Never include the values of any coefficients and parameters within formulae: make these factors part of the data model.
- Never input the same piece of data more than once.
- Add notes to data values to indicate their source and any associated confidence level.
- Choose cell widths and appropriate formats to give visual clues as to anomalies in the data.
- Use limit checking formulae to ensure that the data values are sensible.
- Link graphs to the data to bring attention to less obvious errors.
- Put in reminders that you need to consider (or must change) particular values.
- In organisational databases understand just what the data values refer to so as to avoid 'translation' errors.
- Use macros to help navigate around the system and ensure things are done as intended.
Publication
2000, European Journal of Operational Research, Volume 127, Issue 1, pages 14-27
Full article
The role of OR specialists in 'do it yourself' spreadsheet development
