I have spent the last few days looking at a client’s Excel spreadsheet. They are using it to manage all of their treasury positions – amounting to several hundred million dollars.
Don’t get me wrong – Excel is a wonderful tool, but under normal circumstances this is probably not the optimal choice to make. In their case there are some exceptional circumstances that means that this may be a reasonable (if short-term) solution but it prompts me to ask if anyone has a favourite MS Excel story.
My personal favourite is of a former employer of mine in London. They are a major investment bank that is also one of the largest commercial banks in their home country. In short, a huge operation with assets in the hundreds of billions of pounds.
When I joined them in the late 1990s, they had acquired many other banks and trading operations over several years, including the one that I was formerly employed by. The problem was that each of those entities had their own general ledger and the bank had not taken steps to integrate or eliminate them at all.
The problem should be obvious – there was no one place where the bank’s general ledger was kept. The solution adopted was a classic one. At the end of every month a full ledger dump was taken from each GL and integrated using Excel – with all of the problems of GL account mapping and consolidation to deal with. The time taken to close off the GLs, get the dump, process it and then put put the management reports out meant that, at best, it took until mid-month to get the numbers. Control was effectively impossible.
After a year of this, it was decided that a better solution was needed – besides which the 65,536 row limit was being breached and the speadsheets took hours to recalculate. The solution? MS Access. When I left that had eventually moved on to SQL Server – but it was still taking nearly the same amount of time to get everything done.
Internal audit always paid close attention, but the risks were always just huge. There was also no real way to verify any of the numbers other than tracing each one back to the host systems and that could take hours per number.
6 comments
16 January, 2009 at 15:48
AUSQUANT
While I’ve never worked anyway that was using Excel for compiling its GL, there has always been some tension on the use of it to price deals for P&L purposes.
While Excel’s shortcomings are numerous, it has the flexibility to enable almost any new and interesting deal to be set up in it and with an add in or two doing the grunt work price any sort of deal you like. Most systems on the other hand don’t allow this kind of flexibility and you quickly wind up either restricting business or doing some rather horrendus fudges.
Personally I feel the best approach is to allow small numbers of deals be doen on this basis to see if it is a profitable business line before proceeding to an expensive system upgrade.
16 January, 2009 at 22:07
Ross Gayler
Identification of the risks in using spreadsheets (not just Excel) for complex tasks is something of a cottage industry among consultants and academics. For example, see the European Spreadsheet Risks Interest Group (http://www.eusprig.org/) – especially their zoo of publicly reported spreadsheet errors (http://www.eusprig.org/stories.htm). For two examples of interesting technical approaches to avoid the risks and retaining the benefits of spreadsheets see http://www.spreadsheet-factory.com/ and http://www.lumina.com/
19 January, 2009 at 16:34
Colin
Andrew I think you are quite right, and you highlight a perhaps little known aspect of the credit crisis. The bulk of management of the positions in ABCP of all types is managed in Excel, as are the development of business cases in Capital Markets. There are other problems with Excel for such activities – the opportunity for error is mammoth. Spreadsheets are created by well-intentioned experts, but the possibility of one cell being incorrect is enormous, with obvious consequences.
25 January, 2009 at 23:54
Clive
An Excel “war story” of a different type occurred in the early days of data gathering and matching to create longitudinal datasets for analysing credit card default behaviour (and hence to build application, and behavioural, PD models).
To get sufficient time span, it was necessary to exhume some PC-based legacy systems that had captured the info supplied at application time. The data files from these had been exported into Excel and these xls archives were all that was now available.
Unfortunately Excel has weak support for character versus numeric distinction, and all the credit card numbers had been input as numerics i.e. they now appeared as something like 5.220405061387E+15. CC numbers are 16 digits long, but PC double precision only reliably retains about 14 significant digits. So, the key linking field, the CC no., was known certainly for the first 13 digits and almost surely for the 14th but only vaguely for the 15th, and the 16th was random. ‘Matching’ and ‘record linkage’ etc. takes many forms but this particular mode of imperfect data – caused by passing through Excel – was an interesting variation. A bit like having hardcopy records chewed all down the edge by a dog.
[We needed to know the exact CC no. so as to match the application info with the performance data on the core banking system.]
So we had to design a matching algorithm that started out using matches on the first 14 digits and then using some heuristic filtering of multiple matches based on other data fields.
26 January, 2009 at 13:19
penguinunearthed
I once had a spreadsheet to review – I was the independent consultantreviewing it for accuracy. It had been built to the edges of spreadsheet performance, and then the person who built it suddenly realised that they had a whole extra piece of calculation to put in.
It ended up being 50 Meg (really!), and excel just couldn’t cope with the amount of data. Somewhere deep in the middle of the main spreadsheet (somewhere around the 20,000th row) the spreadsheet just crashed, and the calcs from that part were complete rubbish.
We ended up making them take bits out and put them in Access before we could sign off, which didn’t make us particularly popular as a reviewer.
16 February, 2009 at 18:35
Jacques Chester
Just wanted to drop by and say that the link to EuSprig was pure gold.