# Northline Workshops — October 2026 reconciliation

## Basis and result

Period: October 1–31, 2026, inclusive. All amounts below are CAD, tax excluded. Revenue uses paid order gross less eligible refunds settled in October; advertising uses October ad dates. These rules, the unique-ID rules and the contribution definition come from `inputs/policy.md`, **Northline Workshops accounting note — October 2026**.

**Calculable net revenue is $2,520.00 and contribution after advertising is $2,070.00. These are provisional, not business profit.** Course refund RF05 has an unknown amount; refund RF04 cannot be matched to an order. Neither is silently treated as a valid zero-dollar refund. See the unresolved items below.

| Metric | Workshop | Course | Combined |
|---|---:|---:|---:|
| Paid order count | 5 | 4 | 9 |
| Gross sales | 1,920.00 | 900.00 | 2,820.00 |
| Applied refunds (deduction) | 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 identified missing-amount refund, but RF04's product cannot be determined and is excluded from all product totals.

### Calculation evidence

- Workshop gross: 240.00 + 480.00 + 240.00 + 720.00 + 240.00 = **1,920.00**, from `inputs/sales.csv` S01, S03, S06, S12, S14. Course gross: 120.00 + 240.00 + 180.00 + 360.00 = **900.00**, from S02, S07, S10, S13.
- Applied refunds: Workshop **240.00**, `inputs/refunds.csv` R01 (N1003); Course **60.00**, R02 (N1006). Product is joined from the retained order, not inferred from the refund amount.
- Workshop ads: 150.00 + 100.00 + 50.00 = **300.00**, `inputs/ads.csv` A01, A03, A06. Course ads: 90.00 + 60.00 = **150.00**, A02, A05.
- Net revenue = gross − applied refunds. Contribution = net revenue − ads. Net revenue per paid order = net revenue ÷ paid order count; refunded orders remain in the paid-order denominator. Combined per-order revenue is 2,520.00 ÷ 9 = **280.00**, not an unweighted average of product averages.

## Record and amount reconciliation

The cleaned CSVs are **full-row audit ledgers**, not included-only extracts. Filter `included_in_october_total=yes` before aggregating. Every original `row_id` is retained, including excluded records; `exclusion_code` links to the explanation in `exceptions.csv`.

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

These raw amount bridges deliberately include duplicates and ineligible records to reconcile the source files; they are not economic totals. The refund unknown is the same missing R06 amount on both sides of the bridge, not an assumed zero.

### Sales exclusions — `inputs/sales.csv`

| Row | Amount | Reason |
|---|---:|---|
| S04 | 480.00 | Duplicate N1003 business record; retain S03. |
| S05 | 120.00 | N1004 pending, not paid. |
| S08 | 240.00 | N1007 cancelled, not paid. |
| S09 | 120.00 | Superseded N1008 record; retain S10, updated 2026-10-16T09:00:00, at 180.00. |
| S11 | 240.00 | September 30 order, outside October. |
| S15 | 120.00 | November 1 order, outside October. |
| **Total** | **1,320.00** | **6 excluded rows; 9 included.** |

### Refund exclusions — `inputs/refunds.csv`

| Row | Amount | Reason |
|---|---:|---|
| R03 | 60.00 | Duplicate RF02; retain R02. |
| R04 | 240.00 | RF03 pending; not settled. |
| R05 | 120.00 | RF04 settled but N9999 is absent from sales; no valid paid order or product established. |
| R06 | Unknown | RF05 settled October 25 for N1011 (Course, sales S13), but amount missing. |
| R07 | 240.00 | RF06 settled November 2; October order S14 does not make this an October refund. |
| **Known total** | **660.00 + unknown** | **5 excluded rows; 2 applied.** |

### Ad exclusions — `inputs/ads.csv`

| Row | Amount | Reason |
|---|---:|---|
| A04 | 100.00 | Duplicate AD03; retain A03. |
| A07 | 80.00 | November 1 spend, outside October. |
| **Total** | **180.00** | **2 excluded rows; 5 included.** |

