Top 7 Sins of Model Design
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
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
* 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
What are your key DON’Ts? We’d love to know.