There is more than a hint of scandal drifting around the world of the humble spreadsheet. Or not so humble, as the case might be.

Financial experts, analysts and the IT executives being told to “do something about it” have become acutely aware that spreadsheet software such as Excel and its sister product, the Access database, are being used in highly inappropriate ways – ways that are compromising the integrity of many businesses’ decision-making data and threatening to undermine compliance commitments.

The argument is well observed by analysts. Senior and middle management – at companies of all sizes – habitually use Excel and Access to build what are essentially user-defined applications. “Most highly regulated companies use spreadsheets as the final, undocumented connections between their ERP system and their financial and tax reporting,” says Jay Heiser, a research director at Gartner.

The industry adviser points to financial services companies using elaborate sets of linked spreadsheets to price exotic financial derivatives and track their exposure; wholesale energy market traders using Excel to track their asset valuations; pharmaceutical companies involved in drug trials using it to consolidate their results prior to submission to regulators; and, more universally, the spreadsheets used by ‘power users’ in all levels of organisational decision-making.

What they are all doing is using the powerful capabilities of Excel and Access (notably the built-in ODBC database connectivity facility) to extract data from ERP systems and feed that into their desktop spreadsheets for manual manipulation and analysis, often with the rekeying of data that is supposed to be a fixed record of business activity. Most organisations are living with these ‘spreadmarts’ or ‘shadow data systems’ on a ‘Don’t ask, don’t tell’ basis, says Heiser.

The prevalance of those applications (and given the logic embodied in them, there is no doubt they are applications) is only now being realised. One of London’s largest public organisations recently audited its applications portfolio with a view to consolidation and the assessment of its risk exposure. It identified 11,200 separate applications, most of which were built by individuals or departments using Excel and Access.

And at a recent conference on information management, Lloyds TSB’s head of group IT architecture and design, Chris Nottage, said he would not hazard a guess at the number of applications his organisation was running as so many of them were based on spreadsheet programs.

Such confessions are by no means exceptional. A recent survey by The Data Warehousing Institute (TDWI) of 204 companies (three-quarters with revenues of $100 million or more) found that 90% are using spreadmarts to a significant degree.

Declaration of independence

What has triggered that proliferation is the inadequacy – or at least a perception of the short-comings – of more tightly controlled alternatives.

ERP systems that power most business’s operations don’t make particularly easy analysis platforms. Their data structures are set up for transactional models, and as executives such as John Riley, VP of business systems at drinks company Constellation Europe, have found, the conventional means of sucking data from ERP systems is often so slow that it drives users to take that on themselves. At his organisation until relatively recently when the situation was brought under control (see article ‘Stellar analytics’), “anything that could be going on was: ODBC access with Access, ODBC access with SQL Query Analyzer, ODBC access with Excel” – to the point where IT staff were regularly being stopped in the corridor by users seeking advice on how to compose complex SQL queries or service these home-grown applications.

Arguably, business intelligence is supposed to address just such demand. But, in many cases, it only does half the job. The fact is that BI tools typically extract data to an offline database, so that queries do not impact the transactional activities of the ERP system and so the data can be structured appropriately for analysis. For some users, a snapshot is good enough, but for others nothing but the operational data will suffice – even if that means they have to input items into their own spreadsheet.

Furthermore, as Wayne Eckerson, director of research at TDWI, points out, BI tools do not always support the types of complex analysis, forecasting or modelling that business analysts need to perform, or they may not display data in the format executives desire. “Spreadmarts often fill a business requirement for information that IT cannot support in a timely, cost-effective manner. [So it] should not be an entirely pejorative term,” he says.

If a financial controller is trying to close the company’s monthly or quarterly books, for example, Excel is very often the primary vehicle. Says Heiser, “In an ideal world, all financial material and highly regulated information would be processed in well-managed, purpose-built applications. In the real world, ever-changing conditions mean that traditional forms of IT application development are too inflexible, slow and expensive.”

But the business risk inherent in this widespread use of shadow data cannot be overstated.

Corrupt consistency

On a day-by-day basis, spreadmarts are the enemy of data consistency, leading to confusion and decision paralysis. TDWI’s respondents talked of ‘duelling spreadsheets’.

