Become the forensic analytics expert in your organization using effective and efficient data analysis tests to find anomalies, biases, and potential fraud - the updated new edition
Forensic Analytics reviews the methods and techniques that forensic accountants can use to detect intentional and unintentional errors, fraud, and biases. This updated second edition shows accountants and auditors how analyzing their corporate or public sector data can highlight transactions, balances, or subsets of transactions or balances in need of attention. These tests are made up of a set of initial high-level overview tests followed by a series of more focused tests. These focused tests use a variety of quantitative methods including Benford’s Law, outlier detection, the detection of duplicates, a comparison to benchmarks, time-series methods, risk-scoring, and sometimes simply statistical logic. The tests in the new edition include the newly developed vector variation score that quantifies the change in an array of data from one period to the next. The goals of the tests are to either produce a small sample of suspicious transactions, a small set of transaction groups, or a risk score related to individual transactions or a group of items.
The new edition includes over two hundred figures. Each chapter, where applicable, includes one or more cases showing how the tests under discussion could have detected the fraud or anomalies. The new edition also includes two chapters each describing multi-million-dollar fraud schemes and the insights that can be learned from those examples. These interesting real-world examples help to make the text accessible and understandable for accounting professionals and accounting students without rigorous backgrounds in mathematics and statistics. Emphasizing practical applications, the new edition shows how to use either Excel or Access to run these analytics tests. The book also has some coverage on using Minitab, IDEA, R, and Tableau to run forensic-focused tests. The use of SAS and Power BI rounds out the software coverage. The software screenshots use the latest versions of the software available at the time of writing. This authoritative book:
- Describes the use of statistically-based techniques including Benford’s Law, descriptive statistics, and the vector variation score to detect errors and anomalies
- Shows how to run most of the tests in Access and Excel, and other data analysis software packages for a small sample of the tests
- Applies the tests under review in each chapter to the same purchasing card data from a government entity
- Includes interesting cases studies throughout that are linked to the tests being reviewed.
- Includes two comprehensive case studies where data analytics could have detected the frauds before they reached multi-million-dollar levels
- Includes a continually-updated companion website with the data sets used in the chapters, the queries used in the chapters, extra coverage of some topics or cases, end of chapter questions, and end of chapter cases.
Written by a prominent educator and researcher in forensic accounting and auditing, the new edition of Forensic Analytics: Methods and Techniques for Forensic Accounting Investigations is an essential resource for forensic accountants, auditors, comptrollers, fraud investigators, and graduate students.
Table of Contents
List of Cases xiii
About the Author xv
Preface xvii
Abbreviations xxi
Analytics Software Used xxv
Introduction 1
Temptation in an Occupation 2
Fraudulent Checks Written by the CFO 4
Fraudulent Purchases Made by a Purchasing Manager 7
Donna was a Gamblin’ Wreck at Georgia Tech 9
Forensic Analytics 11
An Overview of Tableau 13
The Risk Assessment Standards 19
Discussion 21
Chapter 1: Using Microsoft Excel for Forensic Analytics 23
The Fraud Types Relevant to Forensic Analytics 23
The Main Steps in a Forensic Analytics Application 25
The Final Report 27
An Overview of Excel 28
Importing Data into Excel 29
Some Useful Excel Formatting Features 30
Protecting Excel Spreadsheets 32
The Valuable "IF" Function 33
The PIVOTTABLE Routine 36
The Valuable VLOOKUP Function 38
Using Excel Results in Word Files 40
Excel Warnings and Indicators 42
Excel Dashboards 43
Dashboards in Practice 46
Summary 47
Chapter 2: The Initial High-Level Overview Tests 50
The Data Profile 51
The Histogram 56
The Periodic Graph 58
Descriptive Statistics 60
Preparing the Data Profile Using Excel 62
Preparing the Data Profile Using Access 64
Preparing the Histogram in Excel and Access 68
Preparing the Histogram in IDEA and Tableau 72
Preparing the Periodic Graph in Excel and Access 74
Summary 76
Chapter 3: Benford’s Law: The Basic Tests 79
An Overview of Benford’s Law 80
Some Early Discussions of Benford’s Law 83
Selected Articles from the Eighties 85
Selected Articles from the Nineties 88
Scenarios Under Which Data Should Conform to Benford 90
The Two Scenarios Under Which Accounting Data Sets Should Conform to Benford 93
Other Considerations for the Conformity of Accounting Data 94
Accounting Data Examples 95
Preparing the Benford Graph Using Excel 98
Preparing the Benford Graph Using Access 99
Summary 101
Chapter 4: Benford’s Law: Advanced Topics 103
Conformity and the Likelihood of Material Errors 103
The First Digits Versus the First-Two Digits 107
Measuring Conformity Using the Z-Statistic 109
The Chi-Square and the Kolmogorov-Smirnoff Tests of Conformity 111
The Mean Absolute Deviation (MAD) Test 112
The Effect of Data Set Size of Conformity to Benford 114
Using Benford’s Law in a Forensic Accounting Setting 116
Using Benford’s Law for Journal Entries in an External Audit 119
Using Benford for Subsidiary Ledger Balances in an External Audit 123
Preparing the Benford Graph in Excel 125
Summary 126
Chapter 5: Benford’s Law: Completing The Cycle 127
The Number Duplication Test 127
The Number Duplications in Accounting Textbooks 132
The Electric Utility Company Fraud Case 134
The Petty Cash Fraud Scheme 136
The Last-Two Digits Test 139
The Fraudulent Credit Card Sales Scheme 141
The Missing Cash Sales Case 142
Running the Number Duplication Test in Excel 144
Running the Number Duplication Test in Access 146
Running the Last-Two Digits Test in Excel 148
Running the Last-Two Digits Test in Access 149
Running the Number Duplication Test in R 151
Summary 153
Chapter 6: Identifying Anomalous Outliers: Part 1 154
The Summation Test 155
The Fraud That Was Red Flagged by Two Qualitative Outliers 158
The Largest Subsets Test 161
The Largest Subset Growth Test 165
The School District Transportation Fraud 168
The SkyBonus Fraud Scheme 170
Running the Summation Test in Excel 170
Running the Summation Test in Access 171
Running the Largest Subsets Test in Excel 172
Running the Largest Subsets Test in Access 173
Running the Largest Growth Test in Excel 174
Running the Largest Growth Test in Access 176
Running the Largest Subsets Test in R 179
Summary 180
Chapter 7: Identifying Anomalous Outliers: Part 2 182
Examples of Relative Size Factor Test Findings 184
The Scheme That Used a Vault That Was Over Capacity 186
The Scheme That Added Sold Cars to the Car Inventory Account 189
The Vice Chairman of the Board Who Stole 0.5 Percent of His Salary 193
Running the RSF Test in Excel 194
Running the RSF Test in Access 199
Running the RSF Test in SAS 208
Summary 212
Chapter 8: Identifying Abnormal Duplications 214
The Same-Same-Same Test 215
Duplicate Payments and Various Types of Fraud 217
The Same-Same-Different (Near-Duplicates) Test 220
The Near-Duplicates Fraud Scheme: Introduction 221
The Near-Duplicates Fraud: The Act 222
The Near-Duplicates Fraud: Getting the Legal Process Started 224
The Near-Duplicates Fraud: Two Sentencing Hearings 228
The Near-Duplicates Fraud: Epilogue 230
The Subset Number Duplication Test 231
Running the Same-Same-Same Test in Excel 233
Running the Same-Same-Different Test in Excel 235
Running the Subset Number Frequency Test in Excel 237
Running the Same-Same-Same Test in Access 239
Running the Same-Same-Different Test in Access 240
Running the Subset Number Frequency Test in Access 242
Summary 245
Chapter 9: Comparing Current Period and Prior Period Data: Part 1 247
A Review of Descriptive Statistics 249
An Analysis of the Purchasing Card Data 250
My Law: An Analysis of Payroll Data 255
An Analysis of Machine Learning Data 257
An Analysis of Grocery Store Sales 261
The Scheme That Used Bank Transfers to a Secret Bank Account 263
Running the Descriptive Statistics Tests in Excel 268
Running the Descriptive Statistics Tests in Minitab 269
Running the Descriptive Statistics Tests in SAS 270
Summary and Discussion 272
Chapter 10: Comparing Current Period and Prior Period Data: Part 2 274
Vectors and Measures of Change 275
An Analysis of the Purchasing Card Data 280
Taxpayer Identity Theft Refund Fraud 282
The Tax Return That Omitted a Million Dollar Prize 284
The Tax Returns for 2000 and 2001 285
The Indictment for Tax Evasion 291
The Tax Evasion Trial 292
The Verdict and Sentencing 298
An Analysis of Joe Biden’s Tax Returns 299
Running the VVS Test in SAS 303
Summary and Discussion 304
Chapter 11: Identifying Anomalies In Time-Series Data 306
An Analysis of the Purchasing Card Data 307
Using IDEA for Time-Series Analysis 311
The Fraud Scheme That Withdrew Funds from Customer Accounts 312
Employee Data Access After Termination 317
A Time-Series Analysis of Grocery Store Sales 321
Using Correlation to Detect Fraud and Errors 322
Using the Angle θ on Trial Balance Data 324
Using the VVS on Customer Rebates 327
Showing the VVS Results in a Dashboard 332
Running Time-Series Analysis in SAS 334
Summary and Discussion 336
Chapter 12: Scoring Forensic Units for Fraud Risk 338
An Overview of Risk Scoring 339
The Audit Selection Method of the IRS 340
The Fraudulent Vendor with a Post Office Box in the Head Office 344
Risk Scoring to Detect Vendor Fraud 348
Risk Scoring to Detect Errors in Sales Reports 354
The Predictors Used in the Sales Report Scoring Model 356
The Results of the Sales Report Scoring Model 364
Summary and Discussion 365
Chapter 13: Case Study: An Employee’s Fraudulent Tax Refunds 367
Background Information 368
The Nicest Person in the Office 369
The Early Years of Tax Refund Fraud Scheme 372
The Later Years of Tax Refund Fraud Scheme 375
An Analysis of the Fraudulent Refund Amounts 376
The End Was Nigh 383
The Letter of the Law 386
Sentencing 391
Mary Ayers-Zander 392
Epilogue 393
Appendix 13A: The Fraudulent Refunds 394
Chapter 14: Case Study: A Supplier’s Fraudulent Shipping Claims 401
Background Information 401
The Fraudulent Shipping Charges Scheme 403
An Analysis of the Shipping Charges 405
Charlene’s Lifestyle 408
The Scheme is Discovered 409
The Corley Plea 412
Charlene’s Appeal for a Reduced Sentence 415
The Government’s Response to Charlene’s Memorandum 417
The Sentencing Hearing 417
The Sentence 419
Motion to Delay the Prison Term 420
Conclusions 423
Chapter 15: Detecting Financial Statement Fraud 425
An Overview of Financial Statement Fraud 426
Biases in Financial Statement Numbers 427
Enron’s Financial Statements 430
Enron’s Chief Financial Officer 432
HealthSouth’s Financial Statements 433
WorldCom’s Financial Statements 436
WorldCom’s Rounded Numbers 440
Using Benford’s Law to Detect Financial Statement Misconduct 442
Beneish’s M-Score 446
Detection and Investigation Steps 447
Detecting Manipulations in Monthly Subsidiary Reports 449
Summary 454
Chapter 16: Using Microsoft Access and R For Analytics 455
An Introduction to Access 456
The Architecture of Access 457
A Review of Access Tables 459
Importing Data into Access 461
A Review of Access Queries 462
Converting Excel Data into a Usable Access Format 465
Using the Access Documenter 466
Database Limit of 2 GB 468
Reports 469
Miscellaneous Access Notes 471
An Introduction to R 472
Installing R and R Studio 472
The Advantages of Using R 474
R Markdown 475
Running Arithmetic Code in R 475
Calculating the VVS in R 477
Summary 479
Appendix 16A: A Discussion of the Basic Commands 480
Chapter 17: Concluding Notes on Fraud Prevention and Detection 482
The Annual Cost of Employee Fraud 483
The Legal Process 484
”I’m a Lawyer, Trust [Account] Me” 485
The Rights of the Defendant 487
Possible Defenses Against an Embezzlement Charge 490
The Economics of Crime Model 492
Internal Controls 493
Fraud Risk Assessments 495
Detective Controls 496
Crime Insurance 498
Fraud Detection Methods 500
Other Fraud Prevention Methods 501
Final Words 504
Bibliography 507
Index 515
