Derrick Wong (Managing Director)
Popular Analytics Tests (Final Series)
Updated: May 16
Many Internal Audit team needs to prove ROI if they intend to invest on Analytics technology. The best way is to achieve quick implementation of analytics use cases, and start testing it out on their data. When it comes to analytics use cases, we always share with our customers on 2 things:
1) Analytics is only limited to your own creativity.
2) No Two Organizations are Identical, But there will be commonly use analytics which many will be using the same tests.
So to achieve quick ROI on your analytics, we have come up with 6 areas of the common and popular analytics tests that you can consider. They are:
1) Employee Spending
2) Vendor Management
4) GL JE Risk Scoring
5) Duplicate Payments
6) Counterparty Validation & Outliers
Do follow us on our LinkedIn or subscribe to our newsletters for updates. We have discussed about Duplicate Payments in Part 5 (url link) series. For part 6, we are going to discuss about Counterparty Validation & Outliers.
The risks of transacting with counterparties on watch lists is high. The GSA SAM list contains people and organizations that have committed fraud against the federal government. The OFAC list consists of parties that are suspected of or have committed terrorism. And there are many other watchlists worldwide that should be considered. There are multiple ways in which your counterparties (employees, customers, vendors, and contractors can be compared to such lists.
Normalized Names and Addresses
Use the SortNormalize function to standardize the names and addresses of your counterparties and the population of the watch list. Then, use a many-to-many Join to identify matches. It's possible to integrate a fuzzy search dimension by specifying a filter in the Join for matches that are within one character of each other: Difference(Vendor_Name, OFAC_Name) <=1
Percent of Word Matches
The normalized names and addresses can be parsed into their individual words with a script. A matching word score can be calculated to display the % of matching words between any two pairs of names or addresses. This level of granularity allows you to quickly order the results with the highest % matches first for review.
Outliers are transactions where the materiality is well beyond historical expectations. Because of their size, errors in processing them can result in misstatements. A very large outlier can also distort what would be considered "normal" for a population.
Population Level testing
A common criterion for outliers is that the values are more than two standard deviations above the average for a given population. The Statistics command can quickly generate the mean and the standard deviation:
To identify outliers, create a filter: Total_Cost > 1491 + (2 * 3155.49)
Single Category Level testing
It's also possible to identify outliers at a category level, such as vendors, using the enhanced Summarize command:
1. In the Summarize command, choose the Vendor field as the key field.
2. Open the "Fields to process" dialog
3. Select "Amount" twice
4. Change the Type to AVG and STDDEV
The output file contains the mean and the standard deviation for each vendor: 5. Create a computed field for the 2 SD threshold: AVG_Amount + (2 * STDDEV_Amount)
6. Open the transaction file.
7. Join to the vendor threshold file and add the threshold field to the output.
8. Filter for Amount > Vendor_Threshold
Multiple Category Level Testing
It's also possible to add multiple levels to the category testing. For example, you could test by Vendor-Product combinations to test for pricing consistency. Follow the same steps as in the previous example, using the Vendor and the Product ID as the key fields.
We hope these 6 part series of Popular Analytics Tests has help you in enhancing and getting more value from your data analytics. Please feel free to reach out to our team firstname.lastname@example.org for further discussion on your areas you will like to cover.