Top 7 Sins of Model Design

  • Tagged with:
  • Excel,
  • Formatting,
  • named ranges,
  • Spreadsheets,
  • Styles,
  • VBA/macros,

Everyone loves lists, so here’s one of my own — the top 7 things not to do when designing and building a financial model.

Nothing controversial in this I hear you say? Fear not — this list will ruffle a few feathers. A few of these are on others’ best practice lists. So here goes …

1. DO NOT have separate sections for Inputs, Calculations and Outputs

Many people say you should have physically separate sections for Inputs, Calculations and Outputs. While this sounds catchy and pleasantly modular, it leads to bigger problems than it solves. Every part of your model should be easy to understand and review in context. That means when you read a formula you should be able to see the inputs to this formula without having to flip back and forth to other worksheets. Keeping things together and in context does not mean confusing inputs from calculations though. Inputs should still be clearly identified (cell shading, colours, etc.) and where possible, separated from calculations using a blank row or column.

2. DO NOT use named ranges

Naming cells and ranges using your native language sounds terrific. Surely a formula that says Revenue – Expenses makes more sense than one saying X51-X173? Well, this is all nice in theory, but what if your range named Revenue is pointing to the wrong range? Naming ranges takes time, as does checking whether your range names are correct. Named ranges just add another layer of complexity between the underlying logic and the way this is expressed in your spreadsheet.

3. DO NOT waste time formatting your spreadsheet

Formatting your spreadsheet with lots of fonts, colours, borders and shading can make you feel like an artist and certainly lets you exercise the right side of your brain for a change. But formatting takes time and effort which can be better spent designing, testing and documenting your model. Also, formatting can sometimes mask errors and obscure the real meaning of your data—from innocuous mistakes like formatting the wrong row in bold, to misleading practices like using a white font on white background to “hide” data. The key part of this recommendation is the word “waste”: do not “waste” time formatting. Formatting should be brief, informative and efficient. Never format cells using ad hoc cell formatting, and always use styles (see here).

4. DO NOT use Excel’s error checking

Excel provides native background error checking* but it really doesn’t work well. Turning it on and blindly trusting that it’s protecting you against errors would be a big mistake. To test this feature I built a simple model, seeded it with some common errors and turned on Excel’s error checking. It correctly identified 1 out of 7 errors, missed 6 and suggested 5 errors which were not in fact errors at all (click here to download this model). It’s like hiring a security guard to protect your home, then feeling comfortable leaving your front door unlocked and windows open, only to discover a week later that your guard has been at the pub down the road all week.

* Tools>Options>Error Checking in Excel 2003 or File>Options>Formulas>Error Checking in Excel 2010.

5. DO NOT use VBA/macros

When models are to be used and possibly maintained by others, you should only use VBA/macros when you have no other choice. Most users are not knowledgeable enough to understand and review VBA code, yet most people can work their way around the normal workbook interface. What happens when something goes wrong with the macro? Who will fix it? Who will check it is working properly? Sometimes macros are the only way to achieve the desired result—in these cases the macros should be concise, anticipate changes that may be made to the model over time, and be well documented.

6. DO NOT use multiple worksheets

Multiple worksheets (i.e. tabs) in a workbook are a great innovation and useful when different model structures need to be combined in one financial model. I still remember the bad old days when the only choice was to have multiple diagonally offset blocks of data in one worksheet to deal with differing model structures. However, like all good things, this feature has become abused. I’ve seen serious financial models with over 50 worksheets, many of which were only used for scratch pad-like calculations, redundant scenarios, and half-built models. Bulky models are confusing, time consuming to check, slow to recalculate, and have a higher chance of containing errors. Only use multiple worksheets where your model structure requires it. Delete redundant worksheets. And try not to replicate worksheets just to model alternate scenarios.

7. DO NOT trust your model

When all is said and done, you should never blindly trust your model, and you definitely should never unquestioningly trust someone else’s model. Most spreadsheets contain errors (see here). Many errors will be trivial but some might be material. A significant part of your model design and building should be directed towards protecting against errors (both real present errors and possible future errors). Things to consider include: good documentation, data validation, conditional formatting, range testing, high-level sense checks, cross checks and balance checks, and where possible get another person to check your work.

What are your key DON’Ts? We’d love to know.

1 Comment so far Join the Discussion

  1. Edouard

    10th April 2010 at about 12am

    I believed in those financial modeling standards… before. That is to say before creating important Excel models, importing data from csv files in one tab, creating pivot charts in another, and presenting the information in yet another tab.
    You CANNOT follow those “things not to do” in such models. The imported data increases in size every day, so trying to put the information on one sheet is doomed to fail.
    Not using named ranges means not using dynamic named ranges, which are so usefull for creating such models.
    Plus you have to format the elements of your final dashboard, which would wreck havoc on all your “one sheet layout” if you had it.
    In my view, those rules are ok for typically simple financial models, but really wrong in any advanced modelling. For such a model, please see (as an example)

Join the discussion


Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.