Guidance on best practice for spreadsheet design

This module provides guidance on how to design, test, document, and present your spreadsheets. Ensure other people can utilise, test, and extend the functionality of your spreadsheets. I include some cautionary tales about spreadsheets, such as the following:

"In the report into the 2012 JPMorgan Chase trading loss, a lack of control over spreadsheets used for critical financial functions was cited as a factor in the trading losses of more than six billion dollars which were reported as a result of derivatives trading gone bad.” (“Spreadsheet - Shortcomings”, 2013)

The spreadsheet following is a more professional, elaborate version of the spreadsheet used in Spreadsheet Laboratory Tutorial 1. The basic principal is that it should be EASILY POSSIBLE for another user or spreadsheet “meister” to:

  • Understand what you were attempting to achieve
  • Understand the logic behind your spreadsheet calculations and construction
  • Test, use, audit, correct, or extend the functionality of your spreadsheet.

Note the use of the following sections to fully document your spreadsheet professionally. But first, view the spreadsheet used to illustrate these principles here:

Mellalieu, P. J. (2013). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [Google Sheets Fork]. Retrieved from https://docs.google.com/spreadsheet/ccc?key=0AjwyjX8GotyadDgxa0VxekEwUXV0TUw4TVdfa2UtSGc&usp=sharing

An older Excel version is available here:

Mellalieu, P. J. (2013). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [Excel DropBox]. Retrieved from https://www.dropbox.com/s/h43t3udu8d4y61q/Decision%20Modeling%20FINAL%20-%20PUBLIC.xls

Required sections for a professional spreadsheet

HEADER

  • Descriptive Title
  • Author
  • Date of modification
  • Version

Remember to have a system of “retreating” back to find earlier versions if you make mistakes whilst “enhancing” your spreadsheet. TimeMachine, DropBox, and/or Google Sheets are suitable approaches. Remember to change your version number when you COMMENCE to implement a series of  enhancements.

PURPOSE

A succinct description of what the spreadsheet achieves, including key inputs, and outputs produced.

INPUTS

Keep all input data close to the top of your spreadsheet. Avoid mixing input data throughout the spreadsheet.

Signal the place where the user enters input data by changing the cell border to a colour, such as pale green.

Data Validation and Spreadsheet Protection

Protect the spreadsheet so that ONLY the cells that can receive data input by the user can be modified. See the video below "How to protect cells in an Excel Spreadsheet", (2013).

Include checks on the input data to ensure that the user enters correct, feasible data. eg in Laboratory 1, the Net Returns must be either positive or negative numbers. Spaces or characters are non-valid data. See the video below “Data Validation in Excel”, (2012)

Consider applying more sophisticated checks. For example, note how the IF built-in function checks that the sum of probabilities is exactly equal to 100 per cent: the message “OK” is displayed. If not, a warning message is displayed. (Spreadsheet, Table 1.1)

Consider providing a context-dependant “help” message to inform users about a particular cell when they hover or click over that cell. This feature is enabled in the Data Validation feature mentioned above.

OUTPUTS

Present the flow of your calculations in a natural flow from top to bottom, left to right.

Group related calculations into tables.

Annotate the tables with a brief heading description, as you would in a formal report. (Table 1, 2, 3 About this table etc)

Highlight the most important results using a cell border colour such as salmon.

Provide brief explanatory notes about what calculations and equations are being conducted through the spreadsheet. As required, provided correct citations to formulae used, using author, date, chapter, section, page numbers.Additional explanation could be provided in a NOTES and REFERENCES section later in the spreadsheet.

LIMITATIONS

Detail warnings about the range of problems and data to which the spreadsheet can be applied. Ideally, you should encode tests in your data INPUT section to ensure that out-of-range data is unable to be entered by the user.

Document all assumptions used in the design of the spreadsheet.

QUALITY ASSURANCE

Provide details of how the spreadsheet was tested or audited to verify that:

  • Calculations were true and correct
  • Out-of-range data entry conditions were identified and the user warned correctly
  • Extreme range values perform as expected. eg what happens if you enter 0 or 100 per cent for the Probabilities of the States of the Economy in Table 1.1.

