Discover how to detect fraud, biases, or errors in your data using Access or ExcelWith 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 settingExplores use of statistical techniques such as Benford's Law, descriptive statistics, correlation, and time-series analysis to detect fraud and errorsDiscusses the detection of financial statement fraud using various statistical approachesExplains how to score locations, agents, customers, or employees for fraud riskShows you how to become the data analytics expert in your organizationForensic Analytics shows how you can use Microsoft Access and Excel as your primary data interrogation tools to find exceptional, irregular, and anomalous records.
Author(s): Mark J. Nigrini
Series: Wiley Corporate F&A
Edition: 1
Publisher: Wiley
Year: 2011
Language: English
Pages: 481
Tags: Юридические дисциплины;Криминалистика;Криминалистическая методика;
FORENSIC ANALYTICS: Methods and Techniques for Forensic Accounting Investigations......Page 1
Contents......Page 7
Preface......Page 13
About the Author......Page 17
1 Using Access in Forensic Investigations......Page 19
AN INTRODUCTION TO ACCESS......Page 20
THE ARCHITECTURE OF ACCESS......Page 22
A REVIEW OF ACCESS TABLES......Page 24
IMPORTING DATA INTO ACCESS......Page 26
A REVIEW OF ACCESS QUERIES......Page 28
CONVERTING EXCEL DATA INTO A USABLE ACCESS FORMAT......Page 31
USING THE ACCESS DOCUMENTER......Page 38
MISCELLANEOUS ACCESS NOTES......Page 42
SUMMARY......Page 43
2 Using Excel in Forensic Investigations......Page 45
PITFALLS IN USING EXCEL......Page 46
IMPORTING DATA INTO EXCEL......Page 48
REPORTING FORENSIC ANALYTICS RESULTS......Page 50
PROTECTING EXCEL SPREADSHEETS......Page 52
USING EXCEL RESULTS IN WORD FILES......Page 54
EXCEL WARNINGS AND INDICATORS......Page 58
SUMMARY......Page 59
3 Using PowerPoint in Forensic Presentations......Page 61
AN OVERVIEW OF POWERPOINT......Page 62
PLANNING THE PRESENTATION......Page 63
COLOR SCHEMES FOR FORENSIC PRESENTATIONS......Page 64
PROBLEMS WITH FORENSIC REPORTS......Page 68
SUMMARY......Page 79
4 High-Level Data Overview Tests......Page 81
THE DATA PROFILE......Page 82
THE DATA HISTOGRAM......Page 85
THE PERIODIC GRAPH......Page 87
PREPARING THE DATA PROFILE USING ACCESS......Page 88
PREPARING THE DATA PROFILE USING EXCEL......Page 95
CALCULATING THE INPUTS FOR THE PERIODIC GRAPH IN ACCESS......Page 97
PREPARING A HISTOGRAM IN ACCESS USING AN INTERVAL TABLE......Page 99
SUMMARY......Page 101
5 Benford’s Law: The Basics......Page 103
AN OVERVIEW OF BENFORD’S LAW......Page 104
FROM THEORY TO APPLICATION IN 60 YEARS......Page 107
WHICH DATA SETS SHOULD CONFORM TO BENFORD’S LAW?......Page 115
THE EFFECT OF DATA SET SIZE......Page 116
THE BASIC DIGIT TESTS......Page 117
RUNNING THE FIRST-TWO DIGITS TEST IN ACCESS......Page 120
SUMMARY......Page 125
6 Benford’s Law: Assessing Conformity......Page 127
ONE DIGIT AT A TIME: THE Z-STATISTIC......Page 128
THE CHI-SQUARE AND KOLMOGOROV-SMIRNOFF TESTS......Page 129
THE MEAN ABSOLUTE DEVIATION (MAD) TEST......Page 132
TESTS BASED ON THE LOGARITHMIC BASIS OF BENFORD’S LAW......Page 133
CREATING A PERFECT SYNTHETIC BENFORD SET......Page 139
THE MANTISSA ARC TEST......Page 140
SUMMARY......Page 147
7 Benford’s Law: The Second-Order and Summation Tests......Page 148
A DESCRIPTION OF THE SECOND-ORDER TEST......Page 149
THE SUMMATION TEST......Page 162
SUMMARY......Page 169
8 Benford’s Law: The Number Duplication and Last-Two Digits Tests......Page 171
THE NUMBER DUPLICATION TEST......Page 172
RUNNING THE NUMBER DUPLICATION TEST IN ACCESS......Page 173
RUNNING THE NUMBER DUPLICATION TEST IN EXCEL......Page 182
THE LAST-TWO DIGITS TEST......Page 185
SUMMARY......Page 190
9 Testing the Internal Diagnostics of Current Period and Prior Period Data......Page 191
A REVIEW OF DESCRIPTIVE STATISTICS......Page 193
AN ANALYSIS OF ALUMNI GIFTS......Page 196
AN ANALYSIS OF FRAUDULENT DATA......Page 200
SUMMARY AND DISCUSSION......Page 207
10 Identifying Fraud Using the Largest Subsets and Largest Growth Tests......Page 209
FINDINGS FROM THE LARGEST SUBSETS TEST......Page 211
RUNNING THE LARGEST SUBSETS TEST IN ACCESS......Page 213
RUNNING THE LARGEST GROWTH TEST IN ACCESS......Page 215
RUNNING THE LARGEST SUBSETS TEST IN EXCEL......Page 218
RUNNING THE LARGEST GROWTH TEST IN EXCEL......Page 221
SUMMARY......Page 228
11 Identifying Anomalies Using the Relative Size Factor Test......Page 230
RELATIVE SIZE FACTOR TEST FINDINGS......Page 231
RUNNING THE RSF TEST......Page 233
RUNNING THE RELATIVE SIZE FACTOR TEST IN ACCESS......Page 234
RUNNING THE RELATIVE SIZE FACTOR TEST IN EXCEL......Page 244
SUMMARY......Page 250
12 Identifying Fraud Using Abnormal Duplications within Subsets......Page 251
THE SAME-SAME-SAME TEST......Page 252
THE SAME-SAME-DIFFERENT TEST......Page 253
THE SUBSET NUMBER DUPLICATION TEST......Page 254
RUNNING THE SAME-SAME-SAME TEST IN ACCESS......Page 256
RUNNING THE SAME-SAME-DIFFERENT TEST IN ACCESS......Page 257
RUNNING THE SUBSET NUMBER DUPLICATION TEST IN ACCESS......Page 262
RUNNING THE SAME-SAME-SAME TEST IN EXCEL......Page 266
RUNNING THE SAME-SAME-DIFFERENT TEST IN EXCEL......Page 270
RUNNING THE SUBSET NUMBER DUPLICATION TEST IN EXCEL......Page 274
SUMMARY......Page 280
13 Identifying Fraud Using Correlation......Page 281
THE CONCEPT OF CORRELATION......Page 282
USING CORRELATION TO DETECT FRAUDULENT SALES NUMBERS......Page 290
USING CORRELATION TO DETECT ELECTRICITY THEFT......Page 294
USING CORRELATION TO DETECT IRREGULARITIES IN ELECTION RESULTS......Page 296
DETECTING IRREGULARITIES IN POLLUTION STATISTICS......Page 300
CALCULATING CORRELATIONS IN ACCESS......Page 305
CALCULATING THE CORRELATIONS IN EXCEL......Page 309
SUMMARY......Page 313
14 Identifying Fraud Using Time-Series Analysis......Page 315
AN APPLICATION USING HEATING OIL SALES......Page 317
AN APPLICATION USING STOCK MARKET DATA......Page 321
AN APPLICATION USING CONSTRUCTION DATA......Page 324
AN ANALYSIS OF STREAMFLOW DATA......Page 331
RUNNING TIME-SERIES ANALYSIS IN EXCEL......Page 337
CALCULATING THE SEASONAL FACTORS......Page 338
RUNNING A LINEAR REGRESSION......Page 340
FITTING A CURVE TO THE HISTORICAL DATA......Page 342
CALCULATING THE FORECASTS......Page 343
SUMMARY......Page 348
15 Fraud Risk Assessments of Forensic Units......Page 350
THE RISK SCORING METHOD......Page 351
THE FORENSIC ANALYTICS ENVIRONMENT......Page 353
A DESCRIPTION OF THE RISK-SCORING SYSTEM......Page 354
P1: HIGH FOOD AND SUPPLIES COSTS......Page 356
P2: VERY HIGH FOOD AND SUPPLIES COSTS......Page 357
P3: DECLINING SALES......Page 358
P4: INCREASE IN FOOD COSTS......Page 360
P5: IRREGULAR SEASONAL PATTERN FOR SALES......Page 362
P6: ROUND NUMBERS REPORTED AS SALES NUMBERS......Page 364
P8: INSPECTION RANKINGS......Page 365
P10: USE OF AUTOMATED REPORTING PROCEDURES......Page 366
FINAL RESULTS......Page 367
AN OVERVIEW OF THE REPORTING SYSTEM AND FUTURE PLANS......Page 368
SOME FINDINGS......Page 369
SUMMARY......Page 371
16 Examples of Risk Scoring with Access Queries......Page 373
THE AUDIT SELECTION METHOD OF THE IRS......Page 374
RISK SCORING TO DETECT BANKING FRAUD......Page 378
RISK SCORING TO DETECT TRAVEL AGENT FRAUD......Page 382
RISK SCORING TO DETECT VENDOR FRAUD......Page 387
VENDOR RISK SCORING USING ACCESS......Page 394
SUMMARY......Page 403
THE DIGITS OF FINANCIAL STATEMENT NUMBERS......Page 406
DETECTING BIASES IN ACCOUNTING NUMBERS......Page 413
AN ANALYSIS OF ENRON’S REPORTED NUMBERS......Page 416
AN ANALYSIS OF BIASED REIMBURSEMENT NUMBERS......Page 417
DETECTING MANIPULATIONS IN MONTHLY SUBSIDIARY REPORTS......Page 422
PREDICTOR WEIGHTINGS......Page 439
CONCLUSIONS......Page 441
SUMMARY......Page 442
18 Using Analytics on Purchasing Card Transactions......Page 443
PURCHASING CARDS......Page 444
THE NATIONAL ASSOCIATION OF PURCHASING CARD PROFESSIONALS......Page 450
AN EXAMPLE OF PURCHASING CARD DATA......Page 451
HIGH-LEVEL DATA OVERVIEW......Page 453
THE FIRST-ORDER TEST......Page 456
THE LAST-TWO DIGITS TEST......Page 458
THE SECOND-ORDER TEST......Page 459
THE NUMBER DUPLICATION TEST......Page 460
THE LARGEST SUBSETS TEST......Page 462
THE SAME-SAME-DIFFERENT TEST......Page 464
THE RELATIVE SIZE FACTOR TEST......Page 466
CONCLUSIONS WITH RESPECT TO CARD PURCHASES......Page 467
A NOTE ON MICROSOFT OFFICE......Page 468
A NOTE ON THE FORENSIC ANALYTIC TESTS......Page 469
CONCLUSION......Page 470
References......Page 473
Index......Page 477
