April 2009

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 BAD

The enormity and regularity of errors and frauds raise a few important questions:

• Is management aware of the inherent and residual risks of spreadsheet use?
• Has management identified and analyzed spreadsheet use as a risk?
• What internal controls are put in place when developing spreadsheets?
• What role do internal auditors play?
• How can internal auditors detect the errors and frauds?
• What causes the errors and frauds?

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.

THE UGLY

Understanding the causes of spreadsheet errors can help provide solutions. Some of the causes of errors and frauds are:

• Spreadsheets have limited software development life cycle tools.
• Most people are unaware of the tools built into spreadsheet programs and enter data into spreadsheets as they would in a word processor.
• Management is seldom aware of the intricacies of large, multiple, interrelated spreadsheets. Thus, managers either may not acknowledge the existence of risk or identify it as a matter of minor concern.
• Competitive cultures at workplaces mean that individuals who develop complex spreadsheets seldom transfer their knowledge to their colleagues.
• Consequent to this competitive work culture, the board or senior management often must rely heavily on a few people. Unfortunately, if one of these people is dishonest, the chance of fraud increases.
• Many times management and auditors presume that the logic of formulas, calculations, and links is correct. Hence, they seldom check or rigorously test the validity, integrity, and accuracy of a spreadsheet.
• Managers may not apply the same rules of internal control and governance that they do to other processes and systems. Spreadsheets proliferate at an alarming rate in an organization without the necessary controls. For example, data may be entered inconsistently or incorrectly in the absence of validation rules.

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.

THE GOOD

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:

• Ensuring spreadsheets are developed to meet business and security needs.
• Introducing an inventory list of authorized spreadsheets.
• Always checking inputs against source documents.
• Where possible, adding data validation to input cells.
• When importing data from another source, ensuring that totals of records and other numeric data match.
• Ensuring that the software's automated features are not used blindly. For example, by choosing the option to "Enable auto complete of cell values," users may inadvertently enter wrong data that are similar.
• Testing the validity of links, including external ones.
• Considering the use of data forms to enter data.
• Ensuring that managers periodically check for reasonableness of data.
• Protecting spreadsheets or important ranges of data within a spreadsheet.
• Ensuring an audit trail exists.

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 uttam.mukherjee@theiia.org.

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.

Name:

Email:

Subject:

Comment:

---

To make something bold:
<strong>Text to bold</strong>

To make something italic:
<em>Text to italicize</em>

To make a hyperlink:
<a href="URL">Text to link</a>

---