## Cleaning decisions and assumptions

- Duplicate means identical business fields, disregarding the distinct source `row_id`. Retain the first source occurrence for identical records: S03 over S04, R02 over R03, A03 over A04. This operational interpretation preserves the policy's unique order/refund/ad IDs while retaining every source row for audit.
- For conflicting N1008 records, use the latest `updated_at` (S10), as required by `inputs/policy.md`, **Northline Workshops accounting note — October 2026**.
- Normalize date fields to YYYY-MM-DD; slash dates are DD/MM/YYYY and `Oct 05 2026` becomes 2026-10-05. Preserve `updated_at` as an ISO timestamp, including time, because it determines the retained version.
- Strip CAD/$ currency markers and format known amounts to two decimals. Leave R06's amount empty. Do not impute missing values.
- Normalize products to Workshop/Course and ad channels to lowercase after trimming. All supplied sales/ad products map successfully. Refund product is derived from the retained sales order; R05's product remains blank because its order is unresolved. Its original N9999 reference is preserved.
- No known, unique, settled refund total for a matched paid order exceeds that order's gross, including the known November refund R07. RF05's missing amount and RF04's unmatched order prevent a complete cap check. This follows the refund-cap rule in the policy.

## Unresolved evidence and practical recommendation

1. **Obtain RF05's settled amount** (`inputs/refunds.csv` R06). It relates to paid Course order N1011, gross 360.00 (`inputs/sales.csv` S13). Until verified, Course and combined refund-adjusted results remain provisional. If its verified eligible amount is x and nothing else changes, Course net revenue becomes 840.00 − x, combined net revenue 2,520.00 − x, combined contribution 2,070.00 − x, and combined revenue per paid order (2,520.00 − x) ÷ 9. Validate the refund cap before applying it.
2. **Investigate RF04's N9999 reference** (`inputs/refunds.csv` R05). Obtain a valid paid-order match and product, or a source correction. Do not allocate its 120.00 to either product without evidence. Any resulting correction may require recalculating sales as well as refunds; no adjustment is guessed here.
3. **Use the current results cautiously for advertising decisions.** Workshop currently contributes 1,380.00 versus Course's provisional 690.00 after ads, based on the cited included records. Prioritize resolving Course refunds before expanding Course ad spend. These totals do not establish incremental ad effectiveness or customer acquisition cost, and product delivery costs could change the relative economics.
4. **Build a fuller profitability view** before calling either product profitable. COGS, payment fees, salaries and overhead are not supplied (`inputs/policy.md`, **Northline Workshops accounting note — October 2026**). The 2,070.00 calculable contribution is only net revenue less advertising, not net profit.

## Checks performed and handoff

Executed a local Python standard-library reconciliation using decimal arithmetic. Checks passed for:

- All 29 input CSV rows retained once with source row IDs; 16 included and 13 excluded, with one exception record per excluded row.
- Duplicate field equality, latest N1008 update selection, and unique included order/refund/ad IDs.
- Date and monetary formatting, paid-order joins for applied refunds, cumulative known refund caps, and preservation of the missing refund amount.
- Product and combined results against the displayed totals; raw known amount = included amount + excluded known amount for each file.
- Written CSV readback and equality with generated records; unchanged SHA-256 hashes for all four raw input files before/after processing.

Deliverables: `output/cleaned-sales.csv`, `output/cleaned-refunds.csv`, `output/cleaned-ads.csv`, `output/exceptions.csv`, and `output/summary.md`.

Remaining limitations: RF05 amount and RF04 order linkage cannot be resolved from the supplied files; no external verification was attempted. Full business profit cannot be calculated without the missing cost categories.

Estimated human completion effort: **15–25 minutes once supporting refund/order records are available** to resolve the two exceptions, validate caps and refresh the report. This is an estimate, not measured time, and excludes waiting for records or compiling a full cost ledger.
