Spreadsheet based modelling plays a very important role in the majority of businesses with the availability of Microsoft Office products and the relative ease of use of applications such as Excel, meaning that anybody can create a ‘model’ from the most simple single page example to the extremely complex utilising elements of automation provided by Visual Basic for Applications (VBA).
We have found that the use of spreadsheets is most prolific in area’s such as accounts preparation and reporting, pricing decisions, investment decisions, budgeting and forecasting and whilst these models can provide business leaders and decision makers with flexible tools in order to make informed decisions, in-house built models can suffer from a lack of quality, robustness and reliability as well as non-existent version control, all of which could have a severe impact on the quality of decision making. In fact, a report published in 2014 by the Institute of Chartered Accountants in England and Wales (ICAEW) on good spreadsheet practice determined that approximately 90% of spreadsheets contain errors. Some material errors have been well publicised alongside the embarrassment and in some cases, the financial losses that have arisen as a result.
Many spreadsheets simply evolve over time without well-structured design or integrity checks and in most cases are poorly documented. Making a minor change can often take a long time or have unexpected consequences resulting in errors from incorrect calculations or input assumptions.
The 2014 ICAEW report identified 20 key principles of good practice related to spreadsheets, their creation and their ongoing usage. VFD adopts these principles or, if providing an advisory function encourages all of our clients to do so to help reduce risk in spreadsheet models. The ICAEW principles in summary;
The Spreadsheet’s Business Environment
- Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly: If you have spreadsheets that play a key or critical role in your organisation, ensure that they are developed and tested, managed and monitored to an appropriate level. Spreadsheets that form part of an organisation’s key business processes will need to be managed differently from ad hoc spreadsheets for short-term use by an individual.
- Adopt a standard for your organisation and stick to it: This might be one that is developed in-house, or adopted from outside and shared with other organisations. A common standard within an organisation facilitates collaboration, aids understanding and saves development time. The standard should include, among other things, consistent conventions on use of cell formatting. For example, you could use Cell Styles to easily apply an organisational standard formatting scheme to your workbook.
- Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence: For anyone designing, developing or maintaining (as distinct from just using) a spreadsheet, this will include: awareness of the range of functions available, clear understanding of such basic concepts as relative and absolute cell references, and an appreciation of the importance of carefully checking the results of functions.
- Work collaboratively, share ownership, peer review: The extent of collaboration and review needed will depend on the size and complexity of your organisation and of each project.
Designing and Building your Spreadsheet
- Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job: Spreadsheets are not the answer to every problem. A lot of time can be wasted, and errors caused, by using a spreadsheet when some other application would be more appropriate. Very often the more appropriate tool might be a word processor (if it’s a table containing text), a database (if large quantities of similar data items need processing) or an existing software package (to undertake well-established processes, such as bookkeeping, for which specialist packages are readily available). Even if a spreadsheet is still the most appropriate tool, it’s worth looking for existing templates before starting a new one from scratch.
- Identify your audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this: If the only audience envisaged is yourself, you could perhaps justify less explanation and help. However, good documentation is helpful if you come back to a spreadsheet sometime after you’ve originally created it. Additionally, many spreadsheets often turn out to have a much wider audience than originally intended. Ensure that adequate instructions, validation and help are included to promote ease of use and avoid input errors. Even if parts of a spreadsheet are ‘locked’, keep calculations visible.
- Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet: This should give such basic information as author, purpose, version number, and description of general approach. Also include explanations of colour codes and other formatting conventions, any sources of input data (with, where appropriate, hyperlinks to the original data), and any macros and what they do. The more complex the workbook, or the more it needs to be shared, the greater the requirement for good documentation. Conversely, a simple spreadsheet to be used only by the person who designs it might be less rigorously documented.
- Design for longevity: Design spreadsheets to adapt to any reasonably foreseeable future changes in values (tax rates, etc) or volume (e.g. items in a data set) of data used in calculations. However, the need for adaptability should be balanced against following the Agile principle of ‘The simplest thing that could possibly work’. For example, construct formulas so that inserting new values – either by inserting new rows or by adding to the bottom of the existing data – will not require any amendments to the formulas. This could be done with functions that use ranges including blank space for new data, or by using Excel’s Table feature.
- Focus on the required outputs: Work backwards: be clear about the purpose of the spreadsheet, what outputs achieve that purpose and therefore what inputs and logic are required to derive the outputs.
- Separate and clearly identify inputs, workings and outputs: A properly structured spreadsheet will be easier to understand and to maintain. If pivot tables are used, it may be possible to relax this principle, but clarity remains crucial. Design to ensure that any input should be entered only once.
- Be consistent in structure: Use the same columns for the same things in each workbook, especially when working with time series. A consistent convention within a workbook reduces the risk of error where one sheet refers to another. For example, a common convention is that time flows horizontally from left to right (and a specific column is always ‘Year 1’) and calculations flow vertically from top to bottom. Such a structure will help to avoid circular references.
- Be consistent in the use of formulae: On any worksheet use the smallest practicable number of different formulae. Where it is necessary to use different formulae, ensure that groups of cells using the different formulae are clearly separated. One common approach used to minimise the number of different formulas in use is to make use of cell anchoring, using $ signs to indicate parts of references that should not change upon a copy and paste. This allows a block of formulas to be built from a single function and both reduces the chance of error and also makes review more efficient.
- Keep formulae as short and simple as practicable: Shorter formulae are easier to build (and therefore less likely to contain errors) and easier to understand and to review. Stage a calculation through multiple cells rather than build a long, complex formula.
- Never embed in a formula anything that might change or need to be changed: Instead, put such values into separate cells and reference them. This ensures that values enter the spreadsheet only once, and if change is needed would happen in just one place. It also allows for all formulae cells to be locked without denying access to input values.
- Perform a calculation once and then refer back to that calculation: Do not calculate the same value in multiple places (except perhaps for cross checking purposes). This reduces risk of error, and is more efficient, since fewer calculations are being performed.
- Avoid using advanced features where simpler features could achieve the same result: In particular, avoid using programming code unless necessary – in which case ensure that it is clearly documented within the code itself, as well as in a documentation worksheet. Similarly, avoid circular references, and control and document any exceptions. Do not change the software’s key default settings (for example, do not turn off automatic recalculation) unless essential, in which case include a prominent message to warn users.
Spreadsheet Risks and Controls
- Have a system of backup and version control, which should be applied consistently within an organisation: The appropriate levels of backup and version control will depend on the organisation and the nature of the work, but there should always be, at the very least, a reliable means of preserving, identifying and restoring earlier versions of a workbook.
- Rigorously test the workbook: The level of testing required will depend on the size, complexity and criticality of the workbook, with riskier workbooks needing a greater degree of independent testing. Self-review is limited in how many errors can be identified. To truly improve the chances of catching mistakes in a spreadsheet, a system of peer review is recommended. Testing could include adjusting inputs to see if the change in outputs matches expectation, testing extreme values to see how the spreadsheet operates, and double-checking the results of key calculations.
- Build in checks, controls and alerts from the outset and during the course of spreadsheet design: These checks might include, for example, tests to ensure that a balance sheet balances, assets do not depreciate below zero, and so on. One approach would be to build in a set of audit tests to check validity and use flags to signal compliance or non-compliance. Use a master flag to summarise all the individual flags and place it prominently (on the output sheet, or even throughout the workbook e.g. on sheet headers) so that users are bound to see it.
- Protect parts of the workbook that are not supposed to be changed by users: The level of protection will vary according to the nature of the spreadsheet and the kind of use/users it will have. It might include locking whole worksheets, all cells containing formulae, or everything except designated input cells.
VFD models for your business can help take the worry away from spreadsheet creation allowing your team to concentrate on their day jobs or if you simply require an expert review of key models in use within your business and a professional opinion to give you the assurance you require to validate your decision making we would be a happy to provide the support you need.
