When the Sarbanes Oxley Act was passed in 2002, many companies had to deeply study the control of internal unpaid funds. It takes time to implement internal control, but if fraud or leakage is found, it can prove to be a very cost-effective measure. The following are methods to strengthen the audit of unpaid funds. They need certain data mining and programming capabilities, but it is quite simple to implement.
- Repeated payment
In most cases, duplicate payment may not be related to fraud, but it is still a major payment leakage that can be prevented and recovered. Mark Van Holsbeck, head of corporate cyber security at Avery Dennison, estimates that the company’s recurring payment rate is 2%. Although 2% doesn’t sound like a lot, if the company’s total invoice is $75 million, repeated payments may account for $1.5 million. View statistics.
Medical insurance – Ministry of Health; the director of the public service audit estimated that the medical insurance paid 89 million dollars repeatedly in 1998.
Cingular – We found again that due to the electronic fund payment in the TDMA account, the amount of online payment was deducted twice from the customer’s checking account.
Medical support – we found at least $9.7 million in duplicate payments during the two-year audit period, and it is estimated that up to $31.1 million in duplicate payments can be added.
In the process of eager to find out the overpayment, many companies have emerged: unpaid money reassessment, automatic audit, unpaid money recovery, ACL, cost recovery solutions, etc. The prosperity of these companies proves that repeated payments are still taking place at an alarming rate.
Many software packages have control over duplicate invoices, but it usually requires some in-depth queries to find all invoices. For example, many accounting software packages perform a duplicate invoice check to prevent duplicate invoice numbers from being entered for the same vendor. Just add one. A ″; repeated payment will be applied if the invoice number is changed or 1p is changed. Another common error is found in the supplier’s documentation. Duplicate supplier numbers of the same supplier are the most important reason for duplicate payments.
IT programmers can help you write SQL code for these connections. The SQL code will be displayed as: EEE “:
Table DUPES_eee is created as
A * Selection
Invoice A, Invoice B
Where A. VENDORID=B. Suppliers and
A. INVOICENUM=B. INVOICENUM and
A. Invoice Date=B. Invoice Date and
A. INVOICEAMT=B. Invoicing and
A. ID B.ID
The ID field must be a unique record identifier to distinguish one record from another. In Microsoft Access, these fields are typically created using the data type ‘0’. Automatic numbering;. This field can be easily created in open code using counters. For each record, this field will be incremented by 1(counter=counter+1).
- Realize some fuzzy matching
Implement “; similar”; it is not exact matching, but fuzzy matching, which is precisely why this method is more accurate and powerful than many methods. We are “; similar”; its meaning includes:
After deleting the invoice number, if the invoice number is correct, it will be regarded as similar.
Letters and punctuation marks.
If the difference between the invoice dates is less than the specified amount(for example, 7 days), the invoice dates are considered similar. For example, if you enter a date tolerance of 7 inches, all invoices up to 7 days will be considered similar. We usually set the allowable date limit to 21 days to capture repeated payments every 3 weeks. This generally cancels legal rent payments.
If the amount meets one of the following three criteria, it will be considered as a similar amount:
-
Amount 5%+/- other amount
-
One amount is twice the other: $220.15 and $440.30
-
The amount starts with $123.45 and $1234.55, starting with the same first four figures.
The next time you perform a duplicate payment review, try to make a similar match in the invoice number, date, and amount fields. Shorter and more accurate report!
- Benford’s law
What’s going on here?
Banford Law(first proposed by astronomer Simon Newcomb in 1881) points out that if we randomly select a number from the physical constants or statistical data tables, the probability of the first number will be “0”. If all the numbers in 1 inch are likely to be the same 0.1, it is about 0.301 more than we expected. Generally; law “; the probability of representing the first number is” 0 “. d ‘is
Here, ln refers to natural algebra(radix e). This numerical phenomenon is described by the title of Newcomb as “; description of the frequency of use of different natural numbers”; it appeared on pages 439-40 of the American Journal of Mathematics(1881). In 1938, Benford found it again and published a report entitled Mathematics. The law of indefinite constants “; in Proc. Amir. Phil. Soc78551-72. [1]
In fact, you can easily recreate this function in Excel. Enter 1, 2, 3 to 9 in the column to form 9 rows in A1 to A9 cells. In cell B1 in the second column, the function “”=ln(1+1/A1)/ln(10). This function is also copied from cell B2 to cell B9, and a probability is generated.
How is it used to identify fraud?
If we know the normal frequency of numbers, we can identify the frequency of numbers that violate normal behavior. For example, Benford concluded that the first number in a series would be “0”. 1 inch approximately 30% of the time. Similarly, if you use the same function, you can expect the first number to be “0”. Type 8 is about 5.1% of the time. The estimated frequency of the first figure of the invoice amount is shown in the figure below.
Review the unpaid invoice. If the first number on the invoice is “0” and the time is 50% of 8 inches, we may have a lot of legal payments starting with “”. 8 inches;; or we have a fictitious invoice amount. The fraudster usually creates an amount starting with a higher figure, such as 8 or 9, but does not know that the auditor currently has the ability to identify such abnormal payments.
- Rounded Amount Invoice
Fraudsters usually issue invoices with rounded amounts, that is, invoices without coins. ok You are the liar Ethan “; enough to do other things. A simple way to identify the rounded amount invoice is to use the MOD function of Excel. Suppose the invoice amount is 150.17 dollars. Then MOD(150.17,1) Divide 150.17 by 1. 17. Therefore, if you use the MOD function to divide the non penny amount by 1, the remaining values are 0. You will also attempt to sort suppliers by a high percentage of rounded invoices. To do this, you can calculate the number of invoices rounded up for each supplier and divide it by the total number of suppliers’ invoices to obtain the percentage. Then, in descending order, the most suspicious supplier is determined first.
- Invoice less than approved amount
People who cheat are not always “liars”; the “sharpest knife in the drawer” assumes that employees know other dollar thresholds approved by management. For example, the supervisor can only approve invoices less than 3000 dollars, and the administrator can only approve invoices less than 10000 dollars. What is the easiest way to get the most money? Create invoices below the administrator approval level. $9998 at an approval level of $10000. If the approval level is $3000, it is $2978.
To identify these potential illegitimate invoices, identify invoices that are less than 3%(or less) of the approved amount. For example, if the approved amount is $3000, an invoice between 2910 and $2999 will appear suspicious.
- Confirm theft search
Most accounts payable departments check accounts payable with monthly bank accounts to determine any differences between the two. This process also helps to identify cheque fraud. A simple way to detect potential check fraud is to identify missing check numbers or blank account numbers. This is usually shown as “*” on the bank invoice. Or “#” means the check number is discontinuous.
Another more advanced method is to use electronic reverse pure payment. Stolen cheques can be identified by consolidating cheque registers, accounts payable documents and bank statements. Better still, if the bank has OCR(Optical Character Recognition) function, it can identify the actual recipient on the check.
From a technical perspective, you have three different databases to describe an activity. Use three data sources to find one-time payment differences. If the inspection number is unique, merge all three data sources according to the inspection number and compare each of the following fields:
-Recipient
-Check amount
-Inspection date
Use SQL code or other programming language to identify all checks in the database, not other databases. It also identifies all checks in the three data sources, but the recipient’s name, amount, and date are different.
- Invoice quantity exception event
Monitoring the number of supplier invoices is a way to warn of abnormal behavior. The rapid increase in the number of bills may indicate the legitimate growth of business, but it may also indicate that fraudsters have more confidence in stealing money. In conclusion, this growth may require further investigation. Suppose the supplier has two invoices in one month and 70 invoices in the next month. Even if the reason is not deceptive, you may want to know why.
To calculate the invoice growth rate from one month to the next, find the difference in the invoice quantity and divide it by the quantity in the first month. In our example, from 2 invoices to 70, the difference(68) divided by the number of invoices in the first month(2) increases by 3400%. It is important to set the threshold percentage. We want to set the threshold percentage to 300% or higher when auditing. If you set the threshold to 300%, you will capture increments from 3 to 13, so it may not be interesting. Therefore, you may need to set the minimum number of records of interest, such as the number of invoices in the second month. If you set the threshold to 300%, you will find more interesting growth, such as 50-220.
- Suppliers whose cheques are cancelled or returned
Cancelled and returned cheques do occur during normal unpaid months. What is more unusual is that a supplier has many canceled checks or checks that are canceled regularly. Canceled checks are usually legal transactions. But canceled checks may be returned to the wrong person and rewritten by the fraudster. The following is a true story about how a check returned by an employee was made into a fraudulent check.
“An unpaid cheque was returned to the unpaid clerk because she was the culprit of the invoice entry.” The clerk left the check on his desk and forgot it for several months. While clearing the table, she found the returned check. When she checked the payment details, she realized that this was the repeated payment of the invoice, and the supplier returned the check. She also noticed that the recipient’s name was printed in the “slightly lower position. The recipient”; cheque. After some efforts, she succeeded in sorting the cheque and inserting her name above the original recipient. The font was similar to the original recipient, with “” or “” added ; her name after her name. The fraud was found by an unpaid money inspection institute, which searched for duplicate payments, and the supplier asked him to provide two copies of canceled checks to prove duplicate payments.
This algorithm is easy to implement. Calculate the number of checks cancelled or returned by each supplier and divide it by the total number of checks for that supplier. The list is then sorted in descending percentage order, with the most suspect vendor at the top of the report.
- Higher than the average payment of each supplier
This algorithm can identify invoices that are much higher than the average level of a particular supplier. Assume that the supplier usually has an invoice of $1000 to $3000. Suddenly a bill of $25000 appeared. You may want to investigate this exception, or you can use this warning mode to investigate.
You can also calculate the average and standard deviation of each supplier’s invoice amount for implementation; then calculate the z-score of each invoice amount.
Z score=(invoice amount – average amount)/standard deviation
Then, if all suppliers with a z-score higher than 2.5 are displayed, it means that the payment is higher than the standard deviation of the average 2.5. If the report is too large, raise the z-score threshold above 3.0.
Using this algorithm, you can catch employee fraud in a medium-sized medical manufacturer. The fraudulent employee receives paychecks of $500 to $1000 per week. Three invoices of 40000 US dollars suddenly appeared. Since 40000 US dollars was far higher than the average salary of the employee, the salary was marked as further research. The suspicious invoice occurred on the same day or almost the same day without invoice number. After the new controller was notified of the suspicious payment, The new controller realized that an employee had left the legal “bank”; before marriage “; i don’t know about the stolen $40000 check.
- Supplier/employee mutual inspection
“Trustworthy but verified”; most employees are trustworthy! But it is not bad to confirm whether data mining really exists. Here is a simple way to cross check the supplier and employee files to see whether there are employees who have installed fictitious suppliers.
Use the following variables to merge supplier files and employee files:
address
Tax ID No
Telephone number
Bank line number
If you have a good programmer, please try to make a fuzzy match in this field. For the address, please extract the zip code from JUST THENUMBERS at the distance, and then compare the numbers. This will eliminate the matching of noise characters. Drivers “;” and “; suite
You can also match tax ID numbers vaguely to avoid data entry errors. If you specify the same tax ID, even if there is only a 1-digit gap, you can hear the supplier/employee ring tone!
This algorithm makes it possible to detect the actual employee(“Kathy”). The SSN is the same as the EIN(tax ID number) of the company. The company name, “ABC Company”, happens to be the same last name as the employee(probably her spouse) at the same distance, in the same city, and in the same state. Without this model, employee fraud will not be detected.
- Supplier addressed by mail
This algorithm compares the vendor address with the email delivery address. Email, etc “;. In order to conceal the fraud, some fraudsters will send emails to addresses instead of mailboxes. Not all listed suppliers are fraudulent. Because suppliers may actually be next to mailboxes, etc. But this list is a unique way to review sellers who may appear in other warning lists.” .
For a copy of the mail delivery form, contact the author of this document. Or, if you have time, you can search the email on www.411.com, put the address in the database, and then match the address.
abstract
Professional fraud is a growing problem. In fact, the Association of Certified Fraud Examiners(ACFE) estimates that 5% of annual income will be lost due to professional fraud. Fraud is not 100% preventable, but you can take several steps to continuously prevent and detect fraud. At least once every 6 months, the repeated payment shall be scanned, and the annual cross check shall be performed between the supplier’s documents and the employee’s documents. These two steps can accurately identify leaks. Otherwise, it may be ignored.
Author Information
Christine L. Warner is the general manager of Automatic Auditor and LLC, with more than 20 years of experience in data mining, fraud detection, statistical analysis and complex custom programming. She has written several articles about using data mining to detect fraud. Death fraud: This kind of identity theft is vivid. , they wrote together with Cheryl Hyde, and won the most influential Glochbed Award in the Fraud Journal(ACFE) in 2011. Christine once served as the sub project director of the audit of all Medicaid Integrity subcontractors in the Northeast of the United States, and personally developed more than 50 medical fraud algorithms and payment fraud algorithms.
