Forensic Analytics: Methods and Techniques for Forensic Accounting Investigations
Buy Rights Online Buy Rights

Rights Contact Login For More Details

More About This Title Forensic Analytics: Methods and Techniques for Forensic Accounting Investigations


Discover how to detect fraud, biases, or errors in your data using Access or Excel

With over 300 images, Forensic Analytics reviews and shows how twenty substantive and rigorous tests can be used to detect fraud, errors, estimates, or biases in your data. For each test, the original data is shown with the steps needed to get to the final result. The tests range from high-level data overviews to assess the reasonableness of data, to highly focused tests that give small samples of highly suspicious transactions. These tests are relevant to your organization, whether small or large, for profit, nonprofit, or government-related.

  • Demonstrates how to use Access, Excel, and PowerPoint in a forensic setting
  • Explores use of statistical techniques such as Benford's Law, descriptive statistics, correlation, and time-series analysis to detect fraud and errors
  • Discusses the detection of financial statement fraud using various statistical approaches
  • Explains how to score locations, agents, customers, or employees for fraud risk
  • Shows you how to become the data analytics expert in your organization

Forensic Analytics shows how you can use Microsoft Access and Excel as your primary data interrogation tools to find exceptional, irregular, and anomalous records.


MARK J. NIGRINI, PHD, is an Associate Professor at The College of New Jersey, where he teaches auditing and forensic accounting. His current research addresses forensic and continuous monitoring techniques and advanced theoretical work on Benford's Law. Dr. Nigrini has published his Benford's Law and forensic accounting research in academic journals and in professional accounting and auditing publications. He has been interviewed on radio and television and his work has been discussed in publications including the Wall Street Journal and the New York Times.


Preface xi

About the Author xv

Chapter 1: Using Access in Forensic Investigations 1

An Introduction to Access 2

The Architecture of Access 4

A Review of Access Tables 6

Importing Data into Access 8

A Review of Access Queries 10

Converting Excel Data into a Usable Access Format 13

Using the Access Documenter 20

Database Limit of 2 GB 24

Miscellaneous Access Notes 24

Summary 25

Chapter 2: Using Excel in Forensic Investigations 27

Pitfalls in Using Excel 28

Importing Data into Excel 30

Reporting Forensic Analytics Results 32

Protecting Excel Spreadsheets 34

Using Excel Results in Word Files 36

Excel Warnings and Indicators 40

Summary 41

Chapter 3: Using PowerPoint in Forensic Presentations 43

Overview of Forensic Presentations 44

An Overview of PowerPoint 44

Planning the Presentation 45

Color Schemes for Forensic Presentations 46

Problems with Forensic Reports 50

Summary 61

Chapter 4: High-Level Data Overview Tests 63

The Data Profile 64

The Data Histogram 67

The Periodic Graph 69

Preparing the Data Profile Using Access 70

Preparing the Data Profile Using Excel 77

Calculating the Inputs for the Periodic Graph in Access 79

Preparing a Histogram in Access Using an Interval Table 81

Summary 83

Chapter 5: Benford’s Law: The Basics 85

An Overview of Benford’s Law 86

From Theory to Application in 60 Years 89

Which Data Sets Should Conform to Benford’s Law? 97

The Effect of Data Set Size 98

The Basic Digit Tests 99

Running the First-Two Digits Test in Access 102

Summary 107

Chapter 6: Benford’s Law: Assessing Conformity 109

One Digit at a Time: The Z-Statistic 110

The Chi-Square and Kolmogorov-Smirnoff Tests 111

The Mean Absolute Deviation (MAD) Test 114

Tests Based on the Logarithmic Basis of Benford’s Law 115

Creating a Perfect Synthetic Benford Set 121

The Mantissa Arc Test 122

Summary 129

Chapter 7: Benford’s Law: The Second-Order and Summation Tests 130

A Description of the Second-Order Test 131

The Summation Test 144

Summary 151

Chapter 8: Benford’s Law: The Number Duplication and Last-Two Digits Tests 153

The Number Duplication Test 154

Running the Number Duplication Test in Access 155

Running the Number Duplication Test in Excel 164

The Last-Two Digits Test 167

Summary 172

Chapter 9: Testing the Internal Diagnostics of Current Period and Prior Period Data 173

A Review of Descriptive Statistics 175

An Analysis of Alumni Gifts 178

An Analysis of Fraudulent Data 182

Summary and Discussion 189

Chapter 10: Identifying Fraud Using the Largest Subsets and Largest Growth Tests 191

