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.
Table of Contents:
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
About the Author :
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.
Review :
"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)
