Fraud Analysis Techniques Using ACL E-Book

David Coderre

• Herausgeber: John Wiley & Sons
• Kategorie: Fachliteratur
• Sprache: Englisch

Seitenzahl: 207

Veröffentlichungsjahr: 2009

Beschreibung

"When people ask me what they can do to better utilize ACL, I tellthem, 'Take an instructor lead course, participate in the ACLForum, and study (not read, study) David Coderre's Fraud AnalysisTechniques Using ACL.' I studied this book, and would not be whereI am today without it. Even without the anti-fraud material, thebook is worth the investment as a tool to learning ACL!" --Porter Broyles, President and founder of the TexasACL User Group, Keynote Speaker at ACL's 2009 San FranciscoConference, Official ACL Super User "For individuals interested in learning about fraud analysistechniques or the art of ACL scripting, this book is a must-read.For those individuals interested in learning both, this book is atreasure." --Jim Hess, Principal, Hess Group, LLC Your very own ACL Fraud Toolkit--at yourfingertips Fraud Analysis Techniques Using ACL offers auditors andinvestigators: * Authoritative guidance from David Coderre, renowned expert onthe use of computer-assisted audit tools and techniques in frauddetection * A website containing an educational version of ACL from theworld leader in fraud detection software * An accompanying website containing a thorough Fraud Toolkitwith two sets of customizable scripts to serve your specific auditneeds * Case studies and sample data files that you can use to try outthe tests * Step-by-step instructions on how to run the tests * A self-study course on ACL script development with exercises,data files, and suggested answers The toolkit also contains 12 'utility scripts' and a self-studycourse on ACL scripting which includes exercises, data files, andproposed answers. Filled with screen shots, flow charts, example data files, anddescriptive commentary highlighting and explaining each step, aswell as case studies offering real-world examples of how thescripts can be used to search for fraud, Fraud Analysis TechniquesUsing ACL is the only toolkit you will need to harness the power ofACL to spot fraud.

Leseprobe

Table of Contents

 

Title Page

Copyright Page

Preface

About This Toolkit

Who Should Use This Toolkit?

What Is Included in This Toolkit?

System Requirements

How to Use This Toolkit

Install the Fraud Toolkit Application:

How This Book Is Structured

Script Code Conventions

Flowcharts

Modifications/Updates

Contacting the Author

Acknowledgments

Introduction

Using Data Analysis to Detect Fraud

Fraud: Risks and Costs

Why Do People Commit Fraud?

Why Use Data Analysis Software?

Identifying Fraud

Proactive Fraud Investigation

Benefits of Data Analysis with CAATTs

About Scripts

Customizing Scripts

Creating Your Own Fraud Application

Further Reading

 

Chapter 1 - Start and Menu

 

Launching the Fraud Toolkit Tests

Starting the Fraud Toolkit Application

Placement of Start and Fraud Menu Scripts

How the Scripts Work

Start

Fraud Menu

Log Files

Exiting a Script

Working without the Fraud Menu

 

Chapter 2 - Completeness and Integrity

 

Checking for Blanks and Data Type Mismatches

Running Completeness and Integrity

How the Scripts Work

Carriage Returns

Understanding the Verify Command

Understanding the Group Command

Review and Analysis

Case Study: General Ledger Accounts Unaccounted For

 

Chapter 3 - Cross-Tabulation

 

Organizing Your Data to Find Trends

Running Cross-Tabulation

Benefits of Cross-Tabulation

How the Scripts Work

X-Axis Labels

Workspaces

Review and Analysis

Case Study: Not Enough Clients

Case Study: Calling Cards

 

Chapter 4 - Duplicates

 

Finding Higher-Risk Items

Running Duplicates

How the Scripts Work

The Role of Subscripts

Case Study: Duplicate Payments

Dup_Dialog Script

If Statements

Dup_Multiple_Keys1

Macro Substitution

KeyChange

Define Field

LENGTH() and HEX()

Review and Analysis

