Newsletter Old site

Newsletter Sep 2003

News update 2003-09: September 2003

A monthly newsletter on risk management in financial services,
operational risk and user-developed software from Louise Pryor

Comments and feedback to Please tell me if
you don’t want to be quoted.

Subscribe by sending an email to
Unsubscribe by sending an email to
Newsletter archived at

In this issue:
1. How many spreadsheets are out there?
2. Fraud
3. FSA update
4. Wild blackberries
5. Newsletter information

If you’re in or near Edinburgh you shouldn’t miss the forthcoming
performance of Mahler’s 8th Symphony, the “Symphony of a Thousand”, at
the Usher Hall on Sunday 30th November. It should be quite an
experience: Edinburgh Bach Choir, Edinburgh Royal Choral Union,
Jubilo, Edinburgh Youth Choir, and Sinfonia are joining forces for the
occasion. There won’t be 1000 of us, but there’ll be quite a few!

Tickets from the Usher Hall, 0131 228 1155

1. How many spreadsheets are out there?

How many spreadsheets do you have in your organisation? You might
be surprised. I have had a recent (unconfirmed) report of a major
bank with 250,000 spreadsheets. Someone at another organisation I
visited recently thinks that they have 15,000.

It’s difficult to know how to interpret these numbers: there may be
many duplicates included if there was a simple count of files with
the .xls extension. Some of the spreadsheets might be little more
than one-off doodles used to add a column of figures. But by any
measure there are a lot of them. What we don’t know is how they
compare in volume to “normal” IT systems.

The usual measure of software quantity is “lines of code”.
Microsoft Office is thought to contain about 25 million lines of
code and a system described as “an extremely large corporate
banking legacy system which had been in use for over 10 years” had
4.7 million lines of code.

It’s difficult to come up with an equivalent measure for
spreadsheets, which can include both spreadsheet formulae and
Visual Basic for Applications code. Moreover, “normal” systems
contain code for user interfaces, whereas spreadsheets rely on
their own formatting facilities. However, for a spreadsheet with no
VBA, a measure based on the number of formulae cells or unique
formulae (ie, a column containing the same formula copied down
contains only one unique formula) might be appropriate.

