control, and governance
The Good, the Bad, and the Ugly
Auditors can help protect their organization from errors and fraud by putting spreadsheet risks in their sights.
Uttam Mukherjee, CPA, CISA, ICWAI
Senior Internal Auditor
Catholic Superannuation and Retirement Fund
Imagine the finance director of a city council presenting the city's budget to the board. A few days later, his deputy reveals that errors in links contained in a spreadsheet have caused a US $5 million deficit in the water and sewer fund. Or, imagine the chief financial officer of a large listed company who is forced to resign because of a drop in profits and share price resulting from inaccurate financial reporting caused by spreadsheet errors. Finally, imagine the chief investigator of a government agency trying to pin down a suspect on price-fixing charges. Midway through the legal proceedings, she discovers figures in the spreadsheet given to the court are different from the primary data. The errors appear to have crept in while copying and pasting chunks of data.
Such horror stories illustrate the errors and frauds that stem from spreadsheets. An Internet search for the phrase "errors and frauds in spreadsheet" reveals 277,000 results documented on websites such as the European Spreadsheet Risks Interest Group. Strangely, many IT functions disown and disregard spreadsheets. Not long ago spreadsheets were never considered part of the audit agenda. Now they are.
The enormity and regularity of errors and frauds raise a few important questions:
It is important for management and internal auditors to be aware of all the potential negative outcomes from using spreadsheets. It is equally important for them to understand the causes and effects of these outcomes as well as their roles in preventing and detecting them.
Understanding the causes of spreadsheet errors can help provide solutions. Some of the causes of errors and frauds are:
Spreadsheets are easily susceptible to manipulation, either through oversight or evil intentions, which can result in huge financial and nonfinancial losses. However, it is possible to curb the bad and ugly elements by adopting good solutions.
Solutions can be found easily for all these causes, but implementing them is difficult. Many spreadsheet experts advocate best practice guidelines for developing spreadsheets, such as:
Many times the underlying cause of errors can be traced to lack of training among users and managers. The primary users of spreadsheets need to understand what they are doing and the implications of errors. Similarly, managers must understand the complexities and risks. There is no substitute for appropriate internal control through checks and balances.
THE ROLE OF AUDITORS
First and foremost, an auditor must be aware that a spreadsheet is susceptible to errors. An auditor must carry out procedures no different from any other audit for assessing and checking internal controls in a spreadsheet in conjunction with the related process. Auditors who do not have access to sophisticated tools or software can use some of the functions in Excel to weed out mistakes.
Identifying duplicate entries Inadequate controls in data input can cause unwanted duplicate entries. Auditors can identify duplicate entries in several ways. One way is to run a countif formula - =IF(COUNTIF(range of cells)>1,"Duplicates","No duplicates") — where the first entry is shown as unique and the rest as duplicate (see example chart below).
The same countif function could also be used to highlight the duplicate entries through choosing format/conditional formatting. In the window for condition, select formula and write =COUNTIF(range of cells)>1. Next, choose a format by changing the color of the text or the cell. In the "Conditional Formatting" screen shot below, duplicate entries have been highlighted in red. A3:A10 represents a range of data and A4 corresponds to the current cell position.
Extracting Unique Records
This function is helpful where unique records are required from many duplicate entries. An auditor can use the advanced filter mode (click Data/Filter/Advanced Filter) to extract unique records to a location and compare the extracted list against the original to identify duplicates. In the advanced filter window, the option for "unique records only" must be selected.
Matching Two Sets of Data
Sometimes the same data is created and maintained in two places. For example, the same list of creditors could be found in an accounting application and in Excel. Excel's "match" function can decipher whether the two sets of data are identical. The formula is =MATCH(range of cells, second range of cells, FALSE), where a match would indicate a row number in the second range (see chart below). The first two rows depict two separate data sources; the third row shows the matches and nonmatches (#N/A).
Identifying Inconsistent or Inaccurate Formulas
Many accounting spreadsheets are filled with formulas. An auditor can identify all formulas that need to be checked by using the "Go To" function (Ctrl-g) and clicking the "Special" button to select all cells that contain formulas (see "Go to Special" screen shot below).
When the cells are selected, scroll through them with the tab key only. Another way to highlight all the types of errors is to use the "Tools/Options/Error" checking tab (see "Options" screen shot below). This tool is particularly useful in spreadsheets with a large amount of data and formulas, because users can concentrate on the exceptions only, rather than scrolling through heaps of cells.
Tracing or Evaluating Formulas
There are times when auditors may want to substantiate their findings by tracing and evaluating an error. The "formulas auditing" tool can help auditors trace through all sheets and cells associated with the formulas (see "Evaluation Formula" screen shot below).
Checking for Broken Links
Complex spreadsheets are often interlinked. A link is a reference to cell(s) in another spreadsheet. The accuracy of information depends on whether the links are valid or broken. Auditors can check the validity of links by opening the edit links window (click Edit/Links) and clicking on the "check status" button (see "Edit Links" screen shot below). Microsoft's website provides advice on how to edit links.
Checking the Integrity of Data
It is common for an auditor to come across huge amounts of data and information in spreadsheets. Sometimes auditors may extract data from an application to a spreadsheet for testing. In both cases, tests for data integrity are possible through filtering data (click Data/Filter) to search for blanks in mandatory fields or find unwanted data by customizing the filter.
STEPPING UP THEIR ACT
Just as businesses across the globe have geared up to face increased legislative requirements and protect their assets from fraud through better governance and risk management programs, internal auditors must step up their act too. In this environment, auditors are bound to come across spreadsheets in most audits they undertake. As such, they must become knowledgeable and competent in how spreadsheets work so that they can pinpoint issues and errors and suggest preventive action that audit clients can take.
Uttam Mukherjee, CPA, CISA, ICWAI, is senior internal auditor with Catholic Superannuation and Retirement Fund in Burwood, New South Wales, Australia.
To comment on this article, e-mail the author at email@example.com.
COMMENT ON THIS ARTICLE
Internal Auditor is pleased to provide you an opportunity to share your thoughts about the articles posted on this site. Some comments may be reprinted elsewhere, online, or offline. We encourage lively, open discussion and only ask that you refrain from personal comments and remarks that are off topic. Internal Auditor reserves the right to edit/remove comments.