A simple structure that makes your Excel forecast easier to update each month

Imagine an Excel forecast model that stretches out monthly into the future. The forecast for the current year-to-date consists of a few months of actuals, and a few months of forecast, predicting the expected landing point for this year. But then another month rolls by and the actuals need to be updated and the year-to-date expected outturn refreshed. For many models the update process involves someone finding all the cells that relate to the month that's just passed, and over-writing (what was) a forecast cell with the latest actual.

That's kind-of dangerous. The process requires someone to find all the right cells in the model that need updating. That might be OK if it's a small model. It might be OK if you're completely familiar with the model and rate yourself not to forget one of the cells that needs updating. It's less OK if:

• the model is bigger or getting slightly huge
• you are prepared to admit to yourself that you're human, could get distracted once or twice during a day, and you're potentially prone to the very tiniest bouts of forgetfulness (hey - you're human!)
• writing over cells in the model could mess up downstream calculations
• you pause to reflect on the fact that slavery is outlawed, you're not trying to create yourself a job for life, and one day someone other than you (on whose synapses the model's workings are not so firmly imprinted) could be the one who needs to grapple with the update process.

A better model structure that helps with the update process: store actuals in their own layer

It's easy for (bigger) models to start breaking during the monthly update process. But there is a better way to structure your models making them easier to update. It takes a bit of advance thought and planning when it comes to establishing the design for your model build, but once you understand (or have a 'vision' for the build that involves incorporating this 'easy-update' structure) the concept really does start to seem simple. Notice the formula in the formula bar below: ='Forecast Calcs'!I12*I$4+Actuals!I16*I$3.

Or: [forecast layer] x [forecast switch] + [actuals layer] x [actuals switch]

We're seeing that part of the trick here is to store the actuals in their own 'layer' or tab in the model. That way someone hitting the model for the first time knows exactly where to go to slot in the actuals in each month. The actuals tab is, in a way, 'dumb'. It's just a collection of input numbers. But the layer is 'smart' in that, as a design feature, it stops someone accidentally overwriting any forecast calculations and risking breaking the model.

Leave the forecast alone, held in its own layer too

In the model structure shown above the forecast calculations stay the forecast calculations at all times. Of course you will likely want to update your forecast as time goes on but it always sits there doing its job running its own calculations a little more clearly (than when the forecast is a direct mix of actual numbers and calculations both stretching across the page, with select calculations manually overwritten each month).

Positioning yourself to be able to create the actuals plus forecast 'sandwich'

What you have sitting at the top of this model is an amalgamation of the prior period actuals plus the forecast calculations. Notice again how simple the formula is that amalgamates the two pieces of the puzzle. It's the switch at line 4 multiplied by the forecast calculations, plus the switch at line 5 multiplied by the past actuals. That's as complicated as it needs to be (it's not complicated at all).

You need to plan in advance and build this structure in from the start. But holding the actuals in their own layer makes it easier for someone to find what they have to update. And it avoids the risk of muddling downstream calculations that relate to the process.

There are a couple of other things going on as well though that make the whole tasty recipe really work:

1. the help provided to the mechanism by making sure the model operates a consistent timeline across the top of every Excel tab
2. the integration of balance sheet modelling, with room created for the latest balance sheet at the start of the forecast each month.

The role of a consistent timeline for all model tabs

The switches established at the top of the timeline, and the choices made about what's contained there, are essential to getting the whole mechanism working. The timeline is consistent throughout the whole of the model and sits at the top of each tab of the model. Each tab uses the exact same structure so that e.g. period 7's figures always sit in column K for every single tab.

When a month passes by and last month's figures change from being forecast to becoming actual, the first stage in the update process simply involves changing one cell in the model (C11 below). The switches across the whole timeline and the whole model respond. Room is cleared in the actuals tab for the new numbers (H16). Someone knows what they need to update where. The formula that amalgamates the actuals with the forecast adapts, and the new year-to-date expected outturn arrives very quickly.

Notice a very good latest result for period 4, with that result automatically arriving in the year-to-date forecast at the very top of the model (H11):

The extension into balance sheet modelling

The micro-model that you're seeing today integrates the three financial statements. If you'd like to know more about getting that working in your model see here:

• 6 steps to getting your balance sheet balancing
• how to avoid breaking down in tears during your weekend away at Oxford.

In today's example you can see how the balance sheet forecast adapts to the switching in the model, with everything ready to shunt forward when a new month rolls by, and the model's balance sheet check staying true at all times.

The concept could/ would be extended e.g. if you were planning to raise debt as part of a transaction, with the transaction date added as a switch in the timeline, and transaction related changes slotted in on that date (in the same fashion as balance sheet numbers are being slotted in at the start of the forecast here). That's an extension that sees the whole model quickly flexing and staying in balance both:

1. when a month goes by and a forecast period becomes actual
2. if the transaction gets, say, pushed out by a month and all the transaction amounts need to move sideways.

Simple is good

It's a simple concept: a model structure that makes sure the year-to-date projections amalgamate the actuals and the forecast calculations, each held in their own separate 'clean' spreadsheet tab. But it's something that requires a good amount of foresight, needs to be designed in from the start, requires a consistent timeline and a consistent model structure. It's the kind of thing that's almost-slightly-easy if you know about it and build it in from the very beginning. It's the kind of thing that becomes very tricky to re-work an existing model for otherwise.

It's a great feature for a financial model and, if I ruled the world, I'd make it totally compulsory. I built too many models, with too much time spent chasing balance sheet errors, and too much crying, before the light dawned for me.

Here's to happy modelling - with far fewer tears!

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.