Checking for Duplicates

Payroll Example

Accounts Payable Example

 

Chapter 5 - Gaps

 

Identifying Transactions Missing from a Sequence

Running Gaps

How the Scripts Work

Review and Analysis

Case Study: Free Calls

 

Chapter 6 - Data Profile

 

Establishing Normal Values and Investigating Exceptions

Running Data Profile

How the Scripts Work

Data Profile Test Parameters

Review and Analysis

Statistics

Stratify

Round Amounts, Exact Multiples, and Frequent Values

Round Amounts: Multiples of 5, 10, 25, or 100

Exact Multiples of . . .

Frequently Used Values

Profiling with Character Fields

Items with the Most Exact Multiples

Least/Most Used Items

Case Study: Receipt of Inventory

Case Study: Exact Multiples

Filtering and Drilling Down

 

Chapter 7 - Ratio Analysis

 

Pinpointing Suspect Transactions and Trends

Running Ratio Analysis

How the Scripts Work

Max/Max2 and Max/Min Ratios

Two Fields: Num_field1 / Num_field2 Ratio

Review and Analysis

Case Study: Dormant but Not Forgotten

Case Study: Doctored Bills

 

Chapter 8 - Benford’s Law

 

Identifying Anomalous Data

Understanding Benford’s Law

Identifying Irregularities

Running Benford Analysis

Running Benford Custom Analysis

How the Benford Scripts Work

Review and Analysis

Case Study: Signing Authority

Further Reading

 

Chapter 9 - Developing ACL Scripts

 

Introduction

Data Analysis: Generic Approach

ACL Commands

DISPLAY Command

Variables

DEFINE Field/Expression

Workspaces

Scripts

RUN ACL Script from DOS

Saving a Script to a .BAT file

Interactive Scripts

ACCEPT Command

Dialog Boxes

Adding Selections to Drop-Down and Project Item Lists

Macro Substitution

Editing Dialog Boxes

Subscripts

Special Uses for Subscripts

Repeating a Script

Error Trapping

Consolidation Exercise

Advanced ACL Scripting Techniques

GROUP Command

Simple GROUP

Conditional GROUP

Nested GROUP

LOOP and OFFSET()

Applications Menu

Building an Application Menu

Creating Submenus

 

Chapter 10 - Utility Scripts

 

Auto_Execute

Extract_Values

Ending Balance Verification

Running_Total

Maximum and Minimum Values

Appendix: ACL Installation Process

Glossary

Index

Download CD/DVD content

Update Service

BECOME A SUBSCRIBER!

Did you purchase this product from a bookstore?

 

If you did, it’s important for you to become a subscriber. John Wiley & Sons, Inc. may publish, on a periodic basis, supplements and new editions to reflect the latest changes in the subject matter that you need to know in order to stay competitive in this ever-changing industry. By contacting the Wiley office nearest you, you’ll receive any current update at no additional charge. In addition, you’ll receive future updates and revised or related volumes on a 30-day examination review.

If you purchased this product directly from John Wiley & Sons, Inc., we have already recorded your subscription for this update service.

To become a subscriber, please call 1-877-762-2974 or send your name, company name (if applicable), address, and the title of the product to:

 

mailing address:

Supplement DepartmentJohn Wiley & Sons, Inc.One Wiley DriveSomerset, NJ 08875

 

e-mail: [email protected]: 1-732-302-2300

 

For customers outside the United States, please contact the Wiley office nearest you:

Professional & Reference Division John Wiley & Sons Canada, Ltd. 22 Worcester Road Etobicoke, Ontario M9W 1L1 CANADA Phone: [phone] Phone: [phone] Fax: [phone] Email: [email protected]

 

John Wiley & Sons, Ltd. The Atrium Southern Gate, Chichester West Sussex PO 19 8SQ ENGLAND Phone: [phone] Fax: [phone] Email: [email protected]

 

