Review techniques

Anyone who has been through a financial model audit knows that it can, if not well managed, be a stressful experience with a lot of pressure on the modeller who has built the financial model. Finding a significant spreadsheet error at a late stage in a transaction can result not only in embarrassment for the modeller, but also in large additional legal costs for the consortium or the bidder/buyer.

Can we decrease model risk by finding spreadsheet errors earlier?

I spend a lot of time thinking about ways of minimizing model risk and how to automate the verification of the validity of a financial model or a spreadsheet. A blog entry by Nick Crawley at Navigator (5 reasons Google will replace Microsoft Excel) got me started thinking of a new way of validating financial models in real time, at a fraction of the cost.

Online on-demand shadow modellers

My idea is based on using the lower cost of labour in some countries combined with the salary discount many people would accept for being able to work from home or from a pleasant location (such as their cottage in the country or beach house). One could imagine a team of “shadow modellers” (ex bankers, analysts and modellers) who continuously review all the work performed on a centralised spreadsheet platform, like Google Spreadsheets. These Shadow Modellers could be placed anywhere in the world geographically, and being in a different time zone is even an advantage. They don’t even need to be employed by the company but could be an ‘on demand’ service which you simply use on an as needed basis.

Benefits of a centralised on-line spreadsheet platform

Today, using corporate networks and Excel files this approach would be practically impossible for many reasons but shifting spreadsheets to a centralised platform would makes this work a lot more efficient. One could even have a financial model reviewed while it is under development, a functionality that is built in natively in Excel but doesn’t work in practice for more complex spreadsheets (because of file corruption and data loss).

To be fair, I think Google Spreadsheets has some way to go before it can fully replace Excel in the corporate space but with the pace it is developing I certainly wouldn’t bank on Microsoft dominating the spreadsheet space in ten years from now. Google are working on new functionality including Macros, Drawings and Solver so we are definitely getting there.

Would this approach replace a traditional financial model audit?

I don’t really see this as a replacement for a formal financial model audit in its traditional sense. However, I think the centralised review approach could have applications for teams who lack the capacity or resources to perform peer reviews of analysis before internal decisions are executed. Maybe this could simply be seen as a structured peer-review framework, but at a lower cost than having two people from the same team performing it, or as a quality insurance for smaller teams with only one analyst.

Operis, the developer of the spreadsheet review software OAK (Operis Analysit Toolkit) recently announced a new version OAK 4.00.

Graphically Operis has taken on the huge task of bring OAK into the ribbon-style interface of Excel 2007 and Windows Vista. Graphical updates are of course necessary to keep a software up to date but with the lack of uptake of Excel 2007 and Vista by the wider financial modelling community it doesn’t excite me nearly as much as the more technical improvements. We are yet to analyse the full impact of the new OAK functionality, but the promises are definitely very positive:

• Complete conversion from VBA to C#, which speeds some functions up one thousand fold
• Full compatibility with Excel 2007 and Vista, including ribbon interface and large spreadsheets (which, of course, make the performance all the more valuable)
• More capable setup utilities, capable of diagnosing and overcoming many obstructions to installation in the more locked down environments typical of banks
• Innovative and technically ambitious commands that seek to clarify the intent of complicated calculations, going much beyond simple precedent tracing
• Various enhancements to the already market-leading comparison functionality
• Exposure of all the functionality to COM, so that it can be driven by VBA and similar programming environments, allowing favourite review processes to be automated

OAK (Operis Analysis Toolkit) v 4.00

Updates from David Colver (Joint Chief Executive) indicate that Operis are in great shape to handle the continuous demand for PPP/PFI services in the UK and I would like to believe that the new version of OAK will make them even stronger. Delivering a big software update in the middle of a financial crisis is a very positive signal from Operis, so good on them for pulling this off when many other companies are scaling back investments to manage cash flow.

Not all financial models need to be formally audited or reviewed by an independent party. In some situations the modeler or the company about to rely on the model simply wants to increase their confidence in the financial model. In these situations, there are several alternatives that may be more appropriate than a full formal model audit.

Model review vs model audit

The fees associated with a formal financial model audit are partly due to the extremely structured process involved in a financial model audit. By relaxing certain aspects of this process can achieve the same amount of work for a significantly lower fee.
An efficient approach to model review will differ to a model audit in the following ways

