Spreadsheets–the power and the glory
In 2009 shortly after the banking crisis was unfolding, I wrote a piece on the danger of spreadsheets. It turns out that danger costs in the billions.
In January 2012, losses on a derivatives portfolio that ultimately cost the bank more than $6bn stood at a mere $100m. A review team at that time identified that an important risk calculation for the portfolio was error-prone.
Spreadsheets offer a fabulous and awe inspiring power to produce analysis and presentation of numbers that is hitherto unforeseen. The power lies in the functions that allow rapid calculation of enormous amounts of data. “Spreadsheet jockeys” are valued because the depth of analysis that is provided is so powerful that it becomes unimaginable to the lay person.
I have been in a bank meeting where a risk analyst developed a spreadsheet that highlighted enormous risk in a portfolio that almost killed the project. Once we highlighted the vlookup error all was well, but it shouldn’t have to be that hard, and the lost weeks and lost credibility on both sides took a lot of time to get back.
To those who are knowledgeable about spreadsheets, there is a suspicion that is borne of past mistakes, a formula that used ‘relative’ copying in an unexpected way, or a range that incorrectly excluded data. We have all been there and watching the new guy with his sheets pronounce some new found insight can be rightly questioned until the data and analysis is evaluated by third parties.
The danger of spreadsheet lies in the personal creation without validation. First Lotus, then Microsoft created an unimaginably powerful tool that is very hard to validate. Everyone has their own ways of laying out data, and building the formulae. Its personal.
Lucy speaks in the FT article above about enterprise software, but that starts to sound like loss of functionality. She also speaks of better spreadsheet consistency and practices, that makes more sense:
Not all efforts to control spreadsheets need to come from on high, though. There are various grassroots control efforts. Analysts at investment banks, for instance, often adopt norms that make spreadsheets easier for peers to review. New hires learn these practices.
There will still be losses from incorrect spreadsheets, but it is incumbent on spreadsheet creators to follow rules and maintain simplicity and transparency.