Investigating missed supplier credits
Recovering money the business was owed by checking supplier invoices against the contracts behind them.
Achieved results
- Supported approximately $3 million in recoveries within one year.Verified outcome. Historical financial result shown in US dollars (USD).
- Invoice validation checks and an audit trail were introduced to identify discrepancies and track outstanding amounts.
Business context
In a Business Intelligence role at a large e-commerce business (anonymised here), supplier invoices were paid against a mix of contracts, rebate terms, buy prices and recorded costs. With thousands of invoices, small mismatches were easy to miss and manual checking could not keep up.
The problem
Differences between supplier invoices and agreed contract terms meant money owed to the business was being missed. I investigated those discrepancies and helped introduce a more consistent validation process.
My role
I designed and ran the reconciliation: building the SQL logic to compare invoices against contract terms, investigating what it surfaced, and introducing checks that caught issues before payment.
Investigation and approach
- 01Extracted invoice, contract, rebate, buy price and cost data into one place.
- 02Built SQL reconciliation queries flagging variances by supplier, invoice and reason.
- 03Investigated discrepancies with Finance, Vendor Management and Customer Success.
- 04Introduced invoice validation checks and an audit trail to track outstanding amounts to recovery.
The analysis, for you to explore
Supplier invoice reconciliation
Illustrative reconstruction using representative sample data.
Invoiced (sample)
$425,566
Per contract
$413,702
Total variance
$11,864
Invoiced above contract terms
Outstanding
$3,012
Flagged + under review
Recovered (sample)
$8,852
16 exception invoices
Invoiced vs contract value by supplier (sample, USD)
- Discrepancy
- Invoiced
- Per contract
Exception invoices — invoices above the contracted terms, with their current status.
| Invoice | Supplier | Month | Invoiced | Per contract | Variance | Type | Status |
|---|---|---|---|---|---|---|---|
| INV-2402 | Northline Supplies | 2024-08 | $19,123.50 | $18,300.00 | +$823.50 | Rebate not applied | Recovered |
| INV-2403 | Northline Supplies | 2024-09 | $4,389.00 | $4,200.00 | +$189.00 | Rebate not applied | Under review |
| INV-2405 | Juniper Packaging | 2024-11 | $27,192.00 | $26,400.00 | +$792.00 | Price mismatch | Recovered |
| INV-2406 | Juniper Packaging | 2024-12 | $4,389.00 | $4,200.00 | +$189.00 | Rebate not applied | Under review |
| INV-2408 | Juniper Packaging | 2025-02 | $8,151.00 | $7,800.00 | +$351.00 | Rebate not applied | Recovered |
| INV-2409 | Southport Timber | 2025-03 | $26,928.00 | $26,400.00 | +$528.00 | Quantity error | Under review |
| INV-2411 | Southport Timber | 2025-05 | $4,326.00 | $4,200.00 | +$126.00 | Price mismatch | Recovered |
| INV-2412 | Southport Timber | 2025-06 | $40,755.00 | $39,000.00 | +$1,755.00 | Rebate not applied | Recovered |
| INV-2414 | Cascade Ceramics | 2024-08 | $4,326.00 | $4,200.00 | +$126.00 | Price mismatch | Recovered |
| INV-2415 | Cascade Ceramics | 2024-09 | $4,326.00 | $4,200.00 | +$126.00 | Price mismatch | Recovered |
| INV-2417 | Elmfield Glass | 2024-11 | $27,588.00 | $26,400.00 | +$1,188.00 | Rebate not applied | Recovered |
| INV-2418 | Elmfield Glass | 2024-12 | $27,984.00 | $26,400.00 | +$1,584.00 | Missing credit | Recovered |
| INV-2420 | Elmfield Glass | 2025-02 | $8,151.00 | $7,800.00 | +$351.00 | Rebate not applied | Flagged |
| INV-2421 | Harbor Freight Co | 2025-03 | $27,192.00 | $26,400.00 | +$792.00 | Price mismatch | Recovered |
| INV-2423 | Harbor Freight Co | 2025-05 | $40,755.00 | $39,000.00 | +$1,755.00 | Rebate not applied | Flagged |
| INV-2424 | Harbor Freight Co | 2025-06 | $27,588.00 | $26,400.00 | +$1,188.00 | Rebate not applied | Recovered |
Findings
- Rebate terms were the most common source of value leakage in the sample.
- Flagging discrepancies before payment shortened recovery conversations considerably.
- A shared audit trail removed duplicated investigation between teams.
Deliverables
- Reusable SQL reconciliation queries
- Discrepancy reports by supplier and reason
- Invoice validation checks
- Audit trail tracking outstanding amounts
Tools used
- SQL
- Excel
- Stakeholder workshops