In any organization, there may be thousands of vendors but it is difficult to determine which vendors are the most ripe for review from an error, fraud, or cost recovery perspective. Usually, these vendors are identified as the business naturally realizes which vendors have the most issues, adjustments, and company time to reconcile their account.
However, waiting for these vendors to show their faces through process errors is a reactive versus proactive approach. In order to identify the vendors that are most problematic now, we can run a variety of reports and string the results together so as to identify those vendors showing or “scoring” the most. In essence, we can mathematically calculate risk for each vendor based on the reports we consider “risky”.
In this post, we will explain using a sample ACL project of four scripts how to calculate a score for a vendor by combining the results for three reports using a key field, the unique vendor number. You can download a zip folder containing an ACL document with scripts here:
The provided scripts will execute three reports which are simple but do have a tendency to detect vendors with a potential for fraud as explained below:
- Report 1 – Score = 34% – Vendors with low volume and high value transactions – many times a fraudster will have fewer opportunities to post a phony invoice so they will try to get as much out of the organization as possible in few attempts
- Report 2 – Score = 33% – Vendors with blank information in critical fields – not everyone remembers to add the key fields to a vendor record when making a phony one.
- Report 3 – Score = 33% – Vendors with over 10 round dollar payments (values divisible by 1,000) – The fraudster may not be creative enough to arrive at amounts that have different digits, especially in the cents.
The key of this scoring approach is to string together these reports which is done via the Scoring_Script. It will kick off the three report scripts (above) and then, more importantly, assign a score to each report which is joined together on a key field, the supplier number.
More specifically, in building the score the ACL provided Scoring_Script:
- Asks for some variables around high values, low volumes and the level of round dollars that are considered high to the company. Then it asks for an invoice and supplier table for analysis with a limited number of fields needed as listed below:
a. Invoice Table
i. Vendor Number
iii. Invoice Amount
b. Vendor Table
i. Vendor Number
ii. Vendor Address
iv. Vendor Telephone
v. Vendor Tax ID
** – Any fields that you do not have you can select the BLANK_TEXT, BLANK_AMOUNT or BLANK_DATE fields accordingly which we create for you when running the script.
- It runs the scripts REPORT_1, REPORT_2, and REPORT_3 in ACL with each report’s results being at the Supplier or Vendor Number level. Therefore, each of the records from these scripts are unique supplier numbers.
- Then, each time it runs a report, a new field is created in the report with the score being written to each unique supplier number record using the ACL code below (for Report 1 below as set to 34%):
DELETE FIELD REP1SCORE OK
DEFINE FIELD REP1SCORE COMPUTED .34
** – Please note that the scores listed above are assigned to each report, respectively in the specific report scripts (i.e., REPORT 1 has the above code listed in the report itself). This could be moved to the Scoring_Script in ACL but we have done this to make the below step of joins easier to read (see Step #4)
- Using a supplier extract table of key fields from the supplier table (supextract), the script joins the report result score fields (outputs from the REPORT_1, REPORT_2, and REPORT_3 script results as in step 3 above) to thissupextract table on the supplier number.
OPEN HIGHDOLL_LOWTRAN SECONDARY
JOIN PKEY %supnum% FIELDS %supnum% SKEY %supnum% WITH REP1SCORE PRIMARY TO “joined” OPEN PRESORT SECSORT
OPEN BLANK_SUPP SECONDARY
JOIN PKEY %supnum% FIELDS %supnum% REP1SCORE SKEY %supnum% WITH REP2SCORE
PRIMARY TO “joined1? OPEN PRESORT SECSORT
OPEN LOT_OF_ROUND SECONDARY
JOIN PKEY %supnum% FIELDS %supnum% REP1SCORE REP2SCORE SKEY %supnum% WITH REP3SCORE PRIMARY TO “joined” OPEN PRESORT SECSORT
- With a supplier extract table now having each of the report scores aligned, the script concludes by calculating the total value and volume for each supplier using the invoice table which is joined back to the joined table above, creating the Final Score Report. It will finish by deleting all temporary join tables and calculating a total score by vendor.
Developing a vendor score is an excellent way to reduce false positives in your vendor sample selection. While this post focused on the development of a score by vendor, the Part 2 post on Transactional Scoring will take the analysis to the transaction level in order to calculate for each transaction that are the most risky for review.
(Source: ACL Blog)