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.

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