Managing spreadsheet risk in financial services

By Ralph Baxter, CEO, ClusterSeven Transparency, control and validation of critical business data is an increasingly serious concern for the financial services sector. Banks, insurance companies and actuarial firms are facing up to the fact that they need to tackle the data risk issue as a matter of urgency. However, a major part of this …

August 15, 2011 | Clusterseven

By Ralph Baxter,

Transparency, control and validation of critical business data is an increasingly serious concern for the financial services sector. Banks, insurance companies and actuarial firms are facing up to the fact that they need to tackle the data risk issue as a matter of urgency. However, a major part of this challenge lies outside the knowledge of IT departments – this is the data held and generated in spreadsheets or Access databases. These are increasingly called end user computing applications (EUCs), or user-developed applications (UDA).

Not surprisingly most major financial institutions now have programs to address these risks with a significant number having implemented global control solutions.

One of the main drivers for this new focus has been the number of recent high profile examples where financial institutions have incurred major operational losses or received regulatory fines for a lack of effective risk management and control of end user spreadsheet activity.

A second driver is the fact that the financial services industry faces a raft of significant new regulation and audit scrutiny, such as Solvency II. But how exactly should firms ensure that they introduce effective controls? Is it a question of getting rid of spreadsheets altogether?

There is no doubt that each wave of business change (eg accounting standards, regulatory reporting, stress testing, TARP funding etc) ensures that end-users must use spreadsheets as the immediate solution for these new critical operational requirements while simultaneously putting their employers at increasing financial and reputational risk.

These risks include reliability problems, human error, complexity, capacity limits, lack of transparency, security and a lack of auditing controls.

The issue was recently highlighted in the FSA’s Solvency II Internal Model Approval Process, which noted that spreadsheets in many organisations are not controlled by IT but by other business or control areas, and thus do not form part of their corporate governance processes.

While it is encouraging to see the FSA and major institutions treating spreadsheet risk as a serious issue, there are still vast numbers of organisations leaving themselves open to the danger of financial and reputational risk by not establishing an ownership policy.
Yet, despite a desire to replace spreadsheets with robust, centrally-managed applications, the reality is that spreadsheets are often the only solution that can address immediate needs.

For example, treasury management systems (TMS) were not developed to manage each new and creative financial instruments. The only logical step has been to use one or more spreadsheet to record millions or even trillions of dollars worth of transactions and associated repayments and risk.

Spreadsheets therefore play a vital role in managing treasury functions that are not yet included in core TMS, which are always necessarily slightly behind the business curve. Beyond the specific needs of new instruments, treasurers will always need a tool that manages extra data or applications around the outside of the TMS.

Yet as soon as data is saved in a spreadsheet, it presents another version of the truth. Treasurers then have some data in the TMS and some in spreadsheets. In order to present a unified picture to the CFO, they will, at some stage, need to combine that data, and this introduces further potential for risk.

The purposes of spreadsheets are widespread, from performing complex modelling for trading decisions to accounting reconciliations and financial reporting. A review of a typical corporate network would reveal thousands to millions of spreadsheets in use. The most pressing question that needs answering is: who manages these spreadsheets and ensures that the results they produce are valid?
According to a study by Deloitte (1.), 70 per cent of companies rely on spreadsheets to support their business-critical financial reporting. Bodies such as the Institute of Internal Auditors, the Financial Industry Regulatory Authority, and the Public Company Accounting Oversight Board have all demanded more attention from auditors.

On their own, user-developed applications such as spreadsheets and Access databases have demonstrated the flexibility to support many processes over recent decades. However, it is becoming clear that without careful monitoring and management they may lack the robustness to meet the demands of increasing compliance with regulation such as Solvency II in Europe.

This is not because of user error: by the nature of their work most financial professionals are extremely careful and accurate in their calculations. It is the sheer complexity of interdependent spreadsheets across the business that organisations need to be aware of.
Indeed, there is no question that financial professionals would be very unhappy if asked to relinquish their use of spreadsheets for calculating risk. Spreadsheets are a vital component of day-to-day calculations, tasks and processes. For companies facing the obligations of new regulation it is therefore a major challenge to see how they can maintain this critical flexibility but also demonstrate overall control to the authorities.

So rather than attempt to eliminate spreadsheets from the business, companies need to accept that spreadsheets need to be used – but in order to satisfy the regulators, they need to know when and where spreadsheets are being used.

The first step is therefore to understand what they have, where it is and how it is connected to their business applications. Using the right tools, they can automatically scan their networks to intelligently locate key spreadsheets and Access databases. This builds a complete dependence tree that demonstrates the relationships between files with multiple connections.

Companies are typically surprised not just by how many spreadsheets they are using across the business but also how they are connected – firms can discover many hundreds, sometimes thousands of individual spreadsheets feeding into hubs that support core business processes.

While companies feel that they manage these processes accurately and effectively, it is unlikely that they will have seen the full extent of the spreadsheet estate, and their interdependent relationships. They may even be surprised when asked by the CIO or CEO to provide a detailed picture of spreadsheet use, as their department will necessarily have been running smoothly with regular checks and balances over many years.

By allocating direct responsibility and establishing a unified risk management process, organisations can start to mitigate the threats they face. In some companies spreadsheet risk is not even on the agenda; it is only when a serious financial mistake occurs that this subject is given priority.

This means that the main trigger for firms to invoke an investigation is a request from the business to document and explain their internal model. Of course it is possible to trawl through every user application manually, but this can result in a discovery outcome that not only takes an extraordinary amount of time, but that is also out of date once completed.

The increased regulation and compliance that now impacts spreadsheet usage is not surprising given that the past number of years have seen numerous multimillion-pound errors and frauds attributed to the use of spreadsheets. Central control and clear allocation of responsibilities will help ensure that the risk presented by spreadsheets is understood and appropriately managed.

1. Deloitte report can be assessed at



Top Strategic Technology Trends for 2021: Privacy-Enhancing Computation

White Paper | Behavior detection & predictive analytics Top Strategic Technology Trends for 2021: Privacy-Enhancing Computation

Quartz™ Magazine - The New Age

Case Study | Consultants Quartz™ Magazine - The New Age

TCS Financial Solutions
Quartz™ Magazine - The Future will be Tokenized

White Paper | Infrastructure/architecture Quartz™ Magazine - The Future will be Tokenized

TCS Financial Solutions
Euroclear Finland Modernizes with TCS BaNCS for Market Infrastructure

Case Study | Behavior detection & predictive analytics Euroclear Finland Modernizes with TCS BaNCS for Market Infrastructure

TCS Financial Solutions