Legacy systems II: Managing spreadsheet risk

Nicholas Pratt looks at the risks posed by the use of spreadsheets and how firms should approach the management of such a risk.

When the subject of legacy systems and manual processes come up, the Excel spreadsheet is continually held up as the prime example of the genre. It has proved to be an enduring application for our times but, in a world where IT departments are increasingly concerned about IT governance, the unaudited use of Excel spreadsheets, Access databases and so-called end-user computing (EUC) or ‘shadow IT’, poses an increasingly conspicuous operational risk.

Consequently we have seen a number of new firms emerge that are dedicated to tracking the use of shadow IT, highlighting the potential risks and suggesting a way of managing any downside while maintaining the significant upside that still remains from such applications.

“It is not that using spreadsheets is right or wrong but firms need to be aware of where they are being used,” says Henry Umney, chief executive of Cluster Seven. “Firms need an inventory. They need to prove that IT has a handle on this. If you wind the clock forward ten years, it is now a common theme that people understand.”

Regulators have certainly become more attentive of this risk. “Regulators want to know the complete extent of EUCs and where they reside,” says Umney. “Firms need a process map as well as an IT map. An inventory is key to that, but you also need to know the roles and responsibilities and the attestations around that inventory. For example, how important is each of these EUC s? How key are the processes and how do they affect the organisation?”

Unfortunately, firms have not managed this very well in the past and it has taken time for firms to adopt the best principles. Although Sarbanes-Oxley formalised the issue of IT governance, it was always seen as a bit of a box-ticking exercise. The financial crisis changed all that, not least because it rocked the confidence in financial models and the notion that they were infallible.

However, the focus on model validation has created another issue for firms, says Umney. At a lot of organisations, the audit of EUCs and model risk management (MRM) have been on parallel courses, but these processes need to be unified, he says. “The assumptions and methodologies behind the two have to be consistent and shared across the organisation.

“The EUCs are immature compared to the models, but a lot of the models will take their data from spreadsheets and tools and calculators that fall into the EUC category and in the control of end users. There is an overlap that has been largely ignored, but now we are starting to see some firms take over the process and look to merge the two.”

Esoteric strategies
The transparency requirements of Solvency II have led more firms to look at their EUCs, while the low interest rate market has led more managers to look to more esoteric strategies to generate yield and to carry out the initial valuations on an Excel spreadsheet before being fed into core systems.

For Alan Barry, chief executive and founder of Ireland-based Fund Elements, says that the embedded risk of modelling error is the main issue for firms to consider, especially if the individual who builds the model does not engage in any peer review.

In the absence of peer review and basic IT best practice, this embedded error can go unnoticed for years until a spreadsheet is handed over when someone leaves, a formula becomes corrupted or the wrong version of a spreadsheet is copied or forwarded.

“A key weakness of spreadsheets is that data and logic is not separated as it is easy to copy and paste data and overwrite a formula,” says Barry. “This also leads to a second weakness where the logic is not protected in a read-only server and as a result, there are change controls when updates are made. These are basic practices that you would expect to find in IT-supported solutions.”

Beyond the front office, there is also the risk of data leakage, says Barry. “People in large asset servicing enterprises still put portfolio information into spreadsheets on a regular basis and send them as attachments to clients. If you get the email address wrong, you have lots of issues.”

The large volume of files involved makes it hard to identify the risks and requires some level of automation to identify the high-risk spreadsheets, but once identified, the risks are mainly behavioural, says Barry, who is planning to use some form of AI to help with the identification, tracking and assessment of risk.

“If a file exists, why does it exist? Once you know this, it is easy for the person who created it or is updating it to provide a level of documentation and oversight so that it fits into the overall policies of the enterprise,” says Barry.

So, who should carry out the management of spreadsheet risk? According to Umney, it should be a combination of operational, risk and IT staff, but it often ends up being just IT because it is on a screen. “It is OK for the project to be initiated by IT, but it has to be policed by the risk department and involve the three lines of defence – IT/business/risk and audit,” he says.

And while technology can help with the process, it cannot be expected to automate it completely or remove any need for manual oversight. “Technology can certainly help in providing insight into the inventory and to challenge the veracity of the models. But you need to understand the models and the tools and the EUCs and make sure the inventories are complete and to understand the materiality of these tools, which is a manual task in many ways.”

Umney does not expect to see the complete removal of spreadsheets. “If you could replace them all, firms would have done so,” he says. This is not because they have found the tasks too difficult and have given up. It is because they have recognised that EUC is a strategic part of their IT architecture. “If a portfolio manager wants to test a new trading strategy, they have to provide a spec to IT, who have to model it and test it and that can take up to three months. What you want is the flexibility to start that up and then ultimately feed it into a more regulated model environment,” says Umney.

Barry agrees that the flexibility offered by spreadsheets, along with the limited ability for enterprise IT to handle the demand for new solutions, means that they are not likely to be extinct any time soon. “I can never see the phasing out of shadow IT due to the capacity issues with enterprise IT. In 1988, I used a Lotus123 spreadsheet to run bank reconciliations,” he says.

“The IT department at that point didn’t even know we had a PC. It was prior to networked systems, when IT’s focus was mainframe computers. In 1998, I ran a project to identify shadow IT use at Credit Suisse First Boston in London as part of the Y2K preparations. The intention was to eventually eliminate spreadsheet use. Twenty-one years on, the issue of shadow IT is bigger than ever.”

©2019 funds europe



Innovative US companies are providing some of the solutions to the climate crisis and transition to a more sustainable economy. We see potential opportunities in areas including renewable energy and…
This white paper outlines key challenges impeding the growth of private markets and explores how technological innovation can provide solutions to unlock access to private market funds for a growing…


Visit our dedicated Ireland channel for all the latest news and analysis on the country's investment industry.