Involved in software development since 1984.
Three Principles to Reduce Risk in Finance & Accounting Spreadsheets
Nine out of Ten Spreadsheets Have Errors
Impossible, but true, according to a number of researchers. (1)
Which makes it a statistical certainty that at some stage your financials have carried errors caused by spreadsheets.
In this article, I will outline a framework for reducing reporting risks in the spreadsheet chain supporting your financial statements. Adapt and adjust the framework to suit your corporate circumstances, it will reward you with a substantial risk reduction in the process.
In order to make your spreadsheet exposure more tangible, let me shock you with some EXCEL horror stories.
Reputational Damage and Financial Ruin
Spreadsheets are open to both human error and purposeful fraud, which together have led to many misleading financial statements and wrongheaded financial decisions.
Although not all spreadsheet errors produce real consequences, here’s one that did.
Once, long ago, Eskom, South Africa’s power utility, levied a fine of R 2.2 billion against its largest coal supplier for supplying below standard coal. The fine was big enough to force the supplier, Optimum Coal Mine, into business rescue. Later, as part of an enquiry into this mess, Susan Daniels (Head of Legal) reported that she had asked her team: (2)
‘How did you arrive at this R 2.2 billion?’
‘… it’s not actually 2.2 billion. There was an error in the spreadsheet…’ was the reply.
Once corrected, the formula reduced the fine by ZAR 1bn, which did nothing to alleviate the suffering of the 400 miners who had already lost their jobs as a consequence.
As much as it sounds like a Netflix production, it actually happened. (3)
‘We just lost $6 billion!’
In 2012, a stock trader made a silly cut and paste error that broke a formula and cost JP Morgan Chase $6.2 billion. (4)
The damaged formula underestimated trading risks by 50% leading to some reckless trading by the careless trader. Emboldened by his mistake, he made a series of crazy trades, dancing like a tightrope walker above an imaginary safety net. Inevitably, he fell as was fired.
Later he was criminally charged and became infamous in the media, where he was tagged The London Whale (5). He fled to France and became in his words, ‘… public enemy No. 1’.
How Many F&A Sheets Are Your Responsibility?
Can you estimate how many financial spreadsheets are in daily use your organization?
Any idea how many of these would be involved in risk and compliance? Or how of them carry totals destined to end up on your balance sheet?
If you have not already done so, now is the time.
Take these three steps to Reduce Risk in your F&A spreadsheets
1st Principle: Understand the Problem
No action is required of you to implement this step. You simply need to fully appreciate this statement:
Software is developed by teams; spreadsheets are made in the dark.
In contrast to the work of the typical solo-spreadsheet writer, software development is a team process. The team supervised by a project manager, develops functionality based on a system specification document; the software is tested and user approved before making it into production.
Yet, despite these controls, software development remains a complex, uncertain process. Bugs pop-up everywhere even after extensive testing. So much so that finding and fixing them in production is almost routine. Think of the regular Microsoft, IOS and Android updates we are all regularly subjected to.
Spreadsheet development typically has no such oversight.
Therefore, it does not take much imagination to appreciate the greater risks inherent in spreadsheets created by users with no formal training in good code practices, data structures, error checking and regression testing. And that’s before adding the potential for fraud into the mix.
Now, armed with a deeper understanding of the potential for errors in your sheets, you need to find ways to mitigate your risks.
2nd Principle: Identify critical sheets in your organization
To begin with, you need to identify all the spreadsheets in use in your organization.
There are there are a number of way to achieve this, ranging from manual checks to automated scans. You will need to work your IT dept. to decide on the best processes for your situation.
At minimum, list for each sheet:
- Department name
- User names
- File location
- Approximate value dealt with
Once you have this list, the next task is to categorize each item by business function and potential risk. Setting a value threshold related to a sheet’s contents may assist this process. Flag any sheet containing values affecting your balance sheet.
The outcome should be a relatively small list of critical sheets, concentrated mostly in a few high-risk departments. (6)
“as anyone who’s ever inherited a spreadsheet knows, some operate if not by magic, then at least through unintuitive logic that might take a lifetime to unravel.” Mathew Schartz (6)
3rd Principle: Introduce Compliance Processes for Spreadsheets
Attack your business critical sheets first.
Processes vary depending on circumstances but here is a general framework for all critical spreadsheets:
- Borrow some core principles from world of software development and use these to implement procedures that guide new sheet development
- In a similar way, produce rules and schedules to control the maintenance of existing sheets
- Store critical sheets in a central location that provides secure access and regular backups
- Assign a custodian (not a user or developer) to each critical sheet, with the duty to understand how the sheet works, when it is used and who uses it
- Document and version each critical sheet with an About tab containing:
- Sheet ownership and users
- Details about the functions of the sheet
- Dates of development
- Track of changes and reasons for changes
- List any revisions and updates
- Where possible move the data your sheets consume out of the sheets that consume it
- Never allow data to be copied as part of a sheet that will be reused or duplicated for multiple users; move the data centrally so that it can be shared
- Set data stored in sheets to be read only whenever possible
- Must always be separated from data
- Must never contain hard coded values or values that need to be updated each time a sheet is used
- Where possible employ an internal or external auditor to check and verify the operation of all critical sheets
Lastly, do not forget all the ‘non-crucial’ sheets.
Develop a standardized process that applies to all company spreadsheets:
A lower level of compliance.
- Standardized design elements and styles making sheets easier to identify, train and debug
- Documentation for all sheets
- Versioning for all sheets
There are a wide range risks (7) and many variations to the processes proposed above and it will take time to adapt and adjust the steps that best suit your circumstances.
Just don’t sit around and wait for a crisis to spark action.
Howard Rybko – CDO Syncrony Digital (Oct 2019)
Notes, Resources and References
About EXCEL and Google Sheets: EXCEL is the world’s leading spreadsheet application. And although it is exclusively referred to in this document, Google Sheets is also in wide use. As a rough estimate, EXCEL currently has about a billion users while Sheets has about 200 million. (8)
Operational Impact of Errors: read Impact of Errors in Operational Spreadsheets https://arxiv.org/ftp/arxiv/papers/0801/0801.0715.pdf
Reducing Errors: read Twenty Principles for Good Spreadsheet Practice.
Fraud Protection: read Protecting Spreadsheets against Fraud https://arxiv.org/ftp/arxiv/papers/0801/0801.4268.pdf
Auditing Guidelines: An auditing protocol for spreadsheet models https://www.sciencedirect.com/science/article/abs/pii/S0378720608000505?via%3Dihub
More F&A Sheet Issues: Spreadsheets: Aiming the Accountant’s Hammer to Hit the Nail on the Head - Mbwana Alliy and Patty Brown https://arxiv.org/ftp/arxiv/papers/0809/0809.3597.pdf
SOX and Sheets: The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act - https://www.spreadsheetdetective.com/main/PwC-SpreadsheetsSoX.pdf
- Some spreadsheet risk studies: - EuSpRIG (The European Spreadsheet Risks Interest Group), - Sarbanes-Oxley: What About all the Spreadsheets? – Panko & Ordway https://arxiv.org/abs/0804.0797 - Impact of Errors in Operational Spreadsheets – Powell,Lawson,Baker https://arxiv.org/abs/0801.0715
- REPORT OF THE PORTFOLIO COMMITTEE ON PUBLIC ENTERPRISES ON THE INQUIRY INTO GOVERNANCE, PROCUREMENT AND THE FINANCIAL SUSTAINABILITY OF ESKOM, DATED 28 NOVEMBER -2018 https://www.parliament.gov.za/storage/app/media/Links/2018/November%202018/28-11-2018/Final%20Report%20-%20Eskom%20Inquiry%2028%20NOV.pdf
- Eskom Inquiry, 08 November 2017 - https://www.youtube.com/watch?v=46XnAtSp3eo&feature=youtu.be
- How The London Whale Debacle Is Partly The Result Of An Error Using Excel https://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2?IR=T
- Bruno Iksil AKA The London Whale - https://www.bloomberg.com/quicktake/the-london-whale
- Strategies for Addressing Spreadsheet Compliance Challenges - Microsoft Corporation (B Weber) - https://arxiv.org/ftp/arxiv/papers/0711/0711.4634.pdf
- Top 10 Spreadsheet Compliance Risks and How to Avoid Them – Mathew Schwartz in Truth-to-Power https://www.t2pa.com/analysis-and-advice/top-10-spreadsheet-compliance-risks/
- Spreadsheet Usage Numbers - Hjalmar Gislason – Aug 2018 - https://medium.com/grid-spreadsheets-run-the-world/excel-vs-google-sheets-usage-nature-and-numbers-9dfa5d1cadbd