Supplier Billing Reconciliation
Every Bill Line vs. Agreed Price, History & Receipt

01Executive Summary

The reconciliation verdict is unusually clean — with one live exception that validates the whole exercise. Of 7,517 item lines billed by 20 suppliers over 24 months, every line that flowed through a purchase order matched its agreed PO rate exactly (8,609 matched line-pairs, zero rate variances). Historical pricing is frozen: every vendor-item pair billed at precisely one rate for two years, and where multiple vendors supply the same item, they bill identical rates. No billed quantity anywhere exceeds goods received. Surcharge abuse is structurally impossible here: exactly one freight line exists in the entire AP ledger ($120, correctly coded), and no fuel or handling surcharges have ever been billed.

The exception: bill VB397 from Generation N — an apparel supplier — billed $2,995.00 for one unit of INV_2-Layer Copper, a PCB fabrication material this company buys from Core4Solutions at $10.48. The bill has no purchase order, no receipt, sits open and unpaid, and its memo field contains a 16-digit number formatted like a payment-card number. That is a $2,984.52 overcharge against reference price (285× the agreed rate) — and every attribute of the bill says it should not be paid without investigation.

The structural finding behind it: $375,533 of billing (16.4% of supplier spend) bypassed purchase orders entirely — eight manually keyed bills (VB01–VB08) plus a handful of one-offs, nearly all from Generation N. Line-by-line, the VB01–VB08 rates match the vendor's standard prices exactly, so no additional claim arises from them — but they were never protected by the three-way match, and VB397 is what that unprotected channel eventually produced. The off-PO door, not the pricing, is the risk.

02Reconciliation Coverage & Method

Universe: all VendBill item lines (mainline='F', taxline='F', quantity ≠ 0) — 7,517 lines, $2,292,913, 719 bills, 20 suppliers, Sep 2024–Aug 2026. Bill lines link to their POs via transactionline.createdfrom; item receipts link to the same POs the same way, giving a three-way match: PO (agreed) ↔ receipt (goods) ↔ bill (charged).

03Test 1: Billed Rate vs. Agreed (PO) Rate

Every bill line joined to its originating PO line on the same item; billed rate compared to PO rate at 4-decimal tolerance.

04Test 2: Billed Rate vs. Historical & Cross-Vendor Benchmarks

Two benchmarks per vendor-item pair: (a) the pair's own 24-month billing history (creep detection); (b) rates other vendors bill for the identical item (market check).

05Test 3: Freight, Fuel & Handling Surcharges

06Test 4: Billed Quantity vs. Goods Received

07Claims & Exposures by Supplier

Variances aggregated per supplier across all four tests. A claim is money to recover or withhold, evidenced by transaction ids. An exposure is a control or accrual gap with no current dollar claim.

08Recommendations

1. Hold and investigate VB397 today. $2,995 open bill, 285× reference price, off-category item, no PO, no receipt, card-like number in the memo. Do not pay pending purchasing confirmation. Recover/withhold $2,984.52 minimum; void in full if unordered. Redact the memo either way.
2. Close the off-PO billing channel. $375K (16.4% of spend) bypassed the three-way match, concentrated in Generation N's VB01–VB08. Those bills happened to be priced correctly — VB397 is what the same channel produced next. Require PO reference on vendor bills above a threshold (e.g., $1,000), enforced at entry.
3. Book the GRNI accrual. $32,988 of goods received but never billed across 208 PO-item combos, led by Lotion Co ($10.6K). Chase missing invoices, and verify the Johnson Supply double-receipt (40 in vs. 20 billed on two POs) is a real count, not a receiving error.
4. Adopt the four queries in §10 as standing monthly controls. Rate-vs-PO, price-drift, surcharge-appearance, and billed-vs-received are each one query. This month they return one hit between them; the month a supplier starts drifting, they will return it on day one.
5. Do not launch a broad supplier-recovery program. The evidence shows supplier billing conduct here is essentially perfect. Recovery-audit effort should point at the manual-entry door (and its one live exception), not at compliant suppliers.

09Methodology, Assumptions & Data Quality

Method

Four tests on the full VendBill item-line universe: (1) Agreed price — bill line rate vs. its PO line rate via createdfrom linkage, joined on item, ±$0.0001 tolerance. (2) Historical price — distinct billed rates per vendor-item pair over 24 months (creep detector), plus cross-vendor rate comparison on multi-sourced items. (3) Surcharge terms — enumeration of all OthCharge/Service/expense lines on or alongside product bills, classified against account coding. (4) Goods received — billed vs. received quantity per PO-item combo, both directions, valued at PO rates.

Assumptions

(1) PO rate = agreed price. No separate contract/price-list module is in use in this account; the PO is the commercial agreement of record. (2) Contracted freight/fuel/handling terms — no vendor contract documents exist in NetSuite; the test therefore verifies that no surcharge lines exist at all (which subsumes "above terms"). If offline contracts allow specific surcharges, the §10 surcharge query is the enforcement hook. (3) Cross-vendor rate parity treated as the market reference for off-PO lines. (4) GRNI valued at PO rates. (5) Bills LP-interco-alloc 1–3 (Flexsteel, no item lines) are intercompany allocations, out of scope. (6) Single currency (USD).

