### Available:*

Library | Call Number | Material Type | Home Location | Status |
---|---|---|---|---|

Central Library | HV6768 .N54 2011 | Adult Non-Fiction | Non-Fiction Area | Searching... |

### On Order

### Summary

### Summary

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

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.

### Table of Contents

Preface xi | |

About the Author xv | |

Chapter 1 Using Access in Forensic Investigations | p. 1 |

An Introduction to Access | p. 2 |

The Architecture of Access | p. 4 |

A Review of Access Tables | p. 6 |

Importing Data into Access | p. 8 |

A Review of Access Queries | p. 10 |

Converting Excel Data into a Usable Access Format | p. 13 |

Using the Access Documenter | p. 20 |

Database Limit of 2 GB | p. 24 |

Miscellaneous Access Notes | p. 24 |

Summary | p. 25 |

Chapter 2 Using Excel in Forensic Investigations | p. 27 |

Pitfalls in Using Excel | p. 28 |

Importing Data into Excel | p. 30 |

Reporting Forensic Analytics Results | p. 32 |

Protecting Excel Spreadsheets | p. 34 |

Using Excel Results in Word Files | p. 36 |

Excel Warnings and Indicators | p. 40 |

Summary | p. 41 |

Chapter 3 Using PowerPoint in Forensic Presentations | p. 43 |

Overview of Forensic Presentations | p. 44 |

An Overview of PowerPoint | p. 44 |

Planning the Presentation | p. 45 |

Color Schemes for Forensic Presentations | p. 46 |

Problems with Forensic Reports | p. 50 |

Summary | p. 61 |

Chapter 4 High-Level Data Overview Tests | p. 63 |

The Data Profile | p. 64 |

The Data Histogram | p. 67 |

The Periodic Graph | p. 69 |

Preparing the Data Profile Using Access | p. 70 |

Preparing the Data Profile Using Excel | p. 77 |

Calculating the Inputs for the Periodic Graph in Access | p. 79 |

Preparing a Histogram in Access Using an Interval Table | p. 81 |

Summary | p. 83 |

Chapter 5 Benford's Law: The Basics | p. 85 |

An Overview of Benford's Law | p. 86 |

From Theory to Application in | p. 60 |

Years | p. 89 |

Which Data Sets Should Conform to Benford's Law? | p. 97 |

The Effect of Data Set Size | p. 98 |

The Basic Digit Tests | p. 99 |

Running the First-Two Digits Test in Access | p. 102 |

Summary | p. 107 |

Chapter 6 Benford's Law: Assessing Conformity | p. 109 |

One Digit at a Time: The Z-Statistic | p. 110 |

The Chi-Square and Kolmogorov-Smirnoff Tests | p. 111 |

The Mean Absolute Deviation (MAD) Test | p. 114 |

Tests Based on the Logarithmic Basis of Benford's Law | p. 115 |

Creating a Perfect Synthetic Benford Set | p. 121 |

The Mantissa Arc Test | p. 122 |

Summary | p. 129 |

Chapter 7 Benford's Law: The Second-Order and Summation Tests | p. 130 |

A Description of the Second-Order Test | p. 131 |

The Summation Test | p. 144 |

Summary | p. 151 |

Chapter 8 Benford's Law: The Number Duplication and Last-Two Digits Tests | p. 153 |

The Number Duplication Test | p. 154 |

Running the Number Duplication Test in Access | p. 155 |

Running the Number Duplication Test in Excel | p. 164 |

The Last-Two Digits Test | p. 167 |

Summary | p. 172 |

Chapter 9 Testing the Internal Diagnostics of Current Period and Prior Period Data | p. 173 |

A Review of Descriptive Statistics | p. 175 |

An Analysis of Alumni Gifts | p. 178 |

An Analysis of Fraudulent Data | p. 182 |

Summary and Discussion | p. 189 |

Chapter 10 Identifying Fraud Using the Largest Subsets and Largest Growth Tests | p. 191 |

Findings From the Largest Subsets Test | p. 193 |

Running the Largest Subsets Test in Access | p. 195 |

Running the Largest Growth Test in Access | p. 197 |

Running the Largest Subsets Test in Excel | p. 200 |

Running the Largest Growth Test in Excel | p. 203 |

Summary | p. 210 |

Chapter 11 Identifying Anomalies Using the Relative Size Factor Test | p. 212 |

Relative Size Factor Test Findings | p. 213 |

Running the RSF Test | p. 215 |

Running the Relative Size Factor Test in Access | p. 216 |

Running the Relative Size Factor Test in Excel | p. 226 |

Summary | p. 232 |

Chapter 12 Identifying Fraud Using Abnormal Duplications within Subsets | p. 233 |

