Spreadsheets under Scrutiny: A Modern Approach to Excel Risk Remediation

Excel is the single most used business software package in the world. It is estimated that Excel is used by a billion people, with at least half a billion actively using Excel in the workplace today, many of whom are self-taught or work in environments where controls are not as robust as they should be.

The widespread business reliance on Excel creates multiple issues, such as data performance limitations and a lack of version control, that often go unnoticed or unaddressed and can result in costly errors and missed opportunities. This article outlines a modern and cost-effective approach that mitigates the operational risk of Excel whilst maximising its many capabilities.

Spreadsheets Under Scrutiny

In the financial services industry, regulatory bodies look for transparency and auditability in all financial processes. However, within the financial operations of an organisation there are often numerous processes that are Excel-based; for instance the budgeting, forecasting, and variance analysis of Financial Planning & Analysis (FP&A). The risks associated with Excel usage can be material and therefore subject to particular scrutiny by auditors and regulators. Well-known examples for relevant risk types include:

• Financial Risk: Formula errors, leading to inaccurate financial control and potential monetary losses.
• Regulatory Risk: Failing to maintain proper audit trails and version control in Excel spreadsheets, resulting in non-accordance with audit and regulatory requirements.
• Compliance Risk: Mismanagement of sensitive data within Excel spreadsheets due to a lack of documentation around process controls.
• Operations Risk: Dependence on a single employee (key person dependency) for managing critical Excel-based processes, resulting in a “single point of failure” to the entire operation.
• Technological Risk: Excel might encounter scalability issues as data volumes or complexity increase, such as slow performance and an inability to handle datasets efficiently.
• Human Risk: Accidental deletion of crucial data or intentional tampering with figures in an Excel spreadsheet, leading to incorrect analyses and decisions.
• Market Risk: Inability to quickly respond to market events due to Excel complexity.
• Reputational Risk: Public disclosure of errors in financial reports or operational metrics due to Excel mismanagement, damaging the company’s credibility and trust with stakeholders.

Excel auditing studies suggest that about 90% of spreadsheets contain at least 1% of errors in their formulas, and there are numerous publicised examples of the sometimes severe consequences of spreadsheets going wrong. For more information on some of those, the European Spreadsheet Risk Interest Group offers case studies and research on spreadsheet errors and their impacts.

A recent example listed in 2024 includes Norway’s sovereign wealth fund losing roughly $92 million on an error relating to how it calculated its mandated benchmark within Excel. Larger incidents include the approximate $6 billion loss of “The London Whale” in 2012 at JPMorgan Chase, which was thought to be partially due to a formula error in a risk calculation spreadsheet that significantly understated the VaR, dividing by the sum of the rates instead of their average.

The Challenge with Change

The risks associated with Excel have been widely acknowledged for at least two decades. Up until now, traditional approaches to mitigate Excel operational risk have included a variety of methods.

• One common strategy is to map the business process within the spreadsheet to the most relevant existing IT system and build out the equivalent capability within that system. Changes to the business process are then subject to IT prioritisation, software development and testing, and alignment to release schedules. In some organisations, that can take weeks or months to complete; way longer than the business can tolerate in response to rapidly changing needs. Consequently, recently retired spreadsheets can often re-appear quickly and the cycle begins again.

• Another approach involves registering Excel files and End-User Applications (EUAs) to maintain an inventory, followed by conducting risk assessments to identify high-risk spreadsheets. These high-risk spreadsheets can then be remediated with appropriate controls, such as access restrictions, validation checks, and regular data audits. However, these measures are not foolproof and still require regular self-attestation and occasional external auditing to ensure ongoing compliance and effectiveness.

A significant issue that often goes unaddressed is the lack of consideration for the impact of business and IT changes on business critical spreadsheets. For instance, modifications in the structure of data feeds can have detrimental effects on the accuracy and functionality of existing spreadsheets, resulting in flawed analyses and reports. Similarly, updates to business logic without corresponding updates to spreadsheets can cause inconsistencies and errors in calculations.

