Our top 5 financial modelling errors, and how they could have been avoided.
All across the land, darkness has settled and people are sleeping safely in their beds. A skulk of foxes play by the light of the full moon and a lone owl swoops down from its perch. Suddenly there is a blood curdling scream...
Oh no! I didn't include row 65 in my total formula and I've overstated profit by $50m!
Roll credits....
We've all been there. Hopefully not with a $50m error, but anyone who has built a financial model in a spreadsheet will have made errors. And at least one of them would have scared you half to death.
If you're reading this wondering what I am talking about then you really do need to worry because you just didn't find them!
Ignorance isn't bliss when it come to spreadsheet errors.
Various studies have concluded that more than 80% of spreadsheets have errors.
Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas. You can read his paper "What we know about spreadsheet errors" here.
Here are our top five financial modelling errors that have made the headlines:
1. JP Morgan hit with $6 billion trading loss
The financial modelling team at JP Morgan created a number of value-at-risk (VaR) financial models in Excel. The models significantly understated the company’s risk and this was noted as a major factor in its $6 billion trading loss that year.
See our full article here.
2. Fannie Mae restate unrealised gains by $1.2 billion
Fannie Mae (the Federal National Mortgage Association) were forced to restate their unrealised gains by $1.2 billion after discovering “honest mistakes" in one of their spreadsheet-based financial models.
See our full article here.
3. TransAlta Corp lose $24m
The financial modellers in TransAlta's bids team made an error in the bids model which led them to win a number of contracts in the New York power market that it did not actually need to win and at prices it had not intended.
See our full article here.
4. Kodak restate due to $11m error
Kodak were forced to restate two quarters of their financial records after discovering that they had overstated severance payments by $11m.
See our full article here.
5. AstraZeneca sees 0.4% drop in share price
Drug maker AstraZeneca inadvertently issued confidential information in a spreadsheet which formed part of its release to analysts.
See our full article here.
How can you avoid making errors in your financial models?
Humans make mistakes.
Humans using spreadsheets for financial modelling make mistakes.
It is just a fact of life. Let's embrace that fact and see how we can manage it.
There are a number of well-proven methods for managing the risks inherent in financial modelling:
1. Reduce the risk of making mistakes in the first place
Adopting a standardised, good practice approach to how you build your financial models is key to reducing the risk of making errors.
Read our article on financial modelling standards here.
2. Follow a rigorous review and testing process
It is with good reason that software engineers allow anything from 20-50% of their project time for testing. The good reason being that they know from experience that humans make errors.
If you work on the basis that you have almost certainly made mistakes in your model then dedicating plenty of time to the review and test phase becomes easy to justify.
3. Make use of technology
Fear not, you're not about to hear me saying that "Excel is dead" or that we should not use Excel to build financial models. But I am a strong believer in using technology to make our lives easier.
Modular financial modelling build tools such as OpenBox and Modano can help you build less risky models in a fraction of the time of conventional builds.
I've been incredibly impressed by the capabilities of Brixx as a non-Excel based financial modelling tool for SME three statement builds. Well worth a look.
And finally, spreadsheet review tools have been around for years but are still well under used. A few of my favourites are OAK, EXChecker, nxt, Arrisca Analyser and PerfectXL.
At Full Stack Modeller, I head up the Tech Stack section of our course, keeping members up to date with the latest developments in financial modelling technology.
Full Stack's Financial Modelling Errors Series
See our complete financial modelling error series here.
