News update 2003-03: March 2003
===================
A monthly newsletter on risk management in financial services,
operational risk and user-developed software from Louise Pryor
(http://www.louisepryor.com). Comments and feedback to
news-admin@louisepryor.com. Subscribe by sending an email to
news-subscribe@louisepryor.com. Unsubscribe by sending an email to
news-unsubscribe@louisepryor.com. Newsletter archived at
http://www.louisepryor.com/newsArchive.do.
In this issue:
1. Modelling problem
2. Spreadsheets: why test?
3. FSA update
4. Software versions
5. Newsletter information
===============
1. Modelling problem
On 6th March 2003 Provident Financial Group of Cincinnati announced
a restatement of its results for the five financial years from 1997
to 2002. Between 1997 and 1999 Provident created nine pools of car
leases. Part of the financial restatement was because the leases
were treated off balance sheet, rather than on balance sheet as was
later thought to be appropriate. But there was also a significant
restatement of earnings, because there was a mistake in the model
that calculated the debt amortisation for the leases. It appears
that the analysts who built the model used for the first pool “put
in the wrong value, and they didn’t accrue enough interest expense
over the deal term. The first model that was put together had the
problem, and that got carried through the other eight,” according
to the Chief Financial Officer, who also went on to say that he did
not think other banks had made similar errors. “We made such a
unique mistake here that I think it’s unlikely.”
It appears that the error was found when Provident introduced a new
financial model that was tested against the original, and that the
two models produced different results. They then went back and
looked at the original model to see which one was correct. We don’t
know that these were spreadsheet models, but it’s entirely
possible. And the lack of testing may have led to earned income
being overstated by $70 million over five years. Provident also
faces a class action suit from investors.
If I am right, and the erroneous model was a spreadsheet (and from
the fact that those who built it were referred as “analysts” rather
than “programmers” or “developers” some sort of user-developed
software seems likely), this is a classic example of a spreadsheet
being built as a one-off and then reused without adequate
controls. Later pools must have used a different spreadsheet, as
they were not subject to the same restatement.
The CFO has more confidence than I do in the ability of other banks
to avoid similar errors.
See http://cappigun.notlong.com for the press release from
Provident, and http://www.cincypost.com/2003/03/12/prov031203.html
and http://nitcrish.notlong.com for press coverage from the
Cincinnati Post and New York Times.
===============
2. Spreadsheets: why test?
You should test your spreadsheets because they may contain errors
(see item 1). People fail to test their spreadsheets because they
underestimate the benefits of doing so: mainly, they simply don’t
know how many spreadsheets contain errors. Think about it for a
moment. Do 10% of spreadsheets contain errors? Or 20% (for the
pessimists among you)? These rates are high, and should be enough
to make alarm bells ring, but the actual rates are probably far
higher.
A few years ago Professor Ray Panko, at the University of Hawaii,
pulled together the available evidence from field audits of
spreadsheets. Of the 54 spreadsheets that were audited, 49 had
errors. That’s an error rate of 91%.
Other studies show that the error rate per cell is between 0.38%
and 21%. These results are difficult to interpret: are they
percentages of all cells, cells containing formulae, or unique
formulae? (If a formula is copied down a row or column, it may
count as many formula cells, but is only one unique formula). If we
assume a rate of 1% of unique formulae having errors, and look at
spreadsheets containing from 150 to 350 unique formulae (this is a
fairly typical size in my experience), we find that the probability
of an individual spreadsheet containing an error is between 78% and
97%.
To make matters worse, people tend to overestimate their own
capabilities. Panko describes an experiment in which people were
asked to say whether they thought spreadsheets that they had
developed contained errors. On the basis on their responses, about
18% of the spreadsheets would have been wrong; the true figure was
81%. The actuary who told me “As far as I am concerned, none of my
spreadsheets has ever had a bug in it” was probably deluding
himself.
There’s a great deal of misplaced confidence in the accuracy of
spreadsheets. Another actuary who recently said “Of course, in a 1%
world we can’t afford to test our spreadsheets properly” must have
missed a word out. He should have said that they couldn’t afford
*not* to test their spreadsheets.
Panko’s web page at http://panko.cba.hawaii.edu/ssr/ has loads of
interesting information about spreadsheets, including the error
rates described above. There is further discussion at
http://www.louisepryor.com/showTopic.do?code=errorRates.
===============
3. FSA update
Well, we’re certainly living in interesting times and they are
every bit as interesting in the financial world as elsewhere. The
FSA must be hoping for a bit of boredom, just like the rest of us.
On 26th February Howard Davies gave a speech on “Managing Financial
Crises”. In what might seem a surprising view, he took the line
that we are not in one at the moment. However, his definition of a
crisis is quite specific, and, luckily, does not apply to the
current situation. The speech is at
http://www.fsa.gov.uk/pubs/speeches/sp115.html.
As the markets remain turbulent, but mainly in a downward
direction, we’ve had more details on the flexibility provided by
waivers of the rules for with profits life insurance, in a letter
to CEOs of life companies
(http://www.fsa.gov.uk/pubs/other/ceo_letter_wp.pdf). The letter
contains some general guidance, which was issued without the normal
consultation period because there was a worry that any delay would
not be in the interests of consumers. As every financial
commentator in the land has explained, often more than once, the
problem is that adhering to the letter of the solvency requirements
may force life companies to sell equities into a falling market,
thus both reinforcing the downward direction of the market and,
possibly, going against the most appropriate investment strategy
for the life office. Is this another instance of targets distorting
the quality they are trying to measure?
The feedback on CP142, Operational risk systems and controls is
just out. There are no significant changes as a result of the
feedback.
New consultation and discussion papers out this month:
—————————————————–
CP172 Electronic money: Perimeter guidance
CP173 Amendments to the Interim Prudential sourcebook for
Investment Businesses chapter 5 rules on consolidated
supervision
CP174 Prudential and other requirements for mortgage firms and
insurance intermediaries
CP175 Miscellaneous amendments to the Handbook (No. 7)
DP21 Implementation of the Distance Marketing Directive
Feedback published this month:
—————————–
CP142 Operational risk systems and controls
Current consultations, with dates by which responses should be
received by the FSA, are listed at
http://www.fsa.gov.uk/pubs/2_consultations.html
===============
4. Software versions
I only narrowly averted disaster last week. I was giving a talk,
and had been told that “Powerpoint is standard, from diskette.” So
that was OK. But wait! What version of PowerPoint? It turned out to
be 97, and I had prepared my talk in 2002. Most of the animations
didn’t work, to such an extent that some vital information simply
didn’t appear. Luckily, I discovered this in the comfort of my own
home, using a PowerPoint97 viewer, instead of in front of an eager
audience.
This is actually a big problem with Microsoft Office products. One
of the reasons that they are so widely used is that they are widely
used, and seen as the standard. Unfortunately, although they are
backwards compatible they are not forwards compatible (not
surprising, really). When you buy a new copy you have to buy the
latest version (if you are an individual; you have more choice if
you are a volume licensee); and it’s quite likely that people you
are trying to be compatible with have an older version.
I don’t have any hard evidence, but it seems to me that Office 97
is very widely used, in spite of the fact that there are two more
recent releases (2000, and 2002 aka XP). This means that there is
no effective standard: people with recent versions may use features
that aren’t available in 97.
Maybe the feature bloat of which Microsoft is often accused has
even worse effects than we thought. Office 97 clearly has enough
features for many people, and the extra features in later versions
are worse than useless as they are positively harmful if you want
compatibility.
===============
5. Newsletter information
This newsletter is issued approximately monthly by Louise Pryor
(http://www.louisepryor.com). Copyright (c) Louise Pryor 2003. You
may distribute it in whole or in part as long as this notice is
included. To subscribe, email news-subscribe@louisepryor.com. To
unsubscribe, email news-unsubscribe@louisepryor.com. All comments,
feedback and other queries to news-admin@louisepryor.com. Archives
at http://www.louisepryor.com/newsArchive.do.