Category: Notes

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.

Next time you paste some figures into a spreadsheet, be afraid. Be very afraid. Someone at TransAlta Corporation managed to lose $24m (Canadian) through a “clerical error” in pasting into an Excel spreadsheet. The spreadsheet in question was used to submit bids to the New York Independent System Operator (New York ISO) for May 2003 transmission congestion contracts (TCCs). It’s not really important to understand what TCCs are: the crux of the problem is that you put bids in at the end of one month that affect the prices you pay during the next. Once submitted, the bids can’t be retracted. Because of the spreadsheet error, TransAlta ended up with more TCCs than they wanted, and at higher prices than they intended. They didn’t spot the error until they were notified that their bids had been successful.

There are at least two operational failures evident in this story.

First, the whole thing was an accident waiting to happen if pasting numbers into the spreadsheet was part of the normal process of preparing these bids. Manual pasting is notoriously prone to errors. If data has to be transferred between spreadsheets, automatic links (used with appropriate precautions) or automated copying and pasting via macros (ditto) are much safer. If the pasting was unusual, then extra care should have been taken and more thorough testing should have been performed.

Which brings us to the second failure. How come the error wasn’t found before the bids were submitted? The answer has got to be that there was inadequate review and testing. I suspect that there weren’t formal tests, and that the reviewing was not systematic. It isn’t difficult to devise effective reviewing procedures that should pick up this sort of problem (we are told that the error was caused by mismatching bids and prices, which implies that the error was due to misaligning the pasted region).

See the transcript of the investor conference at which the error was explained for more details.

In June 2003 a pasting error was discovered before it was too late. The error came to light in a university examiners meeting: the spreadsheet containing the marks for a small joint degree (3 or 4 students) was found to be wrong. The numbers just didn’t make sense, as the average marks were inconsistent with the range of individual marks. The numbers were recalculated by hand before the end of the examiners meeting, and no harm was done, let alone $24m worth (see the note on TransAlta Corporation).

The problem was caused (like so many others) by copying and pasting: the formulae were correct in the first row, but not in the later rows. History doesn’t relate, but presumably it was a question of relative versus absolute references.

The spreadsheet had actually been checked before the meeting by the Chair of one of the departments involved. Unfortunately, the check had been confined to the first row, which was indeed correct.

There are several morals to this story.

• Be very afraid when copying and pasting
• Don’t rely on checking the formulae, look at overall reasonableness too
• If you’re checking a representative sample of formulae, remember that the first row (or first column) probably isn’t representative.