Spreadsheet risk: The devil is in the data

A SINGLE keystroke error caused huge embarrassment for the London 2012 organising committee (LOCOG) late last year when a member of staff recorded that 20,000 tickets remained available for synchronised swimming events – when actually only 10,000 remained.

LOCOG discovered the spreadsheet error when it reconciled tickets sold against seating configurations, and was forced to spend the Christmas period contacting ticket holders and offering them alternatives. At a keystroke, a one-digit mistake was responsible for creating huge amounts of extra work as well as reputational damage for LOCOG itself.

This is just one example of spreadsheet risk, albeit a very high profile one. Spreadsheet errors can include budgeting errors, financial statement errors, pricing errors, fraud and bad decision-making as a result of poor information. These errors can lead to significant financial and reputational losses. Tackling data risk issues quickly becomes a challenge which lies outside the knowledge of IT departments as the data that poses a risk is held and generated in spreadsheets.

The challenge is that spreadsheets are used everywhere, by companies of all sizes and across all sectors. On the upside, spreadsheets are easy to use, low-cost, flexible and readily available. On the downside, they create multiple risks such as poor data integrity, hidden data, poor documentation and duplication of information.

So rather than attempt to eliminate spreadsheets from the business, companies need to accept that spreadsheets need to be used – but that in order to reduce risk and satisfy auditors, they need to know when and where spreadsheets are being used.

It is part of the finance director’s remit to ensure that there is one version of the truth when it comes to financial reporting, especially in today’s highly transparent and tightly regulated business environment. Establishing adequate spreadsheet processes is now part of this remit.

Adequate processes

Establishing ‘adequate spreadsheet processes’ is a mixture of culture, process and technology, and we suggest the following nine steps as a starting point.

1. Recognise that operational spreadsheets exist. There are many CFOs and finance directors who simply don’t realise how many spreadsheets actually populate their financial processes – it can literally be thousands.

2. Do not be lulled into a sense of confidence by the lack of recent material errors in your business. The reality is that this lack of mistakes has a cost to the organisation in terms of many working/evening/weekend hours of checking/correcting by diligent employees.

3. Realise that there is no magic wand, or vendor software solution, that can eliminate all operational spreadsheets in the business. It becomes a question of which spreadsheets to live with, how you live with them, and for how long.

4. Create a definition of which spreadsheets are important – this equals discovery/risk assessment. While technology can help, this process requires engagement by the business and often external consultants – otherwise you just land up with a list of a million spreadsheets that is no use to anybody. At this point it can make sense to improve the structure of important but poor quality spreadsheets in terms of inputs/outputs/functional areas/use of protection so as to minimise the likelihood of future errors (this has become known as ‘remediation’).

5. Determine what constitutes anomalous behaviour in a spreadsheet and report it succinctly to the right individuals. For those organisations bound by compliance requirements an audit trail of activity may be sufficient but without focused exception reporting there will be far too much data to review as part of ‘business-as-usual’.

6. Automate all the manual checks that employees would otherwise conduct. Not only does this save time, automatic checking can be done far more regularly/consistently than any manual check. Some checks require no input from the business (e.g. alerting the appearance of cell errors). Other checks require more attention to detail (e.g. that static data has not been changed and that dynamic data – such as foreign exchange values – have changed). All of these checks can be automated.

7. Use the new understanding of the content and data of the spreadsheet to eliminate other spreadsheets (e.g. those used for reconciliation) as these checks can also be automated.

8. Use the new transparency to highlight specific spreadsheets for replacement by new systems/upgrades i.e. align business need with IT road map.

9. Recognise that spreadsheet creation, operational use and eventual replacement is a continuing journey so establish continuing processes that recognise this ‘conveyor belt’ of innovation through to systemisation.
By allocating direct responsibility and establishing a unified risk management process, organisations can start to mitigate the threats they face. In some companies spreadsheet risk is not even on the agenda; it is only when a serious financial mistake occurs that this subject is given priority.

Businesses must take a lead on the introduction of new measures such as providing automated solutions to give clear visibility of business-critical spreadsheet activity and replace slow unreliable manual checks. In combination with end-user training, this will ensure that spreadsheets are used reliably and efficiently where they are necessary.

Ralph Baxter is CEO of ClusterSeven

Related reading

data hunt