Most procure-to-pay analytics test one table at a time: duplicate invoices in the invoice file, vendors without tax identifiers in the vendor master, payments above a threshold in the payment run. Those tests catch errors and the clumsiest fraud. They do not catch collusion, because a buyer and a vendor working together produce records that are individually clean: a purchase order that exists, a receipt that was posted, an invoice that matches, a payment that was approved. The evidence of collusion is not in any one record; it is in the relationships between records across the stages of the process, and it only appears when the tables are joined.
This catalog is that second layer: twenty cross-stage tests that join requisitions, purchase orders, receipts, invoices, payments, the vendor master, bid records, and the employee master, each stated with its logic, what a hit suggests, the false positives to expect, and the follow-up procedure. It covers the patterns that single-table tests miss, split purchases under approval limits, purchase orders raised after the invoice, receipt bypass, price creep on renewals, the bid loser who reappears as a subcontractor, and the employee whose details collide with a vendor’s, and it sets out the data preparation, the scoring that turns hundreds of hits into a short list, and the investigation protocol that keeps the auditor on the right side of the line between analysis and accusation. The accounts payable analytics guide is the single-table layer this catalog assumes is already running; the procurement audit guide covers the controls the tests are probing.
In this guide
- Why single-table tests miss collusion
- The data model: stages, tables, and join keys
- The catalog: twenty cross-stage tests
- Data preparation, normalization, and the employee join
- Scoring hits and building the investigation list
- The investigation protocol: from analytics to interview
- Worked example: eighteen months of Brightwater Foods’ P2P data
- What the catalog cannot prove
- Common mistakes
Why single-table tests miss collusion
Procure-to-pay controls are built as a chain: a requisition is approved, a purchase order is issued, goods are received, an invoice is matched to both, and a payment is released. Each link has a control and each control leaves a record, and a fraud that involves one person defeats one link, which is why single-table tests find it: the invoice with no purchase order, the payment to a vendor with no tax identifier. A fraud that involves two people, a buyer and a vendor, or a receiver and a supplier, defeats the links at both ends at once. The purchase order is real because the buyer raised it; the receipt is real because the receiver posted it; the invoice matches because the vendor priced it to. Every record passes its own test. What the collusion cannot hide is the shape of the records across time and across tables: the order that was raised the day after the invoice arrived, the four orders that sum to just under a limit, the price that rose 6 percent at every renewal, the vendor whose bank account matches an employee’s.
| Single-table test | What it catches | What it misses | Cross-stage test that catches it |
|---|---|---|---|
| Invoices without a purchase order | Unauthorized purchases | The purchase order raised after the invoice to make it match | Test 5: PO date after invoice date |
| Purchase orders above approval limits | Nothing; they are approved | Four orders of $9,800 to the same vendor in a week against a $10,000 limit | Test 1: split purchases |
| Duplicate invoices | Double payment | The invoice paid with no receipt ever posted | Test 7: receipt bypass |
| Vendors without a tax identifier | Incomplete master data | The vendor whose address is an employee’s home | Test 15: employee-vendor collisions |
| Price above contract | Overbilling on contracted items | The renewal that rose 6 percent a year for four years without a re-bid | Test 10: price creep on renewals |
| Bid file completeness | Missing bids | The losing bidder who is paid as a subcontractor of the winner | Test 14: bid loser becomes subcontractor |
The data model: stages, tables, and join keys
The catalog needs eight tables, and the work of the engagement is mostly in obtaining them complete and joining them correctly. The table lists each with the fields the tests use and the key that joins it to its neighbors; the population completeness check for each is the same as for any analytic, a reconciliation to a control total, and the IPE guide covers it.
| Stage | Table | Fields the tests use | Joins to | Completeness check |
|---|---|---|---|---|
| Request | Requisitions | Requisition number, requester, approver, date, amount, vendor requested, cost center | PO by requisition number | Count and value to the requisition system’s period totals |
| Order | Purchase orders and change orders | PO number, buyer, approver, dates, vendor, line items with unit price and quantity, original and revised amounts | Requisition; receipts and invoices by PO number | Count and value to the PO register |
| Receive | Goods receipts | Receipt number, PO number, receiver, date, quantity | PO; invoice via PO line | Count to the receiving log; quantity to inventory movements |
| Invoice | AP invoices | Invoice number, vendor, invoice date, entry date, entered by, PO number, amount, match status, override flag and user | PO; payment by invoice number; vendor master by vendor number | Value to the AP subledger and GL |
| Pay | Payments | Payment number, invoice numbers, date, amount, bank account paid, released by | Invoice; vendor master | Value to the bank statement |
| Master data | Vendor master with change log | Vendor number, name, address, phone, email, tax identifier, bank account, created by and date, changes with user and date, status | Everything, by vendor number | Record count to the system; change log to the audit trail |
| Sourcing | Bid and tender records | Tender number, bidders, bid amounts, winner, evaluators, dates | PO by tender number; vendor master by bidder name | To the procurement team’s tender log |
| People | Employee master | Employee number, name, home address, phone, personal email, bank account for payroll, hire and termination dates, department | Vendor master by normalized address, phone, email, bank; PO and invoice by user | Headcount to HR; handled under the privacy rules below |
The employee master is the table that turns a control analytic into a fraud analytic, and it is also the table that raises privacy questions. Home addresses, personal phone numbers, and bank accounts are personal data; the function needs a documented lawful basis, usually the organization’s fraud prevention interest, minimization (only the matching fields, hashed where the tooling allows), restricted access, and a retention rule. The fraud risk management guide covers the governance; the point here is that the join is legitimate and must be done carefully.
The catalog: twenty cross-stage tests
The tests are grouped by the behavior they detect. Thresholds are written as brackets because every organization sets its own, and the false-positive column is as important as the logic: a test whose hits are mostly innocent is still useful, provided the auditor knows which innocent explanations to eliminate first.
| # | Test | Tables joined | Logic | What a hit suggests | Common innocent explanations |
|---|---|---|---|---|---|
| A. Approval limit evasion | |||||
| 1 | Split purchases | PO, requisition | Two or more POs to the same vendor from the same requester within [7] days whose sum exceeds an approval threshold while each is below it | Deliberate splitting to stay under a limit the requester or buyer cannot approve | Legitimate call-off orders against a contract; separate deliveries; different cost centers with a shared vendor |
| 2 | Just-below-limit clustering | PO | Distribution of PO amounts in the band from [95] to [99.9] percent of each approval threshold, by buyer, versus the buyer’s overall distribution | A buyer who prices orders to a limit | Standard-priced items that happen to sit below a limit; budget-driven order sizing |
| 3 | Post-approval increases | PO, change orders | POs increased after approval by more than [10] percent or past a threshold, where the increase was approved at the original level | Approval obtained at a low amount and inflated afterward | Legitimate scope changes with re-approval; price adjustments per contract |
| 4 | Requester equals approver, directly or by proxy | Requisition, PO, employee master | Requester and approver are the same person, or the approver is a delegate set by the requester, or the approver reports to the requester | Circumvention of the approval control | Small functions with genuine single-approver setups documented as exceptions |
| B. Sequence anomalies | |||||
| 5 | PO after invoice | PO, invoice | PO creation date later than the invoice date, or than the invoice entry date, on matched invoices | The order was raised to legitimize an invoice already in hand; the buyer and vendor agreed the purchase outside the process | Emergency purchases with a documented after-the-fact PO policy; date-entry conventions |
| 6 | Receipt after payment | Receipt, payment | Goods receipt posted after the payment date for the invoice that referenced it | Payment released without goods; receipt back-posted | Services with milestone billing; receipt posting delays at period end |
| 7 | Receipt bypass | Invoice, receipt, PO | Invoices paid against POs with no receipt, or with a match override flag, by vendor and by overriding user | Payment for goods never received, with an insider clearing the match | Service POs not requiring receipt; blanket orders with periodic receipt; override for tolerance differences |
| 8 | Receipt without inventory movement | Receipt, inventory | Goods receipts for stock items with no corresponding inventory increase within [2] days | Phantom receipts | Direct-to-consumption items; timing lags |
| 9 | Invoice velocity | Invoice, vendor master | First invoice from a new vendor within [7] days of vendor creation, especially where the same user created the vendor and entered the invoice | A vendor set up to be paid immediately, often by one person | Urgent new-supplier onboarding with documented approval |
| C. Pricing patterns | |||||
| 10 | Price creep on renewals | PO line items, contracts | Unit price for the same item or service from the same vendor rising by more than [3] percent per period for [3] or more consecutive periods without a re-bid or contract amendment | A vendor raising prices with a buyer who does not push back, or shares the margin | Indexed contracts; market price increases documented in the file |
| 11 | Unit price above contract | PO line items, contract price list | Line unit price exceeds the contracted price for the item by more than tolerance | Overbilling accepted by the buyer | Contract price list out of date; unit-of-measure differences |
| 12 | Same item, different vendors, different prices | PO line items, item master | The same item number bought from several vendors with a price spread above [15] percent, by buyer | A buyer steering volume to an expensive vendor | Quality tiers; delivery terms; genuine sole-source situations |
| 13 | Round-amount and just-under-threshold invoices | Invoice | Invoices in round thousands, or within [2] percent below a review threshold, by vendor, at rates above the population’s | Invoices written to a number rather than to work performed | Fixed-fee services; retainers |
| D. Vendor relationships | |||||
| 14 | Bid loser becomes subcontractor | Bid records, PO, invoice line descriptions, vendor master | A losing bidder on a tender appears as a payee of the winner (from invoice line descriptions or subcontractor disclosures), or receives POs from the organization for the same scope within [12] months | Bid rotation or a pre-arranged split of the work | Legitimate subcontracting disclosed in the bid |
| 15 | Employee-vendor collisions | Vendor master, employee master | Vendor address, phone, email domain, or bank account matches an employee’s, after normalization; vendor created by an employee whose details match | An employee-owned vendor, undisclosed | Employee expense reimbursement vendors; family businesses disclosed under the conflicts policy; shared buildings |
| 16 | Create-and-pay by one user | Vendor master, invoice, payment | The same user created or changed the vendor, entered the invoice, and released or approved the payment | End-to-end control by one person | Small AP teams with documented compensating review |
| 17 | Bank change followed by payment | Vendor master change log, payment | Payment to a vendor within [30] days of a bank account change, especially a change made outside the vendor portal or by an unusual user | Payment diversion, whether by an insider or an impersonated vendor | Genuine bank changes verified by callback and documented |
| 18 | Dormant vendor reactivated | Vendor master, invoice | A vendor with no activity for [18] months receives a payment, particularly after a master data change | An old vendor record hijacked for a new purpose | Seasonal or project suppliers |
| E. Timing and behavior | |||||
| 19 | Approvals in seconds | PO or invoice approval log | Approval timestamp within [60] seconds of routing, by approver, at rates suggesting no review | Rubber-stamp approval; a control operating in form only | Low-value items with a documented light-touch policy |
| 20 | Off-hours and period-end activity | PO, invoice, payment logs | Vendor creation, invoice entry, or payment release on weekends, holidays, or in the last two days of a period, by user, above the user’s normal pattern | Activity timed to avoid review | Documented period-end workload; time-zone effects in global teams |
The catalog is deliberately longer than any one engagement needs. A first run uses tests 1, 5, 7, 10, 15, and 17, which between them cover approval evasion, sequence, pricing, and relationships with the fewest tables, and adds the rest as the data model matures. The vendor master audit guide covers tests 15 to 18 in the context of a master data engagement, and the purchasing card guide covers the parallel catalog for card spend, where split purchases are the dominant pattern.
Data preparation, normalization, and the employee join
Cross-stage tests fail on join quality before they fail on logic. A vendor address stored as “123 N. Main St., Ste 4” in one system and “123 North Main Street Suite 4” in another will never match, and a test that relies on the match will report a clean result over a real collision. Normalization is therefore the first step and the one worth the most time: addresses parsed and standardized, phone numbers reduced to digits, email addresses lower-cased with the domain split out, names stripped of punctuation and legal suffixes, bank accounts compared on the account number alone. The second step is the join key map, a one-page table of which field joins which tables, checked on a sample of known-linked records before any test runs. The third is the population reconciliation for every table, documented in the sampling memo format even though the tests are full-population, because the completeness of each table is what the conclusions rest on.
| Field | Normalization | Match rule | Note |
|---|---|---|---|
| Address | Parse to number, street, unit, city, postal code; standardize abbreviations; drop punctuation | Exact on number plus postal code; fuzzy on street | Post office boxes match on box and postal code; shared office buildings produce innocent hits |
| Phone | Digits only; drop country code where consistent | Exact on the last nine digits | Shared switchboards produce innocent hits |
| Lower case; split local part and domain | Exact on full address; flag domain matches for personal-domain vendors | A vendor using a personal email domain is itself an attribute | |
| Name | Upper case; remove punctuation and legal suffixes (LLC, Inc, Ltd); token sort | Fuzzy above [85] percent similarity | Family names in a small town produce innocent hits; confirm with a second attribute |
| Bank account | Account number only; routing separately | Exact | The strongest single attribute; a match is followed up regardless of other fields |
| Tax identifier | Digits only | Exact | A vendor sharing a tax identifier with an employee is a direct follow-up |
Scoring hits and building the investigation list
Twenty tests over two years of data produce hundreds of hits, most of them innocent, and the auditor who investigates them in the order the tests ran will run out of hours before reaching the ones that matter. Scoring solves this by counting hits per entity, vendor, buyer, and approver, weighting them by the strength of the test, and ranking. A vendor that hits test 15 (an employee’s bank account) needs no other hits to reach the top of the list; a vendor with one split-purchase hit needs several more. The table gives a weighting that has worked in practice; the function calibrates it after the first cycle by looking at which weighted scores corresponded to real problems.
| Weight | Tests | Reason | Action at this weight alone |
|---|---|---|---|
| 10 (direct) | 15 bank or tax identifier match; 17 bank change then payment outside the portal; 14 bid loser paid by winner | Each is hard to explain innocently and easy to confirm | Investigate every hit |
| 5 (strong) | 5 PO after invoice; 7 receipt bypass with override; 16 create-and-pay; 15 address or phone match | Strong indicators with common innocent explanations | Investigate where two or more hits coincide, or one hit with high value |
| 3 (pattern) | 1 split purchases; 3 post-approval increases; 10 price creep; 12 price spread; 18 dormant reactivated | Patterns that need context | Rank by value and frequency; investigate the top of the list |
| 1 (behavioral) | 2 clustering; 4 requester equals approver; 6, 8, 9, 11, 13, 19, 20 | Weak alone; meaningful in combination | Add to scores; investigate only in combination |
The investigation list that results is a targeted judgmental selection in the sense the judgmental sampling guide describes: the criteria are the scores, the coverage is stated, and the conclusion is about the entities examined and the risks the tests target, never an exception rate for procurement as a whole. Where a control conclusion on the process is also needed, it comes from a separate random sample, evaluated separately.
The investigation protocol: from analytics to interview
A hit is a question, not a finding, and the protocol for answering it protects both the organization and the people whose records are involved. The order is fixed. First, eliminate the innocent explanations from the catalog’s last column using records alone: the contract, the tender file, the conflict-of-interest disclosures, the vendor portal log. Second, for what remains, examine the documents behind the transactions: the invoice images, the receiving evidence, the approval trail, the communications on file. Third, and only when the records do not resolve it, interview the process participants, factually and without accusation, in the presence of a second auditor. At any point where the evidence suggests intentional misconduct rather than control failure, the auditor stops and hands off under the function’s fraud protocol, which brings in legal counsel, human resources, and where appropriate the investigations team, because from that point the questions of evidence preservation, employee rights, and potential legal action are not the auditor’s to manage alone. The fraud red flags guide sets out the hand-off criteria and the fraud risk guide the governance around it.
| Stage | Question | Evidence used | Who is involved | Stop rule |
|---|---|---|---|---|
| 1. Record elimination | Does a document explain the hit? | Contracts, tender files, disclosures, policy exceptions, portal logs | Analytics auditor | If explained and documented, close with the reference |
| 2. Transaction examination | Do the underlying documents support the transaction as recorded? | Invoice images, receiving records, approval trail, correspondence on file | Engagement lead | If supported, close; if not, escalate to stage 3 or hand off |
| 3. Interview | What is the process participant’s account? | Structured factual questions; no accusation; notes taken by a second auditor | Lead and a second auditor; the audit manager informed before | Any indication of intent: stop and hand off |
| 4. Hand-off | Is this a control failure or potential misconduct? | The file as assembled, preserved unaltered | CAE, legal counsel, HR, investigations per the fraud protocol | The auditor’s role becomes support to the investigation, not its owner |
Worked example: eighteen months of Brightwater Foods’ P2P data
Brightwater Foods, the $180 million manufacturer with three plants and a co-sourced internal audit function that appears in this site’s cost and co-sourcing guides, ran the catalog for the first time over eighteen months of data: 22,400 invoices, 1,860 active vendors, 9,100 purchase orders, 41 tenders, and 600 employees. Data preparation took the co-source analytics specialist eight days, most of it on address normalization and on reconciling the receiving log, which lived in a warehouse system outside the ERP. The tests ran in a day. The table shows what they produced and what happened to it.
| Test | Raw hits | After record elimination | After transaction examination | Outcome |
|---|---|---|---|---|
| 1 Split purchases | 41 clusters, 9 buyers | 12 clusters, 3 buyers (the rest were call-offs against contracts) | 9 clusters at one plant, one buyer, one packaging vendor, all in the band under the $10,000 plant approval limit | Control finding: the buyer split orders to avoid the plant manager’s approval; no vendor relationship found; the approval threshold logic and the buyer’s authority were changed |
| 5 PO after invoice | 214 | 38 (the rest were emergency maintenance purchases under a documented policy) | 38 concentrated in two cost centers with no emergency justification | Control finding on after-the-fact ordering; the policy was tightened and the cost centers’ approvers changed |
| 7 Receipt bypass | 167 invoices with match overrides | 151 within tolerance or on service POs | 16 overridden by one AP clerk for one vendor, all with receipts posted days later | Investigated at stage 3; the clerk was clearing a backlog under pressure from the plant; the vendor was genuine; a control finding on override authority and monitoring |
| 10 Price creep | 23 item and vendor pairs | 15 (8 had indexed contracts) | 15 pairs with one ingredient supplier rising 5 to 7 percent at each of four renewals without a re-bid, managed by one buyer | Re-bid ordered; the incumbent’s renewal price fell 11 percent; no relationship found between the buyer and the vendor, but the finding on sole-source renewals stood |
| 14 Bid loser as subcontractor | 3 | 2 (one had disclosed subcontracting in its bid) | 2 tenders where a losing bidder was paid by the winner, from invoice line descriptions, for the same scope | Handed off under the fraud protocol; the investigation found an undisclosed arrangement between the two vendors and no employee involvement; both were removed from the approved list |
| 15 Employee-vendor collisions | 7 (2 bank, 5 address) | 4 (3 addresses were employee expense vendors) | 2 bank matches: one a disclosed family business under the conflicts policy; one an undisclosed cleaning contractor owned by a plant supervisor’s spouse, with $61,200 paid over the period | Handed off; the supervisor had approved the invoices; the matter went to HR and legal; a control finding on conflict-of-interest attestation and vendor-master screening at creation |
| 17 Bank change then payment | 29 | 27 verified by callback in the portal log | 2 changes made by an AP user outside the portal with no callback, followed by payments | Both were genuine on investigation; a control finding on the ability to change bank details outside the portal, which was removed |
Two things about the outcome are typical of a first run. Most of the value was in control findings, not fraud: the split-purchase buyer, the after-the-fact orders, the sole-source renewals, and the out-of-portal bank change were process weaknesses that the single-table analytics had never surfaced because each record was individually in order. And the two matters that went to investigation were found by the two direct-weight tests, the bid-loser join and the bank account match, which is why those tests are run every time and every hit is examined. The engagement’s cost, about 220 hours including the co-source specialist, was recovered several times over by the renewal re-bid alone, which is the argument the audit cost guide makes about analytics generally.
What the catalog cannot prove
The catalog finds shapes in records, and there are frauds that leave none. A vendor who overbills for services that were rendered, with a buyer who approves without a kickback, produces a clean pattern; so does a kickback paid in cash, outside every table the organization holds. The catalog cannot see relationships that exist only outside the data, cannot distinguish collusion from a coincidence of records without investigation, and cannot prove intent, which is a matter for an investigation conducted under legal supervision rather than for an auditor reading joins. It also depends entirely on the completeness of the tables; a receiving log that lives in a spreadsheet at one plant is a blind spot until it is reconciled and loaded. The honest scope statement for a catalog engagement is that it tests the organization’s records for the patterns that collusive procurement fraud typically leaves, over the period and populations stated, and that a clean run reduces the likelihood of those patterns without excluding fraud of other kinds. The segregation of duties guide and the ERP segregation analysis guide cover the preventive layer that makes the patterns harder to produce in the first place.
Common mistakes
| Mistake | What it looks like | Fix |
|---|---|---|
| Running tests before normalizing | Zero employee-vendor matches reported with confidence | Normalize first; test the join on known-linked records |
| Treating hits as findings | A split-purchase list presented to management as fraud | The protocol: eliminate, examine, interview, hand off |
| Investigating in test order | Hours spent on weak behavioral hits while a bank match waits | Score and rank; direct-weight tests first |
| Skipping the employee join for privacy reasons | The catalog run without test 15 | Do the join under a documented lawful basis with minimization and restricted access; it is the test that finds insiders |
| Incomplete tables | The receiving log from one plant missing; receipt tests reported clean | Reconcile every table to a control total; disclose gaps in the scope statement |
| Thresholds copied from a template | A $10,000 split threshold at a company whose limit is $25,000 | Thresholds from the organization’s own delegation of authority |
| Accusatory interviews | “Why did you split these orders?” | Factual questions, two auditors, no conclusions in the room; hand off at any sign of intent |
| Concluding on the process from the hit list | “Procurement controls are ineffective” from targeted hits | Targeted conclusions from the catalog; process conclusions from a separate sample |
| One run, then never again | The catalog as a project rather than a routine | Quarterly runs of the six core tests; annual full catalog; the data model maintained |
| Findings without the control | The fraud reported; the control gap that allowed it not | Every investigated hit produces a control finding whether or not fraud is confirmed |
Collusion is designed to pass the controls, and it does; what it cannot do is make the records of a process tell a consistent story across every stage and every table at once. The catalog reads the story. Run it on complete, normalized data, score the hits, follow the protocol, and hand off when the evidence stops being about controls, and the function will find the patterns that single-table analytics were never built to see, and, in most years, will find that the biggest results are the process weaknesses those patterns reveal.
Related guides
- Accounts payable analytics
- How to audit procurement
- How to audit accounts payable
- Vendor master audit
- How to audit purchasing cards
- Fraud red flags
- Fraud risk management and internal audit
- Fraud risk: a comprehensive guide
- Journal entry analytics
- Segregation of duties
- ERP segregation of duties analysis
- Judgmental sampling that survives scrutiny
- Testing information produced by the entity
- Start here
Leave a Reply