A fully flexing structure for your Excel model

One of the things you get to think about in Excel modelling is the exact layout and design for your model. Ideally that’s all done in advance, before you’ve got a big beast of a spreadsheet to manipulate.

Here are a couple of helpful design elements (1) and (2) that you might want to give thought to for your next model build.

(1) A fully flexing 3 statement model

If revenue changes you want the full impacts of that movement reflected in forecast cash flow.  A change in revenue is going to affect working capital, VAT, tax and short term funding, all of which have cash flow implications.

This article guides you through getting the financial statements working together so that the impact of a stress test and balance sheet changes are fully reflected in cash flow. It takes you through a simplified set of 6 steps that will help you get the essential linkages into your model – so the 3 financial statements interact as you’d expect – and so the knock on effects of a change e.g. in revenue or costs are felt in the balance sheet and on to cash flow.

A 'proper' balance sheet check

As part of establishing a simple set of links early on, the idea is that you establish a ‘proper’ balance sheet check in your model.  That is, you establish a balance sheet check early and in the very simplest version of your model, rather than at the end when it’s going to be more difficult to track (perhaps) multiple interacting errors.  As you progress and build more complexity into your model you can keep an eye on your balance sheet check, making sure it stays true as you go.

Here you can see an example mini-model taking two readings on the balance sheet and comparing them to see whether they match.

The initial reading on the balance sheet

At line 11 above you can see a first reading on the balance sheet that’s been provided by the forecast we’ve made of all the various asset and liability line items (contained in line 27 on the ‘Forecast Calcs’ tab)…

…with “cash” at line 25 of the 'Fin Stats' balance sheet accumulating from the bottom of the cash flow statement.

A further reading on the balance sheet

At line 57 in the above screen shot you can see a second reading on the balance sheet, where we look at last year’s balance sheet position and add to that the figure from the very bottom of this year’s profit and loss statement.

If the two calculations match (each giving us a separate result for the total of our balance sheet) then we can be more confident that our financial statements are working with each other as they should. The result is a (truly) balancing balance sheet.

The key is getting the key links needed into your model early on in the build, so your balance sheet check stays true throughout.

(2) Actuals that slot in each month

As well as (1) getting your model’s financial statements working together as they should (helping make sure a change in revenue or costs is going to be fully reflected in cash flow), months are going to pass by.  This article introduces you to structural elements of a model that (2) will make your model easier to update each month.

As explained in the article, the idea here is that you separate out the financial statement elements that regularly update so they sit in their own layer in the model.  The year end forecast is constructed as a ‘sandwich’ of recent actuals (in one layer/ Excel sheet) plus the forecast calculations (a second layer in the model).

The alternative might see someone needing to overwrite forecast formulas in the model manually, replacing them with actual numbers, which is a process prone to error.  Using that approach, all the correct cells need to be overwritten in all the right places (meaning some could be missed).  And there’s a chance that manually changing a number in one place affects downstream forecast calculations.

It’s much safer to hold the actuals in their own (very simple) layer where it’s obvious what needs to be updated.  When the next month’s actuals become available, the model is advanced by one month, freeing up space for the most recent results.  Switching across the top of the model makes sure that an extra month’s historics get used to construct the year end forecast. 

See the screen shots below for a quick demo of a flexing mini-model where that’s all happening.

A quick tour of the micro-model that combines structural elements (1) and (2)

This mini-model contains both of the structural elements described above in one place.

It’s (1) got the essential linkages in place (as described in the link to the first article referenced at the top here) so that a revenue change results in a working capital change which is felt in cash flow – with the balance sheet check always staying true.

At the same time (2) actuals are held in their own layer in the model where it’s easier to update them.  Here you can see forecast financial statements making use of switching at the top of the model (from row 3) and being assembled from the actuals plus the forecast calculations (from row 11).

When a month advances, conditional formatting (at line 16 below) highlights the room that has been created for the new actuals, held in their own Excel sheet.

With the switching operating at the top of the model, new actuals figures are automatically fed into the updated year end forecast.

At the same time, opening balance sheet figures for the month slot into ‘control accounts’ being used to forecast balance sheet items and cash flow movements.  Because the x3 financial statements are linking together properly the model automatically keeps working and balancing as new profit and loss and balance sheet actuals are fed in each month.

Thinking ahead helps!

As long as it’s designed in at the start of  your Excel modelling it becomes much easier to:

(1) keep your balance sheet balancing as you build, and add further complexity to your work, always making sure balance sheet changes are fully reflected in cash flow

(2) make your model easier to update for new actuals each month.

There is a catch though.  It does take a bit of thinking ahead when you’re first contemplating the design for your model!