John Wiley & Sons Australia, Ltd. 33 Park Road P.O. Box 1226 Milton, Queensland 4064 AUSTRALIA Phone: [phone] Fax: [phone] Email: [email protected]

 

John Wiley & Sons (Asia) Pte., Ltd. 2 Clementi Loop #02-01 SINGAPORE 129809 Phone: [phone] Fax: [phone] Customer Service: 65-64604280 Email: [email protected]

This book is printed on acid-free paper. ∞

 

Copyright © 2009 by John Wiley & Sons, Inc. All rights reserved.

 

Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada.

 

No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400, fax [phone], or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, 201-748-6011, fax [phone].

 

Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.

 

For general information on our other products and services, or technical support, please contact our Customer Care. Department within the United States at 800-762-2974, outside the United States at 317-572-3993 or fax [phone].

 

Wiley also publishes its books in a variety of electronic formats.

Some content that appears in print may not be available in electronic books.

 

For more information about Wiley products, visit our Web site at www.wiley.com.

 

Library of Congress Cataloging-in-Publication Data:

 

Coderre, David G.

Fraud analysis techniques using ACL / David G. Coderre. p. cm.

Includes index.

eISBN : 978-0-470-50848-0

1. Fraud. 2. Fraud investigation. 3. Fraud-Prevention. 4. Auditing, Internal-Data processing. I. Title.

HV8079.F7C627 2009

657’ .45028553-dc22

2009010846

 

 

 

Preface

About This Toolkit

Fraud Analysis Techniques Using ACL is designed to help auditors and fraud investigators find the truth hidden in an incredible volume of transactions. Usually, the search ends with the auditor or fraud investigator gaining a better understanding of how an organization conducts its business. However, it can also end with the discovery of fraud.

Who Should Use This Toolkit?

This Toolkit is designed for ACL users from novice to expert. To use Fraud Analysis Techniques Using ACL, you need some basic knowledge of ACL, including how to access data files by building table layouts and how to launch a script. Fraud Analysis Techniques using ACL tells you how to get valuable results using ACL scripts, and demystifies the code behind these scripts. Some readers may want to design their own applications using this Toolkit as an inspiration or guide. In this case, Chapter 9, on Developing ACL Scripts, will be useful.

What Is Included in This Toolkit?

The Toolkit includes two sets of ACL scripts. The first set of 36 scripts is integrated into a menu-driven application. These simple but robust scripts use dialog boxes to prompt you for input as they perform a variety of fraud tests. The scripts are stored in an ACL Project called Fraud Toolkit.ACL, which is included on the CD-ROM at the back of this book. The CD-ROM also contains several sample data files (already defined in the Fraud Toolkit Project) that you can use to try out the tests.

 

The scripts in the second set are “utility” scripts designed to answer common problems. These scripts are stored in an ACL project called Utility Scripts.ACL.

System Requirements

The scripts included with this book require:

• ACL for Windows Version 8.0 or later. While a slightly modified version of the scripts will work for ACL for Windows Version 6.57 or later, the scripts as supplied require Version 8.0, and specific instructions are only included for that version.

• Microsoft Windows® 95/98/ME/NT/2000/XP, Vista or Windows NT® 4.0/2000

Note: While many of the scripts work with the unicode version of ACL, they were not designed to be used with this version of ACL.

How to Use This Toolkit

Using the provided ACL scripts involves five essential steps:

1. Install the application (from the CD-ROM to your system).

2. Define your data files to ACL.

3. Read about the tests you want to use.

4. Run the tests (launch the Start script and follow the menus).

5. Investigate the transactions that are highlighted by the tests that you ran.

Note: If you do not have ACL installed on your computer, see the Appendix for instructions on how to install the educational version of ACL.

Install the Fraud Toolkit Application:

1. Insert the Fraud Toolkit CD into the CD reader. This will automatically start the install program.

2. Click NEXT to accept default directory (C:\ACL Data), or click Browse and select another directory where the Fraud Toolkit will be installed; then click NEXT.

3. Click OK.

How This Book Is Structured

