KPI Tracking Template (Excel and Google Sheets)
By Newtown Analytics · · 4 min read
For smb owners & operators
A KPI template gives a team one place to define its measures, enter each month's actuals and see at a glance whether it is on track. This page describes a simple KPI tracking workbook you can build yourself in Excel or Google Sheets in about an hour: the tabs, the columns, the formulas and a worked example with checked arithmetic. We show the layout inline and do not offer a downloadable file here. The template supports the practice described in business analytics for finance and operations, applies the choices from how to choose KPIs and gives a working form to KPI scorecards. Once it is in use, the next steps are KPI examples by department and the balanced scorecard. It also supports the monthly cycle in budgeting and forecasting.
Tabs
| Tab | Purpose |
|---|---|
| 1. Definitions | One row per KPI: name, owner, formula, source, direction, target, thresholds |
| 2. Data | Monthly actual values for each KPI, one column per month |
| 3. Scorecard | Latest month vs target, with variance, status and trend |
| 4. Trends | Charts of the last 12 months for the main KPIs |
| 5. Actions | Log of issues, owners, actions and due dates |
| 6. Checks | Tests for missing data and invalid values |
The Definitions tab
| Column | Content | Example |
|---|---|---|
| A: KPI | Short name | On-time in-full |
| B: Owner | Named person | Head of supply planning |
| C: Formula | Plain-language definition | Order lines shipped complete on promised date / lines due |
| D: Source | System or report | Order management system |
| E: Direction | "Higher" or "Lower" is better | Higher |
| F: Target | Number for the period | 96% |
| G: Amber floor | Share of target for amber | 95% |
| H: Unit | %, $, days | % |
| I: Reviewed | Date of last definition check |
Keep the definitions tab short and visible. A KPI without a written definition is a source of arguments.
The Data tab
Columns are the KPI name in column A and one column per month from B onward. Enter actuals only, never formulas that hide where a number came from. Add a column at the end for the date the data was last updated. Use a consistent unit in each row.
The Scorecard tab
| Column | Formula idea |
|---|---|
| KPI | =Definitions!A2 |
| Owner | =Definitions!B2 |
| Target | =Definitions!F2 |
| Actual (latest month) | =INDEX(Data!B2:M2, MATCH(ReportMonth, Data!$B$1:$M$1, 0)) |
| Variance | =D2-C2 |
| Actual as % of target | =IF(Definitions!E2="Higher", D2/C2, C2/D2) |
| Status | =IF(F2>=1,"Green",IF(F2>=Definitions!G2,"Amber","Red")) |
| Comment | Typed by the owner |
For lower-is-better KPIs the formula inverts the ratio, so a value above target gives a ratio below 1 and the same status logic works for both directions. A cell named ReportMonth lets the user pick the month from a drop-down list, and every row updates. Apply conditional formatting to the status column to color cells green, amber and red.
A worked example
Suppose the report month shows these results (hypothetical):
| KPI | Direction | Target | Actual | Ratio | Status |
|---|---|---|---|---|---|
| Net revenue ($M) | Higher | 11.5 | 12.0 | 1.043 | Green |
| On-time in-full | Higher | 96.0% | 94.2% | 0.981 | Amber |
| Inventory turns | Higher | 4.5 | 4.1 | 0.911 | Red |
| Days sales outstanding | Lower | 45 | 49 | 0.918 | Red |
Check each ratio: 12.0 / 11.5 = 1.043; 94.2 / 96.0 = 0.981; 4.1 / 4.5 = 0.911. For the lower-is-better case the formula is target divided by actual: 45 / 49 = 0.918. With an amber floor of 95 percent (0.95), the results are green (at or above 1), amber (between 0.95 and 1) and red (below 0.95). This is the same logic as the example on the KPI scorecard page, expressed in cell formulas.
The Actions tab
Add a table with columns for date raised, KPI, issue, root cause, action, owner, due date, status and result. Link each red KPI to at least one action. At each review, start with open actions.
The Checks tab
- A count of KPIs with no data for the report month.
- A flag for values that are negative or outside a plausible range.
- A check that every KPI has an owner, a target and a direction.
- A check that the report month matches a column in the Data tab.
Show "OK" or "Check" at the top of the Scorecard tab so users see problems before they present.
How to use it
- Fill the Definitions tab with the six to twelve KPIs from your chosen set.
- Load the last 12 months of data to establish trends.
- Each month, enter actuals by a fixed day, update the report month, and review the Scorecard tab.
- Ask owners to add comments to every amber and red item.
- Log actions, and review them at the next meeting.
- Once a year, review the KPIs and targets.
Extending the template
You can add a column for the same month last year, a rolling 12-month average, a sparkline for each KPI, a department filter, or a second scorecard that rolls up to company level. When the number of KPIs and users grows, move the data into a database and show the scorecard in a business intelligence tool. See KPI dashboard template.
Want a working version?
If you would like a KPI workbook or dashboard built around your own measures and data sources, with automated refresh from your systems, contact us. We build analytics and planning tools of this kind as part of our Analytics and Planning Automation work.
Keep reading
This article is for general information. It is not accounting, tax, legal or investment advice, and examples use illustrative numbers rather than client data. Confirm decisions that carry financial or legal consequences with a qualified professional.