FRAUDMAGAZINE | 1
FOR FRAUD EXAMINATIONS
By Rich Lanza, CFE, CPA, PMP
Fraud examiners now have a wide
choice of data interrogation tools
they can use themselves without
bothering the IT department. Here’s
a comparison of the features of the
five most popular products.
Jack the fraud examiner had a problem. He had to analyze thousands of figures in an accounts receivable case but his small firm’s IT department was snowed under. So he knew he needed to start the analysis himself but what software should he use? Which ones had the features he needed? And was he going to be able to perform the functions with his limited high-tech ability?
In my travels, I am constantly talking to fraud examiners, audit professionals, and other finance professionals who want to use more data analysis to detect fraud. However, they complain that the software is too difficult to learn. But fraud investigation software has become easy to use. Here we’ll compare five leading products.
We’ll explore:
• core data analysis functions; • other product considerations; and • ways to select a package.
Core data analysis functions
The matrix below summarizes the most common data analysis functions by software vendor. A check mark is placed next to each function if it’s a standard feature of the tool or could easily be completed using the software’s native functions. For example, it may be possible to join or compare an employee address file to a vendor address file using the VLOOKUP() function in Excel, but this can be a complex function to use and isn’t flexible in approach. Therefore, for the Join/Relate function below, Excel wasn’t deemed to have this functionality. On the other hand, IDEA software has a Cross Tabulate com-mand that’s specifically listed as part of its standard menus and so I note in the matrix that it has this function.
HERE ARE THE FIVE PRODUCTS WITH CONTACT INFORMATION
Product Phone Web site
ACL™ 604-669-4225 www.acl.com
ActiveData for Excel™ 613-569-4675 www.informationactive.com IDEA™ 888-641-2800 www.audimation.com Micosoft Excel™ and Acess™ See web site for local
Data Analysis
Features Description ACL™
Active Data For
Excel™ Excel™ IDEA™ Access™
Aging Produces aged summaries of data
based on established cutoff dates.
Append/Merge Combines two files withidentical fields into a single file. An example would be to merge two years worth of accounts payable history into one file.
Calculated Field/ Functions
Creates a calculated field (which can use a Field/ function such as ABS for the absolute value of Functions the field) using data within the file. For example, the net payroll pay to an employee could be recalculated using the gross pay field and deducting any withholding/taxes.
Cross Tabulate Allows you to analyze character fields by setting them in rows and columns. By cross-tabulating character fields, you can produce various summaries, explore areas of interest, and accumulate numeric fields.
Digital Analysis/ Benford’s Law
Audit technology designed to find abnormal duplications of specific digits, digit combinations, specific numbers, and round numbers in corporate data. Since the objective is to find abnormal duplications, auditors need a bench-mark that indicates a normal level of duplication. Benford’s Law gives audi-tors the expected frequencies of the digits in tabulated data. The premise is that we would expect authentic and unmanipulated data to exhibit these patterns. If a data set doesn’t follow these patterns, this may be a cause for auditor concern and review.
Duplicates Identifies duplicate items within a specified field in a file. For example, this report could be used to identify duplicate billings of invoices within the sales file.
Export Creates a file in another software format (for example, Excel, Word) for testing. An example would be to export customer address information to Word for “Mail Merge”ing to customer confirmation letters.
Extract/Filter Extracts specified items from one file and copies them to another file, normally using an “if” or “where” statement. Examples include extracting all balances over a predefined limit.
Gaps Identifies gaps within a specified field in a file. For example, identify
any gaps in check sequence.
Index/Sort Sorts a file in ascending or descending order. An example would be sorting a file by social security number to see if any blank or “999999999” numbers exist.
Join/Relate Combines specified fields from two different files into a single file using key fields. This function is used to create relational databases on key fields. It can also be done in an unmatched fashion to identify differences between data files.
Regression Regression analysis using statistical means to calculate a dependent variable balance (such as net sales) based on various independent variables (for example, product purchases, inventory levels, number of customers, etc.).
FRAUDMAGAZINE | 4
Data Analysis
Features Description ACL™
Active Data For
Excel™ Excel™ IDEA™ Access™
Sample Creates random or monetary unit
samples from a specified population.
Statistics Calculates various statistics on aselected numeric field. These may be total positive items, negative items, average balance, etc.
Stratify Counts the number and dollar value of records of a population falling within specified intervals. Stratifications also provide a useful view into the largest, smallest, and average dollar
transactions.
Summarize Accumulates numerical values based on a specified key field. An example would be summarizing travel and entertainment expense amounts by employee to identify unusually high payment amounts.
As you can see from the matrix, the tools have similar fea-tures. However, products like ACL™, ActiveData for Excel®, and IDEA™give more functions to fraud examin-ers because they were built specifically for that audience. Microsoft Excel and Access, as ubiquitous and powerful as they are, separately may not have all of the features you want. But when combined, the Microsoft Excel/Access platform works well especially when ActiveData for Excel is loaded as an add-in to Excel. While price shouldn’t be the prime selection criteria, the entire Microsoft/ ActiveData for Excel combination costs less than half of one user license of ACL or IDEA (based on single-user pricing). ACL, IDEA, and Microsoft Access should be able to handle as much data as you can feed into these tools. Because of increase memory both in PCs and these software products it’s unusual to hear that any file is too large for analysis.
ACL and IDEA are faster at analyzing large data file; Access will take longer to process report requests. Excel
(and the corresponding add-in Active Data for Excel) is currently limited to 65,536 rows of information for analysis. ACL and IDEA are read-only products so the original data can’t ever be corrupted. Microsoft Access can be set to read-only (with options in the program) and Excel has a protection feature to ensure data is not over-written. Rarely do examiners corrupt original data by entering information onto that data. Rather, poor data integrity is usually due to inadequate usage of the tools at hand or insufficient training.
Other product considerations
Aside from the data analysis functionality, the fraud examiner needs to consider other key factors when making a product selection:
• data import; • training;
• script development/procedure log; and • remote access/server processing
FRAUDMAGAZINE | 5 All products provide a “wizard” that will assist in
analyz-ing the followanalyz-ing file types: Excel, Access, Dbase, Delimited, and Fixed-Width. The wizard will do most of the work in ensuring data of these types is imported cor-rectly and, for the most part, this function is all that’s needed for most examinations provided that most client systems will be able to export data in an ASCII format and in one of these common file types. Some older com-puter systems may only be able to provide data in a main-frame format (such as IBM’s EBCIDIC format) and for these special instances, ACL™ and IDEA are the only tools in our software lineup you can use to natively read these files.
Here are additional data import products:
Open Database Connectivity (ODBC) Utilities - This is the standard database access method developed by Microsoft and adopted by most accounting packages to transfer data between systems. All products offer this functionality allowing the user to import data files with-out any assistance from an MIS Department (given appropriate security). Another added benefit is that the data files are instantly defined in the software package for analysis.
Other import utilities - IDEA provides free add-in utili-ties to assist in the importing of many major accounting package data such as Simply Accounting, MYOB, and Peachtree. ACL has a similar import utility for SAP sys-tems with an added software fee.
Print report files- A report file is a text version of a print-ed report (on the PC). The main hurdle in defining these files is stripping out report headers and in appending information from one line (such as customer name) to other detail lines (such as invoice billing information). Both ACL and IDEA have functionality to easily define report files. You may be able to define these files in Excel by sorting the file but it’s less common and usually leads to a resulting file with poor data integrity.
So tools like ACL and IDEA provide a virtual “Swiss Army Knife” for importing data because they support all formats and have the associated wizards to ensure data is
Training
All the products listed have training not only in their functionality but also in how they can be used in a fraud detection scenario. The software vendors or independent firms using the software have produced a variety of self-study, group-self-study, and private instruction courses. For more information, please see the ACFE’s bookstore at CFEnet.com or the software vendor Web sites for numer-ous training options. All vendors have mailing list com-munities to help connect users of their products to discuss practical application scenarios. Learn more about a soft-ware company’s help desk track record (which may be best determined based on discussions with other current users) because these resources can be pivotal to rectify any technical issues encountered in the field.
Make sure the trainers and help desk are educated not only in the software but in the audit/fraud examination process itself. “CaseWare IDEA Inc. effectively operates through business partners and distributors to ensure a local flavor to the all-important technical support and training needs of users,” says Bob Cuthbertson, president of CaseWare IDEA Inc.
“Distributors are staffed by qualified professionals who are both IT- and audit-proficient and can demonstrate knowledge in the best uses for IDEA as well as the best ways to get the most out of the product,” Cuthbertson says. Also, inspect user groups that the software main-tains. For example, IDEA software has 11 user groups in the United States that meet regularly.
Script development/audit log
Scripts, otherwise known as batches or macros, will auto-mate a set of data analysis procedures so a fraud examin-er can create a set of reports, save the steps, and simply push a button called “Run Reports” to get the same results every time. Scripting also opens the door for con-tinuous monitoring - reports can be set to run on a peri-odic basis with no human intervention, which provides a virtual fraud detection team always working to root out malfeasance.
FRAUDMAGAZINE | 6 While all products have script functionality, ACL utilizes
a more simple text-based language which first-time users seem to understand easily. IDEA, Excel, and Access use a variant of Visual Basic which tends to be more complex to learn but more powerful as it taps into the full extent of Microsoft’s library of software components. All prod-ucts provide standard sets of pre-developed scripts that can be used to examine key accounting processes for fraud. For more information on these pre-developed scripts, see the ACFE Bookstore at CFEnet.com or the associated software Web sites.
In addition to scripts, procedure logs provide a record of data analysis procedures performed within the software for work-paper documentation. ACL, ActiveData for Excel, and IDEA all provide procedure logs while Microsoft Access and Excel don’t.
Remote access/server processing
If you plan to process large amounts of information, need more horsepower for larger data files, and/or plan to have a great deal of remote processing away from the office, you may want to consider server processing. Server access can give you more confidence in the processed data because you’ll link directly to the production machine data. You’ll also use the power of a server to process the data versus, for instance, a laptop that may not have as much power. For example, Access can link to data tables but the data is never downloaded but just accessed on the production machine. The only problem is that Access relies on Open Database Connectivity (ODBC) to down-load files so if the database is not ODBC-compliant, it may not be able to easily connect via the network. In contrast, ACL provides direct links to data housed on numerous platforms including OrACLe, DB2, IMS, or ADABAS databases. “Prior to ACL developing its server-based solutions, the main technological barrier was the practical issue of implementing a system that was non-intrusive on operational processes and capable of being easily configured for specific risk tolerance requirements,” says Peter Millar, director of product management at ACL Services Ltd. “Now, with ACL Desktop/Network edition and ACL’s versatile Server Editions and Application technologies, audit and controls monitoring can be implemented within virtually any business process cycle. The applications are customized for each organiza-tion’s needs and accepted level of risk tolerance,” Millar says. These server applications are generally installed on
just one company’s machine rather than being able to be applied to numerous companies and clients.
On the remote access front, IDEA has one of the best solutions in its IDEA Server product that provides a Web interface to a data processing server on which the software is loaded. The fraud examiner can perform data analysis with any computer that has a browser so you don’t have to carry the software and don’t have to have the most recent version loaded. It’s all handled on the server back at the office. All other products can be accessed via the Web but require remote access software. Regardless which approach is taken, a Virtual Private Network connection generally would be required to ensure the secure process-ing of data over the Internet.
How to select a package Test drive
When I’m in the market for a car, I may like the appear-ance and look of a particular vehicle. And then I’ll look to see how many cars of that model I see on the road. But I finally base my decision on how the car feels. Almost any car will get me from point A to B and will provide simi-lar functions. Shopping for fraud interrogation software is similar. Some of the bells and whistles may be require-ments for a minority of users, but the core functionality seems to be what most people want on a daily basis. My best suggestion is to take a test drive: obtain demo versions of the software packages, and run the same report through each product. You can determine which product feels best. Talk to the company representatives, the help desk, and other users to help form your opinion. If you need a set of data files and some sample tests to perform, read past “Fear Not the Software” columns in The White Paper and Fraud Magazine in which I provide step-by-step case studies using many of the software prod-ucts discussed in this article. I also give links to sample data files to run the tests.
Start small
In the next year, start small and incrementally use some of these software tools rather than run data analysis on every examination. I think Michael Pluscauskas, the pres-ident of InformationActive, said it best. “We realized that many groups invest tens of thousands of dollars purchas-ing and implementpurchas-ing fraud interrogation software only to shelve it for a couple of months and hit a brick wall when they went to use it again because they had forgot-ten most of what they had learned,” Pluscauskas said.
FRAUDMAGAZINE | 7 in our software on a regular basis it actually makes them
much better users of their other more expensive data analysis products.”
perfect option. Just get started fighting fraud with these powerful tools.
— 30 —
Richard B. Lanza, CFE, CPA, PMP, is a director at CPA2Biz where he focuses on developing business solutions. He also pro-vides audit technology and project management assistance. Lanza is the founder of the non-profit Web site, www.auditsoftware.net. His e-mail address is: [email protected]. His Web site is: www.infomagician.com .
