Written by Bob Carter
(Head of Finance in the Insurance and Financial Services industries for FTSE100 multi nationals)
Further to the article on the result of the GrowCFO poll: Which systems do you use for managing cash flows? With the overriding majority using Microsoft Excel, I thought it might be useful sharing an article I originally posted on LinkedIn last week (under a different heading).
In previous articles, I have mentioned my dislike for Excel files being part of a Financial Reporting process. However, most companies still use them and there are processes where Excel is the best suited tool. Research suggests that around 90% of spreadsheets contain errors, with the proportion of cells containing errors ranging between 1% and 7%.
And the impact of these errors?
Four examples of various impacts are:
- Everyone has heard of the Enron scandal and collapse in 2001. In addition to the dodgy accounting, the legal proceedings revealed an estimated 25% of spreadsheet formulae used by Enron contained mistakes; granted given the ethical questions, some of these may have been deliberate.
- Closer to home, an administrator entered 20,000 instead of 10,000 into a cell for available tickets for the heats in synchronised swimming at the London Olympics, an additional 10,000 tickets were sold which resulted in upgrades for more major events, causing a loss. Note, “Closer to home” refers to London, not the synchronised swimming!
- During the 2008 financial crisis, Barclays agreed to purchase assets from Lehman Brothers, except bad spreadsheet management and controls caused them to purchase an additional 179 contracts that were listed in ‘hidden’ rows’, revealed when the Excel file was converted to a PDF.
- And my favourite… in 2010, MI5 agency tapped more than one thousand wrong phones. A formatting error on a spreadsheet caused the agency to apply for data on all telephone numbers ending in “000” instead of the actual last three digits. Luckily my phone number has never ended in 000 or I may not have found this so funny!
Why do these errors occur?
I would suggest that there are three main causes:
i) Simple human error. Unfortunately Excel does not have a Word spell check equivalent (although there are features which can be leveraged) and thus erroneous key strokes can easily go unnoticed. Transposition of numbers is my biggest failing, (difference divisible by 9 anyone?).
ii) Complexity. Take a look at the various models in your finance team. There seems to be a competition amongst new members to the team to build more complex models than their predecessor. And how many of them link to other spreadsheets? And who actually knows how they work?
iii) Security / access. The more people that have access to a file, the more chance there is of cells being accidentally overwritten, numbers hardcode etc. And this is ignoring the issue of compromised sensitive data.
So what can be done to minimise the risk?
i) Integrate into your team.
Spreadsheets play a large part in most finance teams and it therefore makes sense to have a departmental standard / template for all to follow. This will limit the intrusions of user specific challenges coming into the files, more on good practice later. Allied to this is ensuring that all newcomers to the team have the requisite level of knowledge to run and build your models, and know the limitations. I remember, a long time ago now, an instance when a newcomer built a load of models which used @match and @index. For sure it improved the efficiency of the models in the short term, but when that person moved on and the models went wrong, we were stuffed. I’m not saying don’t move with the times, but ensure your whole team is conversant with any formulae used in your models.
ii) Think before acting.
Before rushing headlong into building a spreadsheet, confirm that it is the best medium for what you want to do, and once confirmed that it is, think how you want to structure it (helps if there is a team standard from i) above). Someone once told me it is easier and quicker to throw away a piece of paper on which you have sketched the structure, than to keep restructuring your spreadsheet.
Unless pulling together a one-off ad-hoc file, consider future proofing your work. Think about what additions may be required and if they could easily be incorporated into your file.
iii) Document your spreadsheet.
1) Have an ‘about’ sheet at the beginning which gives details about the purpose of the file, inputs into and outputs from, who designed the file, formatting rules, including any colour coding and the current owner. I would also a change control section, which would include the changes made, when and by whom. If you follow FRCF recommendations, then I would also include the date and by whom the file was last reviewed.
2) If relevant, have an “assumptions” worksheet. What makes absolute sense when you built the spreadsheet, may be totally puzzling six months later of when looked at by a colleague.
3) Have a list of the worksheets used in the file with a brief description. I tend to put this at the end of the file.
4) Have a sheet which lists the formulae used in the worksheet. This ties in with identifying what skills users / the team need to run and investigate the file.
iv) Utilise best practices.
There are many helpful hints on the internet that you can google, I have detailed those I have found the most useful or those that are frequently not followed leading to disastrous impacts.
1) Do not hard code amounts in formulae. Always have a separate ‘values’ sheet and reference to that sheet. That way you only have to change one number. I have lost count of the number of times I have had to change a tax rate and missed one or more formulae where it has been hard coded. And absolutely do not manually adjust formulae to make reclassification and other adjustments. Chance are these will not be erased when rolling forward and you will have unexpected adjustments being made (see 3) below). This also allows formulae to be locked without affecting the ability to input numbers
2) Linked in to this, use range names and not cell references. This will help to explain the formulae. Consider the difference between
=sum(Feb_Sales*Tax_rate) to =SUM(B56*A15)
3) Do not be afraid of using many worksheets (but see iii) 3) above). It can aid understanding and control. For example, instead of having one sheet with sections for ‘input’, ‘reclassifications’, ‘adjustments’, ‘roundings’ and ‘output’, use separate sheets for inputs, workings and outputs.
4) Try to ensure your data flows from top to bottom and left to right. This makes it easier for users to follow the development of totals.
5) Complex, embedded formulae may look impressive, but the feeling of awe for your expertise will soon turn to frustration if anyone needs to unpick them. It is far better to layout the calculation in steps, describing that you are doing at each step, rather like writing VBA codes.
6) Be consistent (departmental standards) with column and row headings, especially variances.
7) Don’t repeat the same calculations, have the calculation once, and then refer to it. This means you only need to change it once.
1) The key element for me around formatting is the signage of the numbers. Will the spreadsheet use accounting format (i.e. dr equals assets and expenses, cr equals, equity, liabilities and expenses), or report format (e.g. an expenses report will show expenses as debits)? Make sure that whichever format is used is clearly stated, consistent and any variances are also consistent with the signage.
2) Similarly, there can be confusion if differing units or currencies are used in the same report. I had a recent interview where I was given an extract management report in advance and had to prepare the key management messages. I had difficulty tying the expenses report into the income statement until I finally realised one was in GBP and the other in USD. With the weak state of sterling, this wasn’t as obvious as it would have been a year or so ago.
3) I am always suspicious of pretty looking input and data manipulation sheets, wondering what they are trying to hide. By all means prettify the output sheet, but keep the other sheets functional – clarity over looks. If you are going to use a lot of formatting, then do this at the end when it is a lot simpler as things will not be changing, and not while trying to build / develop the workbook.
4) Do not merge cells. This can cause unintended consequences for calculations and references.
5) Similarly, do not hide rows or columns (see the Barclay / Lehman example in the introduction). Should there be sensitive data, these can normally be placed in a separate worksheet, and the whole sheet hidden. Still use this option with care.
If your departmental guidelines allow, use the Calibri font. This was introduced by Microsoft in 2007 and the Calibri font is basically a skinnier version of the Arial font created to make both numbers and text more legible. The thinner Calibri font also makes the numbers easier to read on today’s smaller handheld devices. While the popular Times New Roman font makes text more readable, it is commonly considered more difficult to read when applied to numbers.
1) Always consider password protecting your files. Not all files will require passwords but normally those involved in management, regulatory and statutory reporting will do so. As tempting as it may be, don’t use a combination of the period and year or a generic password. You will be surprised at the number of current files I have managed to open because I have known the password to past period files.
2) Build in a separate worksheet detailing cross checks, control totals and alerts (e.g. if a total is not in an expected range). This will give users added confidence in the file.
3) Have a departmental policy for version control / back-ups. Especially when building models I always use a son, father, grandfather policy. For monthly / quarterly reports, especially if the file needs to have a generic name to assist in automatic procedures, I always save copies during the close with a date stamp, sometimes more than once a day if many changes are being made. That way you do not lose too much work if you have to restore and it saves relying on the IT team to restore which may only be possible overnight.
4) It can be time consuming to build into a model, but it can be worth using the protect function to protect those parts of the workbook which are not meant to be changed by the users, especially if the model is shared between teams. External teams adding columns to templates which are then consolidated has resulted in more than one frustrated telephone call.