# Forensic Analytics

## Methods and Techniques for Forensic Accounting Investigations

Wiley Corporate F&A

2. Edition July 2020

544 Pages, Hardcover*Wiley & Sons Ltd*

**978-1-119-58576-3**

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.

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 theta 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