# Northline Workshops — October 2026 reconciliation

## Basis and status

All figures are CAD, tax excluded, for October 1–31, 2026 inclusive. Paid order gross less qualifying October-settled refunds is net revenue; contribution after advertising is net revenue less advertising spend. This is **not business profit**: COGS, payment fees, salaries and overhead are not supplied. These rules come from `inputs/policy.md`, **Northline Workshops accounting note — October 2026**.

**Course and combined net revenue, contribution and revenue per paid order are provisional.** Settled refund RF05 has no amount (`inputs/refunds.csv`, R06), although it links to paid Course order N1011 (`inputs/sales.csv`, S13). Its amount remains blank, not zero, and is excluded from calculable refunds. RF04 for 120.00 also remains excluded because N9999 cannot be matched to a paid order (`inputs/refunds.csv`, R05; `inputs/sales.csv`, S01–S15). Neither issue has been resolved or verified externally.

## October results

| Metric | Workshop | Course | Combined |
|---|---:|---:|---:|
| Paid order count | 5 | 4 | 9 |
| Gross sales | 1,920.00 | 900.00 | 2,820.00 |
| Applied refunds (known, eligible amounts) | 240.00 | 60.00 | 300.00 |
| Net revenue | 1,680.00 | 840.00* | 2,520.00* |
| Advertising spend | 300.00 | 150.00 | 450.00 |
| Contribution after advertising | 1,380.00 | 690.00* | 2,070.00* |
| Net revenue per paid order | 336.00 | 210.00* | 280.00* |

\* Provisional because RF05 is missing its amount. Workshop has no known missing-amount refund in the supplied records; the unmatched RF04 has no established product allocation.

Calculation sources:
- Workshop paid sales: `inputs/sales.csv`, S01, S03, S06, S12, S14; Course paid sales: S02, S07, S10, S13.
- Applied refunds: `inputs/refunds.csv`, R01 (Workshop, 240.00) and R02 (Course, 60.00), linked by order ID to the sales records.
- Workshop advertising: `inputs/ads.csv`, A01, A03, A06; Course advertising: A02, A05.

Formulas: net revenue = gross sales − applied refunds; contribution = net revenue − ad spend; net revenue per paid order = net revenue ÷ included paid order count. Refunds do not remove the original order from that count. Combined per-order revenue is 2,520.00 ÷ 9 = 280.00, not the unweighted average of the product figures. If RF05 is confirmed at amount **x**, with no other changes, Course net revenue becomes 840.00 − x and combined net revenue 2,520.00 − x; contribution decreases by x as well. RF04 requires separate verification and product attribution before any adjustment.

## Record and amount reconciliation

All raw rows are preserved in the cleaned files; `included_in_october=yes` identifies rows used in calculations. Excluded rows remain for auditability and have one primary exclusion reason in `exceptions.csv`. The amounts below are raw-row control sums, **not** revenue or spend before adjustment: they contain duplicates and ineligible records.

| Source | Raw rows | Included rows | Excluded rows | Known raw amount | Included amount | Known excluded amount |
|---|---:|---:|---:|---:|---:|---:|
| `inputs/sales.csv`, S01–S15 | 15 | 9 | 6 | 4,140.00 | 2,820.00 | 1,320.00 |
| `inputs/refunds.csv`, R01–R07 | 7 | 2 | 5 | 960.00 + one unknown | 300.00 | 660.00 + one unknown |
| `inputs/ads.csv`, A01–A07 | 7 | 5 | 2 | 630.00 | 450.00 | 180.00 |

Sales exclusions (`inputs/sales.csv`):
- S04: duplicate of S03, 480.00.
- S09: superseded N1008 record, 120.00. S10 is retained at 180.00 because its `updated_at` is October 16 at 09:00, later than S09's October 15 at 12:00.
- S05: pending, 120.00; S08: cancelled, 240.00.
- S11: September order, 240.00; S15: November order, 120.00.
- Control: 4,140.00 − 480.00 − 120.00 − 120.00 − 240.00 − 240.00 − 120.00 = **2,820.00**. After removing duplicate and superseded rows, 13 unique order records remain; four are excluded for status or period, leaving nine paid October orders.

Refund exclusions (`inputs/refunds.csv`):
- R03: duplicate RF02, 60.00; retain R02.
- R04: pending RF03, 240.00; its populated settlement-date field does not override pending status.
- R05: unmatched N9999, 120.00; no valid paid order established.
- R06: RF05 has an unknown amount; cannot calculate or test its refund cap.
- R07: settled November 2, 240.00; excluded from October even though N1012 was paid in October (`inputs/sales.csv`, S14).
- Known-amount control: 960.00 − 60.00 − 240.00 − 120.00 − 240.00 = **300.00**, with the separate missing amount unresolved. Six unique refund IDs remain after deduplication; only two are calculably eligible in October.

