Applying Forensic Accounting Techniques
Using SQL And Python
Kevin Feasel (@feaselkl)https://csmore.info/on/forensicpython
Who Am I? What Am I Doing Here?
A Case Study
Transportation fraud
Wake County, North Carolina
A Case Study
An auto parts company, working in conjunction with several county employees, defrauded Wake County of at least $5 million over a multi-year period.
A Case Study
In the end, several participants pled guilty and served jail time.
A Case Study
Wake County was able to claw back 5 million dollars and Barnes Motor and Parts paid back 3 million dollars, including a $2.5 million criminal fine.
A Case Study --- A Quick Story
As we go over techniques forensic accountants use to find potentially fraudulent data, we will return to this case several times.
We will cover some of the techniques forensic accounts used to break this case, as well as gain an understanding of why it took so long.
Motivation
Most of us don't spend time hunting down fraudsters using our data. But the practices and principles these forensic accountants use can still be valuable for us.
Ultimately, these techniques come down to knowing your data. "Know your data" is something you hear in a lot of talks, and this talk will go into great detail on what that actually means.
Agenda
- Know Your Data
- Basic Analysis
- Cohort Analysis
- Numeral Analysis
Know Your Data
In today's talk, we will use a simplified version of the Wake County fraud case. This is all fake data.
Fake data lets us understand the key principles without getting lost in hundreds of tables.
Know Your Data -- The Scenario
You work for a company which specializes in external audits of government agencies. Your latest assignment is to review a county transportation agency.
All we have going in is a data model and a login with read-only access. Let’s see where we can go.
Agenda
- Know Your Data
- Basic Analysis
- Cohort Analysis
- Numeral Analysis
Basic Analysis
Our basic analysis will focus on a few important areas:
- Summary Analysis
- Growth Analysis
- Regression Analysis
Summary Analysis
Summary analysis looks at things like row counts, data types, and keys.
In SQL Server, here is some of what I do to learn about a new database.
Step 1: Review Large Tables
Step 2: Review a Large Table
Step 3: Recursively Review FKs
Step 4: Rinse and Repeat
We’ve yet to see Calendar, so that might be our next stop. Or maybe VendorExpenseCategory.
Summary Analysis
Creating a database diagram is also helpful here if you don’t have one.
Growth Analysis
Growth analysis looks at changes in ratios over time. Our goal is to look for major changes which look to be far outside the norm.
Typically, we will perform growth analysis against important additive measures: company growth, revenue, cost, number of customers, etc.
Demo Time
Regression Analysis
What we have:
- A dependent variable: the thing whose behavior we want to explain.
- Independent variables: the things whose behavior explains the dependent variable.
- Known data points relating our independent variable(s) to our dependent variable.
Regression analysis can get complicated, but if you know what the data "ought" to look like, regression can help you confirm or disprove your conjecture.
Demo Time
Agenda
- Know Your Data
- Basic Analysis
- Cohort Analysis
- Numeral Analysis
Cohort Analysis
Looking at top-level aggregates or a single slice (customer, vendor, group) might help us find problems, and looking at a broad-based comparison of these slices may give us a clearer picture.
A data set can be sliced many ways, so there could be a large number of relevant cohorts.
Duplicates and Cardinality
Look for duplicates along two angles.
First, one-time codes or unenforced unique keys can show where the same data shows up multiple times.
Second, things which are very close but not quite the same, like typos (e.g., “Fundation” instead of “Foundation”).
Duplicates and Cardinality
There are several measures for the similarity of two text phrases, including Levenshtein distance, soundex, and even pattern matching using LIKE. Depending upon the level of sophistication necessary, getting the similarity of two items based on text descriptors can take quite a bit of effort!
Duplicates and Cardinality
Tying back to the Wake County fraud case, there were 24 separate days in which the auto supplier submitted 50+ invoices under $2500 to the county. Here, the duplication is in vendor and date and is well outside the expected behavior for a vendor on the up-and-up.
Demo Time
Difference Analysis
When comparing different members of a cohort, remember that there are two types of difference: differences between groups and differences within a group.
Differences between groups may indicate suspicious behavior if one member of the group acts well outside the norm, such as in the number of invoices submitted per day.
Looking at differences within a group can help explain anomalous aggregate behavior.
Box Plots
Box plots are valuable for comparing cohorts.
Outliers
Plotting a trend and looking for outliers (defined either in a technical sense or in the general sense of "well outside the norm") can dig up strange behavior. Real data sets will always have outliers, though, so the existence of outliers is not in itself suspicious.
Clustering Behavior
Something interesting when analyzing a cohort is to look for clustering around certain values. Examples: expense reports and invoices.
Going back to our Wake County example, there was a cluster around $2500, as that was the cutoff point after which a second person needed to sign off on a payment order.
In our example, our cutoff point will be $1000, after which employees need two signatures to complete an invoice.
Demo Time
Agenda
- Know Your Data
- Basic Analysis
- Cohort Analysis
- Numeral Analysis
Round Numbers
One high-level analysis of values is to get a count of "round numbers." We group into types based on the number of 0s at the end (ignoring cents). Examples:
- Type 0: no trailing zeros (e.g., 17,999)
- Type 1: one trailing zero (e.g., 180)
- Type 2: two trailing zeros (e.g., 10,200)
- Type 3: three or more trailing zeros (e.g., 5,000)
Benford's Law
Benford's Law is an interesting phenomenon, where the first digit (or two digits, or three digits) of a set of numbers tends to follow a specific pattern.
Demo Time
Last Digits
Last digits are assumed uniform unless there is a reason to believe otherwise.
Wrapping Up
These techniques are not just for catching fraud. They help you build a deeper understanding of your data and catch problems before they become expensive.
What To Do Monday
- Run row counts and review keys on your most important tables.
- Plot year-over-year trends for key measures and look for unexplained spikes.
- Check for clustering near approval thresholds (expense limits, sign-off cutoffs).
- Compare cohort behavior: does one vendor, employee, or category look different from the rest?
- Run Benford's Law against a financial data set and see what stands out.
Wrapping Up
To learn more, go here:
https://csmore.info/on/forensicpython
And for help, contact me:
feasel@catallaxyservices.com | @feaselkl
Catallaxy Services consulting:
https://CSmore.info/on/contact