Project financing spreadsheets

In 2004 a paper on "Financial Modelling of Project Financing Transactions" was presented to the Institute of Actuaries of Australia Financial Services Forum . It's well worth a read if you are involved in any sort of financial modelling, whether or not you are an actuary and whether or not you are modelling project financing.

The paper includes an analysis of the risks of models and some ideas for managing the risks, gives a clear introduction to Monte Carlo simulation and why you might use it, and has a section on why actuaries might be good people for modelling project financing.

It also includes some statistics on the error rates the authors have found in spreadsheet models of project financing. The authors say “Research has shown that error rates in project financing models can be as high as 10%. Section 5 of this paper provides some statistics on error rates collected by Mercer Finance & Risk Consulting. Out of the thirty highest value projects reviewed during the 2004 financial year, nine (that is, 30%) exceeded the 10% threshold; four exceeded the 15% threshold; and one exceeded the 20% threshold.”

The wording may be misleading here. They are not saying that, for example, 10% of the models have errors. In fact, all the models (100%) that they reviewed contained errors. They are saying that in four of the models they reviewed over 15% of the unique spreadsheet formulae contained errors, and that one model had errors in over one in five of the formulae. This model was one of the smaller ones, too, so it's no use saying “it's only a small model, so it'll be OK”.

Although the spreadsheets they reviewed were all modelling project financing, there is absolutely no reason to suppose that the high error rates are peculiar to the project finance field. Financial models of any sort are complex, and it's hard (but not impossible) to write a spreadsheet that doesn't contain errors.

So let me say, once again, that it's important to get the process right when developing financial models (whether using a spreadsheet or specialist modelling software). Be clear what it is that you want the model to do: write a specification that is detailed enough to test against. Use appropriate techniques when building the model: something that looks like a really cool way of doing things may be difficult for other people to understand. Document the design decisions you make. Use a good change control process to keep track of what's going on. Test the implementation against your specification. Record the tests, so that other people have some reason to believe you when you say the system has been tested. And, above all, don't trust yourself. You are bound to make mistakes in the coding, and if you don't look for them you won't find them.

Resources

Financial Modelling of Project Financing Transactions

A paper that was presented to the Institute of Actuaries of Australia Financial Services Forum, containing interesting statistics on spreadsheet error rates. It's available at http://www.actuaries.asn.au/PublicSite/pdf/fsfpaper2004-jasminandlee.pdf.

Ray Panko

Professor Panko, who is at the University of Hawaii, has done a great deal of research into spreadsheet errors. His page at http://panko.cba.hawaii.edu/ssr/ has many useful resources, including links to many of his papers.

European Spreadsheet Risks Interest Group

This special interest group provides a forum for research on spreadsheets. Its website is at http://www.eusprig.org and includes an excellent collection of spreadsheet horror stories. EuSpRIG organises an annual conference on spreadsheet risks.

Is this spreadsheet a tax evader?

This paper, by Ray Butler of H.M. Customs and Excise, summarises the audit experience, describes the methodology and outlines the results to date of a campaign of spreadsheet testing started in July of 1999. Of the seven spreadsheets selected for audit, six contained significant errors. It was published in the proceedings of the 33rd Hawaii International Conference on System Sciences in 2000, and is available at http://www.eusprig.org/hicss33-butler-evader.pdf.

HM Customs and Excise

Somewhat unexpectedly, some of the most interesting and useful work on spreadsheet errors and auditing is currently coming out of HM Customs and Excise. They have developed some software (SpACE) that they are now making available to businesses. Of particular interest is their document on Methodology for the Audit of Spreadsheet Models.