Tips for preparing a financial model

By Alastair Richards

13 April 2022

Financial models are used for a variety of reasons and purposes, but they often form a key component or basis for making an important decision. It is vital for financial models to be reliable and easy to use. Below is a selection of tips, written by PKF Melbourne Corporate Finance Director Alastair Richards, which should be considered when preparing a financial model.

1. Planning

It is vital to plan out a financial model before it is started. This should include considering factors such as the following:

  • What will the model be used for?
  • Who will use the model?
  • What assumptions should be included and what assumptions will need to be adjustable?
  • What outputs will be required?
  • Will the model need to be updated in the future?

By considering the above factors, the need to rework a model will be reduced. This will result in saving time in the long run and also reducing the probability of errors, which model reworking can often lead to.

2. Label and clearly set out components of the model

A model should be designed to be easy for someone completely unfamiliar with the model to navigate it – for example, identifying where inputs are entered and outputs are found. This includes ordering information in a logical manner and using different colours and formats to distinguish between different aspects.

3. Keep formulae simple where possible

When preparing a financial model, the preparer should consider using simpler and less complex formulae where possible. Sometimes a complex formula can’t be avoided, however in many situations a complex formula can be broken down into multiple steps which are less complex and easier to model and prepare. Alternatively, a different formula and method may be available to address the situation which is also easier to use, prepare and follow. Taking this approach will reduce the chance of an error, make it easier to check and easier to amend should an adjustment be required in the future.

4. Consistent formulae across a row

The formulae used in a row should be consistent throughout that row. This will result in reducing the chance of errors, making the checking of the model easier as well as it being easier to update or adjust the model, should the need arise in the future. It can be difficult to implement this rule, it may mean additional steps need to be added, but it is important for the rule to be adhered to.

5. Built in error checking

A model should include formulae which check for errors automatically. The most obvious is to ensure the balance sheet balances, but many others can also be incorporated, and we recommend they are incorporated where possible. The checks should be formula driven and include features to make it easy to identify when a check does not work. Methods used to identify such situations include conditional formatting on the error checks and summarising the results from the error checks in one place.

6. Review the financial model for errors

Built in error checking will identify many of the potential errors, but in addition, a financial model should also be reviewed. We recommend considering doing each of the following:

  • Use financial model auditing software. There are many packages offered by third party providers which can be very helpful in identifying errors or inconsistencies in a financial model.
  • Perform reasonableness and sensitivity checks on the outputs, to see whether the figures being generated are reasonable. This may also involve graphing data to ensure the fluctuations between periods make sense.
  • Where possible the model should be reviewed by a person or people not involved in its preparation

The above is a selection of tips and certainly not exhaustive, but we consider they are great place to start when preparing financial models.

Should you require assistance with a financial model either in preparing it or checking to make sure it works, PKF Melbourne is available to assist. Please get in touch with an expert with any queries.