Advertising exclusions (`inputs/ads.csv`):
- A04: duplicate AD03, 100.00; retain A03.
- A07: November 1 spend, 80.00.
- Control: 630.00 − 100.00 − 80.00 = **450.00**. Six unique ad IDs remain after deduplication, five in October.

## Cleaning decisions and assumptions

Applied policy: `inputs/policy.md`, **Northline Workshops accounting note — October 2026**.

- Source row IDs are unchanged. Duplicate comparisons ignore `row_id`, which is a source locator: otherwise identical business records have different source row IDs. The first occurrence is retained for identical duplicates. The latest timestamp determines the conflicting order version.
- All valid calendar-date fields are normalized to YYYY-MM-DD; slash dates are DD/MM/YYYY. `updated_at` remains a full ISO timestamp because truncating its time would lose version-selection evidence.
- Known money values are formatted to two decimals after removing `CAD` or `$`. The missing refund amount remains blank. Product names are trimmed and mapped case-insensitively to Workshop or Course; channels are trimmed and lowercased.
- Refund product is derived from the retained order record, not guessed from refund amount. R05's product is blank because its order is unresolved. All supplied sales and ad products map to the two allowed products.
- No refund cap breach was found among deduplicated, settled, known-amount refunds with matched paid orders, including the November settlement. This does **not** establish compliance for missing-amount R06 or unmatched R05.
- Cleaned files contain normalized audit rows, not only included transactions. To reproduce totals, filter on `included_in_october=yes`; do not sum whole files. Blank source amounts and unresolved derived products denote unknown/not established values, not zero. Blank `exclusion_code` on included rows means no exclusion applies; blank `related_row_id` means no cross-reference was supplied, not a missing monetary value. `related_row_id` points to a retained duplicate/version or a relevant linked sales record where useful.

## Practical recommendation

First obtain the settled amount for RF05 and establish whether RF04 is a valid refund against a paid order. Do not finalize Course or combined net results until those exceptions are resolved. Retain settlement evidence and rerun the reconciliation after corrections; no corrections to missing source facts have been made here.

Workshop currently contributes 1,380.00 after advertising versus Course's provisional 690.00, using the cited included records. This supports monitoring Workshop as the larger contributor, but does not demonstrate incremental ad effectiveness or justify scaling advertising on its own. Maintain a cautious budget while resolving refunds and collecting product-level delivery costs, payment fees and other expenses. Use those costs before drawing conclusions about business profitability.

## Deliverables, checks and handoff

Files:
- `output/cleaned-sales.csv`
- `output/cleaned-refunds.csv`
- `output/cleaned-ads.csv`
- `output/exceptions.csv` — 13 excluded source rows and explanations
- `output/summary.md`

Checks actually performed with Python's standard library: duplicate business-field equality; latest timestamp for N1008; product and combined metric assertions; row-count and known-money reconciliations; included-ID uniqueness; included dates in October; CSV read-back with all source row IDs preserved; normalized dates and two-decimal money; missing refund amount and unmatched product kept blank; sales/ad product mapping; known linked settled refunds within paid gross; and unchanged SHA-256 hashes of the four accounting input files during processing. All passed. The calculation/check script is retained at `work/reconcile.py` outside the deliverables directory.

Remaining limitations: RF05's amount and RF04's valid order/product linkage require evidence not supplied. No bank, settlement-system or external accounting verification was available or performed. Costs needed to determine business profit are absent. **Estimated human follow-up: 15–25 minutes once the missing settlement and order evidence is available**, excluding time waiting for that evidence; this is an estimate, not measured time.

## Independent submission review

The original five deliverables and calculation script were preserved byte-for-byte under `original-submission/`, with SHA-256 hashes in `original-submission/manifest.json`, before review changes. No accounting or CSV correction was needed. All seven metrics for all three reporting columns were independently recomputed from raw inputs and agree with the table above. Every normalized source field, all 29 source row IDs, all 13 exclusions and the reconciliation table were also checked. The four CSV deliverables remain byte-identical to the original submission.

The only revision to the accounting explanation clarifies blank metadata fields versus genuinely unknown source values. Review details are in `output/change-log.md`; executed check results are in `output/review-checks.txt`, produced by `work/review.py`. Tests were local and did not obtain missing settlement or order evidence. The unresolved items and follow-up estimate above remain unchanged.
