Spreadsheet Risk – What’s wrong with spreadsheets?

2011-12-01

What’s wrong with spreadsheets?

Let me start by asking two very simple questions. Are spreadsheets a problem to your organisation? How do you know?

It’s not uncommon for company directors and department managers to rely on spreadsheets when running their business. You may know some yourself. And whilst for the majority this works well, when spreadsheets go wrong the impact on the business can be catastrophic. This report highlights some of the issues and remedial actions that can be taken to mitigate the risks presented by End User Computing.

There is a huge litany of sources highlighting the risks of using spreadsheets where checks and controls one might typically expect to see in a business critical process are absent. By way of example, the European Spreadsheet Risk Interest Group or EuSpRIG has been compiling some of the most hard hitting stories for years. If you get time then please read EuSpRIG’s article “89 Spreadsheet horror stories“. It makes for interesting but strangely uncomfortable reading. You’ll begin to get the idea of just how bad it can get.

From my own experience, I would say that spreadsheet risks are overlooked. No one believes it’s going to happen to them and as has been seen in the run up to our most recent economic downturn, human nature dictates that we ignore the risks when the going is good. No one wants to be the person to rain on the parade, if you pardon the metaphor. And whilst a near miss is usually enough to prompt action, it doesn’t have to be this way. As an internal audit manager you have the power to highlight risks, and steer action accordingly. What you do in your role, makes all the difference. So what can you do?

Why is it such a problem?

End user computing is the single most problematic area of audit. Sitting outside the control framework means that spreadsheets aren’t covered by general computer controls, nor are they covered by any application controls testing. External audit teams who come to rely on spreadsheets should be able to verify the data independently, or perform their own investigation to verify its contents. Simply reviewing one monthly spreadsheet in a year isn’t enough to cover a period. They need to be checked substantially.

The types of error include:

• input errors – where the wrong figures have been input
• logic errors – where the wrong formulae are used, or the wrong values are added
• interface errors – where data from other applications is brought down incorrectly; plus others

But as an internal audit manager, why should I care? As a responsible member of your company’s internal audit function your remit should include operational and performance issues. If it were my responsibility, I would want to know the extent to which the business relies on spreadsheets and the risks that poses to it as a going concern. If you don’t do anything audit of spreadsheets presently, then please give it some consideration. Spreadsheets represent a significant weakness in any process.

My organisation has tens of thousands of spreadsheets – how can I possibly sort this out?

A sweep of your disk drives will invariably identify tens or possibly hundreds of thousands of spreadsheets created in your organisation. Many will be old and archived, some may be duplicates that have been distributed via email, some may simply be tea rosters, or lists and catalogues of staff absence. In fact there are many, many reasons why someone might want to create and keep a spreadsheet. And that is fine. The vast majority of this can be ignored. Crucially however, we need to isolate from the mire, those spreadsheets which are business critical applications. Business critical apps impact on the business directly; on the accounts, operations, or finance; spreadsheets which, if incapacitated, would stop the business in its tracks, or at the very least lose it significant amounts of money. As a responsible internal audit function, we need to locate them and put them under the businesses control. But how?

Finding business critical spreadsheets can be difficult, but there are some very effective techniques. I’ve found that requests for information can fall on deaf ears but… with the blessing of management there are ways to acquire this information but they won’t always make you popular. If you’re interested then why not drop me a line? Subjectivity makes quantifying the importance of your spreadsheets a personal thing. But, thinking about what constitutes an application critical in your business will make the job easier. If the outputs of your spreadsheet affect operations, finance, or are needed for regulatory compliance then there’s an excellent chance they will be critical. But don’t be afraid to rule something in if you’re unsure. Consider what makes a spreadsheet risky, see below, and apply some common sense. You’ll find it works wonders.

What makes a spreadsheet a risk to my organisation?

Spreadsheet risk is subjective but, if you consider risk in a relative way you’ll find that there are a few clear winners – out of the 400,000 spreadsheets I found in in one organisation I narrowed it down to 12 crucial applications. Consider the following when performing your review:

• the complexity of the spreadsheet – does it use macros or complex Excel functions?
• the purpose and use of the spreadsheet – is simply a tea roster or a list of business assets that tie into the accounting system?
• the number of spreadsheet users – is it used by many people and therefore subject to a large volume of changes, or under the guise of one person and at risk if they are absent
• the size of the spreadsheet – is it a large spreadsheet and subject to a greater possibility of errors passing undetected
• the degree of understand and documentation – is there a record anywhere of what the spreadsheet does, what it is used for and how it does it?
• the frequency and extent of changes – does the spreadsheet change from month to month, or does it remain the same. Is it automated?
• the development and testing of the spreadsheet – has it been developed and tested?
• the everyday checks and balances – are there any means of knowing if the spreadsheet is complete and accurate?

For the record, I didn’t look at all 400,000 spreadsheets. I didn’t need to. Again, if you ‘re interested then drop me a line.

The risks that are identified can be remediated directly or alternatively apply a catch all. Place the spreadsheets on a matrix of criticality – low, medium, high. Those which are of low importance, don’t require any additional controls. Those which are of some significance may require basic controls like documentation and version controls. The most critical could have the complete gambit of controls levelled at them. There is no right or wrong answer here.

What can I do?

Once the business critical spreadsheets have been identified, one needs to start the process of bringing them within a control framework. Consider the following remedial actions, some or all maybe appropriate:

• Change control – any amendments or modifications are logged
• Versions control – to ensure that the latest version can be identified
• Limiting access – making the critical components of the srepadsheet read only, and password protecting the worksheet where appropriate
• Input controls – ensuring that data is reconciled and that inputs are complete and accurate
• Security and integrity – that the application is locked down appropriately, that it is password protected and master data and formulae are read only
• Documentation – that the application is properly documented for the developer and operator
• Development life cycle – applying a standard lifecycle to ensure that the application is properly specified, developed, tested and maintained
• Backups – that crucial applications are backed up and retained
• Archiving – that historic documents are kept, and locked to prevent further changes
• Logic inspection – a formal review of the spreadsheet by an independent person, perhaps comparing to the original specification or a reperformance of the calculation would be of benefit for crucial applications
• Segregation of duties – the user, developer and approver should be segregated to minimise the risk of error or unauthorised changes taking place
• Analytics – implementing analytics as a detective control can help to manage risks in a spreadsheet

End User Computing Policy

An End User Computing Policy will cover many of the topics discussed here today. Do you have an End User Policy? Is it sufficient for our needs? Is it adhered to? Would you know if it wasn’t? Don’t worry if you can’t answer all of these questions, many organisations can’t. I would argue however, that everyone should be aspiring to.

The most radical of changes occur when something terrible happens, but in recognising that there may be a problem and with a little bit of work, the risks can be quantified and remedial actions taken. No one would suggest that every application should be controlled, but how would you know whether it should be unless you look?

Having highlighted the issues and potential downfalls of using spreadsheets, I’m sure that you all be itching to pick you the phone to talk with me, or at the very least be having conversations internally to establish whether what you do is sufficient, or whether you should be doing more. Given the prominence of End User Computing in business today, I believe passionately that we should all be doing more to address the risks that they pose. If we don’t then we only have ourselves to blame.

I look forward to hearing your thoughts, comments and queries.