Fraud Analysis Techniques using ACL + WS
Buy Rights Online Buy Rights

Rights Contact Login For More Details

More About This Title Fraud Analysis Techniques using ACL + WS


"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!"
Porter Broyles, President and founder of the Texas ACL User Group, Keynote Speaker at ACL's 2009 San Francisco Conference, Official ACL Super User

"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."
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.


David Coderre has over twenty years of experience in internal audit, management consulting, management information systems, system development, and application implementation areas. He is currently President of CAATS (Computer-Assisted Analysis Techniques and Solutions). He is the author of three highly regarded books on using data analysis for audit and fraud detection and the Institute of Internal Auditors' publication on Continuous Auditing (GTAG #3).



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 Distribution 92

How the Benford Scripts Work 93

Standard Benford Analysis 93

Benford Custom Analysis 95

Deleting Temporary Variables, Fields, and Files 96

Review and Analysis 96

Case Study: Signing Authority 98

Further Reading 98

Chapter 9: Developing ACL Scripts 100

Introduction 100

Data Analysis: Generic Approach 100

ACL Commands 102

DISPLAY Command 102

SET Command 102

DELETE Command 103

OPEN Command 103

Commands for Defining Relationships 103

Basic ACL Commands 103

IF Command 104

Variables 104

User-Defined Variables 106

Using System and User-Defined Variables 106

DEFINE Field/Expression 106

Workspaces 107

Sharing Workspaces 109

Scripts 109

What Is a Script? 109

Creating Scripts 109

Commenting Scripts 113


END 113

Editing Scripts 113

Running Scripts 114

RUN ACL Script from DOS 115

Saving a Script to a BAT file 115

Interactive Scripts 117

ACCEPT Command 117

Dialog Boxes 118

Adding Selections to Drop-Down and Project

ItemLists 119

Macro Substitution 120

Editing Dialog Boxes 120

Subscripts 122

Special Uses for Subscripts 122

Repeating a Script 123

Error Trapping 123

Consolidation Exercise 124

Advanced ACL Scripting Techniques 125

GROUP Command 125

Simple GROUP 126

Conditional GROUP 126

Nested GROUP 128

LOOP and OFFSET() 132

Applications Menu 135

Building an Application Menu 137

Creating Submenus 137

Chapter 10: Utility Scripts 138

Auto Execute 138

Extract Values 139

Ending Balance Verification 140

Running Total 142

Maximum and Minimum Values 143

Appendix: ACL Installation Process145