Data quality observations

(i) 99.6% PO-linkage coverage is exceptional and is what makes this reconciliation near-total. (ii) VB397's memo contains a 16-digit card-formatted number — a data-handling violation independent of the billing question. (iii) Off-PO bills VB01–VB08 are round-quantity, standard-rate bills keyed monthly Aug 2025–Apr 2026 — consistent with a bulk catch-up or import; worth confirming their receipts exist outside the PO chain. (iv) nexttransactionlink carries only bill→payment links in this account; PO↔bill↔receipt lineage lives entirely on transactionline.createdfrom.

10Appendix: Source Queries

-- Any row returned = a supplier billed off the agreed PO rate
SELECT b.tranid, v.companyname, i.itemid,
       ABS(btl.rate) AS billed_rate, ABS(ptl.rate) AS po_rate,
       ROUND((ABS(btl.rate) - ABS(ptl.rate)) * ABS(btl.quantity), 2) AS variance_value
FROM transaction b
JOIN transactionline btl ON btl.transaction = b.id
JOIN transaction po ON po.id = btl.createdfrom AND po.type = 'PurchOrd'
JOIN transactionline ptl ON ptl.transaction = po.id AND ptl.item = btl.item AND ptl.mainline = 'F'
JOIN vendor v ON v.id = b.entity
JOIN item i ON i.id = btl.item
WHERE b.type = 'VendBill' AND btl.mainline = 'F' AND btl.taxline = 'F'
  AND btl.item IS NOT NULL AND btl.quantity <> 0 AND ptl.rate IS NOT NULL
  AND ROUND(ABS(btl.rate),4) <> ROUND(ABS(ptl.rate),4)
-- Current result: 0 rows across 8,609 matched line-pairs.

SELECT v.companyname AS vendor, i.itemid AS sku,
       COUNT(DISTINCT ROUND(ABS(tl.rate),4)) AS distinct_rates,
       ROUND(MIN(ABS(tl.rate)),4) AS min_rate, ROUND(MAX(ABS(tl.rate)),4) AS max_rate,
       ROUND(SUM(tl.netamount),2) AS spend
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN vendor v ON v.id = t.entity
JOIN item i ON i.id = tl.item
WHERE t.type = 'VendBill' AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL AND tl.quantity <> 0
GROUP BY v.companyname, i.itemid
HAVING COUNT(DISTINCT ROUND(ABS(tl.rate),4)) > 1
-- Current result: 0 rows (every pair bills exactly one rate). Cross-vendor variant:
-- GROUP BY i.itemid across vendors; flag items whose min/max rates differ. 1 hit: VB397.

-- Any OthCharge/Service item line or expense line landing on a bill that also carries product lines
SELECT t.tranid, v.companyname, COALESCE(i.itemid, a.fullname) AS charge_ref,
       ROUND(tl.netamount,2) AS amount
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN vendor v ON v.id = t.entity
LEFT JOIN item i ON i.id = tl.item
LEFT JOIN account a ON a.id = tl.expenseaccount
WHERE t.type = 'VendBill' AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND (i.itemtype IN ('OthCharge','Service','Markup')
       OR (tl.item IS NULL AND t.id IN (
            SELECT t3.id FROM transaction t3
            JOIN transactionline tl3 ON tl3.transaction = t3.id
            WHERE t3.type='VendBill' AND tl3.mainline='F'
              AND tl3.item IS NOT NULL AND tl3.quantity <> 0)))
ORDER BY t.trandate

SELECT po.tranid AS po_num, v.companyname AS vendor, i.itemid AS sku,
  SUM(CASE WHEN b.type='VendBill' THEN ABS(tl.quantity) ELSE 0 END) AS qty_billed,
  SUM(CASE WHEN b.type='ItemRcpt' THEN ABS(tl.quantity) ELSE 0 END) AS qty_received
FROM transaction b
JOIN transactionline tl ON tl.transaction = b.id
JOIN transaction po ON po.id = tl.createdfrom AND po.type = 'PurchOrd'
JOIN vendor v ON v.id = po.entity
JOIN item i ON i.id = tl.item
WHERE b.type IN ('VendBill','ItemRcpt') AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL
GROUP BY po.tranid, v.companyname, i.itemid
HAVING SUM(CASE WHEN b.type='VendBill' THEN ABS(tl.quantity) ELSE 0 END)
     <> SUM(CASE WHEN b.type='ItemRcpt' THEN ABS(tl.quantity) ELSE 0 END)
-- billed > received = claim; received > billed = GRNI accrual. Current: 0 claims, $32,988 GRNI.

SELECT t.tranid, TO_CHAR(t.trandate,'YYYY-MM-DD') AS dt, v.companyname AS vendor,
       i.itemid, ROUND(tl.netamount,2) AS amt, tl.quantity
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN vendor v ON v.id = t.entity
LEFT JOIN item i ON i.id = tl.item
WHERE t.type = 'VendBill' AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.item IS NOT NULL AND tl.createdfrom IS NULL
ORDER BY t.trandate
-- 29 lines, $375,533. VB397 surfaced here: rate 285x the cross-vendor reference.