Building a financial model that is cost efficient to audit is a lesson that many Analysts learn the hard way when they receive a quote exceeding their annual salary.
In the worst cases this happens only a very short time before bid date / financial close and it is too late to improve the model and you have to accept what appears to be an outrageous fee for what you thought was a pretty good financial model.
What is the biggest cost-driver for a financial model audit?
The fee for a financial model audit is at large a function of the complexity and the number of unique formulas in the model. The complexity is a big topic in itself and this article will only deal with the topic of minimizing the number of unique formulas.
A unique formula in the world of financial model audits is represented by one range of cells (horizontal and/or vertical) that can be copied from left to right, or down the page, and the formula remains intact.
In an ideal world you should be able to copy the first column of your financial model (assuming a horizontal timing structure) and copy to the left and the integrity should be intact. If that is the case you have achieved perfect ‘left-right-consistency’.
How to identify unique formulas in a financial model audit?
Using spreadsheet investigation tools this work is done a lot easier. There are a number of third party tools available on the market:
- Spreadsheet Detection
- Spreadsheet Advantage
- Operis Analysis Toolkit (OAK)
The outputs from these tools vary slightly but one quite useful component is what is called the ‘maps’. Theses maps identify the unique formulas to highlight left-right inconsistencies.
Image 1: Perfect left-right consistency in a financial model
Image 2: Unique formulas (and hard-coding) scatted throughout the financial model
How can you keep the costs down in your next financial model audit?
The best way of keeping costs under control in a financial model audit is to work with your model auditor to bring the price down. I would recommend you to ask them to run the first iteration well in advance of a financial decision so that there is plenty of time to fix up and repair structural issues like hard-coded sections.
Another way is to simply run the maps, or ask your model auditor to do it for you (they should definitely do that for free if you have a good relationship with them) and then work through all rows/columns where there are unique cells but there shouldn’t be any. This will not only save you a lot of money, but more importantly, avoid stress on the Analyst at the last critical stage of the transaction.
The most common discussion before engaging a finanical model auditor is the fee and this article gives some good tips on how to manage the cost. My company recently completed a financial model audit for an infrastructure project and due to the lack of experience of the financial analyst (I wasn’t involved at this stage) and the lack of guidance from the accounting firm resulted in a very large fee and no improvements to the model. I wish my colleague would have read this before the project started.