Building a financial model

Because most people only ever occasionally build a financial model, the potential for problems to arise is high.?At best these are irritating and time consuming to fix but at worst they can jeopardise the very purpose the model is being developed for.

Ben Whitworth who leads our financial modelling practice outlines below, some basic guidelines:

1.??????Keep it simple.?A simple model will be quicker to build, is less likely to include mistakes and makes it easier to understand the flow of calculations.?Simply put, if you can do a calculation in 4 simple steps or 1 complicated calculation….do it in 4 simple steps.

2.??????Split inputs, calculations and outputs.?Mixing them up can make a model harder to update, navigate and understand.?Be clear about where all your inputs are so a user can quickly see what is driving the model. Having well organised outputs enables users to focus on what the model is telling them

3.??????Clearly identify your inputs.?How many times have you had to rummage around to find out where the inputs are and then determine if the number is a percentage, a ￡value or something else….??It is far easier to simply say, for example, “all inputs are in yellow cells”, and then they are clearly labelled as %, ￡000’s, No. of customers etc.

4.??????No hardcoded figures in calculations.?Typed in numbers should only ever be put into clearly identified input cells.?This means the user can readily identify all the assumptions in the model and it is much easier to update them if needed.

5.??????One row, one formula.?Each row should ideally only contain one formula which is copied across the entire row.?It means you don’t have to remember to update multiple formulae in the same row if a change is required.?A common example would be the annual totals at the end of each 12-month period - a better solution in this case would be to have annual summaries to the right or left of the main calculations.

6.??????Use 4 - line schedules.?By adopting this approach (opening balance, P&L movement, cash movement, closing balance) you stand a much better chance of making sure that your primary statements (P&L, Balance sheet and Cashflow) all hang together properly and reconcile with each other.?Also, drive your cash position from a direct cashflow (cash inflows and cash outflows) as this is more robust than an accounting style cashflow (which you can have as an additional output).

7.??????Only calculate things once.?Once you’ve done a calculation, any time you need to use the result, make sure you go back to the original calculation.?If you calculate the same number in two separate places there is always a risk of error both in terms of the calculation and any subsequent changes needing to be made to both sets of calculations.?The same is true for input assumptions - only put them in once and always refer back to them.

8.??????Include error checks.?Wherever possible, include error checks in your model.?These can help you identify issues as they occur and enable them to be fixed straight away.?Error checks can apply to inputs (e.g., does our sales split total = 100%), calculations and outputs (e.g., does the balance sheet balance).?Ideally, all error checks should feed into one overall model check.

9.??????Adopt consistent signing conventions.?It makes it easier to interpret what the model is telling the user (e.g. income and assets as positive figures, costs and liabilities as negative figures).

10.??Format properly from the outset.?It’s tempting to think formatting is too time consuming, but the reality is getting it right from the very start (headers, units, titles) means less mistakes and a more professional result.

11.??Printing.?Finally, make sure your model is always set up to print readily as not everyone will access a model electronically. ?Remember to include headings, sensible page breaks, headers and footers.

A fully integrated financial model is an essential business tool.?They help provide future visibility based on what is known, or expected, today and are used to make key assessments of what needs to happen next. Particularly in the current environment, being able to adjust a forecast to take account of changes is incredibly helpful in that decision making process.

To learn more about how Cortus Advisory Group can help you build or manage a financial model appropriate for your business please contact James Groot at [email protected] or Ben Whitworth at [email protected]