Build a Monthly KPI Scorecard with Targets

The monthly management pack opens with a scorecard: a dozen KPIs, this month's figure, the target, whether it is on track, and which way it is heading. The trap is that each KPI behaves differently. Revenue should be above target, but complaint rate and days sales outstanding should be below it; some KPIs add up over the year, others are averages or ratios that must not be summed; and "on track" usually means "within a tolerance", not "exactly on target". Hand-maintained scorecards get these rules subtly wrong in one row and nobody notices. Putting the rules in a definitions table and computing status in pandas makes the scorecard consistent month after month. This page builds one, writes it as a single Excel sheet with coloured status and sparklines, and checks the colours match the rules. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

kpi_actuals.xlsx is a long table with one row per month and KPI from April to September 2026. kpi_targets.xlsx defines each KPI: its monthly target, whether higher or lower is better, the tolerance (as a fraction of target) within which a miss is amber rather than red, and whether the year-to-date figure is a sum or an average.

Step 1 — Keep the KPI rules as data

The definitions table is the heart of the scorecard. Every rule that differs between KPIs lives there, not in code:

import pandas as pd

actuals = pd.read_excel("kpi_actuals.xlsx")
defs = pd.read_excel("kpi_targets.xlsx").set_index("KPI")
print(defs.to_string())

missing = set(actuals["KPI"]) ^ set(defs.index)
assert not missing, f"KPIs without definitions or data: {missing}"

Better says which direction is good, Tolerance how far from target still counts as amber, and YTD how to aggregate across months. The symmetric difference check (^) fails if a KPI appears in the data without a definition or the other way round — adding a KPI then requires deciding its rules, which is exactly the conversation that should happen. Owners can maintain this table in Excel themselves.

Step 2 — Red, amber or green

Status depends on direction. Express each actual as a performance relative to target where positive is always good, then compare with the tolerance:

month = "2026-09"
prev = "2026-08"
wide = actuals.pivot(index="KPI", columns="Month", values="Actual")

def status(actual, target, better, tol):
    perf = (actual - target) / target if better == "higher" else (target - actual) / target
    perf = round(perf, 9)                 # 2.0 vs 2.2 must land exactly on a 10% tolerance
    if perf >= 0:
        return "Green", perf
    return ("Amber" if perf >= -tol else "Red"), perf

card = defs.copy()
card["Actual"] = wide[month]
card["Last month"] = wide[prev]
card[["Status", "Perf"]] = [status(a, t, b, tol) for a, t, b, tol in
                            zip(card["Actual"], card["Target"], card["Better"], card["Tolerance"])]
print(card[["Actual", "Target", "Better", "Perf", "Status"]].round(3).to_string())

For a higher-is-better KPI, performance is (actual − target) / target; for lower-is-better it is flipped, so beating a complaint-rate target of 2.0% with 1.8% would be positive. September's revenue of 468 against 440 is green; gross margin at 37.2% against 38.0% is 2.1% short with a 2% tolerance, so red; complaint rate at 2.2% against a ceiling of 2.0% is 10% worse, exactly at the tolerance, so amber; days sales outstanding at 47 against 40 is far out — red. The round matters more than it looks. Without it, the complaint rate's performance comes out as −0.10000000000000009 in floating point — fractionally beyond the −0.10 tolerance — and the KPI turns red although it sits exactly on the amber boundary. Rounding to a sensible precision before comparing makes boundary cases behave as the definitions say. Defining tolerance as a fraction of the target keeps it meaningful across KPIs measured in thousands of euros and in percentage points alike.

Step 3 — Variance, trend and year to date

The scorecard also shows the variance against target and last month, and a year-to-date figure that respects how each KPI aggregates:

card["vs target"] = card["Actual"] - card["Target"]
card["vs last month"] = card["Actual"] - card["Last month"]

ytd_months = [m for m in wide.columns if m <= month]
def ytd(kpi):
    values = wide.loc[kpi, ytd_months]
    return values.sum() if defs.loc[kpi, "YTD"] == "sum" else values.mean()

card["YTD"] = [ytd(k) for k in card.index]
card["YTD target"] = [defs.loc[k, "Target"] * (len(ytd_months) if defs.loc[k, "YTD"] == "sum" else 1) for k in card.index]
print(card[["Actual", "vs target", "vs last month", "YTD", "YTD target"]].round(1).to_string())

Revenue and new customers add up — six months of revenue total 2,556k against a year-to-date target of 2,640k. Percentages and days outstanding must not be summed; their year-to-date figure is the average of the months (a weighted average is more accurate when volumes differ — weight margin by revenue, for example — and is worth doing if the definitions table carries the weight column). The month-on-month change gives direction: days sales outstanding worsened by 3 days, on-time delivery improved by 3.2 points.

Step 4 — Write a one-page scorecard

XlsxWriter writes the sheet with a status cell coloured by conditional formatting, number formats per KPI and a sparkline of the last six months on each row:

import xlsxwriter

book = xlsxwriter.Workbook("kpi_scorecard_2026-09.xlsx")
ws = book.add_worksheet("Scorecard")
data = book.add_worksheet("Trend")
bold = book.add_format({"bold": True, "bottom": 1, "bg_color": "#EEF2FF"})
title = book.add_format({"bold": True, "font_size": 16, "font_color": "#4338CA"})
num = book.add_format({"num_format": "#,##0.0"})
signed = book.add_format({"num_format": "+#,##0.0;[Red]-#,##0.0;0.0"})
fills = {"Green": "#C6EFCE", "Amber": "#FFEB9C", "Red": "#FFC7CE"}