These challenges require proactive measures, such as continuous monitoring of spreadsheet dependencies, comprehensive impact assessments before implementing changes, and robust communication between business and IT departments to ensure that all potential impacts on spreadsheets are considered and mitigated.

A Modern Remediation Approach

To combat these common challenges, we adopt a new approach to remediation, as depicted in the diagram below.

For higher volumes of data and Excel files that pose the highest levels of risk exposure, such as those used in financial calculations, we recommend using a data preparation tool to help ensure accuracy and optimise performance. Tools such as Dataiku, Alteryx, and Talend can be particularly effective in managing large datasets, automating data workflows, and reducing the risk of errors. These tools not only improve data quality but also enhance scalability and maintainability. Additionally, integrating these tools with existing IT systems can further streamline processes and mitigate the risk of discrepancies caused by business or IT changes.

Equally, as business logic complexity increases, it is often a good idea to leverage an Excel control service. In the extreme case of both complex processing and anever-ending number of rows, both types of tools can be used in combination.

Calling on an Excel Control Service

Excel control services provide a range of functionalities that help maintain data integrity, improve governance, and streamline workflows, offering users multiple options to enhance their Excel control processes. Application of the tools can greatly reduce development timelines, potentially saving a significant amount of time and money. Some examples include:

• Coherent, which enables business users to better control their processes and publish workbooks via an Excel add-in to data pipelines
• Schematiq, which transforms traditional spreadsheets into agile, strategic tools, integrating with business workflows and applications
• Spreadsheet Server, which allows for seamless reporting and analytics directly from Excel
• ClusterSeven, which offers comprehensive spreadsheet management and monitoring solutions to ensure compliance and reduce risk.

Taking the example of Coherent, changes can be made instantly by the business as needed and, as far as business users are concerned, Coherent is just a simple Excel add-in as shown below.

Business Benefits of Coherent

The main benefit of Coherent is the reduction in Excel operational risk. Coherent enables version control of spreadsheet usage in the organisation with a searchable audit trail of every user interaction and underlying calculation. Each version is published and auditable as at the date of upload, so any changes can be itemised and reviewed with roll-back functionality to prior versions where required. It also has additional functionality such as data consistency analysis, large scale testbeds, and regression analysis.

Coherent also enables offline data and business logic from Excel to be published internally, which can feed into data pipelines such as feeds to databases, machine learning, data analytics, or interactive dashboards. Individual Excel workbooks are no longer siloed as any workbook with relevant permissions can download the data and business logic, thereby properly integrating the organisation’s Excel estate. Data transfers between published Excel workbooks are facilitated by simple add-in formulae, serving as a stepping-stone to full strategic end-to-end systems.

In terms of use cases, this functionality can be very effective when consolidating workbooks, such as by a group function that consolidates reports from various locations. A group function could also centrally control the business logic applied by local teams, as the official published version component can be downloaded to those distributed workbooks.

Conclusion

Using an Excel control service, such as Coherent, can greatly decrease Excel risk and improve the quality, security, and compliance of business processes. It also allows for the upload of offline data and business logic, which can be easily fed into data pipelines. In total, it provides enterprise controls for managing spreadsheets across the organisation and provides the much-needed transparency and auditability for users, auditors, and regulators.

Projective Group helps organisations manage the complexity of Excel and optimise their data workflows. We offer comprehensive solutions to address a variety of Excel challenges, including developing robust data pipelines, enhancing spreadsheet controls, and ensuring data integrity. For more information on how we can assist with your specific requirements, including proof-of-concept use case demonstrations and strategies to mitigate Excel-related risks, contact our team today.

About Projective Group

Established in 2006, Projective Group is a leading Financial Services change specialist.

We are recognised within the industry as a complete solutions provider, partnering with clients in Financial Services to provide resolutions that are both holistic and pragmatic.  We have evolved to become a trusted partner for companies that want to thrive and prosper in an ever-changing Financial Services landscape.