01How data flows through the system
Every figure you see is the end of a chain that starts in the raw Amazon files. Nothing is typed in by hand except the few inputs you choose (month‑end date, clearing‑lag, reserve, FX). The pipeline runs once when you click Run processing.
02The source data — what we read, what we use
Each Amazon file has a preamble (rows 1–7), a header on row 8, and data from row 9. There are 30 columns (A→AD). We map them by normalized name, not position, so a header rename doesn’t break anything. Of the 30, five columns drive the entire calculation:
| Column | Header | What it is | Used for |
|---|---|---|---|
| A | date/time | Transaction timestamp (text, e.g. “Jan 15, 2026 5:45:40 PM PST”) | Date coverage; deciding if a payout has cleared |
| B | settlement id | Amazon’s settlement batch number — always increases over time | Grouping; the paid/receivable boundary |
| C | type | Order, Refund, Adjustment, fees… and Transfer (a bank payout) | Separating payouts from sales |
| I | account type | Standard Orders or Invoiced Orders | Splitting the two receivable streams |
| AD | total | Net amount of the row (all sub‑columns already summed) | The amount summed for every figure |
03The four rules behind every figure
These four rules are the whole engine. Understand them and every number on the dashboard follows.
Which settlements are still owed
Amazon pays you in settlement batches. When a batch closes it is paid out as a
Transfer row that is tagged to the next batch. A settlement
is receivable until the payout that
clears it has actually reached the bank by month‑end. A payout counts as
received only if its date is on or before month‑end − clearing‑lag
(default 2 days), otherwise it is in‑transit.
paid_boundary = max(settlement_id of received transfers)
a settlement is receivable if settlement_id ≥ paid_boundary
else paid
“Additional sales after last payment”
For the settlements still owed, sum the total column — split by account type, and excluding the Transfer payout rows themselves. This is the workbook’s Additional sales after last payment line, and it is the bulk of the receivable.
where settlement is receivable
and account_type in {Standard, Invoiced}
and type ≠ Transfer
Rounding, reserve & currency
Add the reserve (a small Finance‑maintained “Net Closing Balance”, ≈ 0, entered by
you), round to whole units, then convert to USD at your rate. The marketplace‑level rounded
figure is exactly the workbook’s Detail!D11.
receivable_usd = receivable_local × fx_rate
# USA fx_rate = 1
Reconciliation identity
Every uploaded row lands in exactly one bucket, so the three buckets must add back to the uploaded total. If they don’t (beyond a $0.01 tolerance) the closing is flagged Unreconciled.
+ paid_orders
+ transfers_total
final_receivable = Σ receivable_usd + manual_adjustment
04Home & Overview — number by number
Below, “Source” names the database table the figure is read from and “Logic” is what produced it. The database is populated once during processing and every screen reads from it.
Dashboard (home)
| On screen | Source | Logic |
|---|---|---|
| Total closings | sessions | Count of month‑end closings you’ve created. |
| Latest closing receivable | reconciliation.final_receivable_usd | The most recent processed closing’s final figure (see Rule 3–4). |
| Processed | sessions.status | How many closings have finished processing. |
| Closings table → Receivable | reconciliation | Per‑closing final receivable, one row each. |
Closing → Overview (the summary cards)
| Card | Source | Logic |
|---|---|---|
| Closing Amazon Receivable | reconciliation.final_receivable_usd | Σ over marketplaces of ROUND(reserve+additional)×fx + manual adjustment. |
| Reconciliation status | reconciliation.status | Reconciled when the identity (Rule 4) holds within $0.01. |
| Reserve total | reconciliation.reserve_total | Sum of the reserves you entered. |
| Unpaid / paid / total settlements | settlements.status | Counts of settlements by status (Rule 1). |
| Exceptions | exceptions | Counts grouped by severity (error / warning / info). |
| Receivable orders | reconciliation.receivable_orders | Σ total over receivable settlements (all order rows). |
| Paid orders (settled) | reconciliation.paid_orders | Σ total over paid settlements. |
| Transfers / disbursements | reconciliation.transfers_total | Σ total over all Transfer rows (bank payouts, negative). |
| Receivable transactions | transactions.receivable_flag | Count of rows that belong to a receivable settlement. |
| Receivable by marketplace | receivable_results (TOTAL) | Per‑marketplace local & USD receivable (Rule 3). |
Trace: total → grouped by settlement → settlement marked receivable → summed = Receivable orders → ROUND(+reserve)×fx = Closing Amazon Receivable.
05Settlement Reconciliation — the cutoff you can see
This screen shows how the paid/receivable line was drawn, and lets you move it. Every row is a settlement batch summed from the source; the Disbursements block lists the Transfer payouts that set the boundary.
| Column | Source | Logic |
|---|---|---|
| Settlement id | settlements.settlement_id | Amazon’s batch number (column B). |
| Account | settlements.account_type | Standard or Invoiced (column I). |
| Orders | settlements.order_total | Σ total for non‑Transfer rows in the batch. |
| Transfers | settlements.transfer_total | Σ total for Transfer rows in the batch. |
| Rows / Period | settlements.row_count · first/last_date | Row count and date span, from column A. |
| Status | settlements.status | receivable / paid from Rule 1. |
| Disbursement · Received? | settlements.transfer_received | Received if payout date ≤ month‑end − clearing‑lag; else in‑transit. |
06A/R Aging & Reconciliation
Accounts Receivable Aging
The aging matrix mirrors the workbook’s bands (Current / 1–30 / 31–60 / 61–90 / 91–Over). Because Amazon settles roughly every two weeks, the month‑end receivable is always Current — every other band is 0. Values come from receivable_results per marketplace and land entirely in the Current column.
Reconciliation
| Line | Source | Logic |
|---|---|---|
| Total uploaded transaction value | reconciliation.uploaded_total | Σ total over every row read. |
| Receivable orders | reconciliation.receivable_orders | Open‑settlement order totals. |
| Paid orders | reconciliation.paid_orders | Settled order totals. |
| Transfers / disbursements | reconciliation.transfers_total | All payouts. |
| Net Closing Balance (reserve) | reconciliation.reserve_total | Your reserve input. |
| Manual adjustments | sessions.manual_adjustment | Optional override you enter. |
| Final closing receivable | reconciliation.final_receivable_usd | Rule 3 + adjustment. |
| Identity difference | reconciliation.identity_difference | uploaded − (receivable + paid + transfers); must be ≈ 0. |
07Transaction Details & Exceptions
Transaction Details
The full drill‑down: every row we read, filterable by settlement, type, and paid/receivable status. Each row keeps a pointer back to its origin so any figure can be traced to a specific file and row for audit.
| Column | Source |
|---|---|
| Date · Settlement · Type · Account · Order ID · SKU · Total | transactions.* (from source columns A, B, C, I, D, E, AD) |
| Recv (receivable?) | transactions.receivable_flag — set by Rule 1 |
| Source (file : row) | transactions.source_file · source_row — the audit trail |
Validation & Exceptions
Nothing questionable is silently dropped — it’s surfaced here instead.
08Worked example — how Jan 2026 reaches $11,110,433
The three January files contain eight settlement batches. The engine sums each, checks each payout’s date against the clearing‑lag, and draws the line. Here is the actual chain (Standard account shown; Invoiced follows the same pattern).
| Settlement | Net orders | Payout (Transfer) | Received by Jan‑31? | Status |
|---|---|---|---|---|
| 25327934061 | +4,471,987 | −15,323,854 · Jan 8 | yes | paid |
| 25434014921 | +3,244,455 | −3,244,605 · Jan 16 | yes | paid |
| 25468048861 | +9,556,111 | −9,555,842 · Jan 30 | no — in transit | receivable |
| 25546871121 | +1,486,342 | — (February) | no | receivable |
The Jan‑30 payout was initiated one day before month‑end, so with a 2‑day clearing‑lag it counts as in‑transit — which keeps settlement 25468048861 fully receivable. Summing the two receivable batches (and adding the Invoiced stream and reserve):
Invoiced receivable = 67,854.71 # settlements 25342616501 + 25564376091
additional_sales = 11,110,307.92
receivable_local = ROUND( 125.44 reserve + 11,110,307.92 ) = 11,110,433
Detail!D11,
C10, B10 and C9 — every cell matches to the penny.09The Excel workbook it generates
“Download Full A/R Aging Excel” produces a complete month‑end working file with live formulas — the same structure as the manual workbook, not a flat table export. It re‑reads the source files and writes every receivable transaction row (~1.2M) so an auditor can follow any total back to its evidence.
| Sheet | What it holds |
|---|---|
| Summary | A/R Aging Summary — per‑marketplace × aging band, TOTAL, allowance, Net Receivable. Pulls from Detail. |
| Detail | The roll‑forward: additional sales, reserve, ROUND, rate, and USD subtotal — with cross‑sheet formulas. |
| COA | The FX table (local amount × rate = USD) per marketplace. |
| USA (1), USA (2)… | Every receivable transaction row, with the bottom SUMIFS subtotals the Detail sheet references. Split across sheets to stay under Excel’s row limit. |
| Reconciliation | The Rule‑4 breakdown and status. |
| Exceptions | Everything from the Validation screen. |
| Processing Audit Trail | Generation time, inputs, file names + SHA‑256 hashes, row counts, FX rates, reserves, app version. |
10Key terms
Settlement
A batch of Amazon transactions paid out together. Its settlement id increases over time, so a higher id is newer.
Transfer
A row of type = Transfer — an actual bank payout. Negative. Excluded from the receivable; used as the “received payment”.
Clearing‑lag
Days before month‑end within which a payout is assumed not to have cleared the bank yet (default 2). It draws the paid/receivable line.
Additional sales after last payment
The receivable base — the summed total of all order rows in settlements not yet paid.
Reserve / Net Closing Balance
A small (~0) Finance‑maintained reconciling figure carried from the prior close. Entered by you; barely moves the total.
Account type
Amazon splits activity into Standard Orders and Invoiced Orders; each has its own settlement chain and its own paid/receivable boundary.