Blog · Board and management reporting
A KPI scorecard compares defined measures with approved targets and makes exceptions actionable. Build a worked scorecard from export totals, calculate directional status and trends, and check the evidence before the review meeting.
A KPI scorecard compares actual performance with approved targets and names who will act when a result misses. Each row needs a definition, reporting period, owner, trend, and next action. Start with checked export totals so the review meeting can focus on business decisions.
Select measures that have a clear management use. An operations manager might review order accuracy, first response time, and dispatch reliability because each points to a different customer experience problem. Keep the KPI definition alongside the scorecard so the underlying population stays consistent.
Agree on the target before calculating status. Also record whether higher or lower is better, who approves changes, and which reporting period a target covers. A target copied from a different queue, store, or contract can create an exception that has no business meaning.
Use the KPI dashboard template for an existing layout and the Excel dashboard examples for presentation ideas. The method here concerns operating definitions and exception handling. Covirage is not affiliated with Microsoft.
An illustrative US distributor, Lakeside Business Supply, compares September 2026 with August 2026. Its source files contain completed orders, support tickets with a first human response, and orders due for dispatch. Each population has its own eligibility rules.
Order accuracy means an eligible completed order with no recorded picking or invoicing error. Response time is total elapsed minutes from ticket creation to first human response, restricted to tickets created in the period that have responded by the reporting cutoff. Dispatch reliability uses orders due in the period, including those still overdue at cutoff.
Summarize each source after checking duplicate identifiers, exclusions, timestamps, and completeness. Put the following checked summary in a worksheet named Raw, with headers in row 1. Numerators contain counts except for response time, where they contain total minutes.
| A: KPI | B: Period | C: Numerator | D: Denominator |
|---|---|---|---|
| Order accuracy | Current | 190 | 200 |
| Order accuracy | Prior | 188 | 200 |
| First response | Current | 2,400 | 100 |
| First response | Prior | 2,800 | 100 |
| On-time dispatch | Current | 180 | 200 |
| On-time dispatch | Prior | 184 | 200 |
For a full export, retain a separate list of the qualifying records behind every summary. An order eligible for both accuracy and dispatch belongs in both measures, but should appear only once within each population. Do not add the denominators across unrelated KPIs and call the result a customer count.
Create a Scorecard worksheet with this column order:
| Column | Field | Purpose |
|---|---|---|
| A | KPI | Exact measure label used in Raw |
| B | Current numerator | Sum of qualifying counts or elapsed minutes |
| C | Current denominator | Sum of eligible observations |
| D | Actual | Current numerator divided by denominator |
| E | Target | Approved value for the current period |
| F | Prior | Prior numerator divided by prior denominator |
| G | Direction | up or down |
| H | Gap | Actual minus target |
| I | Change | Actual minus prior |
| J | Status | On target, Review, Missing, or Check direction |
| K | Owner | Accountable role |
| L | Action | Specific next step and review date |
Enter the measure labels in A2:A4. In Excel, use the following formulas in B2 and C2 and fill down. Microsoft's SUMIFS documentation explains how the function sums values that meet multiple criteria.
=SUMIFS(Raw!$C$2:$C$7,Raw!$A$2:$A$7,A2,Raw!$B$2:$B$7,"Current")
=SUMIFS(Raw!$D$2:$D$7,Raw!$A$2:$A$7,A2,Raw!$B$2:$B$7,"Current")
The actual in D2 is:
=IF(C2=0,"",B2/C2)
For the prior period, this formula in F2 uses the corresponding totals directly:
=IF(SUMIFS(Raw!$D$2:$D$7,Raw!$A$2:$A$7,A2,Raw!$B$2:$B$7,"Prior")=0,"",SUMIFS(Raw!$C$2:$C$7,Raw!$A$2:$A$7,A2,Raw!$B$2:$B$7,"Prior")/SUMIFS(Raw!$D$2:$D$7,Raw!$A$2:$A$7,A2,Raw!$B$2:$B$7,"Prior"))
Extend the source ranges when the file grows. These formulas assume the source numerators and denominators have already passed the completeness checks. A blank numerator in an incomplete export must be resolved before aggregation; SUMIFS cannot establish that the records are complete.
Use these approved targets: order accuracy 97.0%, first response 20 minutes, and on-time dispatch 90.0%. Store rate targets as percentage values and durations as minutes. Format D:F as percentages for the rate rows and numbers for response time.
| KPI | Actual calculation | Actual | Target | Prior calculation | Prior |
|---|---|---|---|---|---|
| Order accuracy | 190 / 200 | 95.0% | 97.0% | 188 / 200 | 94.0% |
| First response | 2,400 / 100 | 24 minutes | 20 minutes | 2,800 / 100 | 28 minutes |
| On-time dispatch | 180 / 200 | 90.0% | 90.0% | 184 / 200 | 92.0% |
Rates are rounded to one decimal place; these duration averages are exact. Set G2:G4 to up, down, and up, respectively. Compute H2 and I2 as follows, then fill down:
=IF(OR(D2="",E2=""),"",D2-E2)
=IF(OR(D2="",F2=""),"",D2-F2)
For rate rows, display differences as percentages but label them percentage points. A spreadsheet difference of -2.0% between rate cells represents -2.0 percentage points. Do not describe it as a relative percentage change.
The status formula in J2 handles direction and missing inputs explicitly. Microsoft documents IF as returning different results according to whether a logical test is true or false.
=IF(OR(D2="",E2="",G2=""),"Missing",IF(G2="up",IF(D2>=E2,"On target","Review"),IF(G2="down",IF(D2<=E2,"On target","Review"),"Check direction")))
| KPI | Actual minus target | Actual minus prior | Status | Interpretation |
|---|---|---|---|---|
| Order accuracy | -2.0 percentage points | +1.0 percentage point | Review | Improving but below target |
| First response | +4 minutes | -4 minutes | Review | Faster but above the time limit |
| On-time dispatch | 0.0 percentage points | -2.0 percentage points | On target | Meets target while deteriorating |
The table proves why trend and attainment belong in separate columns. An improving measure can still miss its target; a worsening one can still meet it.
The scorecard has two Review rows out of three measured rows. That count summarizes the review workload, not the combined quality of service. Do not average the two rate measures and the duration into an overall performance number.
| Owner | Measure | Next action for the review |
|---|---|---|
| Order quality manager | Order accuracy | Inspect the error records and identify the repeated error category |
| Support manager | First response | Compare elapsed response time by issue type and creation window |
| Warehouse manager | On-time dispatch | Review overdue dispatch records behind the declining result |
Add an agreed review date and evidence of completion to each action. The supply chain KPI scorecard applies this operating pattern to supply performance. The broader scorecard dashboard guide covers linking measures to business objectives.
Bring a sample of your own business exports and target register; Covirage maps the columns, and its tools compute and check the 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 your files.
See metrics governance for the supporting approach to consistent definitions and accountability.
A dashboard displays selected measures for monitoring and investigation. A scorecard adds an explicit comparison with approved targets and responsibility for the response. The same page can serve both purposes when its results include definitions, owners, and documented exception actions.
Only when leaders have approved meaningful weights and scoring rules. Averaging unrelated percentages, dollars, and durations produces an arbitrary result. Keeping the individual exceptions visible often gives a clearer basis for decisions than compressing them into one overall score.
Choose measures tied to the service commitments the IT team owns, such as response performance, availability, or change outcomes. Use the relevant service exports, define exclusions and reporting clocks, and assign an accountable owner. Keep service quality visible alongside cost measures.
Match the review cadence to the decision and the availability of reliable evidence. Operational exceptions may need frequent review, while financial measures depend on close procedures. Record the cutoff on every result so newer and older figures are not presented as one current period.