This month, we look at the bigger picture: what constitutes a best practice spreadsheet model. By Liam Bastick, associate director with SumProduct.
Query
You have previously discussed the merits of using styles (see January 2009 Insight), but can you give me any tips for model construction in general?
Advice
Spreadsheeting is often seen as a core skill for accountants - many are reasonably conversant with Excel. However, many would be modellers frequently forget that the important end users of a spreadsheet model are not necessarily sophisticated Excel users and often only see the final output on a printed page - for example, as an appendix to a Word document.
With this in mind, it’s easy to understand why there have been many high profile examples of material spreadsheet errors. Well-structured models won’t eradicate mistakes, but they should reduce both the number and the magnitude of these errors.
Modellers should strive to build best practice models. A good model has four key attributes:
- transparency
- consistency
- flexibility
- robustness
Transparency
Many modellers also often forget that decision makers base their choices on printed material. Models must be clear, concise and fit for purpose.
Most Excel users are familiar with keeping inputs / assumptions, calculations and outputs separate. This concept can be extended. It can make sense to keep different areas of a model separate – for example, revenue assumptions on a different worksheet from cost(s) of goods sold assumptions, and capital expenditure assumptions on a third sheet, and so on.
This makes it easier to re use worksheets and ringfence data. Keeping base case data away from sensitivity data is also important, as many modelling mistakes are made by users changing the wrong, yet similar, inputs. Aside from trying to keep formulae as simple as possible, it makes sense to consider the logical flow of a model at the outset too. Indeed, including a simple flowchart within an Excel workbook can be invaluable: as the saying goes, a picture is worth a thousand words, and can actually help to plan the structure and order of the spreadsheet build.
Example of a basic flowchart
Similarly, a table of contents constructed with hyperlinks helps users and developers alike navigate through larger Excel models:
Sample table of contents using hyperlinks
Consistency
Models constructed consistently are easier to understand as users become familiar with their purpose and content. This in turn gives users more comfort about model integrity and makes it easier to add/remove categories, numbers of periods, scenarios, etc.
Consistent formatting and use of styles cannot be over emphasised. Humans take in much information on a non verbal basis. Consider the following ‘Print’ dialog box:
Typical Excel ‘print’ dialog box
While the above dialog box appears quite flexible, we know the only things we are able to change are the objects in white (for example, I know I cannot print out a list from the above dialog box since the selection has been greyed out).
I use the same style in my worksheets by having a grey background and white in the relevant cells. For example:
Typical assumptions worksheet
Other key elements of a workbook should be consistent. These include:
- formulae should be copied uniformly across ranges, to make it easy to add/remove periods or categories as necessary
- sheet titles and hyperlinks should be consistently positioned to aid navigation and provide details about the content and purpose of the particular worksheet
- for forecast spreadsheets incorporating dates, the dates should be consistently positioned (for example, first period should always be in one particular column), the number of periods should be consistent where possible and the periodicity should be uniform (the model should endeavour to show all sheets monthly or quarterly, and so on). If periodicities must change, they should be in clearly delineated sections of the model. This should reduce referencing errors, increase model integrity and enhance workbook structure.
Flexibility
One benefit of modelling in a spreadsheet package such as Excel is the ability to change various assumptions and see how these adjustments affect various outputs.
When building a model, the user should consider what inputs should be variable and how they should be able to vary. This may force the model builder to consider how assumptions should be entered.
The most common method of data entry in practice is simply typing data into worksheet cells, but this may allow a model’s inputs to vary outside of scoped parameters.
For example, if I have a cell seeking Volumes, without using data validation I could enter ‘3’, ‘-22.8’ or ‘dog’ in that cell. Negative volumes are nonsensical and being able to enter text may cause formula errors throughout the model. The user may wish to consider other methods of entry including using drop down boxes, option buttons, check boxes and so on. These are all relatively simple to construct in Excel using the Developer tab in Excel 2007 or the Forms toolbar in earlier versions.
The aim is to have a model provide sufficient flexibility without going overboard.
Robustness
Models should be materially free from error, mathematically accurate and readily auditable. Key output sheets should ensure that error messages such as #DIV/0!, #VALUE!, #REF! cannot occur (ideally, these error messages should not occur anywhere).
When building, it is often worth keeping in mind hidden assumptions in formula. For example, a simple gross margin calculation may calculate profit divided by sales. However, if sales are non existent or missing, this calculation would give #DIV/0!
The user has two options:
- use an IF statement to check that sales are not zero (proactive test); or
- construct an error check to flag if sales are zero (reactive test, not recommended in this instance).
However, checks are useful in many situations, and essentially each will fit into one of three categories:
1. error checks – the model contains flawed logic or prima facie errors. For example, the balance sheet does not balance, cash in cashflow statement does not reconcile with the balance sheet, or the model contains #DIV/0! error
2. sensitivity checks – the model’s outputs are being derived from inputs that are not deemed to be part of the base case. This can prevent erroneous decisions being made
3. alert checks – everything else! This flags points of interest to users and / or issues that may need to be reviewed: for example, revenues are negative, debt covenants have been breached, and so on.
Incorporating dedicated worksheets into the model that summarise these checks will enhance robustness and give users more confidence that the model is working as intended.
Example of a dedicated error checks summary worksheet (checks linked by hyperlinks)
Further reading
The above merely scratches the surface of best practice models. To read more, download BPM's best practice modelling standards. These standards have been collated over the last seven years by talking to many experienced modellers worldwide. The aim is to reach consensus about the dos and don’ts of model construction.
If you have a query for the spreadsheet skills section, please feel free to drop Liam a line at liam.bastick@sumproduct.com or visit the SumProduct website where you can also subscribe to the new monthly newsletter providing more tricks and tips.
Search for 'Spreadsheet' in CIMA Mastercourses.
September 2009
Back to Insight front page
What did you think of this article? Email tim.cooper@cimaglobal.com.