You’ve spent weeks and weeks building the financial model, getting the data inputs and bringing it all together. You sit back and look at what the numbers are telling you, but something doesn’t seem quite right.

Financial ModelGetting to this stage with a financial model spreadsheet, and then having to go back and re-work it, can be incredibly frustrating.

Done well, spreadsheets offer a tried and trusted way to develop a financial model. But as with any manual process, the potential for errors is reasonably high which can present serious risks for those who will rely on the results for decision making.

Particularly in a COVID world, the ability to make rapid decisions using trusted data is essential. This puts finance teams under increased pressure to deliver accurate reports using model outputs.  

Having worked with many financial departments to uncover inconsistencies and develop best practice financial model templates, here are our top four tips to help your department reduce the risks with financial model spreadsheets…

Tip 1: Build it right the first time

When you start a financial model spreadsheet in a hurry, you often find that you need to go back and make changes later on – and this significantly increases the risk of errors.

Scope the project thoroughly from the start, including identifying:

  • key drivers
  • the data inputs required
  • who you need to consult with
  • how the data should flow
  • what you will want to flex
  • the desired outputs

By doing this, you greatly reduce the chances that you’ll make it halfway through before having to go back, change the structure, and re-check every formula.

Tip 2: Follow a quality model development methodology

building spreadsheetsWhen your spreadsheets are constantly being developed from scratch, people don’t know what to expect. This increases the risk of errors because there are no standards to follow.

The same applies to the quality of your inputs – garbage in leads to garbage out – so it’s essential to have rules around how to confirm the quality of data that makes it into your spreadsheets.

To reduce these risks:

  1. Create a robust model template
  2. Use consistent formatting across all models
  3. Develop procedures around confirming the validity of data
  4. Clearly identify which fields should change (or restrict the ability to do so)

With an established best practice, users will find it easier to create, follow, and interpret the models.

Tip 3: Simplicity is key

Complex spreadsheets with lots of hidden features are difficult to follow, and even more difficult to identify errors.

On the flip side, simplicity makes it easy for people to understand the “story” that the model is telling. Nobody should need to spend hours deciphering formulas or making connections between data inputs.

Take whatever steps are necessary to simplify your spreadsheets as much as possible, such as:

  • building spreadsheetshaving a clear model flow so calculations step through from top to bottom
  • keeping formulas simple (even if you break them into multiple lines)
  • having clearly identified inputs on a separate tab
  • keeping all fields visible
  • using colour coding
  • creating a simple dashboard for users
  • following a best-practice set up

This way, it doesn’t matter if the staff who develop the model need to go on leave or transfer to another area – others will be able to pick up and run with it easily.

Tip 4: Build in error checks

Error checks are essential to ensure a financial model is running all the sums correctly.

Ideally, you want to build error checks into your models so you can see straight away if something is incorrect.

To enhance this, we also use tools that integrate with excel and review all formulas for consistency.   

Get an expert independent review of your spreadsheet models

Often it’s difficult to spot errors or inconsistencies when you have been working on your financial model for a long time. Fresh eyes from an experienced third party offer a valuable way to mitigate against the risk of failing to pick up major miscalculations.

HOW CAN RSM HELP?

At RSM, we have worked with many finance departments to develop best practice spreadsheet models and to provide an independent review of existing models. We can also review inputs to ensure a model provides the level of flexibility and robustness your business partners may need to support decision making.

To find out how we can help you improve the accuracy of your financial model spreadsheet, contact your local RSM transaction specialist today.