Procurement dashboard template: fields and checks

A procurement dashboard template needs separate inputs for purchasing amounts and supplier delivery, plus checks that tie each summary to its source. Use the layout below to build a workbook from your own exports. Keep the reporting period, currency, and measure definitions visible beside the results.

Set up the workbook around its source records

The template below supplies tab layouts, populated examples, and formulas to enter in your own workbook. It is intended for a US purchasing team reviewing September 2026 posted invoices and purchase-order lines due during the same month.

Keep the original exports available for comparison. Use canonical supplier IDs consistently in both inputs. If category assignments need cleanup, the existing procurement category mapping template supports that preparation.

This article covers the workbook structure. The procurement dashboard guide covers the management view, while the procurement KPI template addresses the register of measure definitions.

Populate the purchasing tab

Use these columns in Purchases, with headers in row 1. The line key includes the document and line identifier, so separate lines on one invoice are not mistaken for duplicates. All example amounts are USD, and the scope excludes tax and separate freight charges.

Enter this formula in H2 and fill through H6:

=E2*F2+G2

The adjustment field contains amounts not already included in units times price. Beacon's invoice has a $100 adjustment; its separate credit reduces spend by $200. The resulting net amounts total $24,400. If your export already provides an approved net amount, import that amount and use the reconstruction as a check rather than adding adjustments twice.

Keep delivery at the due-line level

Populate Receipts from the complete PO-line population due in the reporting month, including lines with no receipt. Consolidate receipt events into each due line before calculating its flag. For this example, the agreed OTIF rule requires all due units to have arrived by the original agreed deadline, with no quantity tolerance.

In F2, fill down through F5:

=IF(OR(D2="",E2="",D2<=0),"CHECK",IF(E2>=D2,1,0))

Record an actual zero for a known absence of receipts; leave an unresolved value blank and investigate the resulting CHECK. Block reporting until every eligible line has a valid flag. The OTIF trend guide explains the delivery measure in more detail.

Do not attach every receipt event directly to invoice lines. That can multiply purchasing amounts when one PO line has several receipts or invoices.

Build summaries from each input separately

In Dashboard, put supplier IDs in A2:A4. Put spend in B, share in C, eligible due lines in D, and OTIF in E. The following formulas use the example's exact ranges:

B2 = SUMIFS(Purchases!$H$2:$H$6,Purchases!$C$2:$C$6,A2)
C2 = B2/SUM(Purchases!$H$2:$H$6)
D2 = COUNTIFS(Receipts!$B$2:$B$5,A2)
E2 = IF(D2=0,"N/A",SUMIFS(Receipts!$F$2:$F$5,Receipts!$B$2:$B$5,A2)/D2)

The cell labels identify where to enter each expression beginning with =. Fill them down. Microsoft's SUMIFS documentation describes summing amounts that match criteria; its COUNTIFS documentation describes counting rows that meet criteria. Extend the ranges together when adding records.

Spend shares are rounded to one decimal place. Total OTIF is the two passing lines divided by four eligible lines, not an average of supplier percentages. The displayed summary supports a question for Aster about its incomplete due line while preserving its purchasing exposure.

Build a category summary using the same spend formula with Purchases column D as the criteria range. Components are 10,000 + 8,000 + 1,500 = $19,500. Packaging is 5,100 - 200 = $4,900. These category totals add to $24,400, matching the supplier view.

Put the reconciliation beside the dashboard

The spend difference formula is =SUM(Dashboard!B2:B4)-SUM(Purchases!H2:H6). Check duplicate purchase keys with =COUNTIFS($A$2:$A$6,A2) on Purchases; every result should be 1. Repeat the key check on Receipts using its own range.

Record who reviewed exceptions and when the inputs were refreshed. Before the next month, clear obsolete rows, load the complete new populations, and expand every summary range. Keep missing categories visible as an exception rather than dropping them from category totals.

Where it goes wrong

• Receipt events multiply spend. Summarize each population independently before displaying it by supplier.
• Only received orders enter delivery. Missing receipts disappear from the denominator and make service look better.
• Blank quantities become zero. Resolve unknown inputs before treating them as actual performance.
• Credits are omitted. The dashboard stops agreeing with posted purchasing records.
• Unweighted percentages become the total. Rebuild the overall rate from passing and eligible line counts.

Covirage is not affiliated with Microsoft.

Analyze your populated procurement workbook in Covirage

Bring your purchasing file, due-line receipt file, supplier IDs, and the definitions used in the workbook. Covirage maps the columns, and its tools compute and check spend and supplier comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for procurement teams for how purchasing comparisons support category and supplier reviews.