You never forget your first – spreadsheet. The romance between accountants and the spreadsheet shows no sign of diminishing, despite its many shortcomings. But the time wasted on bad spreadsheets, the problems caused by data duplication, inaccuracy and entry errors, the potential for fraud and misuse, the challenges of even straightforward modifications, and many of the associated risks can all be diminished, suggests FSN contributing writer Lesley Meall, with the support of specialist spreadsheet management tools.
When the VAT rate in the UK recently increased from 17.5% to 20% it caused all sorts of problems for all sorts of organisations. But if a review of the affected software and systems revealed more than a handful of spreadsheets, the problem was particularly challenging, because while some clever people built their spreadsheets without hard-coding the VAT rate, many others did not – and with the value embedded, they needed to run individual manual queries on numerous cells in numerous workbooks.
‘You could check each spreadsheet individually, cell by cell for each permutation of 0.175, 17.5 and 1.175,’ says Ralph Baxter, CEO of ClusterSeven, a provider of spreadsheet management solutions, but this is at best inefficient and ineffective, at worst, a recipe for disaster. ‘You would be reliant on user diligence being maintained all the way through to the millionth cell of the hundredth workbook,’ he adds, and even then you might miss a problem in the associated macro code or Visual Basic for Applications (VBA) code.
Users of ClusterSeven’s Spreadsheet Integrity, and similar products (see below) should have been exempt from such worries. Because these tools can be used to automate the hunt for permutations of the VAT rate, this could be simplified and speeded up; then, armed with a report on where the problems were, valuable time could be spent fixing them, rather than wasted on finding them. But using specialist software tools to check the logic in spreadsheets is just one of the many ways of making them more of a boon and less of a burden.
Nationwide Building Society uses a spreadsheet management system to ensure compliance with Basel II, Sarbanes-Oxley and the Markets in Financial Instruments Directive (MIFID). Greater Bendingo Goldmines uses spreadsheet management software to control and manage the proliferation of spreadhseets, and the many versions of them, that occur during its budgeting processes. Satellite broadcaster DirecTV uses an automated spreadsheet control solution to enforce ‘best practice’ among all of its spreadsheet users. But these are just the tips of a number of rather large icebergs.
Losing the war but winning the battle
‘We all know that the war of fighting the proliferation of spreadsheets in enterprises has been fought and lost,’ observes Boris Evelson, an analyst with Forrester Research. There was a time, a few years back, when some software vendors tried to market their products on the basis that they could eliminate spreadsheets, but this approach has quietly been replaced by something that pragmatically accepts that the spreadsheet will be with us for the foreseeable future. Instead, software vendors now focus on providing tools that make spreadsheets easier to use and manage, and to minimise the associated risks.
So, how do they do this? By providing tools to enable managers and/or end users to control, monitor and manage changes, version numbers, access (read, update, delete etc), circulation and input – just like other controlled IT applications. They also address issues such as the security and integrity of spreadsheet data, documentation, development lifecycle, back-ups, archiving, logic inspections, analytics and investigations, the segregation of duties, roles and procedures. Some cover just spreadsheets, others cover other end-user applications (EUAs) such as Microsoft Access, SQL Server, and BI tools. The features on offer vary depending on the developer, the range of its family of services and products and their particular focus.
Among specialist product and service providers such as Actuate, Cimcon, Finsbury Solutions, Lyquidity, Spreadsheet Advantage and Prodiance, you will find a broad range of tools on offer, including generalist tools for managing spreadsheets and automating the spreadsheet management lifecycle, tools designed to meet special needs in areas such as business intelligence and financial planning, and governance, risk and compliance (GRC) tools to monitor and enforce corporate spreadsheet policies and support compliance with legislation such as Basel II, MIFID, Sarbanes-Oxley (Sarbox), Solvency II, and so on.
- Actuate focuses on open source BIRT, business intelligence and reporting tools. The range of products and services includes the BIRT online spreadsheet (formerly e.Spreadhseet), and the BIRT Spreadsheet Option, which may be used to automate and centralise spreadsheet production, distribution, archiving and security.
- Cimcon offers two product ranges, one aimed at FDA-regulated life sciences industries, and another offering financial solutions; both feature products designed for use with spreadsheets. The financial range focuses on enterprise governance, risk and compliance (GRC) solutions, for regulations such as Sarbox, Solvency II, the NAIC Model Audit Rule, MIFID, and more.
- Finsbury Solutions provides GRC solutions to manage spreadsheets and other end-user apps (EUAs), such as MS Access, SQL Server and BI applications (EUC Enterprise). They address access control, version history, audit, comparisons, analytics, and workflow (Spreadsheet Workbench), forensic investigation and analysis for auditors (EX Checker), and more.
- Lyquidity provides a framework (ComplyXL) for managing spreadsheet versions and audit controls. A number of free utilities (such as a spreadsheet usage monitoring utility) and add-ins (such as a work-book statistics add-in) are also available for analysis of the information in spreadsheets – and there is a viewer for formulas that can make them easier to understand.
- Spreadsheet Advantage is an add-in for Microsoft Excel. It offers a range of tools for recipients and developers of spreadsheets. They can improve version control and circulation, make it easier to analyse the structure of worksheets and the structure and complexity of spreadsheets, compare files or worksheets, understand the calculations in them, and check for formula consistency.
- Prodiance provides GRC products for controlling and managing spreadsheets and other EUAs such as databases and BI tools. The Enterprise Spreadsheet Manager can automate the entire spreadsheet management lifecycle, eDiscovery addresses inventory and risk assessment; Spreadsheet IQ offers diagnostics, and the Link Migration Manager does what it says on tin.
Just as spreadsheets are used for a variety of purposes, there are a variety of reasons for wanting to manage them in a more coherent way. But whether you want to minimise errors, make your financial close more efficient, better facilitate collaboration, eliminate fraud, monitor and minimise the risks associated with spreadsheet use, or need support complying with mandatory legislation, there are spreadsheet management tools available to address all of this, and more.
