Last month saw Microsoft Excel celebrate its 30th birthday and to commemorate the iconic milestone, the team at Operis released a new eBook examining three decades of the world’s most useful program.
The distinction between Excel skills and spreadsheet modelling skills has been known for many years and there are a number of commercial courses now available which teach financial modelling techniques. But training doesn’t protect the individual – or the firm – from embarrassing and expensive errors. The key to model assurance doesn’t lie in adherence to a methodology or set of standards as some spreadsheet modellers suggest; the solution is rooted in a simple, common-sense approach that modellers and their colleagues can use to prevent future disasters taking place.
From the early days of single-sheet models through to the cloud technology of the current Excel, the underlying principles of good modelling practice have remained largely unchanged. Here are four ways modellers can minimise the chances of mistakes and the consequences they often bring.
1. Maintain a defensive mindset
The best way to minimise mistakes in the spreadsheet building process is to accept the fact that there will be mistakes in the first place. The recognition and acceptance of human fallibility often results in a more concentrated and positive effort to find errors and eliminate them.
Once you’ve accepted that errors will be made, you can work at introducing processes to catch them (the simplest being checksums and crosschecks). We work to the maxim that anything that can be tested, should be tested.
It is vital that an environment of quality assurance exists outside of the model development activity – if all people concerned, including managers, are aware of the necessity of checking the model the whole process is made simpler and becomes more co-operative rather than confrontational.
2. Communicate effectively
With the exception of derivative pricing, most finance is relatively simple and can be boiled down to the following two concepts:
- A business earns some revenue, out of which it has to meet various costs
- A financial instrument promises various benefits in exchange for an initial purchase price
Contrary to an apparently common belief, the models that describe these processes do not need to be complicated. They should tell the story of the transaction or proposal in a way that all readers can understand what is happening. Clear presentation is also critical for making sure that everything is communicated effectively and nothing is missed. Are the key results presented on a single page? Does your model have a flowchart to show how the assumptions, calculations and results fit together?
3. Take charge
The ownership of specific spreadsheet models is often unclear, so when things go wrong it can be hard to take action. Lessons should be drawn from the public sector with the identification of a “senior responsible owner” (SRO).
The civil service SRO works with lawyers, accountants, bankers, advisers, investors, operations management, suppliers and contractors, local and national authorities and regulators, all of whom need the model to be as accurate as can be. The SRO must be completely satisfied that the modelling function is working to the highest professional standards and that the model is fit for purpose.
Whatever the name given to the model owner, it should be a rôle separate to, and acting above, the modelling team.
4. Utilise experts
The ultimate level of model assurance is provided by the formal model audit. This has developed into a distinct discipline, practiced by a few specialist firms and a handful of international accounting companies. The best will back up their audit opinion letters with professional indemnity insurance.
Not all the points of practice above are relevant to every situation and model, and there is no definitive method that works in all circumstances. But all Excel models developed in the coming decades will benefit if contributing parties accept the inevitability of human fallibility – and promote accountability, simplicity and quality assurance.
Download your free copy of our eBook here. Call the Operis team on +44 207 562 0400 if you would like any more information.
