539 lines
37 KiB
HTML
539 lines
37 KiB
HTML
<title>Amazon A/R Aging — Dashboard Guide</title>
|
||
<style>
|
||
/* Utopia Brands — lime #CBEA3B (fills/accents), teal #0D3B34 (text emphasis), charcoal darks. */
|
||
:root {
|
||
--paper:#F1F3ED; --card:#FFFFFF; --ink:#1F2424; --muted:#6E736F; --faint:#969B95;
|
||
--line:#E4E6E1; --line-soft:#ECEEE9;
|
||
--primary:#0D3B34; --primary-deep:#0A2E29; --primary-soft:#E4EFEA;
|
||
--brand:#CBEA3B; --brand-ink:#1F2424; --emph:#4C5E12; --accent:#CBEA3B; --accent-soft:#F1F8D8;
|
||
--ok:#0D3B34; --ok-bg:#E4EFEA; --warn:#7A5A00; --warn-bg:#F6EBC8; --bad:#B3261E; --bad-bg:#F7E1DE;
|
||
--code-bg:#EDF0EA; --code-ink:#28382C;
|
||
--shadow:0 1px 2px rgba(31,36,36,.05), 0 6px 20px rgba(31,36,36,.07);
|
||
--mono:ui-monospace,"SF Mono","Cascadia Code","Fira Code",Menlo,Consolas,monospace;
|
||
--sans:ui-sans-serif,system-ui,-apple-system,"Segoe UI",Roboto,"Helvetica Neue",Arial,sans-serif;
|
||
}
|
||
@media (prefers-color-scheme: dark) {
|
||
:root {
|
||
--paper:#0C110F; --card:#161C19; --ink:#E7ECE6; --muted:#9AA39C; --faint:#727C74;
|
||
--line:#28312C; --line-soft:#1D2521;
|
||
--primary:#86B7AD; --primary-deep:#A7CDC5; --primary-soft:#15241F;
|
||
--brand:#CBEA3B; --brand-ink:#12160F; --emph:#B7D24A; --accent:#CBEA3B; --accent-soft:#232A12;
|
||
--ok:#7FBBA2; --ok-bg:#12261F; --warn:#D9B24A; --warn-bg:#2A2410; --bad:#EE9089; --bad-bg:#301617;
|
||
--code-bg:#19211C; --code-ink:#B9CBB7;
|
||
--shadow:0 1px 2px rgba(0,0,0,.5), 0 8px 26px rgba(0,0,0,.4);
|
||
}
|
||
}
|
||
:root[data-theme="light"] {
|
||
--paper:#F1F3ED; --card:#FFFFFF; --ink:#1F2424; --muted:#6E736F; --faint:#969B95;
|
||
--line:#E4E6E1; --line-soft:#ECEEE9; --primary:#0D3B34; --primary-deep:#0A2E29; --primary-soft:#E4EFEA;
|
||
--brand:#CBEA3B; --brand-ink:#1F2424; --emph:#4C5E12; --accent:#CBEA3B; --accent-soft:#F1F8D8;
|
||
--ok:#0D3B34; --ok-bg:#E4EFEA; --warn:#7A5A00; --warn-bg:#F6EBC8; --bad:#B3261E; --bad-bg:#F7E1DE;
|
||
--code-bg:#EDF0EA; --code-ink:#28382C;
|
||
}
|
||
:root[data-theme="dark"] {
|
||
--paper:#0C110F; --card:#161C19; --ink:#E7ECE6; --muted:#9AA39C; --faint:#727C74;
|
||
--line:#28312C; --line-soft:#1D2521; --primary:#86B7AD; --primary-deep:#A7CDC5; --primary-soft:#15241F;
|
||
--brand:#CBEA3B; --brand-ink:#12160F; --emph:#B7D24A; --accent:#CBEA3B; --accent-soft:#232A12;
|
||
--ok:#7FBBA2; --ok-bg:#12261F; --warn:#D9B24A; --warn-bg:#2A2410; --bad:#EE9089; --bad-bg:#301617;
|
||
--code-bg:#19211C; --code-ink:#B9CBB7;
|
||
}
|
||
|
||
* { box-sizing:border-box; }
|
||
body { margin:0; background:var(--paper); color:var(--ink); font-family:var(--sans);
|
||
font-size:16px; line-height:1.65; -webkit-font-smoothing:antialiased; }
|
||
.num, code, kbd, .mono { font-family:var(--mono); font-variant-numeric:tabular-nums; }
|
||
|
||
.wrap { max-width:1160px; margin:0 auto; padding:0 24px; }
|
||
.layout { display:grid; grid-template-columns:1fr; gap:0; }
|
||
@media (min-width:1020px){ .layout { grid-template-columns:216px minmax(0,1fr); gap:44px; align-items:start; } }
|
||
|
||
/* ---- hero ---- */
|
||
header.hero { border-bottom:1px solid var(--line); background:
|
||
radial-gradient(120% 100% at 0% 0%, var(--primary-soft) 0%, transparent 55%); }
|
||
.eyebrow { font-family:var(--mono); font-size:12px; letter-spacing:.16em; text-transform:uppercase;
|
||
color:var(--primary); font-weight:600; }
|
||
h1.title { font-size:clamp(30px,5vw,46px); line-height:1.05; letter-spacing:-.02em; margin:14px 0 10px;
|
||
text-wrap:balance; font-weight:800; }
|
||
.lede { font-size:clamp(16px,2vw,19px); color:var(--muted); max-width:60ch; margin:0; }
|
||
.hero-inner { padding:44px 0 30px; }
|
||
.metaline { display:flex; flex-wrap:wrap; gap:8px 10px; margin-top:22px; }
|
||
.tag { font-family:var(--mono); font-size:12px; padding:4px 9px; border-radius:6px;
|
||
background:var(--card); border:1px solid var(--line); color:var(--muted); }
|
||
|
||
.headline-figure { margin-top:26px; display:flex; flex-wrap:wrap; align-items:baseline; gap:6px 16px;
|
||
padding:18px 20px; background:var(--card); border:1px solid var(--line); border-left:3px solid var(--accent);
|
||
border-radius:10px; box-shadow:var(--shadow); max-width:760px; }
|
||
.headline-figure .big { font-family:var(--mono); font-size:clamp(26px,4vw,38px); font-weight:700;
|
||
color:var(--emph); letter-spacing:-.01em; }
|
||
.headline-figure .cap { color:var(--muted); font-size:14px; }
|
||
.headline-figure .cap b { color:var(--ink); }
|
||
|
||
/* ---- nav ---- */
|
||
nav.toc { position:sticky; top:0; align-self:start; padding:22px 0; font-size:13.5px; }
|
||
@media (min-width:1020px){ nav.toc { top:18px; max-height:calc(100vh - 36px); overflow:auto; } }
|
||
nav.toc ol { list-style:none; margin:0; padding:0; counter-reset:s; }
|
||
nav.toc a { display:block; text-decoration:none; color:var(--muted); padding:5px 10px; border-radius:7px;
|
||
border-left:2px solid transparent; }
|
||
nav.toc a:hover { color:var(--ink); background:var(--line-soft); }
|
||
nav.toc a .n { font-family:var(--mono); color:var(--faint); font-size:12px; margin-right:8px; }
|
||
nav.toc .grouplabel { font-family:var(--mono); font-size:11px; letter-spacing:.14em; text-transform:uppercase;
|
||
color:var(--faint); padding:14px 10px 4px; }
|
||
|
||
main { padding:26px 0 80px; min-width:0; }
|
||
section { padding:30px 0; border-top:1px solid var(--line-soft); scroll-margin-top:20px; }
|
||
section:first-of-type { border-top:none; }
|
||
h2 { font-size:clamp(22px,3vw,29px); letter-spacing:-.015em; margin:0 0 6px; text-wrap:balance; font-weight:750; }
|
||
h2 .h2n { font-family:var(--mono); font-size:.6em; color:var(--primary); font-weight:600; margin-right:12px;
|
||
vertical-align:.12em; }
|
||
h3 { font-size:18px; margin:30px 0 8px; letter-spacing:-.01em; font-weight:700; }
|
||
.sub { color:var(--muted); max-width:70ch; margin:0 0 4px; }
|
||
p { max-width:72ch; }
|
||
a.inline { color:var(--primary); text-decoration:none; border-bottom:1px solid var(--line); }
|
||
|
||
code { background:var(--code-bg); color:var(--code-ink); padding:1px 6px; border-radius:5px; font-size:.86em; }
|
||
.col { background:var(--code-bg); color:var(--code-ink); padding:1px 7px; border-radius:5px;
|
||
font-family:var(--mono); font-size:.84em; white-space:nowrap; border:1px solid color-mix(in srgb, var(--code-ink) 14%, transparent); }
|
||
|
||
/* formula block */
|
||
.formula { background:var(--card); border:1px solid var(--line); border-radius:10px; padding:16px 18px;
|
||
font-family:var(--mono); font-size:14px; line-height:1.7; overflow-x:auto; box-shadow:var(--shadow);
|
||
color:var(--ink); }
|
||
.formula .k { color:var(--primary-deep); font-weight:600; }
|
||
.formula .c { color:var(--faint); }
|
||
.formula .hl { color:var(--emph); font-weight:600; }
|
||
|
||
/* cards */
|
||
.grid { display:grid; gap:16px; }
|
||
@media (min-width:680px){ .cols-2 { grid-template-columns:1fr 1fr; } }
|
||
.rule { background:var(--card); border:1px solid var(--line); border-radius:12px; padding:20px 20px 18px;
|
||
box-shadow:var(--shadow); }
|
||
.rule .kicker { font-family:var(--mono); font-size:11.5px; letter-spacing:.12em; text-transform:uppercase;
|
||
color:var(--primary); font-weight:600; }
|
||
.rule h3 { margin:6px 0 8px; }
|
||
.rule p { font-size:14.5px; color:var(--muted); margin:0 0 12px; max-width:none; }
|
||
|
||
/* tables */
|
||
.tablewrap { overflow-x:auto; border:1px solid var(--line); border-radius:12px; box-shadow:var(--shadow);
|
||
background:var(--card); margin:14px 0; }
|
||
table { border-collapse:collapse; width:100%; font-size:14px; }
|
||
th, td { text-align:left; padding:10px 14px; border-bottom:1px solid var(--line-soft); vertical-align:top; }
|
||
thead th { background:color-mix(in srgb, var(--primary-soft) 60%, var(--card));
|
||
font-size:12px; letter-spacing:.04em; text-transform:uppercase; color:var(--muted); font-weight:600;
|
||
position:sticky; top:0; }
|
||
tbody tr:last-child td { border-bottom:none; }
|
||
td.n, th.n { text-align:right; font-family:var(--mono); font-variant-numeric:tabular-nums; white-space:nowrap; }
|
||
td .src { font-family:var(--mono); font-size:12.5px; color:var(--muted); }
|
||
tbody tr:hover td { background:color-mix(in srgb, var(--primary-soft) 30%, transparent); }
|
||
caption { text-align:left; color:var(--faint); font-size:12.5px; padding:0 2px 8px; caption-side:top; }
|
||
|
||
/* chips */
|
||
.chip { display:inline-flex; align-items:center; gap:5px; font-family:var(--mono); font-size:11.5px;
|
||
font-weight:600; padding:2px 8px; border-radius:20px; white-space:nowrap; }
|
||
.chip.recv { background:var(--ok-bg); color:var(--ok); }
|
||
.chip.paid { background:var(--line-soft); color:var(--muted); }
|
||
.chip.warn { background:var(--warn-bg); color:var(--warn); }
|
||
.chip.bad { background:var(--bad-bg); color:var(--bad); }
|
||
.dot { width:7px; height:7px; border-radius:50%; background:currentColor; display:inline-block; }
|
||
|
||
/* pipeline */
|
||
.pipe { display:flex; gap:10px; align-items:stretch; overflow-x:auto; padding:6px 2px 12px; }
|
||
.pipe .step { flex:1 0 150px; background:var(--card); border:1px solid var(--line); border-radius:11px;
|
||
padding:13px 14px; position:relative; box-shadow:var(--shadow); }
|
||
.pipe .step .s-n { font-family:var(--mono); font-size:11px; color:var(--primary); font-weight:600; }
|
||
.pipe .step .s-t { font-weight:700; font-size:14.5px; margin:3px 0 4px; letter-spacing:-.01em; }
|
||
.pipe .step .s-d { font-size:12.5px; color:var(--muted); line-height:1.45; }
|
||
.pipe .arrow { align-self:center; color:var(--faint); flex:0 0 auto; font-size:20px; }
|
||
|
||
/* callout */
|
||
.note { border:1px solid var(--line); border-left:3px solid var(--primary); background:var(--card);
|
||
border-radius:10px; padding:14px 16px; font-size:14.5px; color:var(--muted); margin:16px 0; }
|
||
.note.amber { border-left-color:var(--accent); }
|
||
.note b { color:var(--ink); }
|
||
|
||
.kv { display:grid; grid-template-columns:auto 1fr; gap:2px 16px; font-size:14px; }
|
||
.kv dt { font-family:var(--mono); color:var(--muted); font-size:12.5px; padding-top:2px; }
|
||
.kv dd { margin:0; }
|
||
|
||
footer { border-top:1px solid var(--line); padding:26px 0 60px; color:var(--faint); font-size:13px; }
|
||
.arrowchain { font-family:var(--mono); font-size:13px; color:var(--muted); }
|
||
.arrowchain b { color:var(--ink); } .arrowchain .to { color:var(--primary); padding:0 4px; }
|
||
@media (prefers-reduced-motion:no-preference){ a, .pipe .step { transition:background .15s, color .15s; } }
|
||
</style>
|
||
|
||
<header class="hero">
|
||
<div class="wrap hero-inner">
|
||
<div class="eyebrow">Finance & Accounts · Reference Guide</div>
|
||
<h1 class="title">Amazon A/R Aging: every number, traced to its source</h1>
|
||
<p class="lede">How the month‑end Accounts Receivable Aging dashboard turns three raw Amazon
|
||
transaction reports into a reconciled receivable — and exactly where each figure on every
|
||
screen comes from.</p>
|
||
<div class="metaline">
|
||
<span class="tag">Amazon‑only</span>
|
||
<span class="tag">Local processing · no third‑party egress</span>
|
||
<span class="tag">Reproduces the manual workbook to the penny</span>
|
||
</div>
|
||
<div class="headline-figure">
|
||
<span class="big">$11,110,433</span>
|
||
<span class="cap">USA closing receivable for <b>Jan 2026</b> — the same figure as
|
||
<code>Detail!D11</code> in the hand‑prepared workbook. Below: precisely how it is built.</span>
|
||
</div>
|
||
</div>
|
||
</header>
|
||
|
||
<div class="wrap layout">
|
||
<nav class="toc" aria-label="Contents">
|
||
<ol>
|
||
<div class="grouplabel">Foundations</div>
|
||
<li><a href="#flow"><span class="n">01</span>How data flows</a></li>
|
||
<li><a href="#source"><span class="n">02</span>The source data</a></li>
|
||
<li><a href="#logic"><span class="n">03</span>The four core rules</a></li>
|
||
<div class="grouplabel">Provenance</div>
|
||
<li><a href="#dash"><span class="n">04</span>Home & Overview</a></li>
|
||
<li><a href="#settle"><span class="n">05</span>Settlements</a></li>
|
||
<li><a href="#aging"><span class="n">06</span>Aging & Reconciliation</a></li>
|
||
<li><a href="#txn"><span class="n">07</span>Transactions & Exceptions</a></li>
|
||
<div class="grouplabel">Proof</div>
|
||
<li><a href="#worked"><span class="n">08</span>Worked example</a></li>
|
||
<li><a href="#excel"><span class="n">09</span>The Excel workbook</a></li>
|
||
<li><a href="#glossary"><span class="n">10</span>Key terms</a></li>
|
||
</ol>
|
||
</nav>
|
||
|
||
<main>
|
||
<!-- 01 -->
|
||
<section id="flow">
|
||
<h2><span class="h2n">01</span>How data flows through the system</h2>
|
||
<p class="sub">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 <b>Run processing</b>.</p>
|
||
|
||
<div class="pipe" role="list">
|
||
<div class="step" role="listitem"><div class="s-n">SOURCE</div><div class="s-t">Amazon files</div>
|
||
<div class="s-d">3× “Custom Unified Transaction” reports. The raw sheet only — the pivot tab is ignored.</div></div>
|
||
<div class="arrow">→</div>
|
||
<div class="step" role="listitem"><div class="s-n">PARSE</div><div class="s-t">Read & normalize</div>
|
||
<div class="s-d">Header auto‑detected at row 8; 30 columns mapped by name; ~2.4M rows streamed.</div></div>
|
||
<div class="arrow">→</div>
|
||
<div class="step" role="listitem"><div class="s-n">GROUP</div><div class="s-t">Aggregate</div>
|
||
<div class="s-d">Summed by marketplace × account type × settlement id; transfers separated.</div></div>
|
||
<div class="arrow">→</div>
|
||
<div class="step" role="listitem"><div class="s-n">CLASSIFY</div><div class="s-t">Paid vs receivable</div>
|
||
<div class="s-d">Clearing‑lag decides which settlements are still owed.</div></div>
|
||
<div class="arrow">→</div>
|
||
<div class="step" role="listitem"><div class="s-n">RESULT</div><div class="s-t">Receivable + store</div>
|
||
<div class="s-d">Receivable, reserve, reconciliation saved to a local database.</div></div>
|
||
<div class="arrow">→</div>
|
||
<div class="step" role="listitem"><div class="s-n">SHOW</div><div class="s-t">Dashboard & Excel</div>
|
||
<div class="s-d">Read back into the screens and the downloadable workbook.</div></div>
|
||
</div>
|
||
|
||
<div class="note"><b>The one number that matters most:</b> the source column
|
||
<span class="col">total</span> (spreadsheet column <span class="col">AD</span>) is the net
|
||
amount of each transaction — product sales, taxes, shipping and all Amazon fees already netted
|
||
together. Almost every figure on the dashboard is a filtered sum of this single column.</div>
|
||
</section>
|
||
|
||
<!-- 02 -->
|
||
<section id="source">
|
||
<h2><span class="h2n">02</span>The source data — what we read, what we use</h2>
|
||
<p class="sub">Each Amazon file has a preamble (rows 1–7), a header on <b>row 8</b>, and data from
|
||
<b>row 9</b>. 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:</p>
|
||
|
||
<div class="tablewrap">
|
||
<table>
|
||
<caption>The columns the engine relies on (the other 25 are carried through for the audit detail)</caption>
|
||
<thead><tr><th>Column</th><th>Header</th><th>What it is</th><th>Used for</th></tr></thead>
|
||
<tbody>
|
||
<tr><td class="num">A</td><td><span class="col">date/time</span></td><td>Transaction timestamp (text, e.g. “Jan 15, 2026 5:45:40 PM PST”)</td><td>Date coverage; deciding if a payout has cleared</td></tr>
|
||
<tr><td class="num">B</td><td><span class="col">settlement id</span></td><td>Amazon’s settlement batch number — <b>always increases over time</b></td><td>Grouping; the paid/receivable boundary</td></tr>
|
||
<tr><td class="num">C</td><td><span class="col">type</span></td><td>Order, Refund, Adjustment, fees… and <b>Transfer</b> (a bank payout)</td><td>Separating payouts from sales</td></tr>
|
||
<tr><td class="num">I</td><td><span class="col">account type</span></td><td><span class="col">Standard Orders</span> or <span class="col">Invoiced Orders</span></td><td>Splitting the two receivable streams</td></tr>
|
||
<tr><td class="num">AD</td><td><span class="col">total</span></td><td>Net amount of the row (all sub‑columns already summed)</td><td><b>The amount summed for every figure</b></td></tr>
|
||
</tbody>
|
||
</table>
|
||
</div>
|
||
|
||
<div class="note amber"><b>Why the pivot sheet is skipped.</b> Each uploaded file contains a
|
||
“Sheet1” pivot table that the Finance team built by hand. The app ignores it automatically and
|
||
reads the single raw data sheet — the one whose row 8 contains
|
||
<span class="col">settlement id</span>, <span class="col">type</span> and
|
||
<span class="col">total</span>.</div>
|
||
</section>
|
||
|
||
<!-- 03 -->
|
||
<section id="logic">
|
||
<h2><span class="h2n">03</span>The four rules behind every figure</h2>
|
||
<p class="sub">These four rules are the whole engine. Understand them and every number on the
|
||
dashboard follows.</p>
|
||
|
||
<div class="grid cols-2">
|
||
<div class="rule">
|
||
<div class="kicker">Rule 1 · Settlement status</div>
|
||
<h3>Which settlements are still owed</h3>
|
||
<p>Amazon pays you in settlement batches. When a batch closes it is paid out as a
|
||
<span class="col">Transfer</span> row that is tagged to the <em>next</em> batch. A settlement
|
||
is <span class="chip recv"><span class="dot"></span>receivable</span> until the payout that
|
||
clears it has actually reached the bank by month‑end. A payout counts as
|
||
<b>received</b> only if its date is on or before <code>month‑end − clearing‑lag</code>
|
||
(default 2 days), otherwise it is <b>in‑transit</b>.</p>
|
||
<div class="formula">
|
||
<span class="c"># per marketplace & account type</span><br>
|
||
paid_boundary <span class="k">=</span> max(settlement_id of <span class="hl">received</span> transfers)<br>
|
||
a settlement is <span class="hl">receivable</span> <span class="k">if</span> settlement_id ≥ paid_boundary<br>
|
||
<span class="k">else</span> paid
|
||
</div>
|
||
</div>
|
||
|
||
<div class="rule">
|
||
<div class="kicker">Rule 2 · The receivable base</div>
|
||
<h3>“Additional sales after last payment”</h3>
|
||
<p>For the settlements still owed, sum the <span class="col">total</span> column — split by
|
||
account type, and excluding the <span class="col">Transfer</span> payout rows themselves.
|
||
This is the workbook’s <em>Additional sales after last payment</em> line, and it is the bulk
|
||
of the receivable.</p>
|
||
<div class="formula">
|
||
additional_sales <span class="k">=</span> <span class="hl">Σ total</span><br>
|
||
<span class="k">where</span> settlement is receivable<br>
|
||
<span class="k">and</span> account_type <span class="k">in</span> {Standard, Invoiced}<br>
|
||
<span class="k">and</span> type ≠ Transfer
|
||
</div>
|
||
</div>
|
||
|
||
<div class="rule">
|
||
<div class="kicker">Rule 3 · The closing figure</div>
|
||
<h3>Rounding, reserve & currency</h3>
|
||
<p>Add the <b>reserve</b> (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 <code>Detail!D11</code>.</p>
|
||
<div class="formula">
|
||
receivable_local <span class="k">=</span> <span class="hl">ROUND</span>( reserve <span class="k">+</span> additional_sales , 0 )<br>
|
||
receivable_usd <span class="k">=</span> receivable_local <span class="k">×</span> fx_rate<br>
|
||
<span class="c"># USA fx_rate = 1</span>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="rule">
|
||
<div class="kicker">Rule 4 · The safety check</div>
|
||
<h3>Reconciliation identity</h3>
|
||
<p>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
|
||
<span class="chip bad"><span class="dot"></span>Unreconciled</span>.</p>
|
||
<div class="formula">
|
||
uploaded_total <span class="k">=</span> receivable_orders<br>
|
||
<span class="k">+</span> paid_orders<br>
|
||
<span class="k">+</span> transfers_total<br>
|
||
final_receivable <span class="k">=</span> Σ receivable_usd <span class="k">+</span> manual_adjustment
|
||
</div>
|
||
</div>
|
||
</div>
|
||
|
||
<div class="note"><b>The reserve, explained.</b> The reserve nets to about zero by design — it
|
||
reconciles the last <em>paid</em> settlement against its bank payout, plus a prior‑month opening
|
||
balance. It cannot be rebuilt exactly from the transaction files alone (a single payout clears
|
||
several settlements, some from the previous month), so it is a small manual input. With the
|
||
reserve left at 0 the USA figure is <span class="num">$11,110,308</span>; entering the workbook’s
|
||
Standard reserve of <span class="num">125.44</span> gives the exact
|
||
<span class="num">$11,110,433</span>.</div>
|
||
</section>
|
||
|
||
<!-- 04 -->
|
||
<section id="dash">
|
||
<h2><span class="h2n">04</span>Home & Overview — number by number</h2>
|
||
<p class="sub">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.</p>
|
||
|
||
<h3>Dashboard (home)</h3>
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>On screen</th><th>Source</th><th>Logic</th></tr></thead>
|
||
<tbody>
|
||
<tr><td>Total closings</td><td class="src">sessions</td><td>Count of month‑end closings you’ve created.</td></tr>
|
||
<tr><td>Latest closing receivable</td><td class="src">reconciliation.final_receivable_usd</td><td>The most recent processed closing’s final figure (see Rule 3–4).</td></tr>
|
||
<tr><td>Processed</td><td class="src">sessions.status</td><td>How many closings have finished processing.</td></tr>
|
||
<tr><td>Closings table → Receivable</td><td class="src">reconciliation</td><td>Per‑closing final receivable, one row each.</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
|
||
<h3>Closing → Overview (the summary cards)</h3>
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>Card</th><th>Source</th><th>Logic</th></tr></thead>
|
||
<tbody>
|
||
<tr><td><b>Closing Amazon Receivable</b></td><td class="src">reconciliation.final_receivable_usd</td><td>Σ over marketplaces of <span class="col">ROUND(reserve+additional)×fx</span> + manual adjustment.</td></tr>
|
||
<tr><td>Reconciliation status</td><td class="src">reconciliation.status</td><td><span class="chip recv"><span class="dot"></span>Reconciled</span> when the identity (Rule 4) holds within $0.01.</td></tr>
|
||
<tr><td>Reserve total</td><td class="src">reconciliation.reserve_total</td><td>Sum of the reserves you entered.</td></tr>
|
||
<tr><td>Unpaid / paid / total settlements</td><td class="src">settlements.status</td><td>Counts of settlements by status (Rule 1).</td></tr>
|
||
<tr><td>Exceptions</td><td class="src">exceptions</td><td>Counts grouped by severity (error / warning / info).</td></tr>
|
||
<tr><td>Receivable orders</td><td class="src">reconciliation.receivable_orders</td><td>Σ <span class="col">total</span> over <b>receivable</b> settlements (all order rows).</td></tr>
|
||
<tr><td>Paid orders (settled)</td><td class="src">reconciliation.paid_orders</td><td>Σ <span class="col">total</span> over <b>paid</b> settlements.</td></tr>
|
||
<tr><td>Transfers / disbursements</td><td class="src">reconciliation.transfers_total</td><td>Σ <span class="col">total</span> over all <span class="col">Transfer</span> rows (bank payouts, negative).</td></tr>
|
||
<tr><td>Receivable transactions</td><td class="src">transactions.receivable_flag</td><td>Count of rows that belong to a receivable settlement.</td></tr>
|
||
<tr><td>Receivable by marketplace</td><td class="src">receivable_results (TOTAL)</td><td>Per‑marketplace local & USD receivable (Rule 3).</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
<p class="arrowchain" style="margin-top:12px"><b>Trace:</b> <span class="col">total</span>
|
||
<span class="to">→</span> grouped by settlement <span class="to">→</span> settlement marked
|
||
receivable <span class="to">→</span> summed = <b>Receivable orders</b> <span class="to">→</span>
|
||
ROUND(+reserve)×fx = <b>Closing Amazon Receivable</b>.</p>
|
||
</section>
|
||
|
||
<!-- 05 -->
|
||
<section id="settle">
|
||
<h2><span class="h2n">05</span>Settlement Reconciliation — the cutoff you can see</h2>
|
||
<p class="sub">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 <b>Disbursements</b> block lists the
|
||
<span class="col">Transfer</span> payouts that set the boundary.</p>
|
||
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>Column</th><th>Source</th><th>Logic</th></tr></thead>
|
||
<tbody>
|
||
<tr><td>Settlement id</td><td class="src">settlements.settlement_id</td><td>Amazon’s batch number (column <span class="col">B</span>).</td></tr>
|
||
<tr><td>Account</td><td class="src">settlements.account_type</td><td>Standard or Invoiced (column <span class="col">I</span>).</td></tr>
|
||
<tr><td>Orders</td><td class="src">settlements.order_total</td><td>Σ <span class="col">total</span> for non‑Transfer rows in the batch.</td></tr>
|
||
<tr><td>Transfers</td><td class="src">settlements.transfer_total</td><td>Σ <span class="col">total</span> for Transfer rows in the batch.</td></tr>
|
||
<tr><td>Rows / Period</td><td class="src">settlements.row_count · first/last_date</td><td>Row count and date span, from column <span class="col">A</span>.</td></tr>
|
||
<tr><td>Status</td><td class="src">settlements.status</td><td><span class="chip recv"><span class="dot"></span>receivable</span> / <span class="chip paid"><span class="dot"></span>paid</span> from Rule 1.</td></tr>
|
||
<tr><td>Disbursement · Received?</td><td class="src">settlements.transfer_received</td><td><b>Received</b> if payout date ≤ month‑end − clearing‑lag; else in‑transit.</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
|
||
<div class="note amber"><b>The clearing‑lag control.</b> Raising or lowering the lag (in days)
|
||
re‑decides which payouts count as received and re‑runs the calculation. This is the one business
|
||
judgment in the whole close: a payout initiated a day or two before month‑end usually hasn’t
|
||
cleared the bank yet, so its settlement stays receivable.</div>
|
||
</section>
|
||
|
||
<!-- 06 -->
|
||
<section id="aging">
|
||
<h2><span class="h2n">06</span>A/R Aging & Reconciliation</h2>
|
||
<h3>Accounts Receivable Aging</h3>
|
||
<p class="sub">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
|
||
<b>Current</b> — every other band is <span class="num">0</span>. Values come from
|
||
<span class="col">receivable_results</span> per marketplace and land entirely in the Current column.</p>
|
||
|
||
<h3>Reconciliation</h3>
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>Line</th><th>Source</th><th>Logic</th></tr></thead>
|
||
<tbody>
|
||
<tr><td>Total uploaded transaction value</td><td class="src">reconciliation.uploaded_total</td><td>Σ <span class="col">total</span> over <b>every</b> row read.</td></tr>
|
||
<tr><td>Receivable orders</td><td class="src">reconciliation.receivable_orders</td><td>Open‑settlement order totals.</td></tr>
|
||
<tr><td>Paid orders</td><td class="src">reconciliation.paid_orders</td><td>Settled order totals.</td></tr>
|
||
<tr><td>Transfers / disbursements</td><td class="src">reconciliation.transfers_total</td><td>All payouts.</td></tr>
|
||
<tr><td>Net Closing Balance (reserve)</td><td class="src">reconciliation.reserve_total</td><td>Your reserve input.</td></tr>
|
||
<tr><td>Manual adjustments</td><td class="src">sessions.manual_adjustment</td><td>Optional override you enter.</td></tr>
|
||
<tr><td><b>Final closing receivable</b></td><td class="src">reconciliation.final_receivable_usd</td><td>Rule 3 + adjustment.</td></tr>
|
||
<tr><td>Identity difference</td><td class="src">reconciliation.identity_difference</td><td><span class="col">uploaded − (receivable + paid + transfers)</span>; must be ≈ 0.</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
<div class="note">Status reads <span class="chip recv"><span class="dot"></span>Reconciled</span> only
|
||
when the identity difference is within the rounding tolerance (default <span class="num">$0.01</span>).</div>
|
||
</section>
|
||
|
||
<!-- 07 -->
|
||
<section id="txn">
|
||
<h2><span class="h2n">07</span>Transaction Details & Exceptions</h2>
|
||
<h3>Transaction Details</h3>
|
||
<p class="sub">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.</p>
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>Column</th><th>Source</th></tr></thead>
|
||
<tbody>
|
||
<tr><td>Date · Settlement · Type · Account · Order ID · SKU · Total</td><td class="src">transactions.* (from source columns A, B, C, I, D, E, AD)</td></tr>
|
||
<tr><td>Recv (receivable?)</td><td class="src">transactions.receivable_flag — set by Rule 1</td></tr>
|
||
<tr><td>Source (file : row)</td><td class="src">transactions.source_file · source_row — the audit trail</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
|
||
<h3>Validation & Exceptions</h3>
|
||
<p class="sub">Nothing questionable is silently dropped — it’s surfaced here instead.</p>
|
||
<div class="kv" style="margin-top:6px">
|
||
<dt>missing_column</dt><dd><span class="chip bad"><span class="dot"></span>error</span> a required column wasn’t found — must be fixed before finalizing.</dd>
|
||
<dt>unmapped_column</dt><dd><span class="chip warn"><span class="dot"></span>warning</span> a header we didn’t recognize (carried through, not used).</dd>
|
||
<dt>duplicate_rows</dt><dd><span class="chip warn"><span class="dot"></span>warning</span> identical rows detected (same settlement+type+order+SKU+time+amount) — e.g. overlapping files.</dd>
|
||
<dt>in_transit_…</dt><dd><span class="chip paid"><span class="dot"></span>info</span> a payout not yet received; noting why its settlement stays receivable.</dd>
|
||
</div>
|
||
</section>
|
||
|
||
<!-- 08 -->
|
||
<section id="worked">
|
||
<h2><span class="h2n">08</span>Worked example — how Jan 2026 reaches $11,110,433</h2>
|
||
<p class="sub">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).</p>
|
||
|
||
<div class="tablewrap"><table>
|
||
<caption>Standard Orders settlement chain — every figure is a sum of column <span class="col">total</span></caption>
|
||
<thead><tr><th>Settlement</th><th class="n">Net orders</th><th>Payout (Transfer)</th><th>Received by Jan‑31?</th><th>Status</th></tr></thead>
|
||
<tbody>
|
||
<tr><td class="num">25327934061</td><td class="n">+4,471,987</td><td>−15,323,854 · Jan 8</td><td>yes</td><td><span class="chip paid"><span class="dot"></span>paid</span></td></tr>
|
||
<tr><td class="num">25434014921</td><td class="n">+3,244,455</td><td>−3,244,605 · Jan 16</td><td>yes</td><td><span class="chip paid"><span class="dot"></span>paid</span></td></tr>
|
||
<tr><td class="num">25468048861</td><td class="n">+9,556,111</td><td>−9,555,842 · Jan 30</td><td><b>no — in transit</b></td><td><span class="chip recv"><span class="dot"></span>receivable</span></td></tr>
|
||
<tr><td class="num">25546871121</td><td class="n">+1,486,342</td><td>— (February)</td><td>no</td><td><span class="chip recv"><span class="dot"></span>receivable</span></td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
|
||
<p>The Jan‑30 payout was initiated one day before month‑end, so with a 2‑day clearing‑lag it counts
|
||
as <b>in‑transit</b> — which keeps settlement <span class="num">25468048861</span> fully
|
||
receivable. Summing the two receivable batches (and adding the Invoiced stream and reserve):</p>
|
||
|
||
<div class="formula">
|
||
Standard receivable <span class="k">=</span> 9,556,111 <span class="k">+</span> 1,486,342 <span class="k">=</span> <span class="hl">11,042,453.21</span><br>
|
||
Invoiced receivable <span class="k">=</span> <span class="hl">67,854.71</span> <span class="c"># settlements 25342616501 + 25564376091</span><br>
|
||
additional_sales <span class="k">=</span> 11,110,307.92<br>
|
||
receivable_local <span class="k">=</span> ROUND( 125.44 reserve <span class="k">+</span> 11,110,307.92 ) <span class="k">=</span> <span class="hl">11,110,433</span>
|
||
</div>
|
||
<div class="note amber"><b>Why this is trustworthy:</b> an automated test processes the three real
|
||
January files and asserts this equals the hand‑prepared workbook’s <code>Detail!D11</code>,
|
||
<code>C10</code>, <code>B10</code> and <code>C9</code> — every cell matches to the penny.</div>
|
||
</section>
|
||
|
||
<!-- 09 -->
|
||
<section id="excel">
|
||
<h2><span class="h2n">09</span>The Excel workbook it generates</h2>
|
||
<p class="sub">“Download Full A/R Aging Excel” produces a complete month‑end working file with
|
||
<b>live formulas</b> — 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.</p>
|
||
<div class="tablewrap"><table>
|
||
<thead><tr><th>Sheet</th><th>What it holds</th></tr></thead>
|
||
<tbody>
|
||
<tr><td><b>Summary</b></td><td>A/R Aging Summary — per‑marketplace × aging band, TOTAL, allowance, Net Receivable. Pulls from <code>Detail</code>.</td></tr>
|
||
<tr><td><b>Detail</b></td><td>The roll‑forward: additional sales, reserve, <code>ROUND</code>, rate, and USD subtotal — with cross‑sheet formulas.</td></tr>
|
||
<tr><td><b>COA</b></td><td>The FX table (local amount × rate = USD) per marketplace.</td></tr>
|
||
<tr><td><b>USA (1), USA (2)…</b></td><td>Every receivable transaction row, with the bottom <code>SUMIFS</code> subtotals the Detail sheet references. Split across sheets to stay under Excel’s row limit.</td></tr>
|
||
<tr><td><b>Reconciliation</b></td><td>The Rule‑4 breakdown and status.</td></tr>
|
||
<tr><td><b>Exceptions</b></td><td>Everything from the Validation screen.</td></tr>
|
||
<tr><td><b>Processing Audit Trail</b></td><td>Generation time, inputs, file names + <b>SHA‑256 hashes</b>, row counts, FX rates, reserves, app version.</td></tr>
|
||
</tbody>
|
||
</table></div>
|
||
</section>
|
||
|
||
<!-- 10 -->
|
||
<section id="glossary">
|
||
<h2><span class="h2n">10</span>Key terms</h2>
|
||
<div class="grid cols-2">
|
||
<div class="rule"><h3>Settlement</h3><p>A batch of Amazon transactions paid out together. Its
|
||
<span class="col">settlement id</span> increases over time, so a higher id is newer.</p></div>
|
||
<div class="rule"><h3>Transfer</h3><p>A row of <span class="col">type = Transfer</span> — an actual
|
||
bank payout. Negative. Excluded from the receivable; used as the “received payment”.</p></div>
|
||
<div class="rule"><h3>Clearing‑lag</h3><p>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.</p></div>
|
||
<div class="rule"><h3>Additional sales after last payment</h3><p>The receivable base — the summed
|
||
<span class="col">total</span> of all order rows in settlements not yet paid.</p></div>
|
||
<div class="rule"><h3>Reserve / Net Closing Balance</h3><p>A small (~0) Finance‑maintained
|
||
reconciling figure carried from the prior close. Entered by you; barely moves the total.</p></div>
|
||
<div class="rule"><h3>Account type</h3><p>Amazon splits activity into
|
||
<span class="col">Standard Orders</span> and <span class="col">Invoiced Orders</span>; each has
|
||
its own settlement chain and its own paid/receivable boundary.</p></div>
|
||
</div>
|
||
</section>
|
||
|
||
<footer>
|
||
Amazon Accounts Receivable Aging · month‑end closing dashboard. All figures on the dashboard are
|
||
filtered sums of the source <span class="col">total</span> column, grouped by settlement and
|
||
classified by the clearing‑lag rule. Processing is local; no data leaves the company server.
|
||
</footer>
|
||
</main>
|
||
</div>
|