• Before first iteration the client reviews and amends for inconsistencies in maps
• Only one or two iterations
• Cell-by-cell review only performed on selected parts of the model
• Documentation limited to only errors and significant risks
• High level analytical check performed
• No opinion letter issued

This approach can typically be performed at a total cost of 20-40% of a formal financial model audit.

1. Generate consistency maps

If you do not have access to a model review add-in package then ask your model audit company to generate consistency maps for you. This is a very quick process and is the first step in reviewing a model for inconsistencies. You can then work through the outputs yourself to save money on external consultants.

2. Ask a colleague to review your model

This is the oldest trick in the book, but still works a charm. In the same way it is very hard to find your own spelling mistakes, it is very hard to find your own errors in a financial model. One very simple way of quickly improving your model is to ask a colleague (ideally someone who doesn’t really like you and who wants to find errors…) to review your model.

3. High level analytical review

One very efficient approach of finding errors in financial analysis, and one constantly applied by credit committees all over the world, is to print a summary sheet and review it with a calculator. Ideally you should be able to calculate the NPV of your project based on key assumptions using a calculator in half an hour. If something doesn’t look right, assume that it isn’t and get to the bottom of it before someone else does - or worse - you don’t find the error and make a bad investment based on you analysis.

4. Prepare a ‘super-summary’

To facilitate a high-level analytical review it can be useful to prepare a ‘super-summary’. This is typically comprised of no more than five to seven lines of annual data - no more! This can then be used to calculate annual metrics useful for sense checking. A ‘super-summary’ can be used for the following purposes:

• Annual average prices overview
• Debt repayment analysis
• Annual trend analysis - revenue, costs, capex, tax, etc
• Annual profit margins analysis

If you are not in a position to engage an independent model audit company to perform an independent financial model review then the following check-list may be helpful. I do not attempt to delve into high level analytical checks but rather strictly focus on algebraic and structural errors that can be easily detected and therefore avoided.

Inconsistent formulae across rows

A solid financial model should have the formulae copied across all columns. Not having consistent formulae significantly increases the risk of error in your model. Even though on face value the model may be right, the inconsistent formula makes it very hard for anyone else to update assumptions or amend the model.

The most efficient way of detecting inconsistent formulae across rows is to purchase a financial model review add-in. There are a number of different products on the market and they all have their strengths and weaknesses. I would recommend downloading a free trial before purchase to make sure that that particular add-in works for you.

If you don’t want to purchase a model review add-in then you can explore the powerful, but often overlooked, built-in Excel functions

• ‘Trace all dependents’ (CTRL + SHIFT + ])
• Trace all precedents (CTRL + SHIFT + [)
• Row or Column Differences (CTRL + SHIFT + \) and (CTRL + \)

These functions are also accessible via F5 (Goto) → Special.

Complex formulae that are hard to understand

Overly complex formulae with nested IF, OFFSET, INDEX, MMULTS, ISERROR, etc, and other functions, etc, significantly increases the risk of an error in your model. This can be easily avoided by taking advantage of the many rows available splitting the formula into smaller, manageable parts.

The following is an example of what not to do:

=IF(G$4<=OpStartDate,0,SUMIF(Operations!$D$4:$AO$4,EOMONTH(G$4,IF(MOD(MONTH(G$4),3)=0,0,3-MOD(MONTH(G$4),3))),Operations!$D163:$AO163))/3

Check for unused assumptions

One very simple check to perform is to make sure that all assumptions in your assumption sheet have been used in calculations. If there are unused assumptions then there is an increased probability that you have mistakenly linked in an incorrect assumptions to flow into the model calculations.
To perform this test you could either use a spreadsheet review add-in or Excel’s built-in tracing functionality.
Sums that exclude lines that should have been included

It may sound unlikely, but one of the most common errors is SUM-functions that do not sum the right rows. Simply work through your SUM-functions to make sure that they have been coded correctly.

Review timing errors

A very efficient way of finding errors in your timing section is to simply change the timing assumptions and check it against changes in graphical representations of the model. Any unexpected spikes, troughs or unreasonable movements in outputs should be investigated. Experience has shown that Timing is a common source of errors and it is recommended that close attention to be paid to model Timing.

Mix of assumptions and calculations

A good model should have all assumptions located in a dedicated section of the model separate to the calculations. Financial model review software will have the functionality to distinguish inputs as opposed to calculations, but you can always use F5 → Special → Formulas → Numbers if you do not have one.