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.
Risks in transaction financial models
The top four regrets of financial modelling which we shared in our previous article, began a heated discussion about how easy it is for experienced modellers to get themselves into trouble. Even those with investment bank training can find themselves without experienced support, using overly complex formulae or risky methodologies under time-pressure, or losing confidence in their model with many changes over time, sometimes by several users.
The key to low risk, high quality financial models is transparency, consistency and simplicity; a great conversation with all stakeholders before you rush into building the model; and adherence to consistent modelling best practices.
You must also be alert for the most common errors in transaction models. Performing 100’s of model reviews each year, we see a lot; here’s our top 5.
- P&L items and inconsistent sign convention
Revenues and costs are positive in the inputs, but costs are assumed to be negative in the calculations or outputs, and so costs incorrectly increase revenue. Particularly troublesome when certain cost lines are being pulled into working capital calculations. Pick a sign convention, clearly document this, and stick to it.
- Inconsistent timelines
If you mix two different timelines in the same worksheet all manner of errors will likely follow. For example, monthly periodicity stops at column Z and from column AA onwards it changes to quarterly periodicity. This recipe for disaster makes importing data difficult, and requires significant formula gymnastics throughout the model, which invariably leads to formula errors. Keep different timeline calculations on different worksheets.
- Hardcoding or hardwiring
It is all too common during time-pressured updates to create or test a specific last-minute sensitivity by hardcoding over a formula, then forgetting to correct/remove it. Similarly hardwiring where a value is included within a formula repeatedly instead of stating it in a separate input and referencing that stated assumption. Assumptions should be in defined input cells.
- Debt tranches not fully repaid
This is another surprisingly common error which is easily checked by looking at the behaviour of the single debt tranche over the life of the model and beyond (just extend the calculation for the purposes of the test). It’s also quite common to see a debt tranche continue to repay, even after it’s been refinanced. Sensitise your debt inputs and check the overall structure holds.
- Real vs. Nominal
No other aspect of a financial model introduces more errors once introduced. Prevalent in certain industries, even if well labelled in the inputs, it’s very easy to mix up real and nominal numbers and produce errors. Further, errors occur commonly when CPI can be sensitised to vary quarter/year on quarter/year, and yet parts of the model assume that it compounds at the same rate for all periods. Labelling of real and nominal must be ruthlessly applied.
Minimising risk is too important
Simple model errors can result in incorrect decisions costing millions of dollars. History is littered with financial losses due to spreadsheet errors. EuSpRIG have a website of horror stories that will chill your bones after just a quick glance. Many of them are transaction models which in a pressured timeline become the riskiest environments to introducing errors. In our experience, every transaction financial model has at least one formula error (which may or may not impact on the model outputs).
To minimise the risk, you have to have it reviewed. Academics have proven many times that humans are very poor at spotting their own errors in spreadsheets. Instead, you need fresh eyes to look at your model, and those eyes could be your mate, your boss or someone external. At the very least they need to be independent, and ideally they’re experienced in reviewing models and have a practised methodology.
New Review Services
It may be that you don’t need the formal model audit report as an output, (often only requested by a lender) but in the past you may have struggled to find a model review scope that is commensurate with the risk profile because your only choices were a full model audit with many iterations or perhaps just removing some worksheets from scope.
That’s why we’ve developed PwC’s Model Analyser service, which is perfectly designed to provide comfort to various stakeholders over the key model calculations and outputs, summarising the key model risks, assessing how it stacks-up against modelling best-practice and providing advice on how to make the model sustainable through the transaction. And all at a lower fee.
PwC’s Model Analyser is a two-stage technology-enabled model review that uses our bespoke technology to analyse your model’s structure and contents and report on its risk and complexity KPIs. This analysis, as well as discussions with key stakeholders is used to design a targeted review approach prioritising the high-risk and high-complexity sections of the model. We report the model’s strengths and risks, and any issues we identify, which will support your business in providing information to your stakeholders such as management and lenders. Due to focused coverage and being less resource heavy, our conclusions can be received faster and with lower fees.
Better transaction models
In summary:
- Not be complacent about our abilities
- Be wary of the Top 5 common errors
- Accept you can’t spot your own errors and ask someone else to check it
- Choose a model review service that’s commensurate with your risk
- Be a deal hero with your amazing financial models
AVCAL, in partnership with PwC will be hosting a Financial Modelling Workshop on 13th and 14th November, for further details and registration please click here.