In this guide 6 sections
A KPI definition sheet holds one row per metric. It fixes the name, formula, source field, inclusions, exclusions, date basis, owner and refresh time, and you fill it in before anyone builds a dashboard. Sales and accounts then sign it. The argument ends.
Why one number turns into three
Sales says the month closed strong. Accounts says it did not. Both read real data, and the gap sits in small choices nobody wrote down, such as whether a sale counts at order, at dispatch or at invoice.
A definition sheet puts those choices on paper. It takes an hour per metric. Most of that hour goes on the sales head and the accounts head disagreeing out loud, in one room, before the review meeting rather than during it.
Start with the five or six figures the owner asks about every week, and leave the long tail of fifty charts for later. That tail can wait.
- Order date, dispatch date or invoice date as the basis
- Credit notes netted off or shown separately
- Tax included or excluded in the figure
- Cancelled and returned lines in or out
- Which branch or entity the row belongs to
The columns every row needs
Nine columns cover nearly every metric: name, plain-words meaning, formula, source field, inclusions, exclusions, date basis, owner and refresh time, in that order from left to right. Never drop the meaning column. A new joiner reads that column first, long before they dare to open the formula column or ask a colleague what a figure includes.
The source field is the part people skip. Name the table and column, such as invoice_header.net_amount, rather than writing “sales”, so a developer can build from the sheet without phoning anyone for clarification.
Give each row one owner. Pick a person, not a department. When the net sales figure looks wrong on Monday morning, that person answers, and nobody forms a committee to discuss it over a long call.
Three rows filled in as examples
Net sales is the sum of invoice net amount, dated by invoice date, excluding tax and freight, less credit notes dated in the same month. The accounts head owns it. It refreshes nightly after the posting run.
Receivables over 60 days is the open invoice balance where today minus due date exceeds sixty, leaving out disputed invoices tagged by the credit controller. The credit controller owns it. It refreshes each morning at 7.
Stock cover in days is closing quantity on hand divided by average daily issues over the last thirty days, per item code, excluding items marked obsolete. The stores manager owns that one.
- Write down the exclusion that surprised you most
- Show one worked example using real invoice numbers
- State the unit and the rounding rule
Getting the sign-off
Do not circulate the sheet and wait. Seat the two people who disagree in one room, with the sheet on screen and the live report beside it, and walk one closed month through each formula.
Differences surface within minutes: a late credit note, a branch transfer booked as a sale, or a manual journal posted on the last night of the month. Write each one into the exclusions column, then move to the next row, and do not reopen a settled rule unless someone brings a new month of evidence.
Record the signing date and both names. Keep it plain. When a rule changes later, add a new version row and never overwrite the old one, because earlier reports then lose the explanation for their figures.
Keeping the sheet alive
A definition sheet rots when nobody owns it. Name one keeper, usually whoever runs reporting, and review the sheet each quarter or whenever a new product line, price list or branch is added.
Hand the finished sheet to whoever builds your dashboards and ask them to show each metric’s rule in a tooltip or footer. Readers see the rule without opening a file.
This week, pick your three most argued-over numbers and draft their rows before the next review meeting, using the three example rows above as your model. Then book the signing session.
KPI definition sheet template
A wide grid with ten columns: metric name, plain-words meaning, formula, source table and field, inclusions, exclusions, date basis, owner, refresh time and version date. Three example rows are filled in. They cover net sales, receivables over 60 days and stock cover, each showing its owner by name, plus a nightly or morning refresh time. A signature block at the foot holds the sales head and the accounts head, with the date they agreed and a line for the version.
Open a blank worksheet to printQuestions owners ask
How many KPIs should the first sheet cover?
Five to eight rows is enough. Pick the figures the owner asks about every week and the ones behind the last argument, then add rows only after these are signed. Keep the first sheet small.
Who should own a KPI?
One named person, always. Pick someone who can explain a movement in the figure, because a department makes a poor owner when nobody answers for a number that looks wrong. The sheet records the name beside the metric.
Can a spreadsheet hold the definitions?
Yes, at the start. A shared spreadsheet with locked formula and source columns works well while the list is short and only a handful of people edit it. Later, move it into the reporting database, so each definition travels with the data it describes and cannot drift away from it.
What if two departments still disagree after the meeting?
Keep both as separate metrics with separate names, such as booked sales and invoiced sales, each with its own row, owner and formula on the sheet. Two clearly labelled figures cause fewer fights than one figure that hides a rule only one team knows, and everyone can see which number answers which question.