by Paul R. Brazina and Bruce A. Leauby

In its “Report to the Nation” on fraud and white-collar crime, the Association of Certified Fraud Examiners estimated that 6 percent of revenue was lost in 2002 as a result of occupational fraud, which includes corruption, asset misappropriation and fraudulent financial reporting.

This is a staggering $600 billion, or about $4,500 per employee, with average losses tending to be greater for smaller firms than larger ones. As firms of all sizes turn to computerized systems to operate their businesses, more fraud is being committed using computer-assisted means. The same technology used to create this  fraud, however, can also detect it.

For years, fraud investigators, computer audit specialists and forensic accountants have used computer-assisted audit tools and techniques to detect fraud in large companies. And the application of CAATTs may have a much broader appeal today considering the implementation of Statement on Auditing Standards No. 99 and its greater emphasis on identifying fraud via the audit, as well as the requirement in the Sarbanes-Oxley Act that dictates internal controls compliance.

Many practitioners, however, assume that extensive knowledge of computer systems and programming is necessary to understand and implement these sophisticated tools, or that these tools are mainly for use at large, resource-rich firms. That is not the case. In the last few years, CAATT software has become more effective, reasonably priced and user-friendly, and thus available to a much wider audience. Two of the more popular data extraction and analysis software programs, ACL from ACL Services Ltd., and IDEA from Audimation Services Inc., can make fraud detection relatively painless and inexpensive.

In general, newer CAATTs allow investigators to obtain a quick overview of the company, develop an understanding of relationships between various data elements, and easily drill down into specific areas of interest. By identifying areas of unusual activity, the CPA and others can pinpoint hot spots and conduct searches to uncover potential areas of fraud.

What CAATTs can do
There are many tools available to CPAs for data extraction and analysis. The standard packages of ACL and IDEA allow the auditor to:

  • Access almost any database;
  • Import data into a working file of unlimited size;
  • Profile characteristics of the database;
  • Perform compliance and substantive testing to 100 percent of the information;
  • Document the testing that has been performed; and,
  • Communicate the results.

Microsoft’s Excel and Access have some features of the more sophisticated ACL and IDEA software, but there is a limit to the number of records that can be examined; the report formats used in Excel and Access must be developed by the CPA; and the integration with the audit process is more difficult.ACL and IDEA software are menu-driven, work exceptionally well in a PC environment, are affordable, and provide excellent training and support. With either of these software solutions, files can be extracted from either a database using an open database connectivity interface or flat files — single-purpose, sequential files, such as an accounts receivable file. One important advantage of these packages is their ability to take information from various configurations and convert the data into usable formats.
The CPA establishes the “view” or profile of the information being tested, including relevant data fields, and excludes information contained in the original data file not relevant to the tests being performed. Thus, the user defines the “view” for testing the data and the presentation in the audit work papers. Once the “view” is defined, it can be modified during the audit process as the audit objectives change.

Data extraction software allows the user to filter data for audit testing. Some common filters include information filters such as “equal to,” “not equal to,” “greater than,” and “less than,” as well as characteristic filters “and,” “or,” and “not.”

These filters can be set up as a stand-alone test or performed in chains. Applying a filter to large data files is easy and gives the auditor valuable information. Additionally, using filters on the entire population of transactions provides much more reliable information than using a sample, thus eliminating sampling risk and decreasing detection risk.

What CAATTs catch
In fraud and forensic engagements, the client normally brings potential irregularities to the attention of the auditor. The auditor must then develop a plan to investigate methods used to fraudulently divert resources from the company, look for direct and indirect forms of enrichment, quantify the loss to the company, and develop evidence to be used to prosecute the perpetrator in civil and criminal actions.

Law enforcement does not have the resources to investigate most fraud cases; therefore, it is incumbent upon a plaintiff to present a well-documented case to the prosecutor. CAATTs are invaluable in accumulating the necessary body of evidence.

In its white paper, “Fraud Detection and Prevention: Transactional Analysis for Effective Fraud Detection,” ACL Services references David Coderre’s 1999 book on fraud detection (“Fraud Detection: Using Data Analysis to Detect Fraud,” Global Audit Publications), which lists various types of fraud and the related symptoms. Proper use of ACL and IDEA tools will disclose and quantify losses sustained by these frauds.

Some examples include:

  • Fictitious vendors. Search for post office boxes used as addresses, matches between vendor and employee addresses or phone numbers, vendors with similar sounding names, more than one vendor with the same address and phone number, and common generic company names or vendor names that sound very much like those of well-known businesses.
  • Altered invoices. Search for duplicates of invoices and compare the invoice amount with the contract or purchase order.
  • Kickbacks. Analyze purchases to identify purchasing agents who deal exclusively with a limited number of vendors.
  • Duplicate invoices. Search for duplicate invoice numbers or duplicate date and invoice amounts.
  • Inflated prices. Prices from a particular vendor are unreasonably high when compared with others.
  • Duplicate payments. Search for repeated requests for refunds, invoices that were paid twice, or vendors with more than one vendor code. Test for duplicated payments with identical invoice numbers and payment amounts.
  • Payroll fraud. The pay of terminated employees still on the payroll is split between the manager and ex-employees. Compare the date of termination with the pay period covered by the check and extract all pay transactions for departure dates less than the date of the current pay period.

CAATTs in financial statement audits
Independent auditors rely on sampling to test compliance with the client’s internal control structure and year-end balances. Standard wording of an audit report indicates that auditors have a responsibility to “plan and perform the audit to obtain reasonable assurance about whether the financial statements and management’s assertion are free of material misstatement.” The standard report goes on to say that the audit includes “examining, on a test basis, evidence supporting the amounts and disclosures in the financial statements.”Many CPAs have a problem working with the concept of “a test basis.” Test basis implies the use of sampling, or examining less than 100 percent of the transactions.

