Digging Even Deeper: Find the uncommon frauds with deductive tools

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

Most data analysis for fraud is specifically focused on precise red flags of fraud. The tools used are therefore query-based, which allows the fraud examiner to extract records that meet a certain criteria such as invoices paid to a vendor who has the same address as an employee. The process is inductive in nature and while it'll find much of the fraud it also misses the uncommon schemes that could sink an organization. Let's face it - any good fraudster knows what's commonly searched for and therefore will develop schemes that are missed by such detection reports. 

Here we'll present deductive and holistic data analysis techniques to find the uncommon frauds that are less predictable to the fraudster. These techniques work like divining rods that don't necessarily provide an exact answer ("there are five employees that are set up to receive vendor payments") but rather point us in the general right direction ("there are 23 invoices that make up 65 percent of the the payment value made during the year"). Because you may be trained to look at specifics these three directions could be new for you:
1. Statistical view
2. Auto-rule generator view
3. Data visualization view

We'll use practical examples and free demo copies of the associated software products.

Statistical view
Many times, the most simple tests are the most powerful. In one case, a company never had aged its accounts payable data with the positive and negative amounts presented in separate agings. Prior to the separate aging approach, they did a net dollar aging which showed they had an immaterial amount of invoices older than one year. However, when they aged the invoices separately, they identified millions of positive and negative amounts.

Unfortunately, while the positive amounts (payables) related to one set of vendors, the negative amounts (receivables) related to a different group of vendors. While the old payable balances weren't of concern, the receivables, some more than six years old, represented uncollectible balances that had been sitting on the company's books. After further research, the receivables related to wire payments were found to never have been reconciled and were effectively "pushed under the rug" year after year by the accounts payable management. While running the aging report separately for positive and negative balances may be considered a routine exercise to some, it was identified as a procedure only after statistics were run on the accounts payable open invoice data file in which high incidences of negative balances were identified.

Hence, running statistical analysis of data files looking for uncommon occurrences such as negative inventory prices or employee payments with no tax withholdings can find frauds that since have been uncovered. One useful tool for producing such reports is ActiveData for Excel,TM which can produce descriptive statistics, stratifications, agings, and Benford's Law analysis (further explained in "Benford's Law Made Easy," by David G. Banks, CFE, CIA, in the Sept./Oct. 1999 issue of The White Paper). While statistical analysis is one of the tool's features, ActiveData can do practically anything with an Excel spreadsheet including tests usually reserved for more advanced audit software. (For further discussion, read the article "Comparing Best Software for Fraud Examinations," by myself in the Nov./Dec. 2004 issue of Fraud Magazine.) To process the statistical analysis discussed in this column, you must have a working license of Microsoft Excel. Download the add-in product ActiveData for Excel as produced by InformationActive: www.informationactive.com/ad/?aid=200 

The add-in is free and will provide you with a fully-functional 30-day license. Just click on the Download button when accessing the above link. (ActiveData is sold through the ACFE Bookstore at a discount.)

Once ActiveData for Excel is installed, you'll need to open the sample workbook that comes with the product by selecting the ActiveData menu item and then the Open ActiveData Sample Workbook, as in Figure 1.

With the spreadsheet open, select the Invoices tab and then the Descriptive Statistics from the ActiveData menu item, as in Figure 2. 

Then select the Amount field and the Finished button to generate a full list of statistics on your data including positive values, negative values, averages, etc., as in Figure 3.  

After obtaining these statistics, you're better equipped to complete an aging of the information which can be done by selecting Age from the ActiveData menu, as in Figure 4.  

Then the Age dialog box can be updated with aging strata. Please note that this box also provides the range of dates as well as the opportunity to graph the results, as in Figure 5.  

After this dialog has been updated, select Finished to view the results, as in Figure 6.  

Auto-rule generator view
Data files have inherent rules that can take days or even weeks to uncover under conventional means, which would include filtering, summarizing, and relating the data file in numerous ways to detect all the rules. Here's an example rule that would be found in the analysis: IF the Country data field="United States" THEN the Currency data field="US Dollars".

A fraud examiner unearthed one such fraud after she ran an auto-rule generator tool on an accounts payable vendor payment file and she noticed one department paid a particular vendor 99.9 percent of the time. (The one exception was for a one-time payment coded to another department for close to $40,000.) While on the surface this may not cause much alarm, it provided a unique instance for the fraud examiner to research. She found an employee committed check fraud by charging to a "hiding place" general ledger account in a department that rarely checked its expenses.

In simple terms, an auto-rule generator automatically finds all rules in a database and tries to break them (as in the one-time department payment above). Replicating this effort manually could take days and might not be as effective because of human error. The product essentially adds a statistician to the examination team who uses high-powered statistics and sampling to search for the needle in the haystack.

One example of an auto-rule generator view is the powerful software, WizRuleTM, which is remarkably simple to use because all the heavy math is hidden from the user. To obtain a free demo copy, send an e-mail to isered@wizsoft.com and note that you were referred by the ACFE's Fraud Magazine. The WizSoft interface is simple and only requires that you open a data file (many formats are acceptable such as Microsoft Access, Dbase, Excel, and Text files to name a few) as seen next to one of the arrows and select the button Issue Reports next to the other arrow, as in Figure 7.  

WizRule does all the hard work in searching for deviations without ever knowing anything about the data file and identifying deviations, as in Figure 8. 

In the above instance, WizRule identified that a particular vendor number LAS00477 was used for 17,604 payments yet there were an additional six payments to a highly similar vendor number (LAS00077). Given this high improbability, WizRule considered them to be deviations for further investigation. Such unique events would otherwise stay hidden in the company database.

Data visualization view
The first two tools use statistics to find issues for follow up. However, some people are more visual and would rather search their data using graphs. When they see something requiring further analysis, they can drill-down by double-clicking on a graph to review the detailed records. In addition to the graphical analysis, the data is entered into a "cube" (similar to the "Rubik's Cube" puzzle made popular in the early 1980s) so that the data may be twisted and turned. One fraud examiner used this type of tool to identify fraudulent sales entries that were posted by the local sales manager who wanted to receive a higher commission. On the graph, the location in that U.S. state (and there were many others) had higher than anticipated sales, which were readily evident in the analysis, as in Figure 9. 

One such product that allows this kind of data mining is BI3 (www.bi3.net) that's available for a free demo using pre-formatted data provided by the company. (This is the same product I used in a financial statement analysis in my May/June 2004 column.) I suggest that you view the short video on BI3: www.auditsoftware.net/community/video/bi3.net_sales_cube/bi3.net_sales_cube.html (To watch the video, you'll need Macromedia Flash player, which you probably have. If not, visit www.macromedia.com.) In the video, sales data for various store locations is reviewed to help pinpoint the reasons for abnormally increasing sales trends. The data files and software used in the video can be accessed for free at: www.bicube.net/SS_App/default.asp.

The username is "bi3" and the password is "buycubes" which will open the data files and allow you to play with the data cubes as discussed in the video.

Uncovering uncommon frauds
Many fraud examiners use query reporting to find fraud. It's a useful tool but lacking in approach. Regardless of the level of query-based reporting that you complete as part of your fraud examination, I highly recommend that you use additional statistical, auto-rule, and visualization techniques. Such additional heuristic views of data will help detect the frauds you don't normally see in your databases.

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.