Findings From the Largest Subsets Test 193

Running the Largest Subsets Test in Access 195

Running the Largest Growth Test in Access 197

Running the Largest Subsets Test in Excel 200

Running the Largest Growth Test in Excel 203

Summary 210

Chapter 11: Identifying Anomalies Using the Relative Size Factor Test 212

Relative Size Factor Test Findings 213

Running the RSF Test 215

Running the Relative Size Factor Test in Access 216

Running the Relative Size Factor Test in Excel 226

Summary 232

Chapter 12: Identifying Fraud Using Abnormal Duplications within Subsets 233

The Same-Same-Same Test 234

The Same-Same-Different Test 235

The Subset Number Duplication Test 236

Running the Same-Same-Same Test in Access 238

Running the Same-Same-Different Test in Access 239

Running the Subset Number Duplication Test in Access 244

Running the Same-Same-Same Test in Excel 248

Running the Same-Same-Different Test in Excel 252

Running the Subset Number Duplication Test in Excel 256

Summary 262

Chapter 13: Identifying Fraud Using Correlation 263

The Concept of Correlation 264

Correlation Calculations 272

Using Correlation to Detect Fraudulent Sales Numbers 272

Using Correlation to Detect Electricity Theft 276

Using Correlation to Detect Irregularities in Election Results 278

Detecting Irregularities in Pollution Statistics 282

Calculating Correlations in Access 287

Calculating the Correlations in Excel 291

Summary 295

Chapter 14: Identifying Fraud Using Time-Series Analysis 297

Time-Series Methods 299

An Application Using Heating Oil Sales 299

An Application Using Stock Market Data 303

An Application Using Construction Data 306

An Analysis of Streamflow Data 313

Running Time-Series Analysis in Excel 319

Calculating the Seasonal Factors 320

Running a Linear Regression 322

Fitting a Curve to the Historical Data 324

Calculating the Forecasts 325

Summary 330

Chapter 15: Fraud Risk Assessments of Forensic Units 332

The Risk Scoring Method 333

The Forensic Analytics Environment 335

A Description of the Risk-Scoring System 336

P1: High Food and Supplies Costs 338

P2: Very High Food and Supplies Costs 339

P3: Declining Sales 340

P4: Increase in Food Costs 342

P5: Irregular Seasonal Pattern for Sales 344

P6: Round Numbers Reported as Sales Numbers 346

P7: Repeating Numbers Reported as Sales Numbers 347

P8: Inspection Rankings 347

P9: High Receivable Balance 348

P10: Use of Automated Reporting Procedures 348

Final Results 349

An Overview of the Reporting System and Future Plans 350

Some Findings 351

Discussion 353

Summary 353

Chapter 16: Examples of Risk Scoring with Access Queries 355

The Audit Selection Method of the IRS 356

Risk Scoring to Detect Banking Fraud 360

Final Risk Scores 364

Risk Scoring to Detect Travel Agent Fraud 364

Final Results 369

Risk Scoring to Detect Vendor Fraud 369

Vendor Risk Scoring Using Access 376

Summary 385

Chapter 17: The Detection of Financial Statement Fraud 388

The Digits of Financial Statement Numbers 388

Detecting Biases in Accounting Numbers 395

An Analysis of Enron’s Reported Numbers 398

An Analysis of Biased Reimbursement Numbers 399

Detecting Manipulations in Monthly Subsidiary Reports 404

Predictor Weightings 421

Conclusions 423

Summary 424

Chapter 18: Using Analytics on Purchasing Card Transactions 425

Purchasing Cards 426

The National Association of Purchasing Card Professionals 432

A Forensic Analytics Dashboard 433

An Example of Purchasing Card Data 433

High-Level Data Overview 435

The First-Order Test 438

The Summation Test 440

The Last-Two Digits Test 440

The Second-Order Test 441

The Number Duplication Test 442

The Largest Subsets Test 444

The Same-Same-Same Test 446

The Same-Same-Different Test 446

The Relative Size Factor Test 448

Conclusions with Respect to Card Purchases 449

A Note on Microsoft Office 450

A Note on the Forensic Analytic Tests 451

Conclusion 452

References 455

Index 459


"Golden, a recognized, practice management advisor and marketing specialist I social media use and strategy, particularly in the Certified Public Accountant field, has written an accessible, comprehensive guidebook to social media for professionals and their firms with an emphasis on the accounting and legal industries. She sets forth a one-stop, reference handbook that can be read from cover-to-cover or by chapter, as needed." (Security Management)