Modelling principle #7: don't 'hardcode'

This time in bite-sized best practice, it's a quick one but it touches on what most modellers would view as one of the deadliest sins in the financial modelling world.

Last time we wrote about segregating inputs, calculations and outputs. This means that all your model inputs are moved to an easy-to-locate place. But did you find and move them all?

Seeing that screenshot will bring many experienced modellers out in a rash. There is that deadly sin: the 'hardcode'. That input tax rate of 17% is just typed into the formula. That assumption is 'hardwired' in - however the rest of the model and its results move, that 17% isn't changing.

Why is it important?

We've established that inputs and assumptions should both be accessible and evident. Hardcoding data into cells achieves neither, and is extremely prone to being overlooked or overwritten. It also introduces the risk that the same assumption is input as different values in different places.

Having the inputs visible also of course helps with keeping it simple and logic reading like a book.

How do we fix it?

Fixing this one is pretty easy. Don't do it. Develop that mental reflex whenever you find yourself typing a number (or any text or date or anything else that might actually be a variable) into a formula such that you stop and think 'let's put this somewhere else'.

It easily ends up looking like this...

...and that then gets fed into the calculations like this:

We'll come back to some of the techniques shown here in later articles in the series.

Let's digress a moment: not all assumptions you are wiring into formulas are numerical ones. Hard-wiring the 17% rate is clearly limiting your tax calculation - but what about the profit number it's applied to, and what costs have been allowed or disallowed in getting to that profit number? We should be aware of what other inflexible assumptions or treatments we bake into our formulas and make sure they've been thought through, and documented.

Common sense should prevail

Actually, we think some minor hardcoding is just fine. Specifically, where common sense would indicate that a value isn't likely at all to change. It's usually safe to assume that there are 12 months in a year, 4 quarters in a year, 3 months in a quarter, and so on. It's fine to divide by 2 to work out the average of two numbers. Okay, so some businesses run 13 accounting periods, not 12, but you'd know this before you start laying out the model. If you build it for 12 but make the 12 an input, and someone changes it to 13, will the model sprout the extra columns needed? No, didn't think so.

You can read more at page 7 of the ICAEW financial modelling code. There'll be a hiatus of a couple of weeks whilst this writer is away on leave, and also hiding from the anti-hardcode zealots for that last paragraph above. We'll be back soon to discuss using your columns in consistent ways.