Top Financial Modelling Tips

How complicated is financial modelling?

Is financial modelling simply playing around with Excel and something that modellers can learn on the job?

No. That view is highly risky and time-consuming. Excel is not Word and, in any case, financial modelling involves far more than merely a knowledge of Excel.

Is financial modelling rocket science?

No. Models should never be incomprehensible black boxes. These types of model are sometimes viewed as advanced, but are often a result of flawed logic.

So, financial modelling is neither simple, nor is it rocket science. What it requires is a disciplined approach, consistency, logic, mathematical skills and attention to detail. Financial models can easily become a confusing contortion of links and long formulae if certain principles are not followed.

Whilst there are many dos and don’ts in financial modelling, here is a selection of my top recommendations:

Never include circular references, copy paste macros or iterations

There are no exceptions to this rule as circular references are the result of flawed logic. Their elimination is the challenge of financial modelling.

Never include numbers in a formula 

The exceptions are 0 and 1. Otherwise the model is inflexible and the assumptions used unclear.

Always copy a formula across a time-based row

There are no exceptions to this rule (or the model will be inflexible). To check this use Ctrl \ on a QWERTY keyboard (or F5 ALT S W).

Keep time periods consistent

The same column in different sheets should always refer to the same time-period (therefore use rows not columns for shorter time-periods). The exception to this is the reporting of annual accounts.

Never repeat a formula

A formula should be generated only once. Accounts are results areas and should never include calculations.

Formulae should be very short

Modelling is mathematics, so the beauty is in simplicity. Show workings, slim down the formulae and use suitable functions (avoid IF, it is vastly overused).

Be organised 

A disorganised model is hard to follow and likely to be inaccurate. As a minimum, use different sheets for assumptions (split into categories), calculations, results and checks. Use colour for inputs (F5 ALT S O XGE) and conditional formatting for checks.

Use shortcuts and function keys

The mouse is a hindrance, so take it out. Shortcuts and function keys speed up modelling considerably.

Think

The brain needs to be fully engaged, so take breaks and disable email pop ups.

If everybody followed the above rules, then financial models would be easier to construct and understand. Remember, financial modelling is neither simple, nor is it rocket science.

For more blogs please click here