Its attractive versatility might by your biggest weakness
Spreadsheets... Well, let's say, Excel [1] is by far one of the best tools when it comes to retrieving data from flat files or other sources and quickly running a cross-sectional “pivot” analysis to quickly discover how much concentration the portfolios of banking clients have on Volkswagen when the bad news kicked in. Indeed, by the time it would take to integrate the analysis in the “big system” (the back-office system, ERP, portfolio management system, etc…) and discover the answer, months would have passed. Thus, Excel is the perfect tool for the smart user who wants to go “beyond the numbers” and be proactive at finding and showing new insights.
The problem comes when your colleagues start liking too much what you are doing and your Excel file (and its macros) become usual practice. After a while, your “tool” is part of the furniture and nobody remembers that…it is still Excel. And someday, when you will be drinking your margarita on a nice beach during your holidays, you might have a call from a colleague asking how “to run the damn macro again”…
Recently, the head of operations of a major European bank was admitting that they had around 8’000 critical spreadsheets undocumented, where probably the only person to remember how it worked and which files have to be linked is the author itself.
This is not the first time worries or problems were reported. For example, in 2008, Moody’s admitted after one-year to have been hiding a “calculation glitch” that distorted the ratings they were providing on specific debt products.
In June 2012 and then in January 2013, the Basel Committee of Banking Supervision cites [4]:
“Where a bank relies on manual processes and desktop applications (eg spreadsheets, databases) and has specific risk units that use these applications for software development, it should have effective mitigants in place (eg end-user computing policies and procedures) and other effective controls that are consistently applied across the bank’s processes.”
The list of examples is long. The Chartered Institute of Management Accountants (CIMA) published some list a while ago, as well as the EuSpRIG that keeps track of some horror stories, among which:
C&C, the Irish group that owns Magners cider. A spreadsheet error caused the company’s shares to fall by 15 per cent in July 2009 after it admitted that its total revenues in the previous four months had dropped five per cent, not risen three per cent as previously reported. See: https://www.ft.com/content/531a9e6a-6fe4-11de-b835-00144feabdc0
The £5.6m fine that Swiss banking group Credit Suisse received from the UK’s Financial Services Authority (FSA) in August 2008. The regulator said the booking process that its structured credit business relied on “was complex and overly reliant on large spreadsheets with multiple entries”, which “resulted in a lack of transparency and inhibited the effective supervision, risk management and control”.
Canadian power company TransAlta: $24 million lost because of a copy-paste error leading to bids being aligned with the wrong contracts. See: https://www.theglobeandmail.com/report-on-business/human-error-costs-transalta-24-million-on-contract-bids/article18285651/
Kodak tumbled harshly in 2005, due to a last-minute accounting adjustment to compensate for an Excel error. See: https://www.marketwatch.com/story/kodak-restates-earnings-adds-9-million-to-latest-loss
London Olympic games in 2012: Due to a piping mistake, 10’000 tickets were oversold for the synchronised swimming event. See: https://www.bbc.com/news/uk-16409480
The January 2013 report of JP Morgan’s on its $6bn trading loss partly attributes it to an excel error (“Report of JPMorgan Chase & Co. Management Task Force Regarding 2012 CIO Losses”).
Finally, a study by Prof. Panko, of the University of Hawaii pretends that 88% of spreadsheets contain errors: https://www.researchgate.net/publication/1912352_Spreadsheet_Errors_What_We_Know_What_We_Think_We_Can_Do
Mid-October 2015, Deutsche Bank officially blamed the messy and outdated technology for numerous errors that occurred and sparked operational exposures. Some have been guessing that this includes undocumented spreadsheet developments.
To complete this recount, Accenture issued a study in August 2015 that confirms that almost half of the World’s biggest 109 banks have a Board without any technological experience, and a further quarter have only one such member.
What to do next?
Dependence on Excel is a fact. Some companies have been refusing to install Excel to their managers but that seems a little bit obscurantist and short-sighted. Creativity, spirit of initiative, willingness to timely respond to appearing needs, desire to test and confirm some assertion with analytical facts, must be encouraged.
Excel is still an incredible tool for prototyping and to rapidly come up with an appraisal, a review, the confirmation of a guess… As mentioned before, the problem comes when it is used thereafter automatically without discernment and becomes part of normal operations and of workflows that ultimately lead to strong dependences on their calculations, remaining undocumented.
By definition, when you write code, you document the chronology of operations at the same time. But when you put formulas in cells that refer to each other without any visible chronology of operations, you don't, naturally at least.
In corporate and banking environments, performing analytics comes undoubtedly with the need to aggregate different flows of data. There is a huge difference between (1) a one-off entirely manual data gathering exercise to produce a punctual report or analysis, (2) a semi-automated data download and aggregation of data for specific needs, and (3) the use of that same process regularly in the continuity of company’s life.
From a prototyping tool, the spreadsheet soon becomes a system in itself. Once your spreadsheet is adopted, it starts to have its own “second life”: some teammates might find it useful for more tasks, data flows will continue evolving and increasing in size and diversity, traceability of previous results will be more and more required, the risk of building a repository database aside of the main system materialises, the overall workflow will be spread among inter-linked spreadsheets, similar operations might not be performed the same way through the same channel, etc… And since the spreadsheet shows you numbers first, not processes, it bears the hidden risk that we are unduly reassured by numbers being “seemingly” correct, or similar, which is not an assurance that the same formulas might produce the desired outcome in a different environment in the future.
So, what should we do? The solution can come from one of the following two tracks:
Make a better use of Excel. That never hurts.
Improve the knowledge on Excel functions and capabilities.
Learn about best practices in the design and management of spreadsheets, with an emphasis on teamwork habits.
Install a (financial) intermediary solution between the back/mid-office system and the use of (Excel) spreadsheets. The solution must be agile, close to the same use, very “lean” and proficient at the same time in terms of functions for the métier, and let the user keep the full ownership of the data.
Continue using spreadsheets for prototyping and rapid design.
Delegate the main workflow, once it has been scrutinized and tested, to that flexible solution.
Use the solution to automate processes (data pooling, treatment and computations, monitoring cockpit). You can keep the prototype alive but linked to the now automated data flow as a way to document the original scope as a side-check or as a workbench for additional features to be tested.
Allow the user to get results back in Excel for post-treatment before reporting. Otherwise, if you don’t allow users to “own” their process and its results, their will quickly readopt their previous behavior, recreate hidden spreadsheets and be even more secretive about them.
The solution must keep the back/mid-office system as the only repository, not creating additional reconciliation issues, to keep all its flexibility in funneling data, analysis and results for the métier. Optimally, that solution comes with a dashboarding layer that allows managers and board members to monitor the activity through KPIs and KRIs.
Both solutions are obviously not mutually exclusive.
[1] by Microsoft Corporation. Available from: https://office.microsoft.com/excel.
[3] It must be noted that Numbers, the alternative proposed by Apple, is displaying blank empty pages where tables can be dropped as objects, by design since its inception. But it lacks the "tools" present in Excel.
[4] Basel Committee (bcbs239). "Principles for effective risk data aggregation and risk reporting." Bank for International Settlements 8 (2013), 28p.