Home /  News /  Top 10 Tips For Financial Modelling

Top 10 Tips For Financial Modelling

In a complex world, it pays to have a clear and easy to understand financial model for your business. Baker Tilly Staples Rodway’s financial modelling team share ten handy tips on how to build a usable and value-adding model for your business.

 

1. The experts use excel

Every year in a different city around the world, the ModelOff! Financial Modelling World Championship is held. Challengers from around the world compete to show off their Excel skills. That’s right – the world’s top modellers still use Excel and so should you. Some add-ins such as Modano can make constructing a model easier, but the clarity Excel offers and the ability to follow through formulas still makes it the best base tool for building any financial model.

excel symbol

2. Clearly show your inputs and assumptions

Show your inputs on a separate tab to your outputs and use consistent colour schemes for assumptions and data inputs. Also, don’t mix hard inputs with formulas in the same cell as it can obscure what is driving the model and can make it hard to spot errors. Using one assumption per cell makes it much easier for a user to review and tweak. It also helps to have text explaining each assumption to help reviewers sense check to reality.

glasses symbol

3. Simplicity is key

100 input assumptions? Good luck with understanding what is driving your business! It is best practice to have a few key assumptions, for example, the rate of customer growth and price increases, that you can review and test the sensitivity of outputs.

key symbol

4. Make it a 3-way

The best models show the relationship on a periodic basis between the income statement, balance sheet and cash flow statement. A useful model is one that tells the whole story of your business from all angles.

laptop

5. Have a 'live' workbook

Every month you will have new management account actual data, and you should be able to insert this into your model seamlessly. You can then show comparisons of actuals versus budgets and forecasts. Also assessing actual trends can help you when you review your assumptions.

 clock symbol

6. Use appropriate periods

If you’re forecasting for 10 years, a monthly model might be overly detailed. Think of your needs and whether a monthly, quarterly or annual model is best for you.

 Calendar symbol

7. What are your key metrics?

Beyond the 3-way statements output, a model can forecast your key ratios and even alert when, say, for example, bank covenants might be breached, or a certain goal is achieved.

Flag symbol

8. Presentation, Presentation, Presentation

Your model should ideally be printable (and scaled to fit), it should be clean and have consistent colouring, fonts, text size, decimal places and alignments. It should be easy to navigate – every tab should have a clear title or use a table of contents with hyperlinks to navigate for larger models. Sloppy presentation does not inspire confidence to those you’re reporting to.

graph symbol

9. Design for audit

As well as the fact your model may be audited at some point, it is helpful to have a model that clearly links assumptions and drivers to the outputs. Doing so makes it easier to spot errors and allows others to ‘pick up’ the model if you need to delegate or move on from your role as the model developer.

Spy glass

10. Error alerts

Balance sheet doesn’t balance? Consolidated balance sheet doesn’t total the same as the separate parts? Chances are when you’re building and updating a model you’ll make mistakes. It pays to have a warning (or a highlighted cell) to show you when something doesn’t add up as it should.

Red light symbol

For guidance talk to your usual Baker Tilly Staples Rodway advisor.

For further advice on modelling, please feel free to contact Tracy Hickman in our Corporate Advisory Team.

Did you find this article interesting? You can see more like it in NUMBERS magazine and with our regular Tax Talk email updates and events. Subscribe today.