‘Code Smell’ is a term used by programmers to describe small problems they notice in a large programme block that indicates there might be a more significant problem in the code. These are little warning signs that indicate the designer may have taken short-cuts or may have lacked attention to detail.
The same principle applies to financial models. Here, though, we might call it ‘Cell Smell.’
Whether you are an accountancy firm, investment bank or any business, financial models are at the very core of all major projects, and small problems can have significant consequences.
Modellers need to train themselves to spot the red flags within a model, and fix problems early. Here are 5 indicators, some of which are best practice and some are key things to avoid, that contribute greatly to ‘cell smell’. I recommend you look out for:
1. Clear separation of inputs, workings and outputs
The number one thing to review before anything else is if the model is structured like it should be. We consider best practice to be where the inputs, workings and outputs have been clearly separated and no unexpected or hard coded numbers are stuck in formulas. A typed up number buried in the middle of a formula is a warning sign that good structure may be lacking elsewhere in the model.
2. Consistent structure across worksheets
I recommend using the same columns to refer to the same periods across worksheets. When you do not have this fundamental structural point in your model, then you are opening yourself up to a catalogue of errors between sheets. Having varying columns requires more complicated formulas, and it’s harder to tell at a glance if any cells are assigned incorrectly. Consistency – across all areas of a workbook – breeds confidence in the model. Of course, this can be easier in principle than practice, because….
3. If multiple people are involved, errors are multiplied
Seeing a model where all of the worksheets are completely different and have been formatted differently is a bit like entering a room and having ten people try to tell you the same story at once. An inconsistent style can point to the fact that the people who did the later work on the model may not be familiar with the workings of the original. Figuring out whether the worksheet even still performs the purpose it was originally designed for can take lot of time to unravel. Spotting this problem early (ideally with the first contributor who goes off-style) can save a lot of headaches later on.
4. Be sure to avoid circular references
In general, project finance models tend not to feature circular references – where formulas refer to themselves – because of how quickly they breed workbook-breaking errors. Still, we often get people coming to us saying there are one or two circular references in their model have been left in deliberately and they are happy with them. Of course, on review we invariably find there are actually 15 circular references, because the modeller has lost track of them. In the world of project finance, it should be universally accepted that you should not put circular references in your models; we prefer to ban them outright, without exception.
5. Simplify your model
It’s easy to think that putting everything in one place makes it easier to spot problems – it keeps you from hunting through the workbook, after all! However, overcomplicated formulas are often a breeding ground for hard-to-spot errors. Instead of focusing on a series of complex formulas, break your calculations down into three or four different steps. This makes your spreadsheet easier to review – something that senior decision-makers (particularly investors) will be more comfortable with. Formulas comprised of a hundred terms that barely fit on the screen are likely to irritate savvy analysts. Similarly, reams of inactive or unused code can cause confusion and lead to errors.
What do you see as the key indicators of Cell Smell? Let us know via twitter @operisgroup
