Building KPI Scorecards for Executives

Executives rarely have time to explore a dashboard. They need to know, in a minute, whether the business is on track, where it is not, and who is dealing with it. A KPI scorecard answers exactly that: a short list of measures, each compared with a target and given a status. Building a good one is less about charts than about choosing measures, defining them precisely and setting targets that mean something.

Scorecard or dashboard?

Power BI, for example, has a scorecard feature built around exactly these elements: goals with a current value and a target, owners, and regular check-ins that build a history (Microsoft Learn: goals and scorecards). A scorecard often links to dashboards for the detail behind each number.

Choose a few balanced measures

Kaplan and Norton's balanced scorecard began from the observation that what you measure is what you get, and that financial measures alone can send misleading signals (Harvard Business Review: the balanced scorecard). "Balanced" means adding strategic measures to financial ones, commonly grouped into perspectives such as financial, customer, internal process and organisational capacity (Balanced Scorecard Institute: basics).

In practice:

• Five to eight KPIs. More than that and the scorecard becomes a dashboard nobody reads.
• A mix of outcomes and drivers. Revenue is an outcome; returning-customer rate and order volume help explain it.
• Each tied to a decision. If a KPI turning red would not change anything, it does not belong. Vanity vs actionable metrics covers how to tell the difference.

Write each KPI down completely

For every KPI, agree and record:

1. Definition and formula, including exclusions, such as whether revenue includes returns or postage.
2. Direction: is higher or lower better?
3. Owner: the person who explains it and acts on it.
4. Target and how it was set.
5. Status rules: what counts as green, amber and red.
6. Data source and refresh date.

How to create effective KPIs covers the definition step in depth.

A worked example on real data

Take a UK online gift retailer's transactions for 2011 (UCI: Online Retail), cleaned as in a sales reporting project: cancellations, postage, fees and vouchers excluded from sales. Five monthly KPIs cover volume, value, customer loyalty and quality:

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
is_service = raw["StockCode"].isin(services) | raw["StockCode"].str.startswith("gift_")
is_cancellation = raw["InvoiceNo"].str.startswith("C")
raw["month"] = raw["InvoiceDate"].dt.to_period("M")
sales = raw[~is_cancellation & ~is_service & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)]
sales = sales.assign(revenue=sales["Quantity"] * sales["UnitPrice"])
returns = raw[is_cancellation & ~is_service]
first_month = sales.dropna(subset=["CustomerID"]).groupby("CustomerID")["month"].min()

def kpis(month):
    s = sales[sales["month"] == month]
    customers = s["CustomerID"].dropna().unique()
    return {"revenue": s["revenue"].sum(),
            "orders": s["InvoiceNo"].nunique(),
            "avg order value": s["revenue"].sum() / s["InvoiceNo"].nunique(),
            "returning customers": (first_month.loc[customers] < month).mean(),
            "return rate": -(returns.loc[returns["month"] == month, "Quantity"] * returns.loc[returns["month"] == month, "UnitPrice"]).sum() / s["revenue"].sum()}

history = pd.DataFrame({str(m): kpis(m) for m in pd.period_range("2011-06", "2011-11", freq="M")}).T
print(history.round({"revenue": 0, "orders": 0, "avg order value": 1, "returning customers": 3, "return rate": 3}).to_string())

           revenue  orders  avg order value  returning customers  return rate
2011-06   738752.0  1525.0            484.4                0.756        0.019
2011-07   689364.0  1452.0            474.8                0.802        0.017
2011-08   725514.0  1339.0            541.8                0.819        0.032
2011-09  1030475.0  1818.0            566.8                0.763        0.017
2011-10  1106670.0  2005.0            552.0                0.738        0.040
2011-11  1457746.0  2751.0            529.9                0.805        0.017

"Returning customers" is the share of a month's identified customers who had bought in an earlier month, and "return rate" is the value of cancelled product lines as a share of revenue, lower being better. Now the scorecard for November, with a simple rule for the example: the target is the average of the previous three months; green means on or better than target, amber up to 5% worse, red more than 5% worse, with the direction reversed for return rate:

current, target = history.loc["2011-11"], history.loc[["2011-08", "2011-09", "2011-10"]].mean()
lower_is_better = {"return rate"}
scorecard = []
for kpi in history.columns:
    change = (current[kpi] - target[kpi]) / target[kpi]
    performance = -change if kpi in lower_is_better else change
    status = "green" if performance >= 0 else "amber" if performance >= -0.05 else "red"
    scorecard.append({"KPI": kpi, "November": round(current[kpi], 3), "target": round(target[kpi], 3),
                      "vs target": f"{change:+.1%}", "status": status})
print(pd.DataFrame(scorecard).to_string(index=False))

                KPI    November     target vs target status
            revenue 1457745.530 954219.560    +52.8%  green
             orders    2751.000   1720.667    +59.9%  green
    avg order value     529.897    553.535     -4.3%  amber
returning customers       0.805      0.773     +4.2%  green
        return rate       0.017      0.029    -41.4%  green

The seasonality trap

The scorecard looks spectacular: revenue 53% above target. But for a gift retailer, November is the run-up to Christmas, and the history shows revenue rising every month from September. A target based on recent months ignores that, so the scorecard would turn green every autumn and red every spring whatever the business did. The amber average order value is more informative: order sizes dipped slightly while volume surged.

Targets need to reflect what is expected for that period:

• Plan or budget targets, agreed with the business, month by month.
• Same period last year, adjusted for planned growth, when at least a year of history exists.
• Seasonal forecasts when patterns are strong.

State how each target was set on the scorecard itself, so nobody mistakes a seasonal peak for a breakthrough.

Designing the one-page scorecard

• One row per KPI: name, owner, current value, target, variance, status and a small trend line of the last six periods.
• Status by more than colour. Add a symbol or word, since red and green are hard to distinguish for many colour-blind readers.
• A one-line comment per exception, written by the owner: what happened and what is being done.
• Period and refresh date in the header, so stale numbers are obvious.
• The same layout every period, so readers learn where to look.

Running it as a process

A scorecard works when it drives a regular conversation. Review it on a fixed cadence, ask owners of amber and red KPIs for their comments before the meeting, and record check-ins so there is a history of what was decided. Review the KPIs themselves once or twice a year: retire measures that never change decisions, and add ones that reflect new priorities.

Common mistakes

• Too many KPIs, so nothing stands out.
• Targets without a basis, especially targets that ignore seasonality.
• No owners, so red KPIs are noted and nothing happens.
• Changing definitions silently, which breaks comparisons over time.
• Only lagging financial measures, which report problems after they are too late to fix.

KPI analysis, business reporting and interactive dashboards are part of the Data Analytics course.

Sources

• The Balanced Scorecard: Measures that Drive Performance — Harvard Business Review, 1992
• Balanced Scorecard Basics (Balanced Scorecard Institute) — Balanced Scorecard Institute
• Get started with goals and scorecards in Power BI (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository