The idea
A monthly report looks like a page of results, but it is a chain. At the top there is a question someone asked. At the bottom there are a few numbers a manager reads in ten seconds. Between them, every step takes the output of the step before it, and each one can be checked against rows of data.
The chain protects you. When a number is wrong, you do not stare at the final figure and guess. You walk back one link at a time: is the sentence quoting the KPI block correctly, does the KPI block use the right cells, do those cells add up the right rows, is the question defined clearly enough that "right rows" means something?
Figure 1
From a vague request to the one sentence a manager reads
- Vague request "How did November go?"
- SMART objective what, which month, which rows
-
SUMIForder totals from lines -
SUMIFSrevenue and orders per channel -
COUNTIFSreturns and return rates -
TEXT(…) & …KPI block, one sentence
- "How did November go?" cannot be answered with a formula. It does not say how success is measured, over which rows, or against what.
- A SMART objective fixes that: net revenue and orders of completed November orders, by channel, shipping excluded. Now every number has a definition, and anyone can check it.
SUMIFrolls the order lines up into order totals. Lines are the data, orders are what the business counts.SUMIFSsplits revenue and orders by channel, for completed orders only, in November only. Here every criterion is one condition of the objective.COUNTIFScounts returns per category and divides by lines sold. A rate is built from two counts, never from other rates.- The KPI block gathers the results, and
TEXTand&turn them into the one sentence a manager reads. Every figure in it can be clicked back to the rows behind it.
Why it exists
A spreadsheet that gets copied forward every month collects errors. A criterion typed as a constant, a range that stops one row short, a total pasted as a value: each is invisible when you glance at the sheet and each shifts the number a little. By the third month nobody knows where figures come from.
Two habits keep a report reliable. First, define the numbers before you calculate them. "Revenue" could mean gross or net, with or without shipping, completed orders or all of them. The objective states the definition once, and the formulas follow it. Second, build every figure with a formula that points at the data, so the sheet recalculates when the rows change and any figure can be traced with Trace Precedents (Formulas → Trace Precedents).
How it works under the hood
Start with a SMART objective
SMART means Specific, Measurable, Achievable, Relevant, Time-bound. For a report, the practical test is simple: could two analysts, working alone, produce the same number? "Show how sales did" fails. "Net line revenue and number of orders per channel for completed November 2025 orders, shipping excluded, compared with October" passes. Write the sentence on the sheet, because it is also the footnote that explains the numbers.
Build from lines up
Order lines are the rows; orders and channels are what the business talks about. SUMIF adds the
lines of one order to get its total. SUMIFS adds the lines of one channel and one month, and
every extra criterion is another condition from the objective: completed only, this month only.
COUNTIFS counts rows that meet conditions, which is how you get the number of orders or returns.
Lock the ranges ($B$2:$B$60) so a formula filled down still points at the same data, and keep a
single criteria cell for the month instead of typing the dates into every formula.
Check that the parts add up
Every breakdown in the report has a total, and the two must agree. Revenue by channel is 118,601.50
for the app, 57,032.54 for the marketplace and 116,730.00 for the web. They add up to 292,364.04,
and a separate SUMIFS over the whole month must give the same figure. When it does not, you
learn about a missing channel, a typo in a channel name, or a date filter that cuts the month short,
and you learn it before the manager does.
Ratios come from totals
Most KPIs are one total divided by another: AOV is revenue divided by orders, the return rate is returns divided by lines sold. A report usually shows them by channel or category, and next to them sits a tempting overall figure: the average of the rows above it. That figure is almost always wrong.
Figure 2
Average order value: total first, then divide
| channel | revenue | orders | aov |
|---|---|---|---|
| app | 118601.50 | 294 | 403.40 |
| marketplace | 57032.54 | 113 | 504.71 |
| web | 116730.00 | 244 | 478.40 |
| aov |
|---|
| 462.17 |
| revenue | orders | aov |
|---|---|---|
| 292364.04 | 651 | 449.10 |
- Three channels, each with its own revenue, order count and average order value (AOV): revenue divided by orders.
- The tempting shortcut: average the three AOVs. 403.40, 504.71 and 478.40 give 462.17.
- The correct figure adds revenue and orders first: 292,364.04 ÷ 651 = 449.10. Marketplace's high AOV counts for much less, because it has only 113 of the 651 orders.
- The app has the most orders (294) and the lowest AOV. A channel's weight in the total is its order count, and an average of averages gives every channel the same weight.
The rule: sum the numerators, sum the denominators, then divide once. The same applies to return rates, where the overall rate is not the mean of the category rates.
Figure 3
Return rate by category, November 2025
- Each bar is returns divided by lines sold in that category. Textiles stands out at 5.30%: 21 returns on 396 lines.
- The overall rate is 59 returns on 1,600 lines, 3.69%. It is not the mean of the six bars (3.40%), because categories with more lines sold count more.
In Excel that is =SUM(revenue)/SUM(orders) for the total row, never =AVERAGE(aov). Where the
rows have different weights, a plain average gives the smallest group the same say as the largest.
The sentence
The last link is words. A KPI block with five numbers needs one sentence that says what they mean:
revenue, orders and AOV in a line that a person can read. TEXT formats a number for the sentence
(TEXT(B2,"#,##0")) and & joins the pieces, so the sentence updates with the data. The
commentary beside it, what changed and why you think so, is the part only the analyst can write.
Common misconceptions
"The average of the channel averages is the overall average"
It is correct only when every channel has the same number of orders. Otherwise it overweights the small channels: 462.17 against the true 449.10 above. When you see an average of averages in a report, rebuild the figure from the totals. A quick test: the overall figure must sit between the extremes and lean towards the value of the largest group. Here it sits close to the app and web, which hold most of the orders.
"If the numbers look plausible, the report is right"
Plausible is how wrong numbers survive. A channel with a misspelled name drops out of the breakdown and nobody notices, because the total still looks like a revenue figure. Reconcile instead: the breakdown must add to the total, and the total must match a figure computed another way (for instance, the sum of the order totals).
"A request like 'how are sales going?' is already a clear objective"
It says nothing about which sales, which period or compared with what. If you start building without a definition, you will make the choices silently, and the next person to open the report will make different ones. A few minutes spent on a SMART objective settles those choices once, in writing, before the first formula.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Right. Combined revenue is 116,730.00 + 118,601.50 = 235,331.50 over 538 orders: 437.43. The app has more orders, so it pulls the result towards 403.40.
Not quite. Averaging the two figures treats both channels as equally large. The app has 294 orders against 244, so it should count for more.Try another answer.
Not quite. A combined average can never exceed the larger of the values it combines, and 881.80 is far above both. AOVs are not meant to be added.Try another answer.
Not quite. The combined figure must lie between the two channel values, and it is pulled towards the channel with more orders, which is the app.Try another answer.
Lesson done: it is ticked on the map.