Accounts Payable Fraud Analytics Toolkit: Duplicate Invoice, Split Purchase and Benford Tests in Excel
Full description
For financial controllers, internal auditors, chief accountants and general managers who need to know whether the payables ledger holds duplicate payments, purchases split to stay under approval limits or invoices from suppliers that do not look right. The workbook runs nine tests on every invoice in a visible engine tab, adds the weights of the tests that fire into a risk score, and ranks the highest scores with plain-language reasons. Benford first-digit and first-two-digit tests compare the amounts with the expected digit shares and report the mean absolute deviation, z-scores and a conformity class. A dashboard shows exceptions by test, and an investigation log tracks each case from open to recovered.
Save with a bundle: this workbook is also in the F&B Control & Audit Pro Bundle, 25 workbooks for $59 instead of $310. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.
What is inside
- AP_Fraud_Analytics_Toolkit.xlsx: 11 tabs, 38,079 formulas, 4 native charts, 13 drop-down and input rules
- Invoices: 2,000 sample invoices from 60 suppliers, plus 100 spare rows
- Tests: 16 columns per invoice covering exact repeat, similar invoice, split purchase, just under limit, weekend and holiday posting, round amount, sequential numbers, new supplier early spend, first digits and risk score
- Benford: first digit (9 digits) and first two digits (90 pairs) with expected against actual counts, z-scores, MAD and conformity
- Suppliers: 80 supplier slots with spend, early spend, flagged invoices and flag rate
- Exceptions: the top 30 invoices by risk score with reasons; Investigation_Log with 60 case rows (25 sample cases)
- Dashboard (one printed page), 15 integrity checks and a Method tab with 16 documented calculations
- README.txt and LICENSE-and-Terms.txt
How it works
- Set the thresholds, weights, approver limits and holidays on Assumptions.
- List suppliers with their set-up dates, then paste your invoice register into Invoices (up to 2,100 rows).
- Read the Dashboard, the Exceptions list and the Benford tables. Open Tests to see which step fired for any invoice.
- Log each case on Investigation_Log with owner, status, amount at risk and amount recovered.
- Confirm that the Checks tab shows ALL OK before you share the results.
Details
- Format: one .xlsx workbook, English
- Software: Excel 2016 or later, or LibreOffice Calc 7 or later
- No macros, no external links, no add-ins, no array formulas
- Instant digital download
Good to know
- Sample data is illustrative. Replace it with your own figures.
- A flag is a reason to review an invoice, not proof of fraud. The workbook is an analysis tool, not legal, audit or financial advice.
- Approval limits, holidays and Benford conformity cut-offs are editable inputs. Verify local requirements and agree the cut-offs with your auditor.
- Single-user license. Do not redistribute.
FAQ
How many invoices can it test?
The Invoices tab holds 2,100 rows. The sample uses 2,000; the rest are spare rows with formulas ready.
Does it find duplicates with a different invoice number?
Yes. The similar-invoice test looks for the same supplier, an amount inside your tolerance and invoice dates inside your window, and leaves out exact repeats so they are not counted twice.
What does the Benford test tell me?
It shows whether the leading digits of your amounts follow the expected pattern. A spike at a digit pair points to a group of invoices worth a closer look, such as amounts just under an approval limit.
Can I change the weights and thresholds?
Yes. Every threshold, weight and band sits on Assumptions. The Checks tab confirms they are inside valid ranges.
Does it need macros or add-ins?
No. Every result is a standard worksheet formula.
