Level 7 · Boss: assemble the monthly report

One sheet, the whole chain

This boss level puts the chapter together on one sheet, the way a real monthly report is built: raw lines on the left, then blocks that each read only the block before them. Aaron will point the finished sheet at the full month; you build and test it on one day.

The day is Wednesday, November 12, 2025, chosen on purpose. It has 49 lines in A2:D50 and 20 orders in the order block G2:I21, with each order’s channel and status. Two of those orders must not count as sales: 11717 was cancelled and 11728 was refunded. If the report gets them right on this day, the status rule works.

Each block feeds one part of the written report from level 1:

report section	where it comes from on this sheet
Objective	Aaron’s ticket: the November numbers, refreshable, by channel
Data and method	The line and order blocks, the revenue formula in E, the status rule in M6
Findings	The channel summary in L1:N4
Executive summary	The KPI block in M9:M11 and the sentence in L13
Limits	One sample day; returns not included; shipping fees out of revenue

Keeping the method in cells (the status rule, the revenue formula) is what makes the data and method section honest: anyone can click a number and see how it was made.

Step 1: line totals

E2   =B2*C2*(1-D2/100)

Filled down to E50. The same definition as everywhere in this chapter and in the SQL case: quantity × price × (1 − discount ÷ 100), with no shipping.

Step 2: order totals

J2   =SUMIF($A$2:$A$50,G2,$E$2:$E$50)

Filled down to J21. Check: SUM(J2:J21) and SUM(E2:E50) both give 12,408.99.

Step 3: the channel summary, completed orders only

Two criteria per cell: the channel in column L and the status rule in M6.

M2   =SUMIFS($J$2:$J$21,$H$2:$H$21,L2,$I$2:$I$21,$M$6)
N2   =COUNTIFS($H$2:$H$21,L2,$I$2:$I$21,$M$6)

Both filled down to row 4. COUNTIFS counts orders because it runs on the order block, where each row is one order. Counted on the line block, the day would have 49 of them instead of 20.

Step 4: KPIs and the sentence

M9   =SUM(M2:M4)
M10  =SUM(N2:N4)
M11  =M9/M10
L13  =TEXT(M7,"mmm d")&" sample: "&TEXT(M9,"$#,##0")&" net revenue from "&M10&" orders, AOV "&TEXT(M11,"$#,##0.00")&"."

L13 reads: Nov 12 sample: $10,065 net revenue from 18 orders, AOV $559.17.

Thinking like an analyst

Reconcile the day before you trust it. All 20 orders add up to 12,408.99, and the report shows 10,065.00. The difference, 2,343.99, must be exactly the orders the status rule leaves out: 1,609.00 for cancelled order 11717 plus 734.99 for refunded order 11728. It is, to the cent, so the filter does what it should and nothing else.

Then compare with a source you already trust. In level 6’s daily table, November 12 shows 18 completed orders and 10,065.00 of net revenue, the same as this sheet. That daily table is built on the definition the SQL holiday case uses, so the template is ready for Aaron to point at the full month, where it will return 651 orders and $292,364.40.

Finally, read the result. The day’s AOV, $559.17, is far above the month’s $449.10, and one order, 11715 at 4,741.96, is 47% of the day’s revenue. A one-day number is fine for testing a template; it is not a trend.

Common mistakes

• Summing the line block by channel: the lines don’t carry a channel or a status, so there is nothing to filter on. Roll up to orders first.
• Counting lines as orders: 49 lines, 20 orders.
• Typing "completed" into each formula instead of pointing at M6.
• Using AVERAGE(J2:J21) for AOV: 620.45, because it includes the cancelled and refunded orders.