This series of articles will give you an overview of how to manage spreadsheet risk. These articles are written by Myles Arnott from Excel Audit
- Part 1: An Introduction to managing spreadsheet risk
- Part 2: How companies can manage their spreadsheet risk
- Part 3: Excel’s auditing functions
- Part 4: Using external software packages to manage your spreadsheet risk
An Introduction to managing spreadsheet risk
The potential impact of spreadsheet error hit the UK business news recently after a mistake in a spreadsheet resulted in outsourcing specialist Mouchel issuing a major profits warning and sparked the resignation of its chief executive.
See the full news article here: http://www.express.co.uk/posts/view/276053/Mouchel-profits-blow
Over the next few weeks we will look at the risk spreadsheets can introduce to an organisation and the steps that can be taken to minimise this risk.
Why do we do what we do in spreadsheets?
Because we all love Excel, right? True certainly, but the main reason is that it is intuitive, flexible, cost effective and provides quick solutions to high priority day to day problems.
And what is the alternative? The IT department. The simple fact is that end user developed spreadsheets often fill the gap between the current business requirements and formally managed IT systems.
Unfortunately this reliance on spreadsheets, rather than robust, well governed IT solutions can add significant risk to an organisation if it is not properly managed.
So what is spreadsheet risk?
Spreadsheet risk is the risk that a business could lose revenue and profit, fail to comply with regulators or find its reputation damaged as a result of spreadsheet error (be it fraudulent or unintentional).
Poorly structured spreadsheets can also lead to a loss of productivity and increased audit costs, further damaging the bottom line.
A recent study of typical enterprise spreadsheets by the Tuck School of Business at Dartmouth found that 94% of spreadsheets and 5% of all formulae within spreadsheets contain errors.
Some further examples of the impact of failing to manage spreadsheet risk
The European Spreadsheet Risk Interest Group (EuSPrIG) are the voice of best practice spreadsheet development and the management of spreadsheet risk.
Below are a couple of examples of what can go wrong from the EuSPrIG website:
C&C Group admit to mistake in revenue results
Shares in C&C fell 15 per cent after it said total revenue in the four months to end-June had not risen 3 per cent as reported, but had dropped 5 per cent. C&C said cider revenues in the UK had fallen 12 per cent, not 1 per cent, while cider revenues in Ireland were flat instead of up 7 per cent as reported last week.
C&C’s group finance director and COO said the error in last week’s announcement occurred after data were incorrectly transferred from an accounting system used for internal guidance to a spreadsheet used to produce the trading statement. “It was basically human error… there’s nothing wrong with our accounting systems,”
The FSA decided to impose a financial penalty of £5.6 million on the UK operations of Credit Suisse in respect of a breach of Principles 2 and 3 of the FSA’s Principles for Business:
- Principle 2 states that “A firm must conduct its business with due skill, care and diligence.”
- Whilst Principle 3 requires that “A firm must take reasonable care to organise and control its affairs responsibly and effectively, with adequate risk management systems.”
More specifically, section “2.33.3. The booking structure relied upon by the UK operations of Credit Suisse for the CDO trading business was complex and overly reliant on large spreadsheets with multiple entries. This resulted in a lack of transparency and inhibited the effective supervision, risk management and control of the SCG {Structured Credit Group}”
Conclusions
Excel is, and is likely to remain, the first choice for businesses when developing financial models and analysing data. The risk that this introduces to businesses if unmanaged is real and potentially material.
What next?
In the next article we will learn how companies can manage their spreadsheet risk.
Added by Chandoo
In my brief usage of Excel, I have experienced several risky situations. Sometimes it just a mild data loss, other times, there was a potential of revenue loss or customer annoyance. Due to the economic slowdown many large and small corporations are employing spreadsheet based solutions. And if you do not understand the risk & manage it, then your risk being featured on EuSPrIG’s horror stories page.
What about you?
Do you know (or experienced) a spreadsheet horror story? Please share your ideas and best practices with us using comments.
Thank you Myles
Many thanks to Myles for writing this series. Your experience in this area is invaluable. I am really keen to learn about the best practices and adopt them in my business. If you enjoy this series, drop a note of thanks to Myles thru comments. You can also reach him at Excel Audit or his linkedin profile.
24 Responses to “Introduction to Spreadsheet Risk Management”
Looks like the start of an excellent series; looking forward to more. One recent story for me was inheriting a spreadsheet that had a specific column of formulas (which should have been identical) Unknown to me, the original author had "tweaked" several rows because the results "just weren't adding up correctly". This led to several frustrating hours of me trying to figure out what the heck was going on....
My basic principle? Never trust - always verify. All else flows from there.
I like to build in lots of check formulas; some are temporary and deleted at a later time while others are a permanent part of the spreadsheet. Don't bury constants inside a formula. Use columns and/or rows to hold intermediate results; you can always hide them when formatting for the final report. If it's really critical, there's nothing wrong with using a calculator and/or a coworker to check your results. View formulas using Tools/Options to see if there is anything flaky, like tweaked formulas as mentioned by Luke. So many other tips...
But it always comes back to don't trust without verifying.
I couldn't agree more with Erin about nevering trusting without verifying. I am fairly new to my position in my company, but one of the things I have found quite shocking with a few of my colleagues is just how quickly they send out results and reports to our executives, and how many times these reports get sent back with questions regarding suspicious results. More often than not, these suspicious results are incorrect and could have been caught very easily if a little bit of time was spent analyzing and verifying and not simply trusting.
I look forward to reading more about best practices and ways to manage risks regarding spreadsheets.
i liked the article and couldn't agree more to it. i look forward to the best practices and better ways to avoid risk in spreadsheet.
Sounds like a good series. I am in the Information Security business and have always used "Trust with Verify" and using secondary check formulas or a colleague to double check data.
I look forward to more on this topic. My biggest problem is my spreadsheets are kept out on a server where many people access them but every month some or of the formulas are over written with values. I haven't come up with a creative solution yet
1. Documentation, or the lack thereof, is my horror story.
2. different systems producing different sales figures, even though the database is the same.
I'm a financial auditor in the United States. We routinely review spreadsheets our clients furnish, and we always check "clerical accuracy," which is basically a check of the formula cells, to make sure they calculate what the client (and we) think it should calculate. I have found mistakes in several spreadsheets -- nothing of a critical nature as yet -- but I did find a calculation of an estimate that was about 50% (about $60,000 USD in that case) off due to a careless error. From there on out, with that particular client, we checked all the spreadsheet information they gave us more heavily than we ordinarily would have. It was not an intentional error, and not a critical calc, thank goodness. Mistakes do happen when humans are involved. I, like others who have responded here, look forward to the rest of this series.
I love this series. I have already had extensive frustration working in poorly constructed sheets. One sheet that was passed out to my team was some massive and inefficient that they literally could not use it. Every time they attempted to filter or sort a field, Excel froze on them. So that they weren't forever crippled, I had to personally fix everyone sheet . Without proper education of this tool, it can significantly cripple an organization!
A spreadsheet that is stored on a server is used by many persons. After some time there are circulating different versions of that spreadsheet through the organisation ... in meetings .... in presentations. The initial one got babies .....
On one side this proves that the initial spreadsheet is intensively used but on the other side it creates uncertainty and confusion.
You may be interested in the survey we ran with a UK actuarial magazine about the use of spreadsheets in insurance firms:
http://www.actuarialpost.co.uk/news/spreadsheets-light-up-on-the-insurance-risk-radar-1528.htm
@Chandoo
"Added by Chandoo
In my brief usage of Excel,..."
You are funny, if you say that you have used Excel briefly, then most of your readers can consider themselves as "never used Excel"!
Looking forward to the rest of this series. Agreed with the comment on verifying. Lack of documentation and over complicated nested formulas used to avoid helper cells are far too common.
Thanks for this insightful article.
It is about time companies can rely on a systematic, holistic approach to financial spreadsheets auditing. I guess more than 80% of the financial figures of any listed company rely, directly or indirectly, on spreadsheets.
I'm also looking forward to acquire the knowledge contained in the rest of this series.
Really good one !!
But we need to understand its important !
Regards
!$T!
Do you know when Part II is being published. Fantastic.
I agree with Erin in terms of building checks and balances. But one can't build all of it.
I try to build few critical ones right at the top of my Assumptions sheet (for e.g Balance sheet check, Cash flow check, funding gap check, etc.). I also build an overall check switch for all checks and balances made in various sheets in the model.
Other way is to go straight to the balance sheet and do ballpark checks on the numbers being displayed line by line and trends thereof. Most of the times i find issues while scanning the balance sheet lines and seeing the trends therein. Just my way.
Any other suggestions.
[...] Part 1: An Introduction to managing spreadsheet risk [...]
[...] Part 1: An Introduction to managing spreadsheet risk [...]
[...] Part 1: An Introduction to managing spreadsheet risk [...]
[...] Introduction to Spreadsheet Risk Management (by Myles Arnott of Excel Audit, a UK-based consultancy firm). Recommend on Facebook Share on [...]
Thanks for making more people know the risks of using Excel without additional measures for business critical. Although I love Excel for it's ease of use, it has many risks. That led to a story I've written on "Excel as a time bomb", see http://www.invantive.com/about-invantive/news/entryid/812/excel-time-bomb.
[…] Spreadsheet Auditing & Risk Management – 4 part article […]
[…] visste jeg at det finnes kurs i «Excel Risk Management» og at det finnes et «European Spreadsheet Risk Interest Group«, men det gjør […]