Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel blunders: the PwC view

by
19th Jan 2011
Save content
Have you found this content useful? Use the button above to save it to your profile.

PwC senior manager Keith Power offers  insights into the most common Excel errors he encounters - and how to avoid them.

Big accounting firms are used to seeing lots of spreadsheets, but Keith Power, a partner with PwC in Dublin, tells of a particularly hairy experience.

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.

Excel tips

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.

“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.

Having frightened the audience, Power offered some pointers to some of the most common Excel errors. How many of these have caught you out?

  1. Your hard disk starts to whirr and Excel freezes when you try to open a chunky set of accounts or financial model.
  2. 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
  3. 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.
  4. You discover mysterious adjustments in a spreadsheet, but you have no idea who made them or why.
  5.  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.”

Document your spreadsheets properly
Having accepted that Excel is the right tool for the job, the PwC partner is adamant that users should apply sensible naming conventions to identify and manage workbooks and use the Document Properties feature to record who created the workbook and why.

“If a client handed you some workings with no information about the period it related to, who prepared it and who it was about, you wouldn’t accept that on paper, but you often do with Excel,” Power complained to the Dublin audience.

A file name such as TB 2011 01 12.xlsx is going to be far more informative than Book1.xlsx and workbook and worksheet titles can help to identify which organisation a workbook relates to and the financial period covered.

Trouble with formulae
Excel SUM formula errorExcel’s Formula capabilities are very powerful, which also makes them a rich breeding ground for spreadsheet errors. One of the most common, and most dangerous is the SUM formula that neglects to pick up new cells added into a column of figures.

Excel’s built-in audit tools will alert the user if a formula does not include data from adjacent cells and Power urged the audience not to ignore the red triangular warnings that appear in the corner of affected cells.

As Simon Hurst explained in an Excel 2007 tables tutorial, using a named range along with the SUM Function can help, because it will offer you the option to select an entire data column when you start to type in the text string for a named range. Excel 2010 has also automatically checks if you want new rows to be included in a formula – it can be an irritant to people who frequently make additions to their sheets, but does serve a useful purpose.

“I came across this in a major store group where someone in finance was told to maintain a sheet that included some 27,000 totals,” Powers told the ICAI seminar. “We had to put through a really large audit adjustment to correct that.”

Embedding values within Excel formulae is another one of the perennial no-nos of Excel use. As well as making the logic of the formula much harder to deduce, the technique can mask errors beneath the surface of the spreadsheet. Much better to keep data and formula cells separate and have the workings out in the open, where any other users will be able to see them.

The same philosophy influences PwC’s approach to adjusting spreadsheet workings. Rather than just altering a figure – and perhaps adding a note of explanation, if you’re very lucky – it’s better to add a new row or column to a sheet to make it blindingly obvious where an alteration has been made:

The most effective way to separate logic from data within Excel formulae is to  use named ranges. This Excel feature allows you to select a cell or block of cells and assign a descriptive phrase rather than a reference such as $A$1:$C$25. The named ranges you have created on a sheet can be accessed from a pull down menu just above cells A & B1 and simplify any formula you create by displaying some descriptive text within the formula itself.

To concludue his tour of common Excel errors, Power offered an invaluable practical tip on print management, and a more philosophical overview of spreadsheet use. To prevent regular occurrences of error number 5 – the output of multiple pages of spreadsheet with bits of your data strewn around them, he encouraged the use of templates and worksheets on which the headers, footnotes and print area are set on Sheet1, and this is then copied whenever you want to add a new sheet.

“It makes them look more professional and easier to maintain,” he said.

PwC’s key principle of spreadsheet use returns to the starting point of Power’s talk. Whether you are dealing with an entire spreadsheet model or even a formula within it, think about it in terms of your input and the process it will undergo to get to your desired output.

“It’s a very simple concept, but very powerful,” he said. “A little effort in design will prevent much re-work and detection.”

For further guidance, visit AccountingWEB.co.uk's ExcelZone and register to receive our regular monthly bulletin of tips and tutorials.

Replies (17)

Please login or register to join the discussion.

avatar
By mikewhit
19th Jan 2011 21:58

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 !

Thanks (0)
avatar
By kirsty1986
21st Jan 2011 16:30

Formula reveal

I love [ Ctrl + ¬ ]* Reveals all the formulae and their origins.  Always worth quickly checking before beginning.

 

*  note: ¬ is at the top left-hand side of the keyboard beside the '1'

Thanks (0)
avatar
By BECKYHALL
21st Jan 2011 17:04

CTRL +¬

But how do you get the formulae to disappear again?

Thanks (0)
avatar
By Comptable
21st Jan 2011 17:37

