Reconcile a monthly sales report in three checks (with a small Python function)

I'm a member of the Duaer team. This post is for whoever builds the month-end sales report from raw exports and wants checks that fail loudly before a reader finds the problem.

The first draft is produced by an AI digital employee; it only counts as delivered once you have accepted it. The method below is generic and works without us. All figures and rows are illustrative toy data, not real numbers.

The pattern: write the definitions down, keep the detail clean, and run three reconciliations (rows balance, totals match a baseline, groupings add back to the headline). The spreadsheet version comes first; a short Python function that runs the same three checks is at the end.

What you need first

Inputs

• This month's raw exports, saved unchanged. If there are several files, combine them into one detail sheet first, with a source_file column on every row.
• Last month's delivered report. It sets the column order, the definitions and the naming, and this month should match it.
• A reconciliation baseline: the order system's own total order count and total sales for the month, or finance's monthly summary. Without a baseline you cannot show that nothing is missing or doubled.

Outputs

An Excel workbook with at least five sheets:

• Summary: the handful of numbers the reader wants, placed first.
• Detail: the cleaned order lines, one per row.
• Analysis: groupings by channel, category and day.
• Reconciliation: the three checks and their results.
• Basis: how every number in the report was defined.

Structure the report

Decide which questions the report must answer before choosing what goes in. An illustrative layout:

Section	Content	Source
Headline	orders, sales, units, average sales per order	totals from Detail
By channel	orders, sales, share per channel	Detail grouped by channel
By category	units, sales, share per category	Detail grouped by category
By day	orders and sales per day	Detail grouped by date
Comparison	difference from last month on the same basis	last month's report

Every block should be computed by formula from Detail, not typed. Then a change to the detail flows through to the summary, and your checks have something firm to test.

The workflow

Step 1: Tidy the detail. Illustrative target columns: order_id, order_date, channel, category, product_id, quantity, sales, status. Dates in yyyy-mm-dd, sales as two-decimal numbers with no symbols, and order IDs treated as text so long numbers are not turned into scientific notation.

Step 2: Decide which rows belong. This is a definition question, and it has to be answered and written down before you clean anything. Typical decisions:

• Do cancelled orders count?
• For orders that were returned and need reversing: is the reversal applied to the original month, or shown as a negative in the month the return happened?
• Are orders assigned to a month by order date or by ship date?
• Are test and internal orders excluded?

Write the answers on Basis and then proceed. A definition that lives only in someone's head will change the first time someone else prepares the report.

Step 3: Duplicates and exceptions. Write the rules first: duplicates are identified by order ID (or order ID plus product ID), and the rule says which one is kept. Rows with unreadable dates, non-numeric quantities or sales, or missing required fields are not quietly patched. Move the whole row to an Exceptions sheet and keep its source.

Step 4: Summary formulas. With Detail holding order ID in column A, channel in C and sales in G:

• Total sales: =SUM(Detail!G:G)
• Order count: first check whether an order is one row or several. If several, count unique order IDs, with a helper column =IF(MATCH(A2,A:A,0)=ROW(),1,0) and sum it.
• By channel: =SUMIFS(Detail!G:G,Detail!C:C,channel_name)

Step 5: Reconciliation step one, row counts. The sum of data rows across the raw files must equal:

rows in Detail + rows in Exceptions + rows excluded by rule

On Reconciliation, write =IF(raw_rows=detail+exceptions+excluded,"pass","fail"). Excluded rows are recorded with a reason (for example "cancelled") and a count, not simply dropped.

Step 6: Reconciliation step two, totals against the baseline. Compare your order count and sales total with the baseline and compute the differences. Illustrative layout:

Item	Report (illustrative)	Baseline (illustrative)	Difference
Orders	1000	1000	0
Sales	50000.00	50000.00	0.00

Zero is the happy outcome. If it is not zero, resist suspecting your formulas first and compare definitions: does the baseline include cancelled orders? Reversals? The same date basis? Each difference should either be explained line by line ("baseline includes N cancelled orders worth X") or labelled "unexplained". Never force it to balance.

Step 7: Reconciliation step three, groups add up to the total. The channel sales in Analysis must sum to the headline total, and likewise for categories and days: =IF(ROUND(SUM(channel_sales)-total_sales,2)=0,"pass","fail"). When a grouping falls short, the cause is almost always a blank or inconsistently spelled value ("online" versus "online "), so look at the "other / blank" line first.

Step 8: Compare with last month. Recompute last month on the same definitions and compare. If one channel moved sharply, first confirm that neither a definition nor a source file changed, and only then describe it as a business movement.

Step 9: Write the basis note. The template is in the next section.

Step 10: Spot-check. Choose five to ten order IDs at random, find each in the raw files, compare date, channel, category, quantity and sales, and confirm the row landed correctly in the report. Log what you checked.

A basis note template

Illustrative layout for the Basis sheet, updated each month:

Item	Statement (illustrative)
Period	1 to 30 September 2026, assigned by order date
Sources	N order exports, file names listed on Notes
Order scope	Completed orders included; cancelled orders and internal test orders excluded
Returns	Returned orders appear as negatives in the month of the return
Meaning of "sales"	Order total as paid, excluding shipping (illustrative)
Meaning of "orders"	Count of unique order IDs
Channel mapping	As recorded on the order; blanks go to "Unassigned"
Baseline	The order system's monthly summary
Reconciliation result	Order difference, sales difference, explained and unexplained parts
Change from last month	Any change in definitions (write "none" if none)

