Avoiding spreadsheet Hell

Spreadsheets are ubiquitous, but they are also a major source of risk, as high-profile examples have shown. Tom Groenfeldt reports on how firms ensure spreadsheet integrity

The JP Morgan Task Force Report into its Chief Investment Office’s $6 billion-plus loss found the bank’s Value at Risk was being calculated with an Excel spreadsheet that “required time-consuming manual inputs to entries and formulas, which increased the potential for errors”.

At another point the report found “the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another”.

JPM is not alone: more than half of c-level executive in financial institutions say they have few or no controls over critical spreadsheets at their firms.

Nearly nine in ten (89%) rely on manual oversight to maintain data integrity, while only 11% report automated controls policy to fully understand changes between different versions of spreadsheets.

The research, conducted by Vision Critical for ClusterSeven, which sells spreadsheet control software, seems to indicate a large gap between executives’ concerns and their actions to manage risk. Just over half (55%) of the top executives rate spreadsheet risk as either serious or very serious.

“Financial services firms, and the senior managers and executives that run them, rely heavily on spreadsheets for much of their business critical processes,” said Ralph Baxter, chief executive of ClusterSeven. “However, there are significant risks associated with this and all stakeholders are now waking up to what these are. Risks include anything from basic ‘cut and paste’ errors to miscalculations, fraud and corrupted files.”

Excel, easily the most widely used tool in finance, creates risk in the financial enterprise. Designed for the desktop, its power and ease of use have led to its widespread use in analytics, trading, modelling and reporting. Excel, designed for individual users, lacks controls, lockdowns and audit trails.

PWC has built an entire consulting practice around Excel, beginning with identification and risk measurement of end user computing (EUC) within a firm. “PwC recognises that EUC‘s are here to stay. Rather than attempting to remove them, we believe that organisations should understand their use and make sure that they are properly controlled,” the company explains. It calls for developing a governance framework and then deciding when and where software should be used to monitor and control spreadsheets.

Implementing spreadsheet management often uncovers material errors in a company’s accounts. In helping one firm move to an ERP system, PWC exported data out of hundreds of spreadsheets and modelled the results, which uncovered “a number of significant errors.”

The consultancy notes that a number of regulations including Solvency II, SOX and Basel III will require improved controls over spreadsheets.

In London, the FSA has required financial firms to manage their spreadsheets, especially those used to feed their capital adequacy modelling under Solvency II, said Mark Allen, head of business intelligence at Canopius Managing Agents, an international insurance and reinsurance group based in London. It turned to ClusterSeven.

“We have approaching 1,000 key business spreadsheets managed under ClusterSeven,” he said. “We haven’t done all of them, and that is too many to be honest: some spreadsheets don’t have material effect.”

“Whenever possible we use SQL for financial reporting systems, but it’s not realistic to replace spreadsheets. While we have our more complex Solvency II data in databases, there will always be the need to pull together information for presentations in spreadsheets.”

Canopius found ClusterSeven didn’t create problems for business users. “Some comparable systems are quite invasive. This just sits in the background and tracks changes. It’s much easier to roll out software when it doesn’t get in the way of users.”

The principle can be extended to other off-the-shelf applications. ClusterSeven offers similar controls for the MS Access Database, which is tucked away in thousands of organisations running vital departmental tasks, often little known to anyone outside the group where it operates.

Another approach to improving on standalone Excel is available from WestClinTech in the US. It has built XLeratorDB which can operate refined Excel calculations, and additional financial calculations, inside SQL Server where they can run up to 100 times faster than Excel and are fully protected by server security.

A commercial real estate financing company in the US with a market cap over $7 billion uses XLeratorDB to improve reporting and projections. When it relied on Excel spreadsheets, individual asset managers in different regions developed their ways of using the models, the firm’s chief information officer said.

“The process was inconsistent, there was no visibility,” he added. If a regional manager failed to update quarterly results in Excel, that would not necessarily be apparent to headquarters. “It only took one or two managers not to update their numbers to realise that we needed to correct this.”