The American Institute of CPAs’ language in SAS 99, Consideration of Fraud in a Financial Statement Audit, points the auditor to the use of technology in searching for fraud by stating: “Computer-assisted audit techniques may enable more extensive testing of electronic transactions and account files. Such techniques can be used to select sample transactions from key electronic files, to sort transactions with specific characteristics, or to test an entire population instead of a sample.”

Software such as ACL can extract 100 percent of transactional data, allowing auditors to drill down on items of interest that they identify. The types
of reports that can be generated using CAATTs include extracting payroll checks that exceed a set dollar amount, aging accounts receivable, selecting a sample of assets for physical examination, and identifying payments to accounts not listed in the vendors’ account ledger.

Complying with SOX
Section 404 of the Sarbanes-Oxley Act of 2002 creates new responsibilities for both corporate management and external auditors in reporting on internal controls. Under the law’s provisions, management has to issue an annual internal control report that contains an assessment of the effectiveness of the internal control structure; the auditor will have to attest to that assessment.

Audit standards have not yet been adopted, but it is easy to see how CAATTs could be used by both management and auditors in assessing internal control. For example, CAATTs could be used to test that credit card balances are within credit limits, identify duplicate invoices, or correlate vouchers posted to purchase order amounts.

CAATTs in court
When gathering evidence to support litigation, CAATT software can quickly identify target information and summarize the findings. The tool can be useful in several types of cases:

  • Divorce litigation: Identify payments made to relatives or close associates of the defendant. List payments from a business for personal use, such as vacations, cars, furniture, entertainment, personal loans, rent payments and lease payments.
  • Contract litigation: Identify payments made to related parties. Measure the revenue generated from relationships with specific customers, vendors and financial institutions. Determine the expenses incurred as a result of a breach of contract.
  • Discrimination: Identify classes of employees and the related compensation paid to individual members of that class or to the class as a whole. Relate payroll with employee benefits and perks paid to specific employees or a class of employees.
  • Product liability: Determine the revenue earned, related expenses and the profitability of a specific product. Match product returns with units sold and the timeframe for the sales and returns.

What CAATTs don’t do
CAATTs allow many tasks to be done more efficiently, but they do not replace the judgment of the CPA. The CPA must still develop the audit plan, identify the relevant database, select the tests to be performed, set the levels of materiality, define an irregularity, and evaluate the results. These programs do not substitute for auditor judgment.Small company use
The value of data extraction and analysis software is well documented for large companies. ACL and IDEA are used by numerous Fortune 500 companies and the Big Four accounting firms. These large enterprises have sophisticated accounting and reporting systems including enterprise resource planning systems, database management systems, and complicated legacy systems.

Specialized software packages such as ACL and IDEA can extract data from the systems, import and export data for analysis, and combine information from various systems for a broader look at the company. The benefits for these large end-users are apparent. The benefits for a small company or a modest-sized CPA firm may not be as clear, but they are equally compelling.

With the availability of PC-based accounting packages, most small businesses use inexpensive software to maintain financial and accounting functions. Packages such as QuickBooks, Peachtree and Great Plains have the ability to export data for analysis. Users have exported data into a spreadsheet to prepare special reports and schedules. As noted earlier, spreadsheet software has some of the same features found in the sophisticated data extraction and analysis packages, but spreadsheets do not have the same ease of use, menu-driven analytical tools, or report-generating capabilities.

CAATTs allow smaller CPA firms to do sophisticated data analysis at affordable costs, and the benefits could be substantial for firms that are heavily engaged in audits, fraud examinations or litigation support.

In a small company environment, CAATTs allow a user to run a test for an entire year in just minutes. Through the use of a pull-down menu, the CPA designs the tests to be performed and the software then identifies transactions with the requested characteristics.

The software generates a summary of the identified transactions, and the accountant can then expand testing or draw a conclusion from the results. At that point, documentation of the audit testing has already been prepared by the software, and the results become part of the evidential material incorporated into the work papers.

Learn more
ACL and IDEA have PC-based, single-user packages for an upfront cost of about $2,000 and an annual maintenance fee of around $500. Both companies offer online and phone customer support and have a variety of training options. There are test versions of ACL and IDEA on each company’s Web site: for ACL Services, for Audimation Services.

Both companies encourage prospective users to try the software before purchasing. As with any software, it takes time to become proficient, and many people indicate that the formal training sessions are more beneficial when taken after working with the system for a few months. There are instructional sessions on the basic use of the software and specialized sessions on fraud detection.

A useful Web site that provides more than 100 free tools for fraud examiners and also works to increase organizational benefits from the use of audit software is

While many larger CPA firms have used data extraction and analysis software to detect fraud, smaller CPA firms are the most vulnerable to fraud losses. With analysis software becoming more user-friendly and more affordable, it is time for smaller companies and smaller CPA firms to fully engage the benefits of these programs.

These programs efficiently turn a company’s data into credible, reliable and useful information, which allows accountants to spend more time planning, focusing their efforts on the areas of greatest risk, and analyzing results.

Paul R. Brazina, CPA, CMA, is an assistant professor of accounting at La Salle University. Reach him at Bruce A. Leauby, CPA, PhD, CFE, is an associate professor of accounting at La Salle University. Reach him at This article originally appeared in the Spring 2004 issue of the Pennsylvania CPA Journal, a publication of the Pennsylvania Institute of CPAs. Reprinted with permission.

Register or login for access to this item and much more

All Accounting Today content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access