One approach to quality assurance is to:

  • Test your spreadsheet generates the same values as those from textbook examples
  • Compare your spreadsheet with values calculated by manual calculations, and other people’s spreadsheets or calculation systems.

Remember to spell-check your spreadsheet using the Tools/Spelling feature.

Ensure sections, tables, and layout are neatly presented for the user, as you would expect in a professional report.

Ensure data formats in cells are correct:

  • currency symbols ($),
  • percentages (%),
  • correct precision for decimals,
  • negative numbers shown in red (Use ‘Conditional Formatting’)
  • clear table headings for rows and columns.

MODIFICATION HISTORY

Notes about the modifications made according to version number.

Notes for future reference about enhancements that could be implemented.

NOTES

As necessary, provide supplementary notes about correct use and background details.

REFERENCES

Provide bibliographic references and URL links to any material referred to in the spreadsheet. In particular, provides references to theory and formulae used in the spreadsheet. Provide references to standard test data and results used to conduct Quality Assurance.

Cautionary tales about the use and abuse of spreadsheets

"Spreadsheets have significant reliability problems. Research studies estimate that roughly 94% of spreadsheets deployed in the field contain errors, and 5.2% of cells in unaudited spreadsheets contain errors." (“Spreadsheet - Shortcomings,” 2013)

"One study in early 2011 of nearly 1,500 people in the UK found that 57% of spreadsheet users have never received formal training on the spreadsheet package they use. 72% said that no internal department checks their spreadsheets for accuracy. Only 13% said that Internal Audit reviews their spreadsheets, while a mere 1% receive checks from their risk department." (“Spreadsheet - Shortcomings”, 2013)

"In 2013 Thomas Herndon, a third year student at the University of Massachusetts at Amherst found major coding flaws in the spreadsheet used by the economists Carmen Reinhart and Kenneth Rogoff in a very influential 2010 journal article. The Reinhart and Rogoff article was widely used as justification to drive 2010 to 2013 European austerity programs." (“Spreadsheet Risk”, 2013)

Bibliography

Barlow, J. (2006). Excel Models for Business and Operations Management [online e-book]. John Wiley & Sons. Retrieved from http://unitec.eblib.com.au/patron/FullRecord.aspx?p=241134

Data Validation in Excel. (2012). Retrieved from
http://www.youtube.com/watch?v=uQm_CxwcOHw&feature=youtube_gdata_player

French, T. (n.d.-a). Formulas and Functions. About.com Spreadsheets. Retrieved August 1, 2013, from http://spreadsheets.about.com/od/excelformulas/u/formulas_functions_user_path.htm

French, T. (n.d.-b). Prevent Invalid Data Entry in Excel. About.com Spreadsheets. Retrieved August 1, 2013, from http://spreadsheets.about.com/od/datamanagementinexcel/ss/2011-11-11-excel-2010-prevent-invalid-data-entry-quick.htm

Google spreadsheets function list. (n.d.). Retrieved August 1, 2013, from https://support.google.com/drive/table/25273?page=table.cs&rd=1

How to protect cells in an Excel Spreadsheet. (2013). Retrieved from
http://www.youtube.com/watch?v=4f1j0oCGeRk&feature=youtube_gdata_player

Mellalieu, P. J. (2013). A Decision Support System for Decision Modeling under Conditions of Uncertainty and Risk [Google Sheets Fork]. Retrieved from https://docs.google.com/spreadsheet/ccc?key=0AjwyjX8GotyadDgxa0VxekEwUXV0TUw4TVdfa2UtSGc&usp=sharing

Spreadsheet. (2013, July 29). In Wikipedia, the free encyclopedia. Retrieved from https://en.wikipedia.org/w/index.php?title=Spreadsheet&oldid=566297326

Spreadsheet - Shortcomings. (2013, July 29). In Wikipedia, the free encyclopedia. Retrieved from https://en.wikipedia.org/wiki/Spreadsheet#Shortcomings

Spreadsheet Risk. (2013, July 29). In Wikipedia, the free encyclopedia. Retrieved from https://en.wikipedia.org/wiki/Spreadsheet#Spreadsheet_risk

AUTHOR

Peter Mellalieu
Created 2013-08-01
Version 1.6 Modified 2013-08-07

Blog comments powered by Disqus