The test is simple: a reader should know how each number was produced without having to ask you.

Hand-over checklist (yes or no)

• [ ] Are the raw files kept unchanged, and does every detail row carry source_file?
• [ ] Are the definitions (which orders count, how returns are treated, which date sets the month) written on Basis?
• [ ] Do raw rows equal detail rows plus exception rows plus excluded rows?
• [ ] Were both the order count and the sales total compared with the baseline?
• [ ] Is every difference explained, or explicitly labelled unexplained?
• [ ] Do the channel, category and day groupings each add up to the total?
• [ ] Are all summary figures formulas rather than typed values?
• [ ] Is there one date format and one number format?
• [ ] Are the definitions the same as last month, or are the changes stated?
• [ ] Were at least five order IDs spot-checked and the findings logged?

Common pitfalls, and the checklist line that prevents each

1. Definitions that live in your head. Cause: you did it last month; someone else does it this month. Fix: every definition is written on Basis, with a comparison to last month.

2. Counting rows as orders. Cause: when an order spans several lines, rows are not orders. Fix: "orders are counted as unique order IDs", plus a hand-check on a few multi-line orders.

3. The total matches but the groups do not. Cause: blanks, trailing spaces or inconsistent labels in channel or category. Fix: keep "groups add up to the total" on the list, and look at the blank/other line first.

4. Adjusting numbers until they balance. Cause: a difference feels uncomfortable. Fix: the checklist says each difference is either explained or marked unexplained, and estimates are not allowed to fill the gap.

5. Month assignment that changes between people. Cause: one person uses order date, another ship date. Fix: put the date basis in the first row of Basis and confirm it on the checklist.

6. A typed summary. Cause: it was quicker. Fix: require that every summary figure comes from a formula, and as a test, change one detail row and see whether the summary moves.

The same three checks as a Python function

If your detail ends up in a CSV or a list of dicts, the three reconciliations fit in one function. This is a starting point, not a finished tool. I ran it on the illustrative rows below:

from collections import defaultdict

def reconcile(raw_rows, detail, n_exceptions, excluded, baseline_orders, baseline_sales):
    """Three month-end checks. detail: list of dicts with order_id, channel, sales."""
    # Check 1: rows in = rows kept + rows set aside + rows excluded by rule
    step1 = raw_rows == len(detail) + n_exceptions + sum(excluded.values())
    # Check 2: compare with the baseline; report the gap, never force it to zero
    orders = len({r["order_id"] for r in detail})  # unique order IDs, not rows
    sales = round(sum(float(r["sales"]) for r in detail), 2)
    diff_orders = orders - baseline_orders
    diff_sales = round(sales - baseline_sales, 2)
    # Check 3: every grouping adds back to the headline total
    by_channel = defaultdict(float)
    for r in detail:
        by_channel[r["channel"].strip() or "Unassigned"] += float(r["sales"])
    step3 = round(sum(by_channel.values()) - sales, 2) == 0
    print("check 1 rows   :", "pass" if step1 else "FAIL")
    print("check 2 orders :", orders, "diff", diff_orders, "| sales:", sales, "diff", diff_sales)
    print("check 3 groups :", "pass" if step3 else "FAIL", dict(by_channel))

# Illustrative toy data, not real figures
detail = [
    {"order_id": "A001", "channel": "online",  "sales": "20.00"},
    {"order_id": "A001", "channel": "online",  "sales": "5.00"},   # 2nd line of the same order
    {"order_id": "A002", "channel": "online ", "sales": "5.00"},   # trailing space
    {"order_id": "A003", "channel": "store",   "sales": "10.00"},
    {"order_id": "A004", "channel": "",        "sales": "8.00"},   # blank channel
]
reconcile(raw_rows=7, detail=detail, n_exceptions=1, excluded={"cancelled": 1},
          baseline_orders=4, baseline_sales=48.00)

Output on those toy rows:

check 1 rows   : pass
check 2 orders : 4 diff 0 | sales: 48.0 diff 0.0
check 3 groups : pass {'online': 30.0, 'store': 10.0, 'Unassigned': 8.0}

Design notes:

• Check 2 prints the difference instead of asserting zero. A nonzero difference should be explained or labelled unexplained, not hidden.
• Channel labels are stripped before grouping, so "online " and "online" land in one group, and blanks go to Unassigned. Check 3 passes either way, so eyeball the group names too.
• Orders are counted as unique order IDs because order A001 spans two rows.
• The function cannot know your definitions (cancelled orders, returns, date basis). Those belong on the Basis sheet, written by a person.

After you finish

Next month: new exports in, Basis reviewed, same three checks run. Duaer's published scope includes merging, filtering, grouping and exporting tables, and batch-processing text-selectable PDFs (where you can select the text with a mouse) and Word files into tables.

If you'd rather have someone do it and hand back a result you can check, get in touch: https://duaer.com/?utm_source=devto&utm_medium=content&utm_campaign=ai-landing&utm_content=d05-devto-en

Comments

[Dev]

One check I would add to the three: reconcile count and sum independently, and reconcile the distinct key count too. A duplicated row plus a dropped row can leave the total exactly right while the detail is wrong, so "totals match the baseline" can pass on a broken export. If order count, distinct order_id count and sales sum each have to reconcile on their own, that offsetting pair gets caught.

The other one worth stating: run the checks on the same detail the summary is computed from, not on a re-import. If the summary comes from one query and the reconciliation re-reads the source with different casting, they can disagree for reasons that have nothing to do with the data.