Murray Trim, a management accountant with Foodstuffs South Island, a New Zealand-based co-operative supermarket chain, says: “We have had the classic situation of two people presenting ostensibly the same data at a board meeting with different figures, which they got from different spreadmarts.”

The problem with spreadmarts, explains Eckerson, is that their creators use different data sources, calculations, calendars, data conversions, naming conventions and filters to generate reports and analyses based on their view of the business.

But there is another reason for the delivery of inaccurate data: users are inadequately skilled – often overconfident – in Excel, Access and ODBC programming. They access the wrong tables, struggle to execute joins and make other mistakes that are not picked up by the kind of quality assurance that would normally be applied to IT-generated applications.

Those facets of spreadmart deployment undermine trust in the data – and in subsequent decisions. Donna Welch, a BI consultant at financial holding company BB&T, told TDWI researchers: “We constantly hear our users talk about management’s distrust of their reports because multiple people came up with different answers.”

Presenting figures internally is one thing; presenting them to the Financial Services Authority, the Securities and Exchange Commission or another regulatory authority is a completely different matter. The consequences of the widespread use of spreadmarts as the basis for financial consolidation and regulatory reporting are scary, says Gartner analyst Neil Chandler (see article ‘Q&A: In search of integrity’).

Are the regulatory authorities aware of the situation? Are government agencies putting pressure on companies to address these issues? “Absolutely,” he says.

“I have heard of audits being refused on the basis of the auditors looking at the internal systems and deciding they are not robust enough. And I even heard of organisations replacing spreadsheet systems in order to pass audits.”

Even more serious is when numbers are submitted as the ‘truth’ to regulatory authorities. Nick Gomersall, senior VP of worldwide sales at The GL Company, a vendor of real-time ERP query and reporting products that address the problem of spreadmarts, predicts that a string of cases will emerge where companies are forced to restate their results after their submitted spreadmart-derived numbers don’t stand up to scrutiny.

“If there are inherent errors, they can be incredibly costly to the organisation,” says Chandler. “For example, if you have to restate your financials, that can have a huge impact on your shareholder value. Alternatively, if it is found that there is fraudulent misuse of data then of course someone in the finance function, particularly the CFO, may find themselves going through some legal process and, in the worst case, even end up in jail.”

In short, says Eckerson, “spreadmarts expose organisations to significant risk.”

There are aspects to shadow data systems that other watchdogs might be interested in. By turning a blind eye to unfettered access to corporate data (outside the controls of the ERP system), organisations often ignore their normal security controls.

Constellation Brands, for example, operates two distinct business units in Europe. It wanted to ensure that users could only access the appropriate data on the right system. That was achieved by choosing a query and reporting toolset (The GL Company’s Really Real-Time Inquiry Suite) that not only closed down the previously uncontrolled access to operational data but also inherited and applied users’ security profiles from the underlying ERP system. “That is a big plus,” says Constellation’s John Riley.

Do something!

At a high level, the solution is to bring control to spreadmarts, making that more rigorous approach attractive to users, while not enforcing spreadmart elimination altogether.

“The technical remedy is to manage and store data and logic centrally in a uniform, consistent fashion and then let individuals access this data using their tools of choice,” argues Eckerson.

That discipline can be applied in various ways: by transforming spreadmarts into managed spreadsheets; by moving the analytics to corporate performance management architectures; by the adoption of financial governance products, some of which provide query and reporting tools that interrogate the ERP database itself – thus ensuring that users are always working with consistent data.

But the problem is not going to go away any time soon – not least of all because the users involved in the creation of spreadmarts are some of the most powerful figures within companies – and they are going to take some persuading that IT can show them a better and less risky way.

However, leaving spreadmarts alone is not an option. TDWI’s survey group suggests the most effective approach is to provide a robust BI solution and related tools that integrate tightly with Excel.

Gartner’s Chandler predicts a stream of spreadmart replacements over the next few years, with management tools enforcing controls over data access and reporting. Some of that will be aided by providing direct – but managed – access to operational data.

As Wayne Eckerson, the analyst who first coined the term spreadmart back in 2002, puts it: “Memo to IT: Deal with it!”