Given the relative success of my ‘Modelling Nirvana’ blog, it is fair to assume that the bulk of readers stumbled across it as a result of a search for the popular 90’s ‘grunge’ band.
So here goes with another Nirvana themed blog, in the hope that some 90’s rockers with bad typing skills stumble across it.
I first came across the term ‘code smell’ at the recent EuSpRiG conference in London and it struck a chord with me.
Wikipedia define ‘code smell’ as follows:
‘In computer programming, code smell is any symptom in the source code of a program that possibly indicates a deeper problem. According to Fowler, “a code smell is a surface indication that usually corresponds to a deeper problem in the system”.
They indicate weaknesses in design that may be slowing down development or increasing the risk of bugs or failures in the future.’
Many people do not realise it, but building a model in excel is effectively ‘computer programming’ and the spreadsheet is the program. There are many areas where spreadsheet developers could learn from the software development industry (quality assurance and testing, code sharing and re-use) but that is a discussion for another blog.
So often, when I open up a model, I can tell within a few seconds that it ‘smells’ bad. It is not necessarily wrong and it may be functioning correctly, but I can see straight away surface indications that highlight poor design which imply that there may be deeper issues.
Here are some signs that a model ‘smells’, that should be obvious within a minute of opening.
Inappropriate / inconsistent use of colour
Generally the first thing you see on opening a smelly model. Wild, garish oranges, browns, reds and yellows scattered all over the place with no obvious rationale. Painful to look at for any period of time. This makes you think the modeller has spent too much time on the colour scheme and not enough on the modelling.
In spreadsheets, colour is good, but it needs to be properly applied. Colour and font are great for identifying particular types of cells or calculations within a model. To be really useful, a consistent, standardised and muted (except error checks) colour scheme should be adopted and applied so that both modeller and user immediately know what a colour means.
If a model is full of extremely long formulae, for me it rings alarm bells.
Not only does it mean that I will be spending many hours of my day deciphering them, it also means that the likelihood of errors being hidden within these massive formulae is pretty high.
I always strive to keep formulae as short, simple and easily traceable as possible. I try to stick to the ‘rule of thumb’ that no formula should be longer than my thumb.
Mixing of inputs, calculations and outputs
Whilst there are exceptions to every rule, in modelling it is generally a good idea to ensure separation between inputs, calculations and outputs. This usually means keeping them on separate worksheets.
Fundamentally, this separation helps the user by grouping together any inputs they will be required to provide and by clarifying where they need to look for key outputs. This reduces the need for the user to delve into the calculation ‘engine’ and means they are less likely to miss any inputs which are hidden in an obscure corner of the model.
Deviation from this rule without good reason (and there are some) implies that the modeller has not been trained in (or disagrees with) accepted good modelling practice.
Inconsistent layout of worksheets
It is true that different worksheets within a model may have different functions and in that case, it is fine for the layouts to be different.
However, where worksheets are doing pretty much the same thing, to have the worksheets laid out differently is a sign of bad design.
I see this often in project finance type models where the timeline starts in different columns or in budgeting / forecasting type models where inconsistent formats of financial statements are used between sheets in a consolidation.
Inconsistency of worksheets makes it much more difficult to copy data or link between sheets and increases the risk of introducing errors whilst doing so.
And a couple that are less easy to spot………..and therein lies the problem, both of these are readily spotted by software checkers but not by the naked eye.
Formulae within a calculation block should be kept consistent, meaning they follow the same logic and can be copied across the block without amendment cell by cell.
Inconsistency of formulae within a calculation block increases the risk of errors and is not good modelling practice.
Embedding constant values within formulae is a bad idea.
It may make perfect sense to code x times 28 into the February widget revenue calculation, but what about next year when it is a leap year and you are on holiday. Will your colleague know it is there? All constants (and variables) should be separated as clearly defined inputs.
If you have any other tell-tale smells, please comment.
If, having read this, you realise to your shame that you or your team are creating smelly models, it may be time to consider some financial modelling training. At F1F9 we provide a range of courses which can help (there are other providers and I am sure they will respond to this post).
If you fear that some of your existing models are smelly, it might be time to get them reviewed or rebuilt. We can help you with that too. Please get in touch.