do it again BECKY

Thanks (0)
avatar
By chanpangchi
22nd Jan 2011 02:06

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.

 

Thanks (0)
avatar
By anthonymellor
22nd Jan 2011 08:22

slowly ? :-)

Thanks (0)
avatar
By David Winch
26th Jan 2011 12:23

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.

Thanks (0)
avatar
By Michael Wood
27th Jan 2011 11:15

"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

 

 


Thanks (0)
avatar
By beverly chester
27th Jan 2011 11:48

Sum error

 The sum errors in excel although occasional really used to frustrate me, not necessarily when I added cells in but just occasionally when we had typed in bank statement details etc or were working on client spreadsheets. We would add a column up but it would refuse to enter a total at all or simply put an obviously wrong total in such as the total of the last few lines in a 700 line spreadsheet. I mentioned this to one of my clients who is something of a whizz kid with computers generally and he suggested I check all formula in the column that I was trying to add. Lo and behold the next time this happened I found that there were different formula in that column, mostly accounting but occasionally numbers etc. when I checked the adds between these erroneous entries they added up to the total excel had given so I now re format every spreadsheet before I add it and I have not experienced any problems since!

Thanks (0)
avatar
By MartinLevin
27th Jan 2011 12:25

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? 

 

Thanks (0)
avatar
By Karen_Conneely
27th Jan 2011 13:13

Reliance on spreadsheets can be risky

Financial directors may be putting their companies at risk by continuing to rely heavily on Excel spreadsheets, and this especially applies to the complex processes associated with managing the capital assets register.

How an organisation manages its assets has multiple effects on a company’s finances, from tangible costs of heightened insurance premiums through to neglected depreciation. Having an accurate and robust asset register that shows the location, value and condition of assets can also help ensure that resources are available and usable when needed as well as achieving compliance with key industry legislation. And whilst spreadsheets are adequate for collecting basic data, there are several compelling reasons for switching to a bespoke system designed to cope with these intricacies.

Too many spreadsheets contain errors, which is to be expected with information entered by hand. Whether it be the background asset data or the formula itself, there’s little doubt that depreciation calculations, when based on a spreadsheet, are likely to be inaccurate – potentially affecting the overarching balance sheet. Furthermore, with the raw data within a spreadsheet dictating that each report be constructed individually using complex macros, this also wastes valuable time and resources at month-end.

Compliance with the latest legislation including IFRS, Sarbanes-Oxley (SOX) and SORP consists of improved financial management and increasingly detailed reporting. With such a high degree of attention focused on your organisation, why would you risk the integrity of your financial data by relying solely on spreadsheets, which are inherently instable and invariably achieve poor audit results?

Karen Conneely, Group Commercial Manager, Real Asset Management

www.realassetmgt.co.uk

Thanks (0)
avatar
By rlatchana
27th Jan 2011 17:32

SUM Formula
A better way to handle the SUM formula blues is to have a blank below the data and let the formula include that row. The blank row is never used - new rows are inserted when needed just above the blank row so the formula always stays intact.

edit: Just noticed MartinLevin saying about the same thing.

Thanks (1)
avatar
By ryanjdunbar
28th Jan 2011 00:32

Limitations of the SUM function

Another limitation of the SUM function occurs when you stick an auto-filter at the top of your range of data and search individual entries.  I always use the subtotal function as it is far more reliable and the blank row idea mentioned above.

Thanks (0)
avatar
By Greenheys
01st Feb 2011 18:49

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.)

Thanks (0)
avatar
By Leopold Stotch
03rd Feb 2011 11:15

Slander

"[Spreadsheets are] inherently instable and invariably achieve poor audit results"

Speak for yourself, dear.

Thanks (1)
avatar
By edhy
01st Mar 2011 04:39

Use autostart templates

Put a template Book.xlt and Sheet.xlt in xlstart directory with all formatting and print header / footers for consistent look of your sheets.

It is good idea to put your report (regular printout required) on one sheet and data and other unstructured input on another sheet(s). Set the printing to “blank” tall and one page wide as default. Further set the print filename, date and time in the footer.

Just to emphasize already mentioned point; use subtotal and blank row to avoid sum blues.

My rule is that if file size gets bigger than one MB, some thing is wrong with the workbook or choice of Excel is wrong.

Regards

Zubair Edhy

Thanks (0)
avatar
By rlatchana
02nd Mar 2011 12:53

Excel file size
I use many Excel files above 1Mb and don't find a problem. If the Excel file size gets too large, use CTRL-END on all worksheets to see if blank rows / columns are being captured between your data and Excel's last cell.

Thanks (0)