Save content
Have you found this content useful? Use the button above to save it to your profile.
Stinky
iStock_SIphotography_stink

'Cell smell' and spreadsheet hell

by
11th Aug 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

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. 

Tags:

Replies (2)

Please login or register to join the discussion.

avatar
By abaco
12th Aug 2015 09:54

Easy Option

When it comes to forecasting profits, cash flow and taxes I take the easy option by using figurewizard then simply export the forecasts to excel and format them to suit.

Thanks (0)
avatar
By fiairlieb1
13th Aug 2015 10:33

Horses for courses

Whilst Excel is a fantastic tool, I see it used too often by accountants and finance staff when all they are doing is replicating something that is better done by the finance system.

Often, millions of pounds are spent to procure a finance system and reporting package (integrated or not) only for accountants to export the basic data into Excel and spend time doing pivot tables etc when they should be looking for a report from the finance system that does this work for them

As I often say, I dont recall any module during my professional exams that showed me how to produce pivot tables, but they did tell me how to interpret the figures that the pivot tables (or finance systems) generated. Too often, accountants (in my experience) now see their job as producing figures using fancy spreadsheets - which, realistically, a non-finance professional could, and should, be doing -  instead of providing a professional service that will interpret the figures and help the business with their financial management

Whilst the advice in the article is good, I would always urge users to first consider whether using a spreadsheet is appropriate, or whether they would be better getting someone else to generate the numbers and for them to then do something with this

Thanks (0)