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.