Excel blunders: the PwC view
PwC senior manager Keith Power offers insights into the most common Excel errorrs he encounters - and how to avoid them.
Big accounting firms are used to seeing lots of spreadsheets, but Keith Power, a senior manager with PwC in Dublin, tells of a particularly hairy experience.
1. Before you open up a workbook and start typing figures into it, think about the outputs you want from it, and how to design the spreadsheet to get the results you want.
2. Use sensible workbook and worksheet naming conventions such as Client TB 2011-12.
Minimise the number of links within your workbook. Don’t link to another sheet if you don’t need to.
3. Use named ranges to help separate data from the calculation element within your formulae.
4. Set the print configuration, titles and footers in Sheet 1 of your template or worksheet and copy this for subsequent pages to ensure they all print out consistently.
A property developer client brought in five workbooks with separate workings for the costs of a new project and asked the firm to review the proposal’s financial feasibility. Some of the spreadsheets included links to workbooks that were not included in the files given to the accountants, so they went back and asked to see them. This uncovered another 50 or so workbooks with more dead-end links, and when they arrived these spreadsheets, too, had links to other workbooks.
“There was a spider’s web of links going to other workings with information paths going up and elsewhere,” Power told the audience at an ICAI IT Services/Relate Software seminar in Dublin on 12 January. By the time his team had traced all of the links, it had 857 workbooks on its hands.
Excel exists around the fringes of standard accounting systems. “When people want to do something out of the ordinary, they use Excel, which is why it’s more prone to error,” Power explained.
PwC has form in this kind of “end-user computing”. Following the Enron crisis and the introduction of the 2002 Sarbanes-Oxley Act in the US, the Big Four firm published a study that effectively put Excel-based financial controls out of bounds for SOX-compliance.
Having frightened the audience, Power offered some pointers to some of the most common Excel errors. How many of these have caught you out?
- Your hard disk starts to whirr and Excel freezes when you try to open a chunky set of accounts or financial model.
- SUM totals for a particular list don’t add up to the correct figure because the formula hasn’t picked up new items added to the list
- You get incorrect results from a calculation because an out of date value (for example VAT or interest rates) has been included in the formula within an individual cell.
- You discover mysterious adjustments in a spreadsheet, but you have no idea who made them or why.
- You try to print out a worksheet and it comes out of the printer with partial sections appearing on several different pages.
Is Excel the right tool and are you using it correctly?
This is should be the starting point for every spreadsheet project you approach. “If the timer starts showing up or Excel starts crashing, you’re probably using the wrong tool,” said Power, who is keener on using Microsoft Access for storing large volumes of transactional data. And often commercial software will be available that does the task you want more efficiently.
The missing links issue as experienced by PwC’s property client is another common drain on a PC’s resources, as Excel will try to find and reference all the links when you open a spreadsheet.
“If you are linking to something like staff rates that aren’t going to change month-by-month, consider just copying and pasting them to simplify your spreadsheet,” Power suggested.
Many financial Excel users habitually copy the full transaction log rather than balances only when they carry a worksheet forward to a new period, swells to the point that your PC crashes when you try to load it.
“But do you need the transactions?” asked Power. “People often just add in all the transactions when they roll into a new period, and five years later you get 60 worksheets. You don’t need all the thransactions, just the balances at the end of each month. This just simplifies your workings.”
For further guidance, visit AccountingWEB.co.uk's ExcelZone and register to receive our regular monthly bulletin of tips and tutorials.
Continued...
The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.
Registration is FREE and allows you to view all content, ask questions, comment and much more.
Or if you are already registered, login here
Checking
When I used C++ to develop financial model, I always use Excel to do a parallel development and make sure the results from both C++ and Excel match each other. This is how I audit my financial model. My business users find my Excel model much easier to understand my C++ programs, they can double check my Excel model.
I wonder how someone audit a financial model that consists of 857 workbooks.
Regards,
Andrew Chan.
Horses for Courses
Pleasing to hear someone say that some tasks are better handled in Access.
Excel should not be the automatic, knee-jerk response to everything numerical.
"Is Excel the right tool?"
Good article and I heartily agree that a good question to ask before embarking on any Excel project is "Is Excel the right tool?".
In my experience Excel is the only desktop tool that most people have to handle structured data. This made it a fantastic productivity enhancer for 15 years but has (often) made it a productivity reducer in the last 5 years when users have built unwieldy Excel workbooks instead of embracing other more appropriate tools.
I would now recommend to anyone to do a quick google search for their task before embarking on an Excel 'project'. For so many common tasks there are better tools out there:
e.g. manipulating data - Access (as mentioned above), gathering data - Wufoo, project management - Basecamp, tracking activities - Saleforce (and other CRMs), etc...
Of course Excel is a great tool and will remain a key tool for accountants for some time but there are alternatives!
M
Spreadsheets - Using Formulae especially "SUM"
I have been using Spreadsheets since 1982. That was way before Excel and the IBM Personal Computer. The software then was SuperCalc.
The manual had a mere 10 or 12 sections. It was so easy to learn the principles of a spreadsheet.
The KEY solution when "summing a column" was to "sum between text cells". So if one inserts a row of hyphens in a cell, to represent an underlining of figures, then if any Rows are ADDED or DELETED then the formulae need not be updated. I tried to tell a client this tip very recently. Instead of a simple "thank you", I got back "I've been using spreadheets for years...".
Are we all too stubborn to learn new ways - or even to forget the "old" tried, and trusted methods?
Excel
Rather like driving, in the head of the person who builds the model's hands, it's fantastic. It's the other idiots you have to watch out for. What's worrying about this is the number of interested parties who pop their heads up and say "you should use our proprietary system". They would say that wouldn't they?
(Yes, I build complex models with embedded layers of error checking, and no I don't let the untrained loose on them.)
Black boxes
As a seasoned software developer, my 'input' would be to make the components of your spreadsheet as self-contained (doing one well-defined job well) and as self-reliant as possible, both to avoid the 'spaghetti links' syndrome described in the article, but also to help you check each 'box' in isolation. This also lets you swap in a replacement or use a bought-in tool or an online service instead, if need be. Plus you can more readily sanity check it, on its own, with test values to put it through its paces.
You can then mock up parts of the sheet with 'empty boxes' that are not ready or not as important, allowing them to return approximate, simplified or fixed results for the time being, without throwing everything else out of place, and let you see the whole model working end-to-end. The empty boxes can be swapped for working ones when ready with nothing else being any the wiser.
Obviously it also allows the system to be much more readily understood since each box is small enough to understand in detail, and then when you put the boxes together you have confidence that they all do their jobs properly.
End of lecture !