How to Find Procurement Control Gaps in Excel: A Worked Example
This tutorial uses a fictional ledger of 150 invoices from 20 invented suppliers, worth NGN 552,660,500, to show why PO compliance and contract coverage should be analysed together. The ledger reports 82.03% PO compliance and 62.06% contract coverage. Simply adding the gaps double-counts some spend, while subtracting the rates misses other control gaps. Create an Excel table called Spend with Amount_NGN, POCompliant (Y/N) and ContractedSupplier (Y/N) columns. Use SUMIFS to total spend in each combination: PO and contract, PO without contract, contract without PO, and neither. In the example, those categories account for 53.41%, 28.62%, 8.65% and 9.31% of spend, respectively. To find spend outside at least one control, subtract the PO-and-contract total from all spend: =SUM(Spend[Amount_NGN])-SUMIFS(Spend[Amount_NGN],Spend[POCompliant],"Y",Spend[ContractedSupplier],"Y"). The result is NGN 257,488,500, or 46.59% of spend. Before recommending action, sort off-contract invoices by value. One NGN 62,500,000 invoice represents 29.81% of off-contract spend in this example. Count off-contract spend from the N flag directly, since blank flags can distort a calculation based on 1 minus contract coverage. Also, a PO number alone does not prove approval came before purchase; without approval dates, this analysis is a review list, not proof of wrongdoing.
Stories are shared by community members. This article does not represent the official view of NaijaWorld — the author is solely responsible for its content.

