Features
Design structures
that make your model
easier to update
A model that's harder vs. easier to update
Imagine an Excel forecast model that stretches out monthly into the future. The forecast for the current year consists of a few months of actuals, and a few months of forecast, predicting the expected landing point for the end of this year.
But then another month rolls by and the actuals need to be updated and this year’s expected outturn refreshed.
For many models that update process involves someone having to find all the cells that relate to the month that’s just passed, and over-write (what was) a forecast cell with the latest actual.
In a large model, the extent of that complexity is something that could inadvertently see an error introduced into the work.
The difficulties with over-writing a forecast with actuals
,The update process that sees someone overwrite forecast cells with new actuals is prone to potential mistakes. It requires someone to find all the correct cells in the model that need to be changed.
The process might be relatively straight forward if it’s a simple model. The update might carry a low risk of not being carried out correctly if the person using the model is completely familiar with the spreadsheet and unlikely to neglect cells that need updating.
The update process will be more prone to error if:
- the model is larger and updating it is a longer process (meaning there’s e.g. more risk of someone getting distracted with other work while the updates go on, then returning to the update but missing something)
- writing over cells in the model could interfere with downstream calculations
- only one person regularly conducts updates of the model. That’s against an alternative that sees the update process rest with more than one staff member (so that when someone is off work or leaves there’s still someone in the finance team who can run the updates).
An improved model design that makes the update process simpler
It’s not surprising that the update process for larger models might result in errors.
When building a model for you we use a design structure that makes the work easier to update.
The design needs to be implemented from the start (and so is something that needs to be thought of and applied at the beginning). The concept itself simple and involves maintaining actuals in their own ‘layer’ in their own spreadsheet tab in the model.
Notice the formula at the top of the screenshot below: =’ForecastCalcs’!I12*I$4+Actuals!I16*I$3.
Or, expressed another way: [forecast layer] x [forecast switch] +[actuals layer] x [actuals switch].
The benefit of using a design that stores actuals in their own layer
If the model stores actuals in their own layer, someone encountering the model for the first time can see where they should slot in new actuals each period.
The actuals tab is, in a sense, ‘dumb’: it’s just a collection of input numbers. But the layering is ‘smart’ because, as a design feature, it stops someone accidentally overwriting any forecast calculations. It reduces the risk that the the update triggers an errror in the model.
The forecast is left alone, also in its own layer
In the model structure the forecast calculations also stay contained in their own layer at all times.
Of course, you will likely want to update your forecast as time goes on. But, as part of the model we build for you, the forecast stays sitting there running its calculations a little more clearly (than is the case when it’s a model that assembles a direct mix of actual numbers and calculations stretching across the rows of a single tab, with specific calculation cells needing to be overwritten each month).
Combining actuals and forecast calculations to create the expected outturn
What you have sitting at the top of this model design is an amalgamation of the prior period actuals plus the forecast calculations.
Notice again how simple the formula is that amalgamates the actuals and forecast layers. It’s the switch at row 4 multiplied by the forecast calculations, plus the switch at row 5 multiplied by the past actuals. That’s the extent of the work needed to assemble the next forecast at the top of the model structure.
The simplicity of the update process each month
Notice the switches operating at the top of each tab of the model. Operating a consistent timeline (and the choices about what’s contained in the timeline) for every Excel sheet is an important part of the model’s design.
Each tab uses the exact same structure so that e.g. period 5’s figures are always contained in column I for every single tab.
When a month passes by and last month’s forecast calculations need to be replaced with actuals, 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 change. Room is cleared in the actuals tab for new numbers (H16).
Someone can see what they need to update where.
The formula that amalgamates the actuals with the forecast in the financial statements adapts, and the new outturn arrives into the top of the model very quickly, as part of a design and a process that is less prone to error.
Updating the model for the latest balance sheet
The illustrative model structure you’re seeing integrates the three financial statements as described here: balance sheet and cash flow modelling.
In this structure you can see below how the balance sheet forecast adapts to the switching in the model, with latest (e.g. receivables) figures automatically slotting into the next period when a new month passes, and the model’s balance sheet check staying true at all times.
The concepts above are extended e.g. if you are planning to raise debt as part of a transaction. In that case a switch is added into the timeline, and transaction related changes get slotted in on the transaction date (in the same manner as you see balance sheet figures being slotted in at the start of the forecast here).
That’s an extension of the design that sees the whole model quickly flexing and staying in balance if the transaction gets delayed by one month and all the transaction amounts need to move to the right in the model.
We employ model designs that make the update process more straight forward for you and less error prone
As described above, it’s a simple concept: a model structure that makes sure the expected outturn for the year amalgamates the actuals and the forecast calculations, with each held in their own separate ‘clean’ Excel sheet, with latest balance sheet (and transaction) amounts automatically worked in and checks staying true.
It requires advance thought about the timeline and switching contained at the top of the model, with consistent layout for each tab of spreadsheet.
It’s a straight-forward concept, but it’s the sort of thing that becomes difficult unless it’s employed from the start of a model build.
You can expect the ‘easy update’ model structure as part of any financial statement build work we complete for you.