# Accounts Receivable Aging — Reverse-Engineered Logic

This document is the source-of-truth specification for how the month-end **Amazon
Accounts Receivable Aging** workbook is calculated. It was reverse-engineered from the
actual January-2026 files and every figure below is verified to the penny by the engine
(`app/core`) and the reconciliation test (`tests/test_reconciliation_jan2026.py`).

> **Benchmark:** USA receivable = **11,110,433** = `Detail!D11`.

---

## 1. Source files

Finance exports the Amazon **Custom Unified Transaction** report in ~10-day chunks:

| File | Rows | Dates | Settlement id range |
|---|---:|---|---|
| `USA…01 to 10 January,2026.xlsx` | 778,356 | Jan 1–10 | 25218044401 → 25434014921 |
| `USA…11 to 20 January,2026.xlsx` | 823,483 | Jan 11–20 | 25231317951 → 25468048861 |
| `USA…21 to 31 January,2026.xlsx` | 777,392 | Jan 21–31 | 25342616501 → 25564376091 |

Each file has **two worksheets**: a user-made **pivot ("Sheet1") that must be ignored**,
and the single **raw data worksheet** the app parses. The app auto-detects the raw sheet
by locating the header row containing `settlement id` + `total` + `date/time`.

### Schema (30 columns, header row 8, data from row 9)
`A date/time · B settlement id · C type · D order id · E sku · F description · G quantity ·
H marketplace · I account type · J fulfillment · K order city · L order state · M order postal ·
N tax collection model · O product sales · P product sales tax · Q shipping credits ·
R shipping credits tax · S gift wrap credits · T giftwrap credits tax · U Regulatory Fee ·
V Tax On Regulatory Fee · W promotional rebates · X promotional rebates tax ·
Y marketplace withheld tax · Z selling fees · AA fba fees · AB other transaction fees ·
AC other · AD total`

- `AD total` is the **net per-row amount** (already nets O..AC) — the only amount the receivable needs.
- `date/time` is **text** ("Jan 15, 2026 5:45:40 PM PST"); `settlement id` and amounts are numeric.
- `account type` (col I) ∈ {`Standard Orders`, `Invoiced Orders`}; **blank/populated on Transfer rows
  but Transfer rows are always excluded from the receivable**.
- **Transaction types:** Order, Refund, Adjustment, FBA Customer Return Fee, Order_Retrocharge,
  Refund_Retrocharge, Service Fee, Service Fee - Reversal, FBA Inventory Fee, FBA Transaction fees,
  Amazon Fees, Fee Adjustment, Shipping Services, SAFE-T reimbursement, A-to-z Guarantee Claim,
  Chargeback Refund, Liquidations, Others, **Transfer**.

---

## 2. The sample workbook (`Accounts Receivable Aging (Jan-26) 1.xlsx`, 18 sheets)

- **`Summary`** — A/R Aging Summary. One row per vendor × bands **Current / 1-30 / 31-60 / 61-90 /
  91-Over**. **All Amazon receivable sits in "Current"** (other bands = 0). `Amazon USA` row =
  `=Detail!D11`. `TOTAL = SUM`, then `Allowance for Sales Returns` (manual) → `Net Receivable`.
- **`Detail`** — the engine (one column block per marketplace; USA uses 3 columns Invoiced / Standard /
  Total). Roll-forward rows R4–R11 + FX rows R23–R24 (see §4).
- **`COA`** — chart-of-accounts / FX table (`B local · C rate · D=B×C · E currency`), rates manual.
- **14 marketplace tabs** (`USA (19-20)`, `USA (21-31)`, Canada, UK, …) — each holds only the
  **unpaid (receivable) transactions** for that marketplace, same 30-col schema, Transfer rows lifted
  to the top (feed Receipts), bottom subtotal cells:
  `AD = SUMIFS($AD$8:$AD$last, $I$8:$I$last, "Standard Orders")` (and `"Invoiced Orders"`).

---

## 3. Settlement classification — which rows are unpaid (the crux)

Settlement ids are **monotonic** (higher = newer). Amazon closes a settlement when the next opens
and disburses it via a **`Transfer` row tagged with the *next/open* settlement id** that pays out the
*prior* settlement(s). **A settlement is receivable until the payout that clears it is *received* in
the bank by month-end.**

Full verified Jan-2026 chain:

| Settlement | Acct | Net orders (Σ total, non-transfer) | Payout Transfer | Received by Jan-31? | Status |
|---|---|---:|---|---|---|
| 25218044401 | Std | 55,026.85 | (Dec payout) | yes | paid |
| 25327934061 | Std | 4,471,986.74 | −15,323,853.80 (Jan 8) | yes | paid |
| 25434014921 | Std | 3,244,454.86 | −3,244,604.61 (Jan 16) | yes | paid |
| **25468048861** | Std | **9,556,111.11** | −9,555,841.69 (Jan 30) | **no (in transit)** | **receivable** |
| **25546871121** | Std | **1,486,342.10** | (February) | no | **receivable** |
| 25231317951 | Inv | 156,883.15 | −228,606.81 (Jan 20) | yes | paid |
| **25342616501** | Inv | **67,501.46** | (February) | no | **receivable** |
| **25564376091** | Inv | **353.25** | (February) | no | **receivable** |

**Algorithm (auto clearing-lag; `app/core/settlements.py`):**
1. Collect `Transfer` rows → disbursements `(date, settlement_id, amount, account_type)`.
2. A disbursement is **received** iff `date ≤ month_end − clearing_lag_days` (default **2**). The
   Jan-30 payout → in-transit → settlement 25468048861 stays receivable. Users can override per
   transfer in the Settlement Reconciliation page.
3. Per `(marketplace, account_type)`: `paid_boundary = max(settlement_id over received transfers)`;
   **receivable settlements = settlement_id ≥ paid_boundary** (equivalently: a settlement is paid iff
   its id < paid_boundary).
4. **Receivable = Σ `total` for non-Transfer rows** (account_type = Standard/Invoiced Orders) in
   receivable settlements.

---

## 4. Receivable calculation (`Detail` roll-forward)

Per marketplace, per account type:

```
Additional sales after last payment = SUMIFS(total, account_type)   over receivable settlements
Net Closing Balance (Reserve)       = Opening + Sales + Receipts + Refunds + Expenses   (≈ 0)
Receivable (local currency)         = ROUND( reserve + additional_sales , 0 )    [marketplace level = Detail!D11]
Receivable (USD)                    = Receivable_local × FX_rate                  (USA rate = 1)
```

Jan-2026 USA:
- Standard additional = **11,042,453.21**, Invoiced additional = **67,854.71**, total = **11,110,307.92**.
- Reserve (Net Closing Balance): Standard **125.44**, Invoiced **0** → `ROUND(11,110,433.36)` = **11,110,433**.

**Reserve / opening.** The reserve nets to ~0 by design; it is a Finance-maintained reconciliation
carry (prior-month `Opening Balance` + last-settlement reconciliation). It cannot be reconstructed
exactly from the date-range files alone because received transfers pay out multiple settlements
including prior-month carryover. It is therefore an **optional per-(marketplace, account_type) input**
(default 0). Without it, the receivable base from the files alone is **11,110,308** (11,110,307.92
rounded); with the workbook's 125.44 reserve it is the exact **11,110,433**.

---

## 5. FX, aging, reconciliation

- **FX:** manual per-marketplace rate to USD (`COA` / `Detail!R23`). USA = 1. Editable & importable;
  never fetched online; every rate is shown and auditable in the export.
- **Aging:** all Amazon receivable is **Current** (matches workbook). A day-based aging function
  (`classify_aging`) is available for completeness.
- **Reconciliation:** identity `uploaded_total = receivable_orders + paid_orders + transfers_total`
  (proves nothing was dropped); `final_receivable = Σ ROUND(reserve + additional_sales)×fx + manual
  adj`. Status **Reconciled** when |identity| and |vs-expected| ≤ tolerance (default USD 0.01).

---

## 6. Notes / open items

- The **01-10 file was originally truncated** (56 MB, cut mid-data during copy); the re-shared 212 MB
  copy is valid. The 01-10 data contains only **paid** settlements (< the receivable boundary) so it
  contributes **0** to the receivable — but it is retained for reserve reconciliation and audit trail.
- Reserve/opening exact reproduction depends on the prior-month close (external to these files);
  surfaced as a manual input with a reconciliation schedule for transparency.
- Scope is **all Amazon marketplaces** (generic engine); only USA files were supplied for Jan-2026,
  so other regions activate when their Amazon files are uploaded.