Crafting the best Excel models

Build exceptional spreadsheet structures with these 4 key principles.
Excel models

Many accountants who build complicated Excel 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.

With this in mind, it becomes easier to understand why we have seen numerous high-profile examples of material spreadsheet errors. This is not to say 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. To avoid the semantics of what constitutes "best" in "Best Practice", "B and "P" are in capitals deliberately. No method is truly "best" for all eventualities, so the goal here is to consider the term as a proper noun to reflect the idea that a good model has four key attributes:

  • Consistency;
  • Robustness;
  • Flexibility; and
  • Transparency.

These four attributes in turn can help model developers decide how best to design financial models. It's a system our company calls CRaFT.

Consistency

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 old Print dialog box from Excel 2003 (see the screenshot "Excel 2003 Print Dialog Box").


Excel 2003 Print dialog box

bastick-1


True, this interface has long since been replaced. But like Quasimodo, does it ring any bells? Feast your eyes on the dialog box in the screenshot. 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 dialog box appears quite flexible, we know the only things we can change are the objects in white. (For example, I know I cannot print out a list from the dialog box since that 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 reading any instructions (see the example worksheet in the screenshot "Sample Worksheet Using Classic Microsoft Design Keys"). We are all Pavlov's dogs.


Sample worksheet using classic Microsoft design keys

bastick-2


Other key elements of a workbook that should be consistent include:

  • Formulae should be copied uniformly across ranges to make it easy to add or 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 (ie, 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, etc.). If periodicities must change, they should be in clearly delineated sections of the model. If you do have a model where you want the first 12 months, say, to be monthly, then annually thereafter, always model everything at the lowest level of granularity (here, monthly) and then use SUMIF to aggregate months into years on output sheets later — it makes formulae so much easier to create and manipulate.

This should reduce referencing errors, increase model integrity, and enhance workbook structure.

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!, 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:

  • 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, eg, the balance sheet does not balance, the cash in the cash flow statement does not reconcile with the balance sheet, or the model contains #DIV/0! errors, etc.;

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 using the "best case"; and

3. Alert checks: Everything else! This flags other issues that may need to be reviewed, eg, revenues are negative, debt covenants have been breached, etc.

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 screenshot "Example of a Financial Model Worksheet With Checks" is a sanitised model 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?


Example of a financial model worksheet with checks

bastick-3


Flexibility

One benefit of modelling in a spreadsheet package such as Excel is being 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 entering all inputs 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.

Transparency

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, the graphic shown in the screenshot "Example of Flowchart for an Excel Workbook" 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.


Example of flowchart for an Excel workbook

bastick-4


Similarly, a table of contents constructed with hyperlinks helps users and developers alike navigate through larger Excel models (see the screenshot "Table of Contents With Hyperlinks").


Table of contents with hyperlinks

bastick-5


Word to the wise

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 into 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, spreadsheet modelling, and the Power BI suite of tools. 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.