IDEA Tech Tip: Using Benford’s Law
Benford’s Law is always a popular topic in the audit and forensic accounting world. This rule, which predicts how often you should expect to see numbers 1 through 9 as the leading digits in accounts payable, deposits, disbursements and other select large data sets, can be an invaluable tool to detect potential fraud.
CaseWare IDEA offers several Benford analysis tests including first digit, first two digits, first three digits, second digit and last two digits. To access these tests, look under the Analysis tab.
More Benford tests are available through the IS Fraud Tool Kit in the IDEA Marketplace, including third digit test and fourth digit test. If you do have the IS Fraud Tool Kit, here are step-by-step instructions from Brian Element, CPA, CISA, CIDA, CISE, CFE – IDEA Certified Instructor and Financial Advisor at Public Works and Government Services Canada on how to access and use these Benford tests.
Go to the SmartAnalyzer ribbon and press Select & Run.
From the Audit Test Navigator, select Benford Analysis under IS Fraud Tool Kit.
This will give you the list of the eight Benford tests available. Select the tests you’re interested in. For this example, I will use the Benford 1st Digit Test.
The first step is to tag the fields that will be used in the test. Each test will use certain fields for the analysis; in the case of the Benford tests, you will need an amount and a client (vendor) field.
Click on No to select your fields. You will now have the option to define your tags.
In the column called Field to Assign you can select the fields that correspond to the Tag Name, in this case Amount and Client Field.
Once you’ve selected the fields, click on the green check mark to exit the dialog. The tag will now change to Yes. If the test needs additional input it will be indicated with Required. Select the Required link and you will get the following dialog:
These dialogs will be different based on the test. For the Benford tests you have several options that must be selected. The first is the minimum number of transactions per vendor; the default is per the recommendations found in the IDEA documentation, but can be changed if needed. The tests can only be performed on Positive or Negative numbers, not both at the same time, so you must select one or the other. Finally, there is an option to create an Excel spreadsheet. This will contain the summary data and graph for each client that meets the minimum number of transactions. Once you have finished making changes, click OK to exit the dialog.
The status will change to a start button and the assigned database will be shown. Once you have the start button, you can run the test.
The test will show its progress:
Once the test is complete, you can view the results.
The file starting with the @ symbol is the SmartAnalyzer result file. In this case there are 324 records in the result. By clicking on 324, you can bring up the transactions. The file is also saved as an IDEA file automatically.
We can see the first two vendors in this example. The following fields will be available:
- VENDORNUM – This is the file you selected for the client.
- FIRSTDIGIT – Shows which digit the results are for.
- ACTUAL – The actual number of times the first digit shows for this client.
- TOTAL_RECORDS – The total number of records for this client.
- EXPECTED – The expected number of times the first digit should appear.
- LOWERBOUND – This is the lower boundary of the range in which the predicted result should fall.
- UPPERBOUND – This is the upper boundary of the range in which the predicted result should fall.
- DIFFERENCE – This is the difference between the actual and expected.
- PERC_DIFFERENCE – This is the percent difference between the actual and expected.
- MAD_RESULT – This is the Mean Absolute Deviation that indicates whether the data conforms to the Benford's test.
- MAD_CONCLUSION – This is the MAD conclusion. There are three results: Nonconformity, Close conformity and Acceptable conformity.
If you select Excel, a spreadsheet will be created (assuming you have Excel installed) that will give a table for each client along with a graph.
You are then able to review the spreadsheet and show graphically which clients do not conform and should be looked at closer.
Have questions about Benford’s Law and IDEA? The ideascripting.com website is an excellent resource. You can also review our presentation on Benford’s Law below:
About Alain Soublière:
Alain Soublière has many years of experience working with computer audit software. He worked in a senior management role as the IDEA Product Manager for many years before becoming Director of Product strategy for CaseWare Analytics and more recently the Chief Product Strategist.
Connect: Alain Soublière