The Same-Same-Same Test | p. 234 |

The Same-Same-Different Test | p. 235 |

The Subset Number Duplication Test | p. 236 |

Running the Same-Same-Same Test in Access | p. 238 |

Running the Same-Same-Different Test in Access | p. 239 |

Running the Subset Number Duplication Test in Access | p. 244 |

Running the Same-Same-Same Test in Excel | p. 248 |

Running the Same-Same-Different Test in Excel | p. 252 |

Running the Subset Number Duplication Test in Excel | p. 256 |

Summary | p. 262 |

Chapter 13 Identifying Fraud Using Correlation | p. 263 |

The Concept of Correlation | p. 264 |

Correlation Calculations | p. 272 |

Using Correlation to Detect Fraudulent Sales Numbers | p. 272 |

Using Correlation to Detect Electricity Theft | p. 276 |

Using Correlation to Detect Irregularities in Election Results | p. 278 |

Detecting Irregularities in Pollution Statistics | p. 282 |

Calculating Correlations in Access | p. 287 |

Calculating the Correlations in Excel | p. 291 |

Summary | p. 295 |

Chapter 14 Identifying Fraud Using Time-Series Analysis | p. 297 |

Time-Series Methods | p. 299 |

An Application Using Heating Oil Sales | p. 299 |

An Application Using Stock Market Data | p. 303 |

An Application Using Construction Data | p. 306 |

An Analysis of Streamflow Data | p. 313 |

Running Time-Series Analysis in Excel | p. 319 |

Calculating the Seasonal Factors | p. 320 |

Running a Linear Regression | p. 322 |

Fitting a Curve to the Historical Data | p. 324 |

Calculating the Forecasts | p. 325 |

Summary | p. 330 |

Chapter 15 Fraud Risk Assessments of Forensic Units | p. 332 |

The Risk Scoring Method | p. 333 |

The Forensic Analytics Environment | p. 335 |

A Description of the Risk-Scoring System | p. 336 |

P1: High Food and Supplies Costs | p. 338 |

P2: Very High Food and Supplies Costs | p. 339 |

P3: Declining Sales | p. 340 |

P4: Increase in Food Costs | p. 342 |

P5: Irregular Seasonal Pattern for Sales | p. 344 |

P6: Round Numbers Reported as Sales Numbers | p. 346 |

P7: Repeating Numbers Reported as Sales Numbers | p. 347 |

P8: Inspection Rankings | p. 347 |

P9: High Receivable Balance | p. 348 |

P10: Use of Automated Reporting Procedures | p. 348 |

Final Results | p. 349 |

An Overview of the Reporting System and Future Plans | p. 350 |

Some Findings | p. 351 |

Discussion | p. 353 |

Summary | p. 353 |

Chapter 16 Examples of Risk Scoring with Access Queries | p. 355 |

The Audit Selection Method of the IRS | p. 356 |

Risk Scoring to Detect Banking Fraud | p. 360 |

Final Risk Scores | p. 364 |

Risk Scoring to Detect Travel Agent Fraud | p. 364 |

Final Results | p. 369 |

Risk Scoring to Detect Vendor Fraud | p. 369 |

Vendor Risk Scoring Using Access | p. 376 |

Summary | p. 385 |

Chapter 17 The Detection of Financial Statement Fraud | p. 388 |

The Digits of Financial Statement Numbers | p. 388 |

Detecting Biases in Accounting Numbers | p. 395 |

An Analysis of Enron's Reported Numbers | p. 398 |

An Analysis of Biased Reimbursement Numbers | p. 399 |

Detecting Manipulations in Monthly Subsidiary Reports | p. 404 |

Predictor Weightings | p. 421 |

Conclusions | p. 423 |

Summary | p. 424 |

Chapter 18 Using Analytics on Purchasing Card Transactions | p. 425 |

Purchasing Cards | p. 426 |

The National Association of Purchasing Card Professionals | p. 432 |

A Forensic Analytics Dashboard | p. 433 |

An Example of Purchasing Card Data | p. 433 |

High-Level Data Overview | p. 435 |

The First-Order Test | p. 438 |

The Summation Test | p. 440 |

The Last-Two Digits Test | p. 440 |

The Second-Order Test | p. 441 |

The Number Duplication Test | p. 442 |

The Largest Subsets Test | p. 444 |

The Same-Same-Same Test | p. 446 |

The Same-Same-Different Test | p. 446 |

The Relative Size Factor Test | p. 448 |

Conclusions with Respect to Card Purchases | p. 449 |

A Note on Microsoft Office | p. 450 |

A Note on the Forensic Analytic Tests | p. 451 |

Conclusion | p. 452 |

References | p. 455 |

Index | p. 459 |