Keeping Excel calculations simple

If formulas are kept shorter in models, with each formula taking a small step in further-developing a calculation, the logic operation being performed inside an Excel cell becomes clearer.

Short formulas make Excel forecasts easier to unpick and understand.

Long formulas, with functions perhaps buried within functions, will always be harder to interpret.

Breaking complex logic into more steps

If there's a relatively-complex calculation required to get to a result, you can look to break the formula apart and spread the logic steps over more lines.

Below you can see a receivables calculation arriving at line 39. The calculation is built up from the opening position (line 34) , with subsequent calculations (lines 35 to 38) working to modify the receivables balances. Rather than perform those calculations in one line (39), each is set out on its own row. Each of lines 35 to 38 is itself built up from simple step-by-step workings in the sections immediately above. For example, cash payments at line 37 is a straight-forward link to the figure at line 27, which is itself built up from stepped calculations.

Keeping calculations simple is a real discipline in model building, that takes close attention at every twist and turn of a build. Rather than perhaps being tempted to pat yourself on the back to see how many functions you can stack inside each other (even though that can be a fun game to play), instead ask yourself how simple each step can be made. As a result in the example we have today, and in building up the receivables calculation, the most complex formula in the set is the kind we see at line 24, which takes an input figure from line 21 and multiplies it by line 24.

Stepping through the logic means it becomes much easier to inspect and understand each ingredient of a calculation that could otherwise start to become more complex. The result is a calculation that takes more space and runs further down the page. But each Excel sheet contains more than a million rows. The 'cost' (needing to scroll further to see the complete calculation) feels like a price worth paying to me - for greater transparency.

The impact of model structure combined with formula selection

Excel regularly gives us multiple ways of solving a problem. For example, when picking a single important data point out of a data set, some people might gravitate towards either a Vlookup, Xlookup, Index (perhaps combined with a Match) or a SumIf. As well as always asking ourselves whether a logic step in a model could be simplified, an experienced modeller will be thinking about exactly which formula they might use and how that formula helps them keep everything inside as each cell as compact as possible.

Have a look at the 900 opening figure that's arriving in the correct position at cell H35 here. "If" we're in the first forecast period "Then" the 900 should appear. If we're not in the first forecast period then zero should appear. Many Excel users would gravitate towards using Excel's If formula to solve that issue. In this model you can observe the thought process that's gone on, with the workings at cell 35 using something that's more compact and simpler than that: it's 900 multiplied by the switch at line 5.

Line 5 in the 'Forecast Calcs' is itself a simple link in the model that tracks back to the 'Setup' sheet.

Rather than build in lots of logic tests sprinkled up and down the model (each of which forces Excel to do a bit of work, each of which has to be correct or could later need to be changed), as part of the design here what we've done is recognise the repeated need to answer the question: "Are we in a first forecast period?". We've centralised the logic at to top of each page in line 5 and, ultimately, for this model, right back in the 'Setup' tab. Choices about structuring the model give us the opportunity to centralise the most regularly-used logic tests.

Then, if we look at the calculation that's been picked for the (centralised) logic test in line 5 of the 'Setup' tab, even that itself is simpler than might be expected. It's something that's even simpler than "If" the last period (at line 3) was an actuals period, "And" if the current period (line 4) was a forecast period, "Then" we're in the first forecast period and should show a value of 1.

Centralising the logic makes it easier to run changes through the model

We've structured and centralised the model logic around the workings at the top of the 'Setup' tab. And we're looking to use the very simplest solution our experience leads us to think of for the work that's being done in cell H5 on the 'Setup' tab.

In this case the hard thinking means all the opening balance sheet values can 'slide' to the right as we go into a new forecast month, and the model stays balancing when we move from month 4 into month 5.

The work involved in taking care over 'simple' pays dividends

It's this (slightly obsessive!) attention to detail that makes the model easier to inspect and modify:

• individual formulas are selected such that they keep cell workings as compact as possible
• logic is centralised wherever it can be (so the number of operations are centralised and performed fewer times), and
• calculations are set out step by step down the page.

The effort means the work becomes much easier for someone new to it to inspect and understand. At that point, the pernickety attention to detail starts to pay off!

More on good financial modelling practices

This article is part of a mini-series of articles on good Excel practices.

You can see more articles in the series here: good financial modelling practices.