Fraud Analysis Techniques Using ACL

Table of Contents:
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 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 COMMENT 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 Process 145 Glossary 149 Index 155

About the Author :
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).