2014-08-01
Part 1 of this post was focused on developing a vendor score to improve sample selections of vendors. The vendor score was assigned based on the key field, a unique vendor number and aligned three reports to this supplier number. This is a simplistic approach as it only provides a binary YES or NO if the supplier is on the report and does not provide a severity of each vendor based on their full set of vendor transactions showing on a given report. To provide an improved approach, this post will now take the scoring concept explained at the vendor level and apply it to thetransactions for that vendor. We arrived at this approach of transactional scoring by first running the vendor score and realizing that the summary per vendor was not accurate enough as it would identify the entire vendor each time even if only one of the vendor’s invoice transactions displayed on the report. Therefore, we determined it was better to count up the invoice transactions that showed on an invoice-level report and then divide that by the total number of invoice transactions for that vendor. So, instead of one vendor transaction leading to a full score, rather we were now taking the number of transactions for that vendor showing on the invoice report (i.e., 100) divided by the total invoice transactions (for example 1,000) to arrive at a 10% report score to transactions ratio.
We will be referring to the code used in Part 1 of this post as to explain how it can be edited in order to run at an invoice transactional level. You can download a zip folder containing an ACL document with scripts here: Download folder
Taking the example above, we wanted to define the transactional score ratio as follows: The rate of (Error / Fraud / Control Failure / Recovery) report occurrence of a transaction whereby the score is set at 100% or 1 for each transaction based on the number of reports it appears on as run at this transactional level. So, if we had 25 reports in the transactional scoring model, we would set the score of each report to 4% or .04. If the transaction appeared on each of the 25 reports, it would receive a total score of 100% (25 reports * .04 score). Over time, the score could be customized as the scores need not be equally rationed but rather customized by report to the location’s data reviewed. For example, in some locations reviewed, Report 2 may carry twice as much weight (or set at .08) as Report 5 (as set equally at .04), as an example.
Please note in our example for this post, we will be using the Invoice Header table where each physical invoice represents one invoice record in the file. This is normally designated by a unique transaction or voucher number assigned to that invoice. Assuming the invoice table is at the invoice transaction level, we will assign a unique record number to each record as done below:
DELETE FIELD RECORD_NUMBER OK DEFINE FIELD RECORD_NUMBER COMPUTED LAST(“000000000000?+ALLTRIM(STRING(RECNO() 12)) 12)
This unique record now becomes the key field for joining the table and the table is no longer the supplier table as in Part 1 of this post but now is the invoice header table. In this way, you are able to take each invoice transaction, assign a unique number to it, and then join the report results for the various invoice transaction scoring reports on the unique number created above. This can be joined back to the original invoice table so each of the 25 invoice report scores would each be a new score field joined to the original invoice table. In the Part 1 post, we completed the join on supplier number and now we are doing so on the unique record number. This would need to be adjusted in the provided ACL project script Scoring_Script.
Assuming the invoice table has now been updated with all of the scores for each of the 25 reports, a total score (assumed to add to 100% or 1) would be calculated for each invoice transaction. In Part 1 of this post, we had a score by vendor and now it is by invoice. This is where it gets interesting. Now that you have the score at the transactional level, you can summarize it on supplier number and when doing so, total not only the score but also the number of transactions. This will allow you to divide the two in order to obtain a score ratio. For example, a vendor summary on the transactional score and their total transactions may lead to:
Vendor A Summary – 491.12 in transactional score out of a total of 1,000 transactions or a 49.11% score ratio Vendor B Summary – 491.12 in transactional score out of a total of 2,000 transactions or a 24.56% score ratio
Beyond the supplier number summary, the score ratio can be applied to all of the Who, What, When, Where perspectives individually so as to identify the ratio of error / fraud to total transactions in each perspective. You may identify a pattern between a departmental summary of the score ratio or on the month/year combination. The various perspectives in invoice data usually translates into the following:
As Part 1 focused on developing a vendor score, an improved approach is to focus on the transactional invoice score and further, the score ratio for each of the Who, What, When, and Where perspectives. These additional viewpoint analysis may spot an area that is prone to error and fraud which may not be as visible at the vendor level.
(Source: ACL Blog) Original text Contribute a better translation