Typically, there are between 20 and 500 formulae cells per unique
formula. It’s difficult to estimate, and varies a lot, but a
spreadsheet that takes up say 3MB on the disk might contain 20,000
– 50,000 formula cells, or say 200 – 1000 unique formulae. This
might be equivalent to 500 – 3,000 lines of code. (This is based on
the spreadsheets I’ve reviewed recently, some of which have coded
calculations in VBA rather than spreadsheet formulae, taken with my
experience of programming in various languages; your mileage may

We can assume an average spreadsheet size of say 1 – 3MB. This
might seem like a large average size, but 6 or 7MB isn’t unusual,
and I’ve come across several in the teens. The largest I’ve heard
of was 100MB.

So, at a rough guess (and it is *very* rough), 250,000 spreadsheets
might be equivalent to 100 million lines of code. On the same
basis, 15,000 spreadsheets would be equivalent to 7.5 million lines
of code.

So what we’ve got here is the equivalent of several large corporate
banking systems floating around the organisation. Of course, all
those spreadsheets have been through rigorous quality assurance
procedures, so we needn’t worry, right?

If you’d like more information about good practices for spreadsheet
development and management, do get in touch by replying to this

2. Fraud

When people are asked to give an example of an operational risk, by
far the most common response is “rogue trader”. In fact, there are
comparatively few of them about, especially in the insurance
industry. Other types of fraud are more frequent and, cumulatively,
give rise to larger losses.

At a recent conference Carol Sergeant, of the FSA, berated the
financial services industry for an inadequate response to the
problem. Apparently many firms do not undertake a thorough analysis
of the actual and potential financial crime risks to which they are
exposed, and are not organised to tackle them effectively.

At the same conference Rosalind Wright, the former director of the
Serious Fraud Office, accused business bosses of concealing the
extent of fraud within and against their companies. She said that
said many financial institutions were too frightened to report
fraud, fearing damage to their reputations. And of course sometimes
it’s the bosses who are perpetrating the fraud in the first place,
so they are doubly unlikely to report it.

The danger here is that firms are losing more money by turning a
blind eye to fraud than they would by trying to prevent it or deal
with it effectively after it has been discovered. An effective
management process for operational risk will cover fraud, so
Sergeant seems to be suggesting that many insurance companies’
processes fall short in this area. In the future, if the proposals
in CP190 and CP195 are adopted, the FSA’s view that a firm is not
doing enough in this area may prove expensive (see below).

Carol Sergeant’s speech is at

Rosalind Wright’s speech is reported at

3. FSA update

Following CP190, setting out proposals on capital requirements for
non life insurers, we now have CP195 for life insurers. Like CP190,
it is full of acronyms. After going through various mathematical
calculations, such as the MCR and ECR (which involve the LTICR and
WPICC) we get to the RCM. However, after all the sums are done, the
acronym that really matters is the ICG, or individual capital
guidance. This will set out the FSA’s view of the capital that is
adequate for the firm’s individual circumstances. The various
mathematical acronyms will be taken into account, but so will other
factors. Section 4.17 says “The more firms are able to demonstrate
that their risk assessment processes capture and quantify all of
the issues in our guidance, then the lower we are likely to assess
their ICG (and vice versa). This provides an incentive for good
risk management.” The overall message from CP195 is the same as
that from CP190: risk management processes matter.

New consultation and discussion papers out this month:

CP195 Enhanced capital requirements and individual capital
assessments for life insurers
CP196 Implementation of the Distance Marketing Directive: proposed
rules and guidance
CP197 Reporting requirements for mortgage, insurance and investment
firms, and supplementary consultation on audit requirements
CP198 Regulatory reporting – a new integrated approach
CP199 Miscellaneous amendments to the Handbook (No. 10)

Feedback published this month:

CP159 Appointed representatives – extending the current
regime. Feedback on CP159 and ‘near final’ rules
CP174 Prudential and other requirements for mortgage firms and
insurance intermediaries – Feedback on CP174 and ‘near
final’ text

Current consultations, with dates by which responses should be
received by the FSA, are listed at

4. Wild blackberries

From Wired (,1294,60052,00.html):
“The eBay ad read ‘BlackBerry RIM sold AS IS!’ So Eugene Sacks (not
his real name), a Seattle computer consultant who always wanted one
of the pager-size devices to check his e-mail, sent in a bid. For
just $15.50, he bought the wireless device with 4 MB of memory. The
BlackBerry didn’t come with a cable, synching station, software or
a manual. But it did come with something even more valuable: a
trove of corporate data.

“After popping a battery into the BlackBerry’s back panel, Sacks
discovered a few things the previous owner wouldn’t have wanted him
to see — more than 200 internal company e-mails from financial
services firm Morgan Stanley and a database of more than 1,000
names, job titles (from vice presidents to managing directors),
e-mail addresses and phone numbers (some of them home numbers) for
Morgan Stanley executives worldwide.”

And then we hear that some ridiculous number of government laptops
go AWOL: “at least 60 of the 200 MoD and government laptops lost or
stolen will have contained sensitive information.”
(Report at

Meanwhile, in Australia, a couple of people just walked into a high
security computer facility and made off with two computers on a
trolley. The report says “The brazen theft has prompted Australia’s
top security agencies to conduct emergency damage audits amid fears
that terrorists may have gained access to highly sensitive
intelligence from the computers.”

We sometimes forget that sensitive information on a computer can
physically get into the wrong hands.

5. Newsletter information

This newsletter is issued approximately monthly by Louise Pryor
( Copyright (c) Louise Pryor 2003. All
rights reserved. You may distribute it in whole or in part as long
as this notice is included. To subscribe, email To unsubscribe, email All comments, feedback and other
queries to Archives at