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. ⁽¹⁾

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: ⁽²⁾

‘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. ⁽³⁾

‘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.⁽⁴⁾

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⁽⁵⁾. 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

1^st 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.

2^nd 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
Purpose
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 ⁽⁶⁾

3^rd 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
Data:
- 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
Formulas:
- 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 ⁽⁷⁾ 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. ⁽⁸⁾

Additional Resources:

Operational Impact of Errors: Read Impact of Errors in Operational Spreadsheets

Reducing Errors: Read Twenty Principles for Good Spreadsheet Practice.

Fraud Protection: Read Protecting Spreadsheets against Fraud

Auditing Guidelines: An auditing protocol for spreadsheet models

More F&A Sheet Issues: Spreadsheets: Aiming the Accountant’s Hammer to Hit the Nail on the Head - Mbwana Alliy and Patty Brown

SOX and Sheets: The Use of Spreadsheets: Considerations for Section 404 of the Sarbanes-Oxley Act

References: