The spreadsheet: The easiest place for committing financial statement fraud

[Some links and figures referenced in this article are no longer be available. —Ed.]

The spreadsheet is one of the most brilliant software tools for almost any industry including accounting and finance. However, because everyone is so comfortable with them, spreadsheets can be excellent tools for committing fraud. Also, they have little to no security in controlling changes within the worksheets. For example, a general ledger in its original form may be secure from misstatements but once exported to Excel for posting an adjusting journal entry all changes can be made without control.

Spreadsheets therefore become the least expected weak link in the financial statement chain. Aside from fraud, they're also ripe ground for errors because of their open nature for change. One improperly copied formula can spell disaster in a spreadsheet that serves as the basis of a month-end journal entry. Examples of such errors and fraud are rampant and have been summarized by the European Spreadsheet Risks Interest Group at the following link: www.eusprig.org/stories.htm. Following are some case highlights:

• AIB/Allfirst Trading Fraud - The fraudster substituted links to his private manipulated spreadsheet which exaggerated bonuses by more than half a million dollars.
• HealthSouth - Two ex-HealthSouth executives admitted that they prepared a false spreadsheet for auditors that inflated HealthSouth's assets and made the company appear to be worth more that it was.
• CFX - The Internal Audit Department noted in its investigation that management created spreadsheets showing desired results first and then adjustments were made to the accounting system to match the spreadsheet.

Given the inherent risks in spreadsheets (and those imposed by fraudsters), fraud examiners need tools to better review them for errors and fraud. This can be accomplished through techniques native in Microsoft Excel and also through additional tools that help to automate and otherwise expand the review process. Be aware that because Excel's tediously repetitive testing can use enormous resources - time, money, manpower - spreadsheet issues easily can be overlooked. This may be a reason to reach for an additional tool (such as EXChecker™), which focuses on auditing spreadsheets, as discussed later in this column.

Starting with native Excel, let's walk through an example spreadsheet and some simple steps you can take to audit the spreadsheet. While there are numerous types of issues that can develop, the focus of this review will be to determine (1) incorrect or improper data entry and (2) formulaic errors or improper formula design.

Starting with data entry issues, let's search a quarterly revenue summary spreadsheet that, on the surface, appears accurate but when one looks more closely at the total rows and columns, the numbers simply don't add-up (E5, E7, B5, and B9). (See Figure 1 below) 

While this is a simple example in which one may be able to detect issues by eye-balling the spreadsheet, there are more defined methods that can more precisely identify such issues. For one, by selecting Edit > Find in Excel, a "+" symbol could be entered to find all occurrence where an amount was added to a formula. (See Figure 2.)  

This approach could be followed for additional mathematical operators including "-," "*," and "/," thereby identifying potential unauthorized changes to formulas. This was the case for cells E5, E7, and D9.

Next, let's look at formula issues which are best identified through the Excel feature that allows a user to review the precedents and dependents of a formula. By selecting cell E4, we can then select Tools > Auditing > Trace Precedents to show the cell values that are in the =Sum() function. (See Figure 3.) 

This can be done for every formula in the spreadsheet which is easier to accomplish by opening the Auditing Toolbar by selecting Tools > Auditing > Show Auditing Toolbar and then quickly selecting the Trace Precedents button as one moves from cell to cell. (See Figure 4.)  

As can be seen in Figure 4, cell C9 is not a formula but rather a hard-entered amount of 19 which is 3 over the sum of the cells C4 to C8 (1+2+3+4+5 = 16). This and other formula errors can easily be identified by this trace feature in Excel. Another option that's beyond the scope of this column is the Trace Dependents function that's also in the Auditing menu that will trace all cells that are dependent on the current formula.

To supplement and expand on the features in Excel, use tools such as EXChecker.™ A free 30-day demo is available at: www.spreadsheetauditing.com/exchecker/auditsoftware.shtml.

EXChecker provides some compelling reasons for making the switch from Excel's native audit features:

• Excel isn't compromised - EXChecker is a separate software apart from Microsoft Excel and as such, doesn't allow any editing to the Excel spreadsheet to ensure the underlying spreadsheet isn't compromised in any way in an examination.
• Easy summary and formula analysis viewing - EXChecker provides a summary analysis of the entire spreadsheet including hidden sheets, number of formulas, errors, and notes. It then color codes the formulas so that a user can easily see inconsistencies in the spreadsheet or copy/paste errors. In Figure 5 below, cells C5 and D5 suggest that a formula is being copied across the row yet cell E5 is a fixed number that was entered (and incorrect when one looks more closely at the spreadsheet).
• Improved precedent and dependent viewing - Unlike the rudimentary tracing features in Excel, EXChecker provides a robust graph of all precedents leading up to a final formula value. This could be used to select a critical field (such as Net Profit) and then tracing all the precedents to this figure. Note that EXChecker not only traces the initial precedent but all precedents in the chain which can be drilled into for more details if needed. (See Figure 6)  
• Analyze all Excel files in a directory - EXChecker can be pointed at an entire directory to tabulate the Excel version, the number of formulas, worksheets, hidden worksheets, arrays, errors, validations and the contents of up to four user-defined cells - all of which combine to indicate the potential complexity of an individual spreadsheet. This can be done to complete a fraud risk assessment of an entire set of company spreadsheets. (See Figure 7)  

Diligent spreadsheet analysis
Microsoft Excel is such an easy tool that everyone uses it for efficiently recording accounting information. While this is a major benefit to business, such ease opens the door for poorly controlled spreadsheets that have a tendency toward errors and fraud. While auditors and examiners tend to focus on setting controls for robust accounting systems, many times the simple spreadsheet is the area that should deserve the most focus. Fraud examiners should be completing more diligent analysis of spreadsheets through a combination of Excel features and complimentary products. To get started, a simple risk assessment of the key spreadsheets used by accounting to post month-end journal entries should identify a host of review opportunities.

Richard B. Lanza, CFE, CPA, PMP, president of Cash Recovery Partners, L.L.C., in Lake Hopatcong, N.J., provides audit technology and project management assistance to companies. With automated report systems and personalized coaching, Lanza says he helps companies get quality results in minutes.