ws.write("A1", "KPI scorecard — September 2026", title)
headers = ["KPI", "Actual", "Target", "Status", "vs target", "vs last month", "YTD", "YTD target", "Trend (6 months)"]
ws.write_row(2, 0, headers, bold)
data.write_row(0, 0, ["KPI"] + list(wide.columns))
for r, (kpi, row) in enumerate(card.iterrows(), start=3):
    ws.write(r, 0, kpi)
    for c, col in enumerate(["Actual", "Target"], start=1):
        ws.write_number(r, c, row[col], num)
    ws.write(r, 3, row["Status"])
    ws.write_number(r, 4, row["vs target"], signed)
    ws.write_number(r, 5, row["vs last month"], signed)
    ws.write_number(r, 6, row["YTD"], num)
    ws.write_number(r, 7, row["YTD target"], num)
    data.write(r - 2, 0, kpi)
    data.write_row(r - 2, 1, list(wide.loc[kpi]))
    ws.add_sparkline(r, 8, {"range": f"Trend!B{r - 1}:G{r - 1}", "markers": True, "last_point": True,
                            "series_color": "#4338CA", "last_color": "#B91C1C"})
last = 3 + len(card) - 1
for status_name, colour in fills.items():
    ws.conditional_format(3, 3, last, 3, {"type": "cell", "criteria": "==", "value": f'"{status_name}"',
                                          "format": book.add_format({"bg_color": colour, "bold": True})})
ws.set_column("A:A", 24); ws.set_column("B:H", 12); ws.set_column("I:I", 20)
ws.set_landscape(); ws.fit_to_pages(1, 1); ws.hide_gridlines(2)
book.close()
print("scorecard rows:", len(card), "| status counts:", card["Status"].value_counts().to_dict())

Status is written as text and coloured by three conditional formats, so a reader who prints in black and white still sees the word, and anyone who sorts or filters keeps the colour attached. The sparklines read from a Trend sheet holding six months per KPI, with the latest point marked in red; they make it obvious that days sales outstanding has climbed two months running, from 39 to 47. fit_to_pages(1, 1) keeps the scorecard on one landscape page when printed or exported to PDF. Sparklines in more depth are covered in Add Sparklines to an Excel Report with XlsxWriter.

Step 5 — Check the colours match the rules

A scorecard with a wrong colour is worse than none. Read the written file back and recompute every status from the definitions:

from openpyxl import load_workbook

ws = load_workbook("kpi_scorecard_2026-09.xlsx")["Scorecard"]
mismatches = []
for r in range(4, 4 + len(card)):
    kpi, actual, target, shown = (ws.cell(r, c).value for c in (1, 2, 3, 4))
    d = defs.loc[kpi]
    expected, _ = status(actual, target, d["Better"], d["Tolerance"])
    if shown != expected:
        mismatches.append((kpi, shown, expected))
print("status mismatches:", mismatches or "none")
print("lower-is-better KPIs:", list(defs.index[defs["Better"] == "lower"]))

openpyxl warns that it does not support the sparkline extension — harmless when only reading, but never save the scorecard with openpyxl, or the sparklines are dropped. The check reads what the reader will see — the values and the status text in the workbook — and recomputes each status from the definitions independently of the code that wrote it. Lower-is-better KPIs are listed explicitly because they are where hand-built scorecards most often go wrong. Running this in the job before the scorecard is distributed catches a broken rule the month it breaks.

Common pitfalls

• Treating every KPI as higher-is-better. Store the direction and flip the comparison.
• Summing percentages for year to date. Average them, ideally weighted.
• Tolerances in absolute units. A fraction of target works across KPIs of any scale.
• Colour without text. Write the status word; colour alone fails in print and for colour-blind readers.
• Rules hidden in code. Keep targets, direction, tolerance and aggregation in a definitions table.

Conclusion

A reliable KPI scorecard starts with a definitions table — target, direction, tolerance and year-to-date rule per KPI — and computes status from a direction-aware performance figure, so lower-is-better KPIs and amber bands are handled the same way every month. Add variance against target and last month, year-to-date figures that sum or average correctly, and six-month sparklines, write it as one landscape page with status text coloured by conditional formatting, and recheck the written statuses before the pack goes out.

Frequently asked questions

How do I calculate red, amber, green status for KPIs in Python? Compute performance as actual minus target over target, flipped for KPIs where lower is better. Zero or more is green, a shortfall within the tolerance is amber, and anything worse is red.

How do I handle KPIs where lower is better? Store the direction in the KPI definitions and compute performance as target minus actual over target for those KPIs, so a positive result always means better than target.

How do I calculate year-to-date for percentage KPIs? Do not sum them. Average the monthly values, or better, compute a weighted average using the underlying volumes, such as margin weighted by revenue.

How do I add trend sparklines to an Excel scorecard? With XlsxWriter, write each KPI's monthly values to a data sheet and call add_sparkline on the scorecard row with the range of those values.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports in Excel.
• Build a Budget vs Actual Variance Report — variance at account level.
• Add Icon Sets with openpyxl — another way to show status.
• Build a Support Ticket SLA Report — the source of service KPIs.