Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

O'Reilly logo
Forensic Analytics: Methods and Techniques for Forensic Accounting Investigations

Book Description

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

    1. Cover
    2. Title Page
    3. Copyright
    4. Dedication
    5. Preface
    6. About the Author
    7. Chapter 1: Using Access in Forensic Investigations
      1. An Introduction to Access
      2. The Architecture of Access
      3. A Review of Access Tables
      4. Importing Data into Access
      5. A Review of Access Queries
      6. Converting Excel Data into a Usable Access Format
      7. Using the Access Documenter
      8. Database Limit of 2 GB
      9. Miscellaneous Access Notes
      10. Summary
    8. Chapter 2: Using Excel in Forensic Investigations
      1. Pitfalls in Using Excel
      2. Importing Data into Excel
      3. Reporting Forensic Analytics Results
      4. Protecting Excel Spreadsheets
      5. Using Excel Results in Word Files
      6. Excel Warnings and Indicators
      7. Summary
    9. Chapter 3: Using PowerPoint in Forensic Presentations
      1. Overview of Forensic Presentations
      2. An Overview of PowerPoint
      3. Planning the Presentation
      4. Color Schemes for Forensic Presentations
      5. Problems with Forensic Reports
      6. Summary
    10. Chapter 4: High-Level Data Overview Tests
      1. The Data Profile
      2. The Data Histogram
      3. The Periodic Graph
      4. Preparing the Data Profile Using Access
      5. Preparing the Data Profile Using Excel
      6. Calculating the Inputs for the Periodic Graph in Access
      7. Preparing a Histogram in Access Using an Interval Table
      8. Summary
    11. Chapter 5: Benford's Law: The Basics
      1. An Overview of Benford's Law
      2. From Theory to Application in 60 Years
      3. Which Data Sets Should Conform to Benford's Law?
      4. The Effect of Data Set Size
      5. The Basic Digit Tests
      6. Running the First-Two Digits Test in Access
      7. Summary
    12. Chapter 6: Benford's Law: Assessing Conformity
      1. One Digit at a Time: The Z-Statistic
      2. The Chi-Square and Kolmogorov-Smirnoff Tests
      3. The Mean Absolute Deviation (MAD) Test
      4. Tests Based on the Logarithmic Basis of Benford's Law
      5. Creating a Perfect Synthetic Benford Set
      6. The Mantissa Arc Test
      7. Summary
    13. Chapter 7: Benford's Law: The Second-Order and Summation Tests
      1. A Description of the Second-Order Test
      2. The Summation Test
      3. Summary
    14. Chapter 8: Benford's Law: The Number Duplication and Last-Two Digits Tests
      1. The Number Duplication Test
      2. Running the Number Duplication Test in Access
      3. Running the Number Duplication Test in Excel
      4. The Last-Two Digits Test
      5. Summary
    15. Chapter 9: Testing the Internal Diagnostics of Current Period and Prior Period Data
      1. A Review of Descriptive Statistics
      2. An Analysis of Alumni Gifts
      3. An Analysis of Fraudulent Data
      4. Summary and Discussion
    16. Chapter 10: Identifying Fraud Using the Largest Subsets and Largest Growth Tests
      1. Findings from the Largest Subsets Test
      2. Running the Largest Subsets Test in Access
      3. Running the Largest Growth Test in Access
      4. Running the Largest Subsets Test in Excel
      5. Running the Largest Growth Test in Excel
      6. Summary
    17. Chapter 11: Identifying Anomalies Using the Relative Size Factor Test
      1. Relative Size Factor Test Findings
      2. Running the RSF Test
      3. Running the Relative Size Factor Test in Access
      4. Running the Relative Size Factor Test in Excel
      5. Summary
    18. Chapter 12: Identifying Fraud Using Abnormal Duplications within Subsets
      1. The Same-Same-Same Test
      2. The Same-Same-Different Test
      3. The Subset Number Duplication Test
      4. Running the Same-Same-Same Test in Access
      5. Running the Same-Same-Different Test in Access
      6. Running the Subset Number Duplication Test in Access
      7. Running the Same-Same-Same Test in Excel
      8. Running the Same-Same-Different Test in Excel
      9. Running the Subset Number Duplication Test in Excel
      10. Summary
    19. Chapter 13: Identifying Fraud Using Correlation
      1. The Concept of Correlation
      2. Correlation Calculations
      3. Using Correlation to Detect Fraudulent Sales Numbers
      4. Using Correlation to Detect Electricity Theft
      5. Using Correlation to Detect Irregularities in Election Results
      6. Detecting Irregularities in Pollution Statistics
      7. Calculating Correlations in Access
      8. Calculating the Correlations in Excel
      9. Summary
    20. Chapter 14: Identifying Fraud Using Time-Series Analysis
      1. Time-Series Methods
      2. An Application Using Heating Oil Sales
      3. An Application Using Stock Market Data
      4. An Application Using Construction Data
      5. An Analysis of Streamflow Data
      6. Running Time-Series Analysis in Excel
      7. Calculating the Seasonal Factors
      8. Running a Linear Regression
      9. Fitting a Curve to the Historical Data
      10. Calculating the Forecasts
      11. Summary
    21. Chapter 15: Fraud Risk Assessments of Forensic Units
      1. The Risk Scoring Method
      2. The Forensic Analytics Environment
      3. A Description of the Risk-Scoring System
      4. P1: High Food and Supplies Costs
      5. P2: Very High Food and Supplies Costs
      6. P3: Declining Sales
      7. P4: Increase in Food Costs
      8. P5: Irregular Seasonal Pattern for Sales
      9. P6: Round Numbers Reported as Sales Numbers
      10. P7: Repeating Numbers Reported as Sales Numbers
      11. P8: Inspection Rankings
      12. P9: High Receivable Balance
      13. P10: Use of Automated Reporting Procedures
      14. Final Results
      15. An Overview of the Reporting System and Future Plans
      16. Some Findings
      17. Discussion
      18. Summary
    22. Chapter 16: Examples of Risk Scoring with Access Queries
      1. The Audit Selection Method of the IRS
      2. Risk Scoring to Detect Banking Fraud
      3. Final Risk Scores
      4. Risk Scoring to Detect Travel Agent Fraud
      5. Final Results
      6. Risk Scoring to Detect Vendor Fraud
      7. Vendor Risk Scoring Using Access
      8. Summary
    23. Chapter 17: The Detection of Financial Statement Fraud
      1. The Digits of Financial Statement Numbers
      2. Detecting Biases in Accounting Numbers
      3. An Analysis of Enron's Reported Numbers
      4. An Analysis of Biased Reimbursement Numbers
      5. Detecting Manipulations in Monthly Subsidiary Reports
      6. Predictor Weightings
      7. Conclusions
      8. Summary
    24. Chapter 18: Using Analytics on Purchasing Card Transactions
      1. Purchasing Cards
      2. The National Association of Purchasing Card Professionals
      3. A Forensic Analytics Dashboard
      4. An Example of Purchasing Card Data
      5. High-Level Data Overview
      6. The First-Order Test
      7. The Summation Test
      8. The Last-Two Digits Test
      9. The Second-Order Test
      10. The Number Duplication Test
      11. The Largest Subsets Test
      12. The Same-Same-Same Test
      13. The Same-Same-Different Test
      14. The Relative Size Factor Test
      15. Conclusions with Respect to Card Purchases
      16. A Note on Microsoft Office
      17. A Note on the Forensic Analytic Tests
      18. Conclusion
    25. References
    26. Index