Spreadsheeting is often seen as a core skill for accountants, many of whom are reasonably conversant with Excel. However, many accountants who build complicated spreadsheets frequently forget that the key end users of a spreadsheet model (ie, the decision-makers) are not necessarily sophisticated Excel users and often see the final output only on a printed page, eg, as an appendix to a Word document or as part of a set of PowerPoint slides.
If you have this in mind, it becomes easier to understand why there have been numerous high-profile examples of material spreadsheet errors. I am not saying that well-structured models will eliminate mistakes, but in theory they should reduce both the number and the magnitude of these errors.
Modellers should strive to build “Best Practice” models. Here, I want to avoid the semantics of what constitutes “best” in “Best Practice”. “B and “P” are in capitals deliberately, as I see this as a proper noun insofar as no method is truly “best” for all eventualities. Plenty of texts include copious instructions on what thou shalt and shalt not do regarding building a spreadsheet. And lo, by the seventh day, the reader is truly bored and sleepeth. I must admit that I have actually contributed to the Odyssey of developing some of these epic tomes that Homer would have been truly proud of. But, hey, I am a big fan of The Simpsons.
I would rather we consider the term as a proper noun to reflect the idea that a good model has four key attributes:
These four attributes in turn can help model developers decide how best to design financial models. It’s a system our company calls CRaFT.
Models constructed consistently are easier to understand as users become familiar with both their purpose and content. This will in turn give users more comfort about model integrity and make it easier to add or remove business units, categories, numbers of periods, scenarios, etc.
Consistent formatting and use of styles cannot be over-emphasised. Humans take in much information on a nonverbal basis. Consider the following old Print dialog box from Excel 2003:
True, this interface has long since been replaced. But like Quasimodo, does it ring any bells? Feast your eyes on the above dialog box. It has a drop-down box, check boxes, option buttons, scroll bars — all manner of data validation. You may have never seen this dialog box before in your life, but you just know where you need to input data. We may not realise it, but we have all been indoctrinated by Microsoft. Whilst 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 exploit this mindset when creating models. The worksheets in my workbooks all contain objects or cells that may be modified by the user and are readily identifiable without the reading of any instructions. We are all Pavlov’s dogs.
Other key elements of a workbook that should be consistent include:
This should reduce referencing errors, increase model integrity, and enhance workbook structure.
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!, etc. cannot occur (ideally, these error messages should not occur anywhere).
My old boss used to promote the “cockroach theory”: Once you saw one of these errors in a model, you would believe the model was infested and never trust it after that. Removing these prima facie errors is straightforward and often highlights that the modeller has not undertaken a basic review of his or her work after completing the task.
When building, it is often worth keeping in mind hidden assumptions in formulae. For example, a simple gross margin calculation may calculate profit divided by sales. However, if sales are nonexistent or missing, this calculation would give #DIV/0!. The user therefore has two options:
However, checks are useful in many situations, and essentially each will fit into one of three categories:
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.
The above is a sanitised screenshot from a real financial model. It is an extract from a worksheet with no fewer than 800 checks with the overall total included at the bottom (this links to the overall check at the top of the sheet, displayed in all worksheets throughout the model). Each check may be switched off if necessary, and each check hyperlinks to where the check is in the model. If you were the recipient of such a model, assuming the checks have been calculated correctly, would you feel more comfortable with this model compared to the usual fare received?
One benefit of modelling in a spreadsheet package such as Excel is to be able to change various assumptions and see how these adjustments affect various outputs.
Therefore, 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. Therefore, the user may wish to consider other methods of entry including using drop-down boxes, option buttons, check boxes, and so on.
I strongly recommend that all inputs are entered as positive numbers, wherever possible — just change the descriptions accordingly. If I were to tell you that last year costs were $10,000 but they have increased 10% this year, you would understand me. But what would you make of my telling you costs were minus $10,000 and had increased by –10%?
The aim is to have a model provide sufficient flexibility without going overboard.
Models must be clear, concise, and fit for the purpose intended. If you can follow it on a piece of paper (ie, no Formula bar), it’s transparent.
Most Excel users are familiar with keeping inputs and assumptions away from calculations — and keeping inputs and assumptions and calculations away from outputs. However, this concept can be extended: It can make sense to keep different areas of a model separate, eg, 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 reuse worksheets and ring-fence data. Keeping base case data away from sensitive data is also important, as many modelling mistakes have been made from 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.
Again, this graphic comes from a genuine model, albeit modified. It should be noted that not only does this graphic show how the model flows, but also each box within the graphic is a hyperlink that takes you to the relevant section of the model, complete with documentation.
Similarly, a table of contents constructed with hyperlinks helps users and developers alike navigate through larger Excel models:
It’s all about design and scoping.
The problem is that we are all crunched for time in today’s business environment with perpetual pressure on producing results more and more quickly. Consequently, we dust off old templates, fit square pegs in round holes, and produce mistake-laden spreadsheets time and time again, resulting in costly management decisions.
The whole process is simply a false economy. Time spent on better scoping out the model and designing the layout will lead to fewer mistakes and greater efficiencies in the long term.
— Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.
