Scattergraphing: Quickly finding needles in the haystack

Sometimes a fraud examination appears daunting because of an extensive amount of data that needs to be sifted to identify exceptions. In one instance, a company was at risk for overreporting revenue on its financial statements. Because this was a new audit engagement, these overstatements may have existed in the prior year as well as the current year. As usual, we had limited time to complete the audit so everyone involved wanted answers fast. What added to the pressure was the fact that there were more than 5,700 customer accounts and any one of them could have been a fraudulent reporting of revenue.

In this situation, some fraud examiners would select samples of customer invoices to confirm through positive and negative confirmation with customers. Others would ask credit managers and accounting personnel if they saw any unusual activity while others would conduct high-level analytical review of financial statements. While effective at times, these procedures are all flawed because customer confirmations may not detect prior-year issues, inquiries of management who may be involved in the fraud may not provide the desired results, and high-level analysis of anything is simply that - high-level - and may not go deep enough to detect fraud.

Another more effective approach (that should be used in conjunction with these audit procedures) is data analysis and in this particular case, graphing. Through the graphing of customer account changes, material swings in invoice amounts and counts can be readily seen at a level that has a better chance of detecting material fraud in the financial statements. What's best is such a technique doesn't require expensive tools but rather the standard graphing abilities of Microsoft Excel.

Making the scattergraph
Scattergraphs can be developed for any data that has an X and a Y axis designation. The data used for this fraud examination will include customer sale and invoice information for a two-year period, summarized by customer.

After opening the Excel spreadsheet, you'll notice there are three tabs of information explained as follows: (1) Invoice Data representing the customer invoice amount and invoice count information for 2004 and 2003, (2) Scatter Data showing the differences between 2004 and 2003 in the the customers' invoice amounts and counts, and (3) Scatter Graph that graphically represents information in the second sheet, Scatter Data. If you review the Scatter Graph worksheet, you'll quickly see that more than 5,700 customers can be represented in one picture with outliers quickly showing themselves. If you count the dots in the boxed areas of the graph, you will find that as little as 30 customers out of 5,726 can be quickly isolated for further analysis. (See Figure 1.)

Figure 1 is no longer available 

To begin developing the graph, the first step is to receive from the client a list of customer accounts with its total sales represented in amount and count form for the past two years. This information can be seen in the Invoice Data sheet with columns B and C holding the invoice amount information and columns E and F containing the number of invoices processed during the year. Columns H and I are simply difference calculations between columns B and C (sales amounts) and columns E and F (sales invoice counts), respectively. To make it easier for the scattergraph to be developed, columns A, H, and I were selected and copied (by selecting Ctrl-C) to a new worksheet named Scatter Data. Please note that the Edit >> Paste Special option was used to create a clean set of data in the Scatter Data sheet without any potential for formula errors. (See Figure 2.)

Figure 2 is no longer available  

Now with the data copied to the Scatter Data spreadsheet, the graph can be created by selecting Insert >> Chart and selecting the (XY) Scatter type graph. When prompted for the data range, the invoice amount and count difference cells would be selected including the titles of the data. If you're following along with the sample data provided with the column, this would be cells B3 to B5729. You will also want to ensure that Excel properly noted that the Series In selection is on Columns versus Rows. Once complete, select the Series tab to make sure it looks like Figure 3, with the X range being the invoice amount difference data and the Y range being the invoice count difference data.

Figure 3 is no longer available  

Select the Next button to be brought to Step 3 where a chart title can be added. It's also suggested that the Legend tab be selected so that the Show legend dialog box can be de-selected, thereby saving space on the graph. After these adjustments, the Next button can be selected one last time to arrive at a dialog window asking where the scattergraph should be placed in the spreadsheet. I suggest that this graph be placed As New Sheet with a name referencing the graph. In the example spreadsheet provided, the results of this exercise are presented in the Scatter Graph sheet (as shown in Figure 1).

Making sense of the scattergraph
It's uncanny that 5,726 unique data points are represented in the sample scattergraph and even more so that the vast majority (close to 5,700) cluster around the center axis of the graph. This leaves 30 exceptional customer accounts to review more closely, which will be done using a methodology (as shown in Figure 4).

Figure 4 is no longer available  

In essense, overreported revenue in the current year or prior year has a high likelihood of being evidenced by large amount swings in customer accounts. The scattergraph will show this (those accounts varying widely on the X axis) but what's more important is that it will show the relative number of sales invoices processed to make that change (as represented on the Y axis). This additional data-coloring helps to isolate the reasons for the amount variations when completing an investigation. Keeping overreported revenue as the risk to analyze, the following sections of the graph (as shown in Figure 4) can be more fully explained:

While the above analysis focuses on large variations in amounts and counts, the client may have simply booked false invoice entiries to numerous customer accounts so as not to be noticed in any analytical analysis. This too can be seen in the scattergraph as a high incidence of clustering towards the zero point of the X and Y axis on the graph. Note that such fraudulent postings are less likely as they require a more concerted effort to falsify earnings in comparison to a few year-end adjustments.

Highlighting exceptions
Graphing techniques can highlight major exceptions for analysis that have a higher likelihood of fraud and errors but may be misinterpreted as expected patterns for the business. The approach here could be used not only in reviewing customer accounts for fraud but also in several areas including accounts payable vendor purchases, inventory part usage, inventory part adjustments, and employee register adjustments, to name a few.

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. Lanza is the founder of the non-profit Web site, www.auditsoftware.net. His Web site is: www.infomagician.com.