10th Jul 18

BLOG: How to avoid the top 4 regrets of Financial Modelling

By Ian Bennett
Partner, PwC

Ian Bennett leads PwC Australia’s Deals Modelling team and has more than 17 years’ experience as a professional financial modeller.


At PwC Deals Modelling we have built and reviewed many transaction models, and along the way we’ve learnt the best and the worst of our profession.

At its best the transaction model:

  • Is a piece of art that is eagerly awaited and seamlessly interacted with
  • Is a single source of truth creating a common platform for all stakeholders and
  • If you own the model you own the deal

Creating the best requires discipline, a focus on the story the model should tell and a strict adherence to modelling best practices.

But let’s explore what happens when modelling goes bad. Here are our top 4 Financial Modelling lessons learned.

1. First Testimony, circular references

The usual suspect! It goes like this: “Here is the model we used for the last deal, we know it’s working, we like the look and feel of it, and you should use it on the next deal. The model has a neat circular reference to calculate interest on cash balances accurately”. It’s like having a tiger as a pet. Many late nights come from accepting the need for a dangerous circular reference. Only too late do you realise that the model is full of an unknown number of them that have been unintentionally created (because excel will only warn you the first time you add one, and from then on it just accepts them whether they’re intentional or not). Hours are lost trying to trace each circular reference, and then redo/rethink the logic for these calculations. And let’s not talk about the circular-ref-filled model where each time it calculates it gives a different answer! And never be tempted by a circular reference’s promises of great accuracy – 99% of the time it’s either immaterial or materially incorrect.

Takeaway: Never accept a circular reference in your model.

2. Second Testimony, simplistic working capital

Day 1: “It’s too easy; just use 45 creditor days and 40 debtor days because that’s our terms”. That’s the working capital discussion. Too late you realise the numbers don’t reconcile and the cash movement doesn’t make sense. What follows are manic hours wrestling to back calculate the right relationships and ratios to get meaningful working capital balances; especially for that tricky first forecast period. Supplier/customer terms aren’t meaningful in a model as they ignore aspects like the special terms for the major suppliers and clients, seasonality of payments, GST, export/import seasonality, tax etc. Always seek to understand the dynamics of the balances as they translate to cash, and back calculate ratios to get balances that make sense. The insight comes from how those ratios move over time or with seasonality.

Takeaway: A monthly working capital forecast is never simple. Apply the mindset of using a meaningful relationship, don’t rely on supplier/customer terms.

3. Third Testimony, feeling the need to use all of the financial data

For some reason we convince ourselves that if the data has been provided, then we should start the model there. Often your inputs are 80% of the model. But without due consideration you can lose track of the story that you want to tell with the model, and the questions the model must answer. Putting sensitivities into such a detailed model is a nightmare. It’s a calculator and an aggregator, but it’s not a dynamic model.

Takeaway: Start by considering the outputs and sensitivities, and do not include data that is unnecessary.

4. Fourth Testimony, no balance sheet

“It’s just an EBITDA and cash model – we don’t need a balance sheet”. The value of a balance sheet isn’t the balances or the accounting, it’s the sense check. Even the most simple balance that you double entry profit & loss and cash flow through, will serve as your smoke alarm as you model the rest of the financials. That smoke alarm can save your life. It will tell you if you got numbers positive not negative; if your sensitivities go the right way; if you’ve included all of your cash flow lines; if your depreciation is working; and if your movement in working capital is correct. Put it in at the start, make sure it balances and keep that light green for the rest of the transaction. And don’t fudge it!

Takeaway: Always include a simplified balance sheet in the model, it could save your life….


Look out for more tips on professional modelling in the next AVCAL newsletter.