Do you have too many spreadsheets?

Why is too many spreadsheets a problem?

Following the recent blunder made by the UK government following their use of Microsoft Excel for Covid-19 results, there has never been a more prevalent time to discuss this question. Are spreadsheets more of an inhibitor than an enabler?

There have been several studies conducted that identified almost ninety per cent of spreadsheets contain errors that invalidate their results, such as copying a formula to too few cells, which means that a total is wrong for example. Raymond Panko, a University of Hawaii professor who has been investigating spreadsheet errors for two decades, explains, “for large spreadsheets, the issue is how many errors there are, not whether an error exists.”

Organisational spreadsheets are vulnerable to error for several reasons:

• Spreadsheets are typically self-developed.
• Spreadsheet development is usually informal, with little planning, and no quality assurance.
• There’s no version control for spreadsheets, that is, no system to ensure that users can find and use the latest and best version.
• Spreadsheets don’t create audit trails, so good records of how and when formulas or processes are changed aren’t always available.

Real-life examples do exist of these issues in the industry in different forms:

1. One being with global aerospace giant Boeing, who unknowingly shared 36,000 coworkers’ details in an email. The spreadsheet, sent to provide the employee’s spouse with a formatting template, contained the personal information of roughly 36,000 other Boeing employees, including Social Security numbers and dates of birth, in hidden columns. Some 7,288 of the affected employees resided in Washington State.
2. Software firm Tibco shareholders received a regulatory sanction after reporting $100 million less than originally anticipated from the company’s more than $4 billion sales to Vista Equity Partners as a result of a spreadsheet error that overstated Tibco’s equity value. According to a regulatory filing, Goldman Sachs, which is advising Tibco on the deal, using the spreadsheet in calculating that Tibco’s implied equity value was about $4.2 billion. The merger agreement, reflecting that number, was announced Sept. 29.

Furthermore, well aware of the reliance on excel spreadsheets in industry and potential risks such as aforementioned, the Food & Drugs Administration (FDA) began to include this in part of their surveillance of organisations. 21 CFR 820.70(i): Automated processes:

• When computers or automated data processing systems are used as part of production or the quality system, the manufacturer shall validate computer software for its intended use according to an established protocol.
• All software changes shall be validated before approval and issuance. 
• These validation activities and results shall be documented.

How can this be resolved?

Spreadsheet Control Project

The European Spreadsheet Risks Interest Group (ESRIG) recommends retrospective action in organisations that are completely reliant on spreadsheets and are in a position where things are out of control. 

Furthermore, PricewaterhouseCoopers recommend organisations begin with a process flow chart facilitated by an independent person along with key stakeholders for major processes. This way the person can identify the spreadsheets used for key business decisions. This will initially identify a few spreadsheets, which can be analysed to determine if they are fit for purpose. However, more importantly, it is likely to mimic the iceberg concept and identify an even larger number of spreadsheets that contribute to the higher-level ones. It is conceivable that this is where the majority of spreadsheets with errors may exist, and subsequently throw off those used for key business decisions. 

Preventive Action

After undertaking the spreadsheet control project (if applicable) organisations then need to look at how to control the issue moving forward. Having identified critical spreadsheets, organisations can go about analysing and remedying any issues identified by the recommendations section below. Testing is part of ensuring spreadsheet quality and complements spreadsheet review. The distinction between the two is an important one. A review consists of looking at the code trying to spot the errors, while to test a program you run it and look at the results. Both are necessary, and neither is likely to find all the errors on its own. 

Spreadsheet Standardisation

The ESRIG recommends the following approach:

1. Determine what role spreadsheets play in your business
2. Create standards
   1. Templates and consistent spreadsheet user training and use covering the following
      1. Ensure that a spreadsheet is an appropriate tool for the requirement.
      2. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should enable this.
      3. Include an ‘About’ or ‘Instruction’ sheet.
      4. Design for longevity.
      5. Focus on the required outputs.
      6. Separate and identify inputs, workings and outputs.
      7. Be consistent in structure and use of formulae.
      8. Keep formulae as short and simple as practicable.
      9. Never embed anything that might change or need to be changed.
      10. Perform a calculation once and then refer back to that calculation.
      11. Avoid using advanced features where simpler features could achieve the same result.
   2. Controlling Spreadsheets
      1. Collaborate, spread ownership, peer review.
      2. Have a system of backup and version control, which should be applied consistently within an organisation.
      3. Rigorously test the workbook for simple hypothetical results.
      4. Build-in checks, controls and alerts from the outset and during spreadsheet design.
      5. Protect parts of the workbook that are not supposed to be changed by users.

Additionally, it is imaginable that many organisations will indeed be very reliant on spreadsheets, however, if the exercise above proves too extensive or unsatisfactory they may benefit from the approach taken by Zurich Financial. They undertook an exercise to deliver a single set of integrated tools that supported Sales and Underwriting functions. Particular focus is on the delivery of new, consistent, pricing tools. Within this context, the re-deployment of several existing spreadsheets as robust, secure, enterprise-class web applications is considered key. Without this, they would not be able to achieve the benefits of a globally integrated business. Authorized users may then access it only via a custom web application allowing a more natural work-flow. Ultimately, users will access these tools via a proprietary portal.

Final Thoughts

Spreadsheets are here to stay, and they have arguably played a significant role in the development of Software as a Service (SAAS) companies developing their requirements to build products from. However, the risks associated with overreliance and lack of control of spreadsheets are great. Particularly in heavily regulated industries such as aerospace, medical device and pharmaceutical. 
To understand how to begin controlling spreadsheets by best practice, check out the ESRIG best practices section today for some tips and tricks to reduce your organisational risk.

---

Want to put your blog writing onto autopilot? Head over to https://blogwritingservicesuk.com/ to find out more how we can help scale your business with blog writing.