The introductory sections of this book explain how to install, run, and customize the Fraud Toolkit scripts. These sections also provide information on using scripts and other computer-aided techniques for fraud detection and deterrence. The chapters that follow deal with the scripts included with the Toolkit. Chapter 1 explains the Start and Fraud Menu scripts, while Chapters 2 through 8 explain the scripts that “power” the various fraud tests.

The beginning of each of the first eight chapters includes step-by-step instructions on how to run the chapter-specific tests. These instructions are followed by an in-depth discussion of how the scripts work. The code for every script is displayed in its entirety and is accompanied by explanations of what key lines of code are accomplishing. Each of the first eight chapters includes a Review and Analysis section that suggests ways to interpret your results. Also included are case studies that offer real-world examples of how the scripts can be used to search for fraud.

Novice users may want to read only the instructions on how to run the tests and the section in each chapter about interpreting the results. Expert users may also want to examine the script code and read about how the scripts work. The categories of the fraud detection tests are:

• Completeness and integrity

• Cross-tabulation

• Duplicates

• Gaps

• Data profile

• Ratio analysis

• Benford analysis

Chapter 9 is a primer on designing and developing your own ACL scripts. It includes instruction, guidance, and exercises. Chapter 10 describes the utility scripts that are also included on the CD-ROM.

Script Code Conventions

The script code presented in this book is formatted in a fixed-width font in a way that allows the reader to easily recognize key elements:

Note: Script code is not actually case-sensitive. For example, the results of a command are the same regardless of whether it is entered as “GAPS” or “gaps.” However, consistent formatting conventions make code easier to write and interpret.

Flowcharts

There are several types of diagrams in this book, the most common being flowcharts. These charts are specifically designed to show operations on data, as opposed to interactions with the user, or program flow in general. The key below shows a generic example of an operation that starts with one file, operates on it using an ACL command, and produces another file.

Typical Data Flow Diagram

Modifications/Updates

Any modifications or updates to the scripts provided with the Toolkit will be posted at www.caats.ca. You are encouraged to check this site from time to time.

Contacting the Author

I can be reached at this e-mail address: [email protected]. Comments, questions, and suggestions about this book or my other books are most welcome.

Acknowledgments

Many people contributed to this project. I want to offer special thanks to Jean-Guy Pitre for proposing major improvements to several of my scripts and Porter Broyles for his ongoing support.

Thanks to my family—Anne, Jennifer, and Lindsay—for not resenting the time I spent working on this project.

Introduction

Using Data Analysis to Detect Fraud

To cope with thousands, even millions, of transactions, and pick out the few that may be fraudulent, auditors and fraud investigators need powerful data analysis tools. However, the data analysis techniques that comprise state-of-the-art fraud detection tools can sometimes be perplexing. I hope that by shedding light on these techniques and providing easy-to-use fraud tests, Fraud Detection Techniques Using ACL will help auditors and fraud investigators discover fraud and take measures to prevent it.

Fraud: Risks and Costs

What is the risk or likelihood of a fraud occurring in the organizations you audit? Though it is not the easiest question to answer, many studies have tried. A 1997 survey by the audit firm of KPMG states that 63 percent of organizations had at least one fraud in the past two years. A 1999 KPMG survey has 57 percent of respondents reporting fraud in their company. It seems clear that the risk of fraud is high.

What about the cost of fraud? A 2008 study by the Association of Certified Fraud Examiners (ACFE) states that, in the United States, financial losses due to fraud are a staggering $994 billion a year—and some studies quote even higher figures. The KPMG Forensic Integrity Survey 2005-2006 found that 74 percent of employees reported that they had observed misconduct in the prior 12 months; and 50 percent reported that what they had observed would result in “a significant loss of public trust if discovered.” When you add to this the intangible costs, such as negative publicity, reduced morale and shareholder confidence, and loss of goodwill, it is easy to see why organizations are concerned about fraud.

In his 1997 book, Occupational Fraud and Abuse, Joseph Wells offers statistics on the median loss due to fraud. As you can see, the median losses are significant, and, if you include fraudulent financial statements, the figures are much higher. The median loss for fraudulent financial statements is $4 million. Although this represents the size of the misstatement rather than an actual loss, it is still considered fraud.

Why Do People Commit Fraud?

Interviews with people who have committed fraud show that most did not set out with the intent to do so. Often, they simply took advantage of an opportunity. Many times the first fraudulent act is an accident. For example, they may process the same invoice twice. But when they realize that nobody has noticed, the fraudulent acts become deliberate and more frequent. The “10-80-10” law, popular among fraud investigators, states that 10 percent of people will never commit fraud, 80 percent of people will commit fraud under the right circumstances, and 10 percent actively seek out opportunities for fraud. So we need to be vigilant about the 10 percent who are out to get us, and we should try to stop the 80 percent from making a mistake that could ruin their lives.

Usually, fraud occurs through a combination of opportunity, rationalization, and pressure: An opportunity arises, the person feels that the act is not entirely wrong, and there is some sort of pressure—financial or otherwise—to commit the fraud.

Why Use Data Analysis Software?

Most frauds are still discovered by outside sources such as police, anonymous letters, and customers. Others are discovered only by accident. This raises questions about the methods auditors are applying to seek out and investigate fraud. What’s more, the amount of undetected fraud invites another question: Are auditors making effective use of data analysis software to detect fraud?

As more businesses store information on computers, more fraud is committed with the help of computers. Fortunately, the same technology gives auditors and forensic investigators a new set of weapons to use in their fight against fraud.

In fact, fraud detection is a task ideally suited for computer-assisted audit tools and techniques (CAATTs). A 1982 study of hundreds of audit working papers found that such techniques identified almost 30 percent of all reported financial errors—more than any other audit method. So, for more than two decades, the use of CAATTs has been the most powerful audit tool for detecting financial errors.

In recent years, CAATTs have become more powerful and more widely used by auditors and fraud investigators. During the last decade, the use of computer-assisted tools and techniques has become standard practice. The degree of automation in business has led to an increase in the complexity of internal controls. Distributed processing, worldwide networking, and remote access to corporate systems have made auditing a more dynamic, difficult, and challenging profession.

The tried-and-true techniques and practices of auditing do not always work. Often a paper trail does not exist and the risks are completely different from those in manual or paper-based systems. For many years auditors and fraud investigators relied upon a manual review of the transactions. Auditors were often depicted with their shirtsleeves rolled up, poring over mountains of paper. For example, it was once standard practice to open the file cabinet and select every fifth file for examination, a technique known as interval sampling.

Today, however, more and more fraud auditors are making automated tools and techniques a part of their investigations. Data analysis software allows investigators to gain a quick overview of the business operations and easily drill down into the details of specific areas. As a result, examinations are much more detailed and comprehensive than a manual review of a sample of files.

Computer-assisted audit techniques can be used to develop an understanding of the relationships between various data elements, both financial and nonfinancial, and to examine the data for trends. Analytical procedures can be used to perform substantive tests and conduct investigations. Computers not only provide analytical opportunities, but also aid in the understanding of the business area and its associated risks.

Identifying risks and measuring losses electronically can improve the overall quality of a fraud investigation. Results can help fraud investigators focus their efforts and address areas of abuse and fraud, rather than waste time reviewing valid transactions.

Identifying Fraud

To discover fraud, you must know what fraud looks like. You must be able to identify symptoms in the data that point to fraud. The saying “it takes one to know one” does not mean that auditors and investigators need to commit fraud. However, if they wish to prevent fraud from happening, they must know who could be involved, what is possible, and how fraud could occur. Often the people who commit fraud are simply opportunists, taking advantage of a weakness or absence of control. Auditors must identify the opportunities before fraud takes place, and address any weaknesses in the controls if they hope to prevent fraud. But they also must be able to think like a perpetrator of fraud in order to detect the fraud.

