By Ian Bennett FCA

Whether you’re going through your annual budgeting process, or developing strategic plans for your client’s budgeting, there are a few top modelling tips we can all follow.

1. Take the simplest approach

I frequently see budgeting models where the inputs are back-calculated from other parts of the model, presumably because the revenue and cost drivers were right originally, but the model hasn’t been changed to reflect the current business. Often this is because a more sophisticated approach is being used in the business to do the calculations and the budget file has to be fudged to get the outputs.

You should consider the appropriateness of the current approach, then work out if the more sophisticated modelling should be included in the budget model.

Ideally, you strip out the unnecessary complexity, which may only be there simply because that’s how it’s always been done.

2. Focus on the outputs and the flexibility

The budget forecast model only needs to be able to:

(a) tell the story you need it to tell to the various stakeholders or produce the specific outputs required downstream in the process, and

(b) answer the questions that you will ask of it, for example, to be able to run specific sensitivities at a group level.

3. Ditch the Frankenstein’s monster and have an annual spring clean

Generally, clients will ask you to rebuild their budgeting models when all hope is lost and the model is a hodgepodge of sections bolted on to the other bolt-ons.

When the bolt-ons are larger than the original model, a rebuild is inevitable. Including a model refresh in your annual budgeting cycle could avoid this major intervention, and it ensures your analysts are all over the current model and can take ownership of it.

4. Repeat calculation blocks and structures

It’s been said many times, but the rewards are enormous – if three things (such as business units, properties or revenue lines) are mostly similar, then build them identically and have switches/inputs that make each of them work correctly, rather than tailoring each individually. This makes the model easier to understand and lets you make changes without having to remember how each of the sections is individually tailored.

“Generally, clients ask you to rebuild their budgeting models when all hope is lost and the model is a hodgepodge of sections bolted on to the other bolt-ons”

5. You want structural flexibility not calculation flexibility

Build flexibility into your models by using a fantastic design that will allow you to effortlessly make changes in the future with low risk. Do not try to build the most complex model now, just in case something happens in the future.

6. 3D references (drill-throughs)

3D references add up the same cell in all worksheets between two worksheets.

They look like this:

Consol!K15 =SUM(BkMk1:BkMk2!K15) where BkMk1 and 2 are blank worksheets used as bookmarks wrapped around the worksheets you’re consolidating (Figure 1).

3D-references are elegant and they force good design choices such as using identical calculation worksheets and using the same rows and columns for the same thing on every worksheet.

Figure 1: 3D-references (drill-throughs) (Click image to enlarge)

7. Avoid the evil functions

High-risk “advanced” functions (eg OFFSET, INDIRECT) are mostly used to get around bad design. You can avoid these high-risk formulas by choosing good model design in the first place and then rigorously sticking to it, no matter how tempting it is to do the quick workaround.

Ian Bennett FCA leads PwC Australia’s deals modelling team and has more than 17 years’ experience as a professional financial modeller.

YOUR QUESTIONS ANSWERED

Have a financial modelling or spreadsheet question you’d like answered in Acuity ? Send it to [email protected] with ‘Ask Ian’ in the subject line.

Read more