"e;When people ask me what they can do to better utilize ACL, I tell them, 'Take an instructor lead course, participate in the ACL Forum, and study (not read, study) David Coderre's Fraud Analysis Techniques Using ACL.' I studied this book, and would not be where I am today without it. Even without the anti-fraud material, the book is worth the investment as a tool to learning ACL!"e; Porter Broyles, President and founder of the Texas ACL User Group, Keynote Speaker at ACL's 2009 San Francisco Conference, Official ACL Super User "e;For individuals interested in learning about fraud analysis techniques or the art of ACL scripting, this book is a must-read. For those individuals interested in learning both, this book is a treasure."e; Jim Hess, Principal, Hess Group, LLC Your very own ACL Fraud Toolkit at your fingertips Fraud Analysis Techniques Using ACL offers auditors and investigators: Authoritative guidance from David Coderre, renowned expert on the use of computer-assisted audit tools and techniques in fraud detection A website containing an educational version of ACL from the world leader in fraud detection software An accompanying website containing a thorough Fraud Toolkit with two sets of customizable scripts to serve your specific audit needs Case studies and sample data files that you can use to try out the 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-study course on ACL scripting which includes exercises, data files, and proposed answers. Filled with screen shots, flow charts, example data files, and descriptive commentary highlighting and explaining each step, as well as case studies offering real-world examples of how the scripts can be used to search for fraud, Fraud Analysis Techniques Using ACL is the only toolkit you will need to harness the power of ACL to spot fraud.

Preface ix

About This Toolkit ix

Who Should Use This Toolkit? ix

What Is Included in This Toolkit? ix

System Requirements ix

How to Use This Toolkit ix

Install the Fraud Toolkit Application x

How This Book Is Structured x

Script Code Conventions xi

Flowcharts xi

Modifications/Updates xii

Contacting the Author xii

Acknowledgments xii

Introduction 2

Using Data Analysis to Detect Fraud 2

Fraud: Risks and Costs 2

Why Do People Commit Fraud? 2

Why Use Data Analysis Software? 3

Identifying Fraud 4

Proactive Fraud Investigation 5

Benefits ofData Analysis with CAATTs 6

About Scripts 6

What Is a Script? 6

Benefits of Scripts 6

Preparing Scripts for Use 7

Copying Scripts 7

Copying Table Layouts 7

Working with Scripts 8

Launching the Fraud Toolkit Application 8

Running the Scripts 9

Filtering 10

Customizing Scripts 11

Customizing: An Example 12

Creating YourOwn Fraud Application 13

Further Reading 14

Chapter 1: Start and Menu 16

Launching the Fraud Toolkit Tests 16

Starting the Fraud Toolkit Application 16

Placement of Start and Fraud Menu Scripts 17

How the ScriptsWork 17

Start 17

Fraud Menu 18

Log Files 18

Exiting a Script 20

Working without the Fraud Menu 20

Chapter 2: Completeness andIntegrity 22

Checking for Blanks and Data Type Mismatches 22

Running Completeness and Integrity 23

How the ScriptsWork 24

Carriage Returns 24

Understanding the Verify Command 24

Understanding the Group Command 25

Deleting Temporary Variables, Fields, and Files 26

Variables for Complete 27

Review and Analysis 27

Case Study: General Ledger AccountsUnaccounted For 29

Chapter 3: Cross-Tabulation 30

Organizing Your Data to Find Trends 30

Running Cross-Tabulation 30

Benefits of Cross-Tabulation 32

How the Scripts Work 33

Challenges of Cross-Tabulation 34

X-Axis Labels 34

Workspaces 35

Deleting Temporary Variables, Fields, and Files 36

Variables for Cross Tabs 36

Review and Analysis 36

Case Study: Not Enough Clients 37

Case Study: Calling Cards 37

Chapter 4: Duplicates 38

Finding Higher-Risk Items 38

Payroll 38

Accounts Payable 38

Running Duplicates 38

How the Scripts Work 40

The Role of Subscripts 40

Case Study: Duplicate Payments 42

Dup Dialog Script 43

If Statements 43

Dup Multiple Keys1 44

Macro Substitution 44

Key Change 45

Define Field 46

LENGTH() andHEX() 46

Deleting Temporary Variables, Fields, and Files 46

Review and Analysis 47

Checking for Duplicates 47

Payroll Example 48

Accounts Payable Example 50

Chapter 5: Gaps 52

Identifying Transactions Missing from a Sequence 52

Running Gaps 52

How the Scripts Work 53

Deleting Temporary Variables, Fields, and Files 54

Review and Analysis 54

Case Study: Free Calls 55

Chapter 6:Data Profile 56

Establishing Normal Values and Investigating Exceptions 56

Running Data Profile 56

How the Scripts Work 57

Flow of Data in Data Profile 59

Data Profile Test Parameters 59

Deleting Temporary Variables, Fields, and Files 61

Review and Analysis 62

Statistics 62

Stratify 63

Round Amounts, Exact Multiples, and Frequent Values 65

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

Exact Multiples of 66

Frequently Used Values 66

Profiling with Character Fields 67

Items with the Most Exact Multiples 67

Least/Most Used Items 68

Case Study: Receipt of Inventory 69

Case Study: Exact Multiples 71

Filtering and Drilling Down 71

Filtering before Profiling 71

Filtering after Profiling 72

Chapter 7: Ratio Analysis 74

Pinpointing Suspect Transactions and Trends 74

Running Ratio Analysis 74

How the Scripts Work 78

Max/Max2 and Max/Min Ratios 78

Two Fields: Num field1 / Num field2 Ratio 80

Deleting Temporary Variables, Fields, and Files 80

Review and Analysis 81

Case Study: Dormant but Not Forgotten 84

Case Study: Doctored Bills 85

Chapter 8: Benford's Law 88

Identifying Anomalous Data 88

Understanding Benford's Law 88

Identifying Irregularities 88

Running Benford Analysis 89

Running Benford Custom Analysis 90

Creating the Custom Distribution 90

Division by Zero 91

Testing against the Custom Dist...