· N Khanna The same invoice, paid twice
A supplier bill can reach you by email, through a portal and again on a past-due reminder, with its number written a little differently each time. On an illustrative company paying 4,000 supplier bills a year, a 24-month export turned up 8 bills paid twice, $15,200, and $6,100 of it was still sitting as credit on suppliers' books. The test takes 1 export and 2 formulas.
The first column I'd read in an accounts payable export is the bill number. I'm not looking at amounts yet, only at how the numbers were typed.
For a supplier whose bills all come in the same way, the numbers look alike. INV-01038, INV-01039, INV-01040.
For a supplier whose bills come in several ways, they don’t. INV-01042 on 1 row, 1042 on the next, INV1042 further down.
That mix is worth 20 minutes, because it means more than 1 person is typing that supplier’s bills from more than 1 copy.
1 bill, 3 ways in
At an illustrative company, 1 invoice can arrive 3 times.
- The supplier’s billing system emails a PDF to the accounts payable inbox. Someone saves it and enters the bill exactly as printed: INV-01042.
- The same supplier also posts its invoices to a customer portal and sends a notice when 1 is ready. Whoever handles the portal downloads it and types the number from the portal’s list, which leaves off the prefix: 1042.
- 30 days later a past-due notice arrives with the invoice attached again. The first payment hasn’t been matched on the supplier’s side yet, so it looks unpaid, and someone enters it to be safe: INV1042.
A bill-capture tool that reads the PDF can add its own variation, such as a dropped dash or a dropped leading 0.
What the warning looks at
QuickBooks Online, for example, has an option in its Advanced settings that warns you when a bill number has already been used for that vendor. Other accounting packages have something similar. If yours is switched off, switch it on.
It is still a check on 1 number for 1 vendor. Whether your software treats INV-01042 and 1042 as the same number is a 2-minute test. Set up a test vendor, enter both, and see whether it warns you.
2 other things get past a per-vendor check whatever the setting says:
- The same supplier set up as 2 vendor records, which I wrote about in the rebate post. Each record has its own list of bill numbers.
- A bill entered with no number, or with your own purchase order number typed in the bill number field.
The arithmetic
The illustrative company pays 4,000 supplier bills a year at an average of $1,800. That’s $7,200,000 a year through accounts payable.
A 24-month export holds about 8,000 bills. The 2 formulas below flagged 14 pairs.
- 6 pairs were genuinely separate bills: a monthly service billed at the same amount every month, 2 installments on 1 contract, and a rebill after a credit note.
- 8 pairs were the same bill paid twice. $15,200 in total, so about $1,900 each.
8 in 8,000 is 1 in 1,000. At about $1,900 a bill, that’s $7,600 a year.
Then the supplier statements, which tell you where each overpayment went.
| Where the overpayment went | Bills | Amount |
|---|---|---|
| Applied by the supplier against a later bill | 5 | $9,100 |
| Still unapplied credit on the supplier’s account | 3 | $6,100 |
| Total paid twice | 8 | $15,200 |
$9,100 plus $6,100 is $15,200.
The $9,100 came back on its own when each supplier applied it to the next bill. In effect the company lent it to them for a month or 2.
The 3 unapplied credits are still on the suppliers’ books. 1 of them, $2,400, sits with a supplier the company stopped buying from in March. With no later bill to apply it to, nobody on either side has a reason to look at it.
So the identified figure here is $6,100. It becomes money when each supplier sends a refund, which takes an email to each of the 3 and a copy of their statement.
Run the test this week
You can do this without us. Google Sheets has the formula built in, so it’s the quickest place to start.
- Export 24 months of bills with vendor, bill number, bill date, amount and paid date.
- In a new column, keep only the digits of the bill number and drop the leading zeros:
=VALUE(REGEXREPLACE(B2,"[^0-9]","")). Bills with no digits will show an error. Look at those by hand. - In the next column, join vendor and digits into 1 key:
=A2&"|"&C2. Then mark every key that appears more than once:=COUNTIF(D:D,D2)>1. - For a second pass, sort by vendor, then amount, then bill date. Read down for 2 rows with the same vendor and the same amount within about 2 weeks of each other.
- For every pair either pass finds, open both PDFs. Same date, same lines, same total means it was paid twice.
- For each supplier with a real pair, ask for a statement of account and read the credit lines.
Most matches aren’t duplicates
The formulas find candidates. A person decides which ones are real, and these are the usual false alarms.
- A retainer or a subscription bills the same total on a regular date. The dates give it away.
- 1042-A and 1042-B both become 1042, and some suppliers bill split shipments that way. Read the letter before you count the pair.
- A corrected invoice can reuse the original number on purpose, so a credit note and its rebill look like a duplicate.
On the illustrative company, 6 of the 14 flags were 1 of these, which is why step 5 is opening both PDFs.
Fewer ways in
Most of this is 1 setting and a few habits.
- Turn on the duplicate warning, and run the 2-minute test above so you know what it compares.
- Pick 1 way in per supplier. If a supplier uses a portal, ask them to stop emailing the PDF, or stop downloading from the portal. Either works. Taking both is how the second copy gets in.
- Type the bill number exactly as it’s printed on the invoice, never from a portal list or a reminder email.
- Before each payment run, sort the run by vendor and amount and read it. On a run of 80 bills that’s a couple of minutes.
- If your bills go through a capture tool, find out whether it has its own duplicate check and which fields it compares.
Closing
In an audit, I’d run this in the first week. It’s 1 export, 2 formulas and a handful of supplier statements, and it rarely needs anyone’s time beyond the person who already runs accounts payable.
It won’t be the biggest number in most businesses. It is 1 of the easiest to prove, because both copies of the bill are usually in the same folder.
If your bill number column comes back perfectly tidy, you either have 1 way in per supplier or 1 very careful person doing the typing (and that person goes on vacation eventually).