Fear Not the Software

When Microsoft is not the best choice for fraud examiners

By Richard B. Lanza, CFE, CPA-CITP, PMP

© September/October 2006
Association of Certified Fraud Examiners

From the September/October issue of
Fraud Magazine

F raud examiners were never meant to be computer programmers when analyzing data files. Hence they need software that "masks" the highly technical tasks while producing results. An easy choice for fraud examiners is Microsoft Excel and Access - the "don't cost nothin' solutions" - because they're loaded on most business computers by default. Also, these products are relatively easy to learn and most people have taken classes in them even before entering the professional world. It's no surprise that a software study completed by the Institute of Internal Auditors found that Microsoft Excel was the top data analysis product, two years in a row (2004 and 2005).

While the Microsoft products can be fantastic tools in an examination, their relative weaknesses should be understood so they don't become the trusty hammers when what is needed is a set of pliers. Therefore the focus here is arming the fraud examiner with key considerations when making a data analysis software decision, which encompasses Excel,™ Access,™ and other fraud specific tools like IDEA,™ ActiveData,™ and ACL.™

The considerations are organized into these sections: 1) file sizes, 2) speed, 3) error prevention, 4) functionality, 5) data import, and 6) training.

1) File Sizes
On page 14 are the data analysis options of several products:
Although IDEA and ACL can handle a practically limitless amount of data (I've processed data files in excess of 70 gigabytes in ACL Software), that much power isn't always needed. Yet similar to a family who buys a four-bedroom home when they currently only need three, it provides room to grow when the need arises. The fraud examiner never knows when the examination may lead to a much more detailed and deeper analysis and thus a need for a more powerful tool.
For those who never see the need to go beyond two gigabytes of data, Microsoft Access and ActiveData for Office (which uses the same Microsoft jet database for analysis purposes) can fit the bill. Please note that in the number of record or row terms, two gigabytes is about 10 million rows assuming there's a record length (length of all of the columns or fields in the database) of about 200. The Excel-based products allow for the least amount of data to be analyzed but this is soon to change. Microsoft Excel 12, due out in late 2006 or early 2007, will allow for a little more than one million records to be analyzed (1,048,576 to be exact). ActiveData for Excel has already become compliant with this new Excel version to take advantage of the sizable increase in processing power.

2) Speed
While there are no known tests that specifically compare the products noted in the column, I've spent time with each of the products through the last 14 years and here are my observations:

• ACL, ActiveData for Office, and IDEA are the fastest products. Each has various speeds for specific tasks and it depends on the functionality being used (see related section on page 19) but you can complete tasks for all in under a minute.
• Microsoft Access is efficient as long as the data files are below one gigabyte. After that, it becomes so slow that many commands aren't worth processing.
• Microsoft Excel and the related add-in ActiveData for Excel are the slowest products, even with the limited file sized (currently limited to 65,536 rows). Because so few records are being analyzed, this isn't as much of an issue; the processing times are never that long.
• Microsoft Access and Excel will "time out" if the processing lasts too long but it doesn't notify the user. Rather, the program simply hangs and waits for the user to force the closing of the product.

CPU processing power, increases in RAM memory, and faster hard drives are helping to increase the speed of all the data analysis products; yet the increases in speed should still stay relative. In other words, don't expect the Microsoft products to be breaking any speed tests with a two gigabyte file anytime soon.

3) Error prevention
I want to say this loud and clear: errors abound in many fraud examination spreadsheets and databases. Fraud examiners have a general comfort with the Microsoft family of products and so they believe MS is the expert. But that's a faulty assumption especially when you use more difficult functions such as VLOOKUP() in Excel or create Relationships of data files in Access. For example, when one examiner related a payroll detail payment file to an employee file (on employee number) he couldn't understand why his payroll detail file was growing in size. But he didn't consider that the employee file contained duplicate employee numbers because several employees worked in multiple divisions. So Access duplicated all the payroll detail records associated with these employees. Thankfully, the examiner found the error before going too far, yet he could have easily made a faulty conclusion that there duplicate payments were made to certain employees.
This isn't a comprehensive list but here are key ways you can prevent errors using ACL, IDEA, or the ActiveData family of products:

• All the tools produce an audit trail/log documenting all the executed steps. But Access and Excel provide no similar functionality for review purposes and require examiners to type their data analysis steps into a word-processing document. Access does have a Documenter function (see Tools\Analyze\Documenter) yet it provides details about each table or query selected and isn't as functional as the audit tool logs.
• When relating files (such as in the payroll/employee example above), the products will never duplicate records because they'll simply write the first occurrence from the secondary file. Therefore, when relating the payroll to employee files the first divisional employee listing from the employee file would be written to all the detail payroll records with no duplicate records added to the resulting table.
• ACL and IDEA will work with a read-only version of the data file. This improves the reliability of the analyzed data and helps otherwise ensure no one in the chain of custody tampers with it.
• As further explained below, the software products, at the "push of a button," will automate many of the functions that are simply difficult to perform in Excel and Access. For example, a report that stratifies the amount field into various strata (such as $0 to $1,000, $1,001 to $10,000, etc.) can be done in Excel but the number of IF() / COUNTIF() statements simply aren't worth the effort and can lead to errors.

4) Functionality
The matrix on page 18 summarizes the most common data analysis functions by software vendors. 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 might 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, Excel wasn't deemed to have this functionality. On the other hand, IDEA software has a Cross Tabulate command that's specifically listed as part of its standard menus, and so I note in the matrix that it has this function.

As you can see from the matrix, the tools have similar features. However, products like ACL, ActiveData, and IDEA give more functions to fraud examiners because they were built specifically for that audience.

5) Data import
One of the most daunting tasks is obtaining and importing client data into the software for analysis. All products provide a "wizard" that will assist in analyzing the following 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 correctly 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 computer systems might only be able to provide data in a mainframe 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. Print report files also can be difficult to import (ones with complex headers and footers or data spread throughout the report). Access, ActiveData, and Excel can't handle these types unless the print report file is simple yet ACL and IDEA should easily be able to import the files with a special report format wizard they've developed for this purpose.

So, tools like ACL and IDEA provide a virtual "Swiss army knife" to import data because they support all formats of data and have the associated wizards to ensure data is imported accurately. Excel, Access, and the ActiveData family should be able to meet most needs but might not be able to import complex or older formatted file structures.

6) Training
Microsoft Access and Excel don't offer many training possibilities for using their products to find fraud. The ACFE has developed the only known CPE training course (for Microsoft Access). Another option is a free white paper that I've written on how to complete many fraud-specific data analysis tasks in Microsoft Excel. (See www.auditsoftware.net/excel-use.html .)

By contrast, ACL, IDEA, and the ActiveData family have self-study, Web-based, and group-study training available, some of which are solely focused on ways the software can detect fraud. The ACFE's bookstore carries many of the self-study titles that also offer CPE credits after completion.

In addition to the training, these products have user groups either in-person or via the Web using discussion boards. ACL's discussion board in particular is impressive with active postings not only from users but also from technical support staff that regularly monitor the postings so they can provide associated answers and solutions.

While I'm not trying to give a sales pitch for the more advanced offerings, I want fraud examiners to notice many of the kinks in the Excel/Access armor. Choose your fraud-fighting weapon wisely!

A special thanks to Mark J. Nigrini, Ph.D, of Saint Michael's College in Colchester , Vt. , for his comments.

Richard B. Lanza, CFE, CPA-CITP, PMP, president of Audit Software Professionals., in Lake Hopatcong , N.J. , provides audit technology and project management assistance to companies. He focuses much of his time in developing computerized audit and fraud tests. Lanza is the founder of the non-profit Web site, www.auditsoftware.net . His e-mail address is: rich@auditsoftware.net .

The Association of Certified Fraud Examiners assumes sole copyright of any article published in Fraud Magazine. Fraud Magazine follows a policy of exclusive publication. Permission of the publisher is required before an article can be copied or reproduced. Requests for reprinting an article in any form must be e-mailed to: FraudMagazine@ACFE.com .

Learn More about AuditAnalytics