5 reasons Excel isn't fit for purpose for compliance monitoring.
Introduction
In 2008 a study done by a professor of IT management at the University of Hawaii revealed that on average 88% of spreadsheets contain errors. He stated “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”
A whitepaper entitled “Capitalism’s dirty secret”, reported that 33% of large businesses report poor decision making due to spreadsheet problems and that 20% of large businesses have suffered direct financial loss due to poor spreadsheets.
A post-mortem analysis of Enron emails by the Delft University of Technology, found 755 Excel sheets with more than 100 errors, with the maximum number of errors in one sheet being 83,273. On average Enron employees sent 100 emails a day with spreadsheets attached.
There is even an organisation called the “European Spreadsheet Risks Interest Group” whose sole function is to highlight potential spreadsheet risks. They maintain a “horror stories” database which is chilling reading.
I want to look at a few reasons why Excel is not fit for purpose for any serious regulatory use and can leave an organisation exposed to dozens of potentially serious issues:
Excel is Error Prone
As mentioned in the introduction, it is exceptionally easy to make hidden errors in excel. Some of my personal favourite stories:
- Two Harvard professors miscalculated global economic growth by failing to add the first 5 rows in a sheet.
- Barclays Capital unintentionally bought 179 assets from Lehman Brothers, because the unwanted assets were hidden (rather than deleted) in Excel.
- Fannie Mae's finance team used spreadsheets to input data into their new accounting system. The sheet contained errors that skewed results by $1.1 billion.
- JP Morgan severely underestimated the downside of its synthetic credit portfolio due to several faulty equations in a sheet used to model risk and a process that required the copy/paste of a large number of cells. This ultimately led to ca. $6.5 billion in losses & fines.
- Fidelity was forced to cancel a year-end dividend distribution due to a single missing “-” sign. A tax accountant omitted the minus sign when they transcribed the net capital loss (of $1.3 billion) from the fund’s financial record to a spreadsheet. This turned the loss into a gain, causing the dividend estimate to be off by $2.6 billion.
There is not one single source of error, but a plethora of mistakes that can be made:
- Copying & pasting: Copying the wrong data, pasting the right data in the wrong place etc. etc.
- Incorrect formulas: Common errors like forgetting to divide by 100, or by not summing up all rows in a column.
- Inconsistent formulas: One cell has a different formula to the one above it.
- Not-chaining formulas: Sometimes a complex calculation is made in two columns, when the calculation is changed it is only updated in one column.
Solution: Data should never be manually copied from anywhere, only people with the required technical expertise should be creating complex formula/macros, formulas must be audited and subject to 4 or 6 eye approval. Above all, all calculations must be thoroughly tested and any time a change is made, the new results must match expectations.
Tracking Change is Difficult
Things change over time, that’s a given. But say you have to prove to management, an investor or even a regulator how you made a certain calculation... 2 years ago?
- What Excel sheet was used to make that calculation?
- Is the Excel sheet that was used two years ago still available?
- If so, how can one see what the formula looked like two years ago?
- Can the data be edited and re-run based on what the calculation should have been? (“what-if”)
Excel is designed to calculate numbers NOW, but it is not suitable as an archival system, nor was it designed with version control in mind. With more and more regulators pushing for complete audit trails, Excel is certainly not fit for purpose in this respect.
Worse still, it is easy for so-called “version proliferation” to happen, where multiple versions of the same spreadsheet are made and circulated. Knowing which one is the “master” (if there actually is one) can be nearly impossible.
Solution: There must be a single source of truth. Everything must be audited and 100% immutable. Point in time reports must be available to show what calculations were made at any time in the past. Major changes to the calculations must be documented with reasons as to why they were changed. What-if scenario analysis should be possible (both with different algorithms and different datasets).
Access Control
Excel was created in an era of one user = one sheet. I’m sure many of us have endured the “Excel round robin” as a sheet was emailed to various people who had to enter their numbers before emailing it to the next person. Maybe changing the file name to “V11” once you’ve made your change?
Excel is not a database nor was it designed to handle multiple users working on the same sheet. Even with modern versions like Excel 365, it is extremely difficult to keep track of multiple users editing multiple cells.
There are rudimentary provisions in Excel for locking certain sheets or cells, but generally in most organisations if a person can open the sheet they can edit pretty much all the contents, which is generally not desired.
Finally, without adequate backup control, it is very easy for a spreadsheet to become corrupt or even deleted entirely.
Solution: Users must have finely-grained access control. From basic read-only access to super user access. Every function within an organisation (Compliance, IT, Audit, Management) must have corresponding permissions so they can only do what is required of them in the system.
Security
Excel and Excel Macros have a notoriously long list of security vulnerabilities and unless your IT Manager is super proactive it is easy to have a version which is vulnerable.
Combine this with the fact that a spreadsheet can easily be emailed outside of an organisation, copied to a USB stick or uploaded to a malicious website. This means that potentially sensitive data can easily leak outside of an organisation
Solution: Keeping Excel up-to-date and having lots of security monitoring in place to ensure sensitive data isn’t shared with 3rd parties.
Lack of Automation
Most compliance functions have to be executed on at least a daily basis. This means that someone has to open an Excel sheet and paste in data and wait for numbers to recalculate. Sometimes it might mean that they have to press the “Play” button for some macros (God forbid) to work their magic.
If the person that normally runs the Excel calculations is ill, then it’s very easy for a regulatory process to be forgotten. Worse still if they leave, the knowledge of how to run the Excel might be incomplete or lost entirely. Maybe they’ve laminated an instruction page and left it on their desk, but more often than not, that’s unfortunately not the case.
I remember once demoing to an Irish asset manager and the Compliance Officer recounted a story of having to make a 50km commute into the office over Christmas as his assistant had taken sick and was unable to run the required Excel calculations.
Solution: For a regulatory process to be bombproof, everything must be automated as far as humanly possible. From data procurement, through calculation of results to publishing of end reports. Manual is no longer an option in 2020. Companies have to demonstrate control over regulatory complexities and excel requires manual intervention, making it hard to manage and scale.
Summary
Excel is a fantastic tool for doing qpps.co/services/shareholding-disclosure/resources/the-impouick calculations, for presenting data in a tabular format or for putting some charts together. It is however, not a tool which should model and support a critical business process which requires being able to stand up to regulatory scrutiny.
With more and more regulators requiring firms to prove “reasonable steps have been taken to prevent regulatory breaches” and “operational resilience“, it is unfortunately a truth that Excel cannot meet this requirement.
Contact FundApps to see how you can consign your manual, error-prone Excel sheet to the recycling bin and adopt cutting-edge compliance-as-a-service which ensures you fulfill all your regulatory obligations.
