Chris Aldred, director at Operis, discusses the 5 key indicators of a problematic spreadsheet.
‘Code smell’ is a programming term that describes small problems in large programme code blocks that indicate there might be a more significant problem afoot. They are essentially little warning signs that indicate to an experienced developer that the author may have taken short-cuts or lacked attention to detail. We see extremely similar problems crop up in the work of spreadsheet designers as well, although in that case we might call it ‘cell smell.’
Accountants are no stranger to the complexities of spreadsheets, but as used to them as we are, it’s still tiresome to find errors or any other problems that have slipped through the cracks and can often cost us valuable time correcting. One thing financial modellers can teach us is how to detect these problems at first glance, thereby saving time and money.
Financial models are one of the core features of any major project, and small problems in a model will more often than not have big consequences.
Accountants are the next line of defence (after the spreadsheet designers themselves and model auditors) against problems in the spreadsheet, and by familiarising yourself with the 5 most common indicators of ‘cell smell,’ that job becomes vastly easier and less time-consuming.
1. Multiple people involved in the modelling
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. That is, not very pleasant or clear.
It’s not only jarring to the eye but it does make it a lot more likely mistakes and miscalculations will slip through the cracks. An inconsistent style can also mean that the people who did the later work on the model may not have been familiar with the workings of the original. The last thing you want to be using is a spreadsheet that has been ‘repurposed’. These often cost companies a lot of time to unpick.
2. No separation of inputs, workings and outputs
Best practice exists where inputs, workings and outputs have been clearly separated and no unexpected or hard coded numbers are stuck in formulas.
A number buried in the middle of a formula is typically a warning sign that good structure may be lacking elsewhere in the model. Mistakes like this are typically a good indicator that the whole model deserves a thorough review before it’s put to use.
3. Lack of consistent structure
I recommend always using the same columns to refer to the same time periods across worksheets.
If your model doesn’t have this fundamental structural basis it can open up a catalogue of errors between sheets. Having varying columns requires more complicated formulas, and it makes it much harder to tell at a glance if any cells are assigned incorrectly. Consistency breeds confidence in the model for good reason.
4. Circular references
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 that have been left in deliberately and they are “happy with them”. Of course, on review, we invariably find that there are significantly more circular references the modeller has lost track of.
In the world of project finance it should be universally accepted that models should contain no circular references; we prefer to ban them outright, without exception, and as diligent accountants this is a good principle to follow.
5. Overcomplicated formulas
It’s easy to think that having everything in one cell makes it easier to spot problems but actually the opposite is often true. Overcomplicated formulas are often a breeding ground for hard-to-spot errors.
Calculations should be broken down into three or four different steps so you are not looking at a series of complex formulas. If your model is not easy to review then you shouldn’t feel comfortable using it. Your regulators certainly won’t.
What these indicators signal is careless design, which more often than not makes using the model far more difficult than it ever needs to be. Each of these signs can be spotted within 5 minutes of opening a spreadsheet. Make them your first check and save yourself time, and pain, in the long run.