Contents
So why are spreadsheets so popular?
Organisations across the world have enjoyed the unfettered use of spreadsheets for more than two decades. Spreadsheets have liberated end users from the limitations of traditional IT and permitted them to develop rapidly a whole raft of critical business applications that help them to manage more effectively and compete more vigorously. By any measure the success of spreadsheets has been astonishing. They have become part of the corporate 'DNA' and so deeply embedded that, in practical terms, there is not a business process, industry segment, functional area or geography that does not rely to some extent on spreadsheets.
However, regulators, auditors and organisations themselves are discovering that over time, 'harmless' spreadsheets designed initially for personal productivity and effectiveness have evolved into fully fledged business critical applications or play a vital role in supporting them. For many, this high degree of reliance on spreadsheets which are informally developed, maintained and used represents a serious operational risk. For example, almost every business professional, from whatever discipline, has had first hand experience of how easily errors can arise because business logic has been changed, a cell reference has inadvertently been overwritten or data has been misplaced. Added to which there are many publicly recorded instances of spreadsheet errors giving rise to serious financial loss or reputational damage.
Although many organisations view the risk of spreadsheet errors very seriously the remedies available to counter the threat in the past were not particularly attractive. Imposing spreadsheet development standards and policies may sound very worthy but in practice is almost impossible to enforce and monitor in a large global organisation. Banning the use of spreadsheets is unworkable and replacing them with traditionally developed IT applications would be costly and unrealistic. Unsurprisingly, faced with these unpalatable options many organisations prefer to do nothing. So the dilemma of how to introduce an acceptable level of control without causing disruption and stifling business competitiveness remains.
But what if business critical spreadsheets could be isolated and monitored unobtrusively? What if management could be alerted immediately to changes in a spreadsheet deemed by them to be risky? What if the proactive management of spreadsheets could benefit the organisation more broadly?
These are just some of the new possibilities available from ClusterSeven, as a result of their groundbreaking technology, best practice methodology and tools in the newly emerging field of Enterprise Spreadsheet Management. It is their pioneering product, Enterprise Spreadsheet Management version 2.3 that is the subject of this review.
ClusterSeven was formed as recently as 2003 but used its first two years of operation to develop the technology that underpins the latest release of its software, working closely with the audit profession to pave the way for its completely novel approach. Headquartered in the City of London , and with business closed exceeding £3 million, the organisation targets large multinationals with complex business critical spreadsheet applications. Financial services companies carrying significant operational risk in trading spreadsheets have been amongst the first to appreciate the virtues of the ClusterSeven solution, closely followed by energy trading businesses and insurance. However, the product resonates well with any organisation or vertical market that places heavy reliance on complex spreadsheets.
Although the initial business growth has been driven by compliance and risk management, the early experience gained by ClusterSeven illustrates that effective spreadsheet management can lead to some unexpected benefits in terms of management reporting and information security. The business is now seeking to parallel its success in London by opening an office in New York.
The uniqueness of the ClusterSeven solution is that it allows organisations to preserve their spreadsheet heritage where necessary and focus merely on the spreadsheets deemed to present an unacceptable level of risk. This requires an understanding of why spreadsheets are successful as well as why they present a risk.
So why are spreadsheets so popular?
Spreadsheets arrived in tandem with the personal computing revolution of the early 1980's. Individuals without deep programming skills quickly discovered that they could rapidly build highly tailored applications out of the gaze of the IT department without any programming knowledge or formal training. Uncontrolled and unconstrained, end users could pave over the cracks in any application area and speedily develop innovative solutions to specialised business needs, for example, where software packages were unavailable, unsuitable or unaffordable. Empowered by the technology, and frustrated by the cost and lengthy development timescales often associated with traditional IT development, users relished the newly found independence that spreadsheets gave them.
Over the years, businesses have benefited enormously from the creative application of spreadsheets to business problems. For example, in the finance function, investment appraisal, scenario planning and strategic modelling would be unthinkable without the aid of spreadsheets. Similarly, major data collection processes such as the annual budget, quarterly re-forecasts, and monthly reporting pack would be unworkable on a global basis for many enterprises were it not for data entry templates crafted in Excel. In addition, many departments have created specialised applications for treasury exposure, tax calculations, monitoring bank covenants or perhaps modelling the impairment of assets under International Financial Reporting Standards, (IFRS) rules. Indeed, for many finance professionals, spreadsheets have been the natural answer to IFRS by providing a flexible means of assessing their impact before committing to permanent changes to their financial systems.
Despite the resounding success of spreadsheets in promoting user productivity, corporate creativity and competitiveness, their use in practice has been dogged by concerns in two main areas. Firstly, the informality with which applications are developed and maintained and secondly, the ease with which their integrity can be compromised when used in live operation.
That is not to say that spreadsheets are created without care and professionalism but rather that they are not subject to the rigours of proven and recognisable development methodologies. Very few organisations have policies and procedures in place governing their development despite the fact that they are business critical applications in all but name. So it is not unusual for spreadsheet applications to be developed without a specification, data definitions, interface specifications, user testing, documentation and external quality control - matters that are second nature in any traditional IT development. Auditors argue that it is this lack of formality which renders the spreadsheet unreliable and can expose the organisation to financial loss and reputational risk.
Even where spreadsheets have been developed according to rigorously laid down standards, these controls can be undermined at a stroke because of the ease with which users can change (deliberately or inadvertently), say, the structure, business logic, and data, creating serious 'unseen' flaws. For example, many group finance professionals have experienced unexpected changes to "standard" spreadsheet templates used for budget data collection or monthly actuals as users in reporting units overwrite a cell, change a description or insert an additional row to cover a local event or need. The problem is exacerbated because these changes are only trapped at a very late stage of the reporting cycle giving rise to significant reworking and delays. As a result, many organisations spend enormous effort checking spreadsheets manually or building validation logic into spreadsheets in an attempt to identify spreadsheets that have been altered without authorisation before they are processed. All of this adds to the hidden time and costs involved in managing a spreadsheet based process.
Many have argued, (especially IT professionals, vendors and auditors) that this is sufficient cause to abandon spreadsheet bound processes and move to fully fledged applications such as web based data capture systems that are very much in vogue for budgeting and forecasting. However, end users often regard the web as a poor substitute for budget calculations 'on the fly' in a spreadsheet. As a result, budget holders often continue to use their badly constructed legacy spreadsheets off-line in order to provide the data entry required by the web based system. In effect, the spreadsheet problem has not gone away, but merely been pushed out of sight and exactly the same risks remain.
The starting point for any ClusterSeven deployment is for the organisation to identify which business spreadsheets contribute operational risk . These are 'notified' to the ClusterSeven software which then works like a surveillance camera monitoring every user interaction, if desired. Like surveillance cameras, the software can be used to monitor spreadsheet activity constantly or take snapshots of activity at pre-defined intervals.
Snapshots of activity can be saved at pre-defined intervals
Similarly, the surveillance monitors at all levels of granularity, be this a whole workbook, a work sheet, a named range or just an individual cell. Alternatively, a recordable event can be simply the opening, closing or saving of a workbook. Irrespective of the trigger, each event results in a copy of the spreadsheet being taken and de-composed (extracted and transformed) into individual change elements that are written away to a Microsoft SQL database for future analysis. If required, these elements can be reconstituted to re-create the original spreadsheet. As such, the ClusterSeven software provides not only a complete history of spreadsheet changes but also a valuable backup so that business critical spreadsheets can be recreated through the system in the event that important work is corrupted or lost. In practice, ClusterSeven reports that many organisations have found it quicker and easier to retrieve spreadsheets through their dedicated server than through the manually-oriented back up and restore routines of the IT department which tend to archive data off-line.
Clearly, ClusterSeven's customers have the best understanding of their processes, their current inventory of spreadsheets, the power users and the areas likely to foment operational or reputational risk. Most large organisations with an internal audit department will have regularly carried out risk assessments and so ClusterSeven's customers are usually in the best position to perform the 'discovery' phase of an implementation which is designed to home in on the high risk areas. Additionally, it can be extremely difficult for an outsider to 'reverse engineer', say, a massive trading spreadsheet built up over the years without documentation. However, working in concert with client staff, ClusterSeven can identify the individual cells, named ranges, input areas, formulae and macros that are critical to the integrity of the spreadsheet. Whilst ClusterSeven's technology is perfectly capable of identifying any change to a spreadsheet the purpose of the risk based approach is to cut out unnecessary 'noise' by only reporting those changes that represent exceptional changes, for example, where one wouldn't expect a change, or where one would expect a change, but limited to prescribed limits.
Financial applications are prime candidates for the ClusterSeven treatment. For example, the strategic planning system used as the basis of shareholder presentations where assumptions about the weighted average cost of capital have been inadvertently changed; an interest calculation has been corrupted by a moved column; or a copied formula has picked up the wrong comparatives. Or perhaps a group reporting system where a subsidiary has altered its brought forward numbers, added an additional row of description, changed the group's notional tax charge, or overwritten the budget exchange rate.
Whilst many years experience gives finance professionals a sixth sense of where problems are likely to occur, ClusterSeven's technical analysis of where spreadsheets are likely to go wrong is invaluable. They have identified around a dozen traceable changes that are most likely to suggest an error in addition to the 'normal' cell errors which Excel would highlight such as #VALUE, # ERROR and #REF? Special conditions that ClusterSeven can monitor are divided into changes relating to constants, changes relating to data and changes affecting functions.
ClusterSeven can detect when a fixed numeric value within a formula is changed or when a when a fixed numeric value is entered into a formula within a cell. Other detectable changes around data include the population of a previously empty cell, the value in a cell is simply altered, overwritten by a formula or deleted altogether.
Whilst any inappropriate change could lead to an undesirable outcome, ClusterSeven is perhaps at its smartest when monitoring function changes since there are fewer visual clues than a data change and the affects of an unplanned change are usually more devastating to the integrity of the spreadsheet.
A "function to data" change highlights one of the easiest errors to make in a spreadsheet but not necessarily at all easy to detect. A "function argument change" occurs when the argument (usually a cell reference) within a formula is changed but without any changes in operands and a "Function Change" condition arises when a function formula within a cell is replaced by another function formula. ClusterSeven will also trap the addition of a formula to a previously empty cell or the straightforward deletion of a cell.
Clearly, depending on the application, many of these changes are perfectly normal within a spreadsheet setting. Afterall this is exactly what spreadsheets are designed to do. The challenge is to reduce the level of 'noise' i.e. to distinguish the acceptable from the unacceptable or the expected from the unexpected. For example, one would not expect a formula to be entered in the data entry area of a budget template and similarly, a formula, changed or overwritten in the assumptions area could spell trouble.
ClusterSeven manages these important practical considerations by providing a matrix style template which allows the administrator to decide which types of event to monitor in a spreadsheet or a part of a spreadsheet. To this end, the software can leverage any named cell ranges already built into the target spreadsheet though it can just as easily monitor special purpose named ranges defined by the administrator outside of the spreadsheet. It is then simply a matter of allocating an event type to a particular area of the worksheet by ticking the relevant square in the matrix. This is an intuitive process as ClusterSeven makes extensive use of Microsoft's look and feel such as panes and dialogue boxes. A particular complication of the monitoring process is that a newly inserted row could give rise to a large number of reportable events because of all of the impacts on the cells below it. ClusterSeven neatly side-steps this by uniquely identifying rows so that a row insertion is properly identified as such and does not give rise to a 'ricochet' effect around the spreadsheet.
Once the target workbooks have been established within the ClusterSeven environment and notifiable areas of the workbook identified, the system works quietly in the background saving the workbook according to the parameters selected during setup. Depending on the configuration of these parameters, management can be notified each time a reportable event is encountered or on a frequency decided by the administrator. Whilst the system can be configured to produce an "alert" or email notification each time a pre-defined event falls outside of tolerable limits, most clients prefer to adopt a new level of best practice by feeding existing approval processes with the regular reports generated by the system.
On line reports are colour coded to denote different event types so that the nature of potentially offending changes can be seen quickly by visual inspection. The user can step through every change in chronological order using time intervals defined in set-up.
On line reports are colour coded to denote different event types
From this high level information, an authorised user can drill down into the detail of the spreadsheet to see the exact change, which is also 'stamped' with the date and time of the change. Special functions allow different instances of macros to be shown side by side so that changed coding can be identified and assessed.
Using standard functionality within the package it is also possible to monitor user access to the spreadsheet since the system can identify which user opened, closed or made a change to a spreadsheet right down to cell level. This could be significant, for example, in a trading or other high risk environment. Authorisation and acknowledgements of changes can also be enforced through the system by requiring certain defined changes to be accompanied by a comment or be 'signed off' by a suitably authorised employee. Organisations subject to Sarbanes Oxley will be particularly appreciative of this functionality as it may enable them to demonstrate a more controlled and reliable process.
One of the enduring ironies of spreadsheets is that as they grow more business critical and complex, the more difficult it becomes to 'unlock' the valuable business information they contain. Taken as a whole, spreadsheets represent an untapped and under utilised resource because the information they contain cannot be shared across the organisation. Nevertheless, two dimensional spreadsheets are of limited value because business problems tend to be multidimensional. Therefore, a special advantage of the MS SQL Server database of deconstructed spreadsheets which is at the heart of ClusterSeven's solution is that it provides a valuable and unique repository of data capable of supporting multidimensional analysis. The ability to query the database and slice and dice it using standard Microsoft technology provides the potential to satisfy a number of management information needs. In the future, this could be enhanced further with the use of XML tags which can add contextual information to the data elements held in the database.
In an accounting environment many thousands of hours are spent 'rolling over' one years's results or budgets into the following year with all the data changes and macro intensive processing that this implies. Simply having the ability to query and manage the data using, for example, Microsoft Analysis Services is just one of the labour saving advantages of capturing spreadsheets within the ClusterSeven environment.
However, one of the immediate benefits of using ClusterSeven 'out of the box' is the ability to denote a particular cell or cells as a KPI (Key Performance Indicator). Like all events in the system it is then possible to track the chronological history of changes to the KPI and graph it for review. This relatively simple facility can provide additional helpful insight into performance.
Tracking the chronological history of changes
Summary
One of the difficulties that ClusterSeven has had to overcome is that their solution is completely novel and totally different from the spreadsheet 'auditing' tools of old which merely attempted to check the logic of a single spreadsheet and generate limited documentation. These first generation products were helpful to the spreadsheet author but of limited value to anyone else. These personal development tools did not provide any safeguards once the spreadsheet was operational, least of all in an enterprise-wide context. They were not designed to manage enterprise-wide risk.
So, ClusterSeven is in a class of its own, pioneering the new approach of Enterprise Spreadsheet Management (ESM) which resolves the long standing dilemma of how to ensure adequate compliance and control of high risk spreadsheets without sacrificing business creativity and competitiveness. No one wants to give up the benefits and flexibility of a technology that has transformed business practice over a period of more than twenty years but, in a post Enron culture, neither does anyone want to be subjected to indeterminate risk.
Utilising readily available Microsoft technologies, and a clever set of functionality, ClusterSeven seems to strike an agreeable balance between the interests of auditors, regulators, IT professionals and business users. Although ClusterSeven's early success has been driven by compliance needs in financial services this will undoubtedly provide a springboard for much broader acceptance as the advantages of their approach are more widely understood and deployed.
About FSN Publishing Limited
FSN Publishing Limited is an independent research, news and publishing organisation catering for the needs of the finance function. The report is written by Gary Simon, Group Publisher of FSN and Managing Editor of FSN Newswire. He is a graduate of London University , a Chartered Accountant and a Fellow of the British Computer Society with more than 23 years experience of implementing management and financial reporting systems. Formerly a partner in Deloitte for more than 16 years, he has led some of the most complex information management assignments for global enterprises in the private and public sector.
www.fsn.co.uk
Whilst every attempt has been made to ensure that the information in this document is accurate and complete some typographical errors or technical inaccuracies may exist. This report is of a general nature and not intended to be specific to a particular set of circumstances. FSN Publishing Limited and the author do not accept responsibility for any kind of loss resulting from the use of information contained in this document.