Who could benefit from the identified weaknesses?

Identified control weaknesses must be examined from the point of view of who can benefit. Without a clear understanding of the control weakness, and an assessment of who could take advantage of the weakness, auditors are still somewhat in the dark. Assessing the degree to which people could benefit from the weakness gives you a measure of “opportunity.” The fraud triangle—opportunity, rationalization, and pressure—is what drives people to commit fraud. The understanding of who could exploit the identified control weakness can focus the search for fraud on the persons with the greatest opportunity to commit the fraud.

Since fraud is often largely a crime of opportunity, control gaps and weaknesses must be found and, if possible, eliminated, or reduced. Widely distributed audit guides and standards address such exposure concerns directly. For example, the IIA standard on proficiency (1210.A2) requires auditors to have sufficient knowledge of possible frauds to be able to identify their symptoms.

Auditors must be aware of what can go wrong, how it can go wrong, and who could be involved. Statement on Auditing Standards (SAS) #99 from the American Institute of Certified Public Accountants (AICPA), “Consideration of Fraud in a Financial Statement Audit,” was also developed to assist auditors in the detection of fraud. It goes further than its predecessor, SAS #82, to incorporate new provisions that include:

• Brainstorming for the risks of fraud

• Emphasizing increased professional skepticism

• Ensuring managers are aware of fraud

• Using a variety of analytic tests

• Detecting cases where management overrides controls

It also defines risk factors for fraudulent financial reporting and theft, and can be used as a basic model for assessing the risk of fraudulent financial reporting. The risks outlined in SAS #99 include factors such as management conditions, the competitive and business environment, and operational and financial stability.

Given that many businesses amass vast numbers of transactions, manually reviewing all documents is both costly and time consuming. Audit software can assist by highlighting transactions that contain characteristics often associated with fraudulent activity. Millions of transactions, including data from previous years or other locations, can be reviewed.

Modern audit software continues to evolve, providing increasingly powerful tools and techniques. Although the software is designed primarily for auditors, others can also use it effectively to combat fraud. Best of all, the types of data interrogation these tools and techniques allow is almost unlimited.

Examples of these techniques include:

• Comparing employee addresses with vendor addresses to identify employees posing as vendors.

• Searching for duplicate check numbers to find photocopies of company checks.

• Searching the list of vendors to identify those with post office boxes for addresses. These can be easily extracted from the vendor file for further follow-up.

• Analyzing the sequence of all transactions to identify missing checks or invoices.

• Identifying all vendors with more than one vendor code or more than one mailing address.

• Finding several vendors with the same mailing address.

Proactive Fraud Investigation

The computer, while faster at many tasks, such as indexing, totaling, matching records, and recalculating values, is not the answer to all of the fraud investigator’s problems.

Data analysis software can use relationships between data items and comparisons across years or locations to identify unusual transactions. However, verifying and interpreting the results will always demand judgment. Therefore, auditors and fraud investigators should not worry about being replaced by computers. Computers are not a substitute for human judgment and know-how; they merely help auditors apply their knowledge. The main role of computers is to give investigators an enhanced ability to carry out queries and analyses based upon the results of their initial concerns, speculations, or identified control weaknesses.

Fraud investigators and auditors can interrogate a company’s data and develop a detailed understanding of it. This understanding can help them recognize and identify data patterns that may be associated with fraud. Patterns, such as negative entries in an inventory-received field, voided transactions followed by a “no sale,” or a high percentage of returned items, may indicate fraudulent activity.

Patterns can also serve as auditor-specified criteria. Transactions meeting the criteria can trigger automatic investigations. Ideally, the data patterns are used to develop a “fraud profile” that gives fraud investigators a way to detect fraud early in its life cycle. An understanding of how the fraud occurs, and what it looks like in the data allows investigators to search the data for the symptoms of fraud. Depending on the degree of risk, analyzing data for fraud symptoms can be a monthly, weekly, or even daily event.