₦airaland Forum

Welcome, Guest: Register • Login • With Google • Trending • Recent • New

Stats: 3,345,877 members, 8,519,931 topics. Date: Tuesday, 17 November 2026 at 06:30 AM

Toggle theme

ProcurementSir's Posts

Nairaland Forum › ProcurementSir's Profile › ProcurementSir's Posts

1 (of 1 pages)

Educational Services › Three-way Match In Excel: Catch The Invoices Your Company Should Never Pay (work by ProcurementSir(op): 6:00am
Every invoice should pass one test before money leaves the account: does it match the purchase order and the goods received note? That test is the three-way match, and most payment errors hide inside it.

Here is a small worked example. Imagine these three invoices land on your desk.

Invoice A: 100 cartons at NGN 50,000 each. The PO says 100 cartons at NGN 50,000. The GRN says 100 cartons received. Total NGN 5,000,000. Clean match. Pay it.

Invoice B: 100 cartons at NGN 55,000 each. The PO says NGN 50,000. The GRN says 100 received. The quantity is fine, the price is not. Paying this invoice as billed overpays by NGN 500,000. This is a price variance.

Invoice C: 120 cartons billed at NGN 50,000. The PO says 100. The GRN says 100 received. You are being billed for 20 cartons that never arrived. That is NGN 1,000,000 of goods you did not receive. This is a quantity variance.

Three invoices, one clean, two that should be held. On a small desk, that is NGN 1,500,000 protected by a ten minute check.

In Excel the check is simple. Put the PO price and the invoice price side by side, and the billed quantity next to the received quantity. Flag any row where the invoice price is not equal to the PO price, or where the billed quantity is greater than the received quantity. Conditional formatting on the flag column turns a 500 line invoice run into a short exception list you can actually work through.

Two rules make it work in real life. First, agree a tolerance before you start: for example, price differences under 1 percent pass, everything above gets held. Second, never let the person who raised the PO approve the exception on their own PO. That one control kills most duplicate and inflated billing.

If you want to practise on a full ledger instead of small examples, there is a free practice dataset and a free chapter from the Procurement Analytics and AI Masterclass at procurementacademy dot space. The ledger has 150 invoices and the same checks scale up: on that practice data, about 47 percent of spend sits outside at least one control, which is why this check pays for itself.

Questions are welcome. The next one will be on contract leakage: how to spot spend that should have been on contract but was not.
Career › How To Audit PO Compliance And Contract Leakage In Excel (worked Example, NGN 55 by ProcurementSir(op): 12:17am On Nov 16
Many procurement reports show PO compliance and contract coverage as two separate bars. This tutorial shows why that hides the real exposure, using a fictional practice ledger: 150 invoices, 20 invented suppliers, NGN 552,660,500 total.

Step 1. The headline numbers
- PO compliance by spend: 82.03%
- Contract coverage: 62.06%
- So 17.97% of spend has no PO and 37.94% has no contract.

Step 2. The two wrong answers
- Adding the gaps: 17.97% + 37.94% = 55.91%. Wrong, because invoices with no PO and no contract are counted twice.
- Subtracting the rates: 82.03% - 62.06% = 19.97 points. Wrong, because it hides spend that has a contract but no PO, and spend that has neither.

Step 3. Build the matrix
Put your data in an Excel table called Spend with columns Amount_NGN, POCompliant (Y/N) and ContractedSupplier (Y/N). Then:

PO and contract:
=SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"Y",Spend[ContractedSupplier],"Y" )

PO, no contract:
=SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"Y",Spend[ContractedSupplier],"N" )

Contract, no PO:
=SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"N",Spend[ContractedSupplier],"Y" )

Neither:
=SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"N",Spend[ContractedSupplier],"N" )

On the practice ledger: 53.41%, 28.62%, 8.65% and 9.31%.

Step 4. The real answer
Spend outside at least one control = total minus the "PO and contract" box:
=SUM(Spend[Amount_NGN])-SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"Y",Spend[ContractedSupplier],"Y" )

That gives NGN 257,488,500, or 46.59% of spend.

Step 5. Sort before you recommend
Sort the off-contract invoices by value. In the practice ledger, one invoice of NGN 62,500,000 is 29.81% of all off-contract spend. Look at that one purchase before proposing a big sourcing project.

Two cautions
- Count off-contract spend from the N flag directly. Do not use 1 minus contract coverage, because blank flags then count as off-contract.
- A PO number does not prove approval came before the purchase. Without approval dates, the matrix is a review list, not proof of wrongdoing.

Questions welcome below. Happy to check anyone's formulas.

1 (of 1 pages)