Procurement KPI template: inputs, formulas and targets

A procurement KPI template connects each purchasing result to its inputs, formula, approved target and owner. Use it as a reusable calculation register, with separate sections for source rows and the review output. A status color is useful only after the population and arithmetic have been checked.

Start with a register that fixes the definition

The layout below measures a US distributor's September 2026 purchasing activity. Its targets are illustrative internal approvals for this example, not suggested industry standards. Amount-based coverage measures use addressable AP invoice spend; purchase-order cycle time uses completed purchase orders.

Add reporting period, definition version, source filename, extraction date, target approver and data-quality status to the register. Give each rule an effective date so a later policy change does not silently rewrite earlier results.

The existing procurement KPI definitions cover a wider set of measures. This template focuses on how to hold the inputs and calculations together rather than introducing another competing list.

Fill the AP input table from purchasing evidence

Use one row per invoice line, retain signed amounts and keep the stable source identifier. Bring supplier, category, entity and date fields in the working file even though the compact example below shows only the calculation columns. All amounts here are USD on the agreed purchasing basis.

The addressable flag follows the approved scope. The contract flag requires a matching supplier, covered category and applicable dates. The approved-PO flag requires evidence of approval before purchase commitment, not merely a PO number added after invoicing.

I03 is excluded by the approved purchasing scope. Its exclusion remains visible rather than being removed from the AP reconciliation. For this example, all required evidence is present and the Y/N decisions have been reviewed.

In a real export, use a separate unresolved status when a contract date or approval record is missing. An unresolved line should not be automatically relabeled N or dropped. Either resolve it before publication or label the affected KPI provisional and disclose its amount.

Enter the invoice-based formulas

With the AP table in A1:E5 in Google Sheets, eligible spend is:

=SUMIFS(B2:B5,C2:C5,"Y")

That is $12,000 + $8,000 + $6,000 = $26,000. Google's SUMIFS documentation describes summing values that meet the specified criteria. Use the same eligibility criteria in each coverage denominator.

Calculate contracted share and timely approved-PO coverage:

=SUMIFS(B2:B5,C2:C5,"Y",D2:D5,"Y")/SUMIFS(B2:B5,C2:C5,"Y")
=SUMIFS(B2:B5,C2:C5,"Y",E2:E5,"Y")/SUMIFS(B2:B5,C2:C5,"Y")

Contract-covered eligible spend is $12,000 + $6,000 = $18,000. Contracted share is $18,000 / $26,000 = 69.23%. Timely approved-PO spend is $12,000 + $8,000 = $20,000, giving $20,000 / $26,000 = 76.92%.

Do not replace a zero denominator with a successful result. Check eligible spend first and show an explicit review status when there is no eligible population. Round displayed percentages to two decimal places while retaining full precision for target comparisons.

Keep the PO cycle table separate

An invoice can refer to a PO with several lines, so calculating cycle time over joined invoice rows can give that PO extra weight. Use one row per completed PO instead. In this example, elapsed time means calendar days from a complete request to approval; open requests remain in a separate backlog review.

In D2, enter =C2-B2 and fill down. The completed-PO average is:

=SUM(D2:D5)/COUNT(D2:D5)

The result is 20 / 4 = 5 calendar days. Check for missing approvals before treating requests as completed:

=COUNTIFS(A2:A5,"<>",C2:C5,"")

Google documents COUNTIFS as counting records meeting all supplied criteria. This check returns zero for the displayed completed population; also reject approval dates earlier than request completion dates.

Build the review output and status rules

Store actuals and targets as numbers, using percentage or day display formats rather than adding text to the values. For an actual in D2, target in E2 and direction in H2, the status formula in G2 is:

=IF(H2="Higher",IF(D2>=E2,"Meets target","Review"),IF(D2<=E2,"Meets target","Review"))

Validate direction values before applying this formula. Gate the output on data-quality checks so a failed reconciliation produces a review of the inputs before a performance judgment.

Keep the calculation register when you build a procurement dashboard. The existing KPI dashboard template illustrates a general presentation layout; it does not replace these purchasing definitions or supply a separate procurement workbook download.

Checks and pitfalls before the monthly review

The AP identity is $36,000 = $26,000 addressable + $10,000 excluded. Within addressable spend, contract coverage reconciles as $26,000 = $18,000 covered + $8,000 uncovered. Preserve these checks alongside the result, and make sure joins have not increased the number of invoice lines.

• Changing scope called improvement. Track policy reclassifications separately; the spend-under-management bridge explains that distinction.
• Contract existence treated as coverage. A supplier agreement still needs the right dates and purchasing scope.
• Calendar and business days mixed. Define one basis in the register and use a matching target.
• Averaging site percentages. Recalculate coverage from summed eligible amounts rather than averaging local rates.
• Targets without approval. Record an accountable owner and effective period before showing a status.

Covirage is not affiliated with Google.

Check your procurement inputs in Covirage

Bring your AP lines, purchase-order export, contract file and approved KPI definitions. Covirage maps the columns, and its tools compute and check the purchasing comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own files.

See Covirage for procurement teams for the purchasing context.