utopia-ai
/
Finance-Accounts
5
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
Finance-Accounts/ar-aging-app/docs/accounts-receivable-logic.md

8.0 KiB
Raw Permalink Blame History

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.