AR Aging Tie-Out
Almost everyone ties the aging total to the general ledger and stops. That test is necessary and
nearly worthless on its own, because the total can agree while the aging itself is wrong — and
the aging is what drives the allowance, the collectibility discussion, and the confirmation
sample.
Four things go wrong beneath a correct total:
- Mis-bucketed invoices. Aging reports commonly bucket from the wrong date, or fail to
re-age after a partial payment or a credit application. So this skill recomputes every
bucket from the invoice date and compares rather than trusting the column the report printed.
- Netted credit balances. A customer with a $90,000 open invoice and a $85,000 unapplied
credit shows as $5,000 current. Both numbers matter and the netting hides them.
- Unapplied cash sitting in the aging as a negative current balance, which understates real
exposure and often means cash was received against an invoice nobody closed.
- Invoices dated after period end included in the aging — a cutoff error that overstates both
revenue and receivables.
The gate
No clean workpaper unless:
- The aging total equals the GL control account balance. You supply the GL figure.
- The buckets foot. Bucket amounts sum to each customer's total, and customer totals sum to
the report total.
- Every bucket agrees to a recomputation from the invoice date using the stated bucket
boundaries and as-of date. Any invoice in the wrong bucket is reported individually.
- Debits and credits are shown gross, not netted, at customer level.
Inputs
- AR aging detail at invoice level — customer, invoice number, invoice date, due date, original
amount, open balance, and the bucket the report assigned. Invoice-level detail is essential; a
customer-level summary cannot be re-aged.
- GL control account balance for accounts receivable at the same date.
- As-of date and bucket boundaries — the client's own, whatever they are. Do not assume
30/60/90.
- Payment terms, by customer if they vary. Aging past terms is the meaningful measure, not
aging past an arbitrary 30 days.
- Allowance for doubtful accounts balance and how it was computed, if testing the allowance.
- Subsequent cash receipts, if available — the strongest evidence of collectibility and the
best test of existence short of a confirmation.
Step 1 — Tie and re-age
python3 scripts/ar_aging.py \
--aging aging_detail.csv \
--gl-balance 2841655.40 \
--as-of 2025-12-31 \
--buckets 30,60,90,120 \
--allowance 148000 \
--subsequent-receipts receipts.csv \
--client "Fairhaven Distributors" \
--out "Fairhaven - 2025 AR Aging Tie-Out.xlsx"
Seven tests:
- Test 1 — Aging total to GL control account. The one everybody runs.
- Test 2 — Bucket footing. Buckets sum to customer totals, customers sum to the report total.
- Test 3 — Bucket recomputation. Every invoice re-aged from its invoice date against the
stated boundaries. Mis-bucketed invoices are listed individually with both buckets shown. This
is the test that finds real problems.
- Test 4 — Credit balances grossed up. Total debits and total credits shown separately, with
customers whose net balance masks a material credit identified.
- Test 5 — Cutoff. Invoices dated after the as-of date, and invoices with a due date before
their invoice date.
- Test 6 — Internal integrity. Open balance exceeding the original amount, zero-balance rows
carried in the aging, duplicate invoice numbers, missing dates, and invoices with no customer.
- Test 7 — Allowance reasonableness, where an allowance balance is supplied. Reports the
allowance as a percentage of total AR and of each aged bucket, and the implied coverage of
balances past terms.
Step 2 — Read the collectibility picture
The workbook aggregates by customer and by age band, but the useful views are these:
- Aging past terms, not past 30 days. A customer on 60-day terms at 45 days is current. A
customer on 10-day terms at 45 days is a problem. Where terms are supplied the schedule shows
days past terms alongside days past invoice, and they tell different stories.
- Concentration. The largest few customers as a percentage of total AR. A receivable book
where one customer is 40% of the balance carries a different risk than the aging profile alone
suggests, and it belongs in the allowance discussion.
- Balances with no subsequent receipt. Where subsequent receipts are supplied, the schedule
flags aged balances with no payment activity after period end. An old balance that has since
been paid is a collection-timing issue; an old balance with no subsequent activity is a
valuation issue, and possibly an existence one.
- Round-number balances on aged accounts, which sometimes indicate an estimated or
placeholder invoice rather than a real one.
- Credit balances aged into old buckets — an unapplied credit sitting for months usually means
a customer overpaid or was double-billed, and it may be a refund liability rather than a
receivable offset.
Step 3 — Deliver
Workbook tabs:
- Tie-Out Summary — the seven tests, the GL agreement, aging profile, concentration, and the
allowance analysis. The signable page.
- Re-aged Detail — every invoice with the bucket per the report, the recomputed bucket, days
outstanding, days past terms, and any flag. Mis-bucketed invoices sort to the top.
- By Customer — gross debits, gross credits, net balance, bucket distribution, days past
terms on the oldest item, and share of total AR.
- Exceptions — every finding from Tests 3 through 6 with the invoice and dollar effect.
- Credit Balances — all credit balances with age, separated from the debit aging entirely.
- Allowance Analysis — allowance as a percentage of total and of each bucket, coverage of
balances past terms, and the customers driving the exposure.
- Subsequent Receipts — aged balances with and without post-period collection activity.
Then, in chat: whether the aging ties to the GL, how many invoices were mis-bucketed and the
dollars involved, the aging profile past terms, concentration, and the allowance conclusion. Lead
with the mis-bucketing if there is any — it means the aging the client has been relying on for
credit decisions and the allowance is wrong.
What to escalate
- The aging not tying to the control account. Until it does, nothing built on the aging is
reliable. The usual causes are unposted cash, a journal entry made directly to the control
account, and a subledger cut on a different date.
- Journal entries posted directly to the AR control account. These bypass the subledger by
definition, so they are invisible in the aging and are a common route for both error and
concealment. Ask for the control account detail and read every manual entry.
- Material credit balances aged beyond a few months — potentially a refund liability, and
possibly unclaimed property depending on the jurisdiction and dormancy rules. Flag the question;
confirm the applicable rules rather than assuming them.
- Aged balances with no subsequent receipts and no collection activity, particularly where the
customer continues to be invoiced.
- Receivables from related parties or employees mixed into trade AR — a classification and
disclosure matter, and they are rarely subject to the same credit discipline.
- An allowance that has not moved while the aging profile deteriorated. A static allowance
against a worsening book is a judgment that needs documenting, not a rollforward.
- Invoices dated after period end in the aging, which overstates revenue and receivables in
the same stroke.
- Write-offs late in the period clearing balances that were about to become conspicuously
aged. Report the pattern as a fact.
Security posture
Fully local: standard library plus openpyxl. No network calls, no uploads, no telemetry.
Customer names and balances never leave the machine.
Dependencies
pip install openpyxl
Built by Adopt AI — free to use and modify.
Appendix — bundled files
If you installed the .skill package these files are already in place and you can ignore this appendix. If you copied the skill as text, create the files below at the paths shown, alongside your SKILL.md. The skill will not run without them.
scripts/ar_aging.py
#!/usr/bin/env python3
"""
AR aging tie-out: aging <-> GL control account, with every bucket recomputed.
Seven tests. The distinguishing one is Test 3: every invoice is re-aged from its
invoice date against the stated bucket boundaries and compared to the bucket the
report assigned. A total can agree to the GL while the aging itself is wrong, and
the aging is what drives the allowance and the credit decisions.
Usage:
python3 ar_aging.py --aging aging_detail.csv --gl-balance 2841655.40 \
--as-of 2025-12-31 --buckets 30,60,90,120 --allowance 148000 \
--subsequent-receipts receipts.csv \
--client "Fairhaven Distributors" \
--out "Fairhaven - 2025 AR Aging Tie-Out.xlsx"
--aging CSV (invoice level - a customer summary cannot be re-aged):
customer, invoice_no, invoice_date, due_date, original_amount, open_balance,
reported_bucket (optional), terms_days (optional), customer_type (optional)
--subsequent-receipts CSV:
customer, invoice_no, receipt_date, amount
"""
from __future__ import annotations
import argparse
import csv
import sys
from collections import defaultdict
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
try:
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
except ImportError:
sys.exit("openpyxl is required. pip install openpyxl")
ZERO = Decimal("0.00")
MONEY = '#,##0.00;[Red](#,##0.00)'
PCT = '0.0"%"'
DATEF = "yyyy-mm-dd"
HDR_FILL = PatternFill("solid", fgColor="1F3864")
HDR_FONT = Font(bold=True, color="FFFFFF")
OK_FONT = Font(bold=True, color="006100")
BAD_FONT = Font(bold=True, color="9C0006")
BAD_FILL = PatternFill("solid", fgColor="FFC7CE")
WARN_FILL = PatternFill("solid", fgColor="FFEB9C")
SUB_FILL = PatternFill("solid", fgColor="D9E2F3")
TOP = Border(top=Side(style="thin"))
DBL = Border(top=Side(style="thin"), bottom=Side(style="double"))
ROUND_MIN = Decimal("1000")
def dec(raw):
if raw is None:
return None
s = str(raw).strip()
if s in ("", "-", "--", "n/a", "N/A", "None"):
return None
neg = s.startswith("(") and s.endswith(")")
s = s.strip("()").replace(",", "").replace("$", "").strip()
if s.endswith("-"):
neg, s = True, s[:-1]
try:
v = Decimal(s)
except InvalidOperation:
raise ValueError(f"Unparseable amount: {raw!r}")
return -v if neg else v
def d0(raw):
v = dec(raw)
return v if v is not None else ZERO
def clean(s) -> str:
return " ".join(str(s or "").split())
def pdate(raw):
s = clean(raw)
if not s:
return None
for f in ("%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d-%b-%Y", "%Y/%m/%d"):
try:
return datetime.strptime(s.split(" ")[0], f).date()
except ValueError:
continue
raise ValueError(f"Unrecognized date {raw!r}. Prefer ISO YYYY-MM-DD.")
def bucket_labels(bounds: list[int]) -> list[str]:
labels = ["Current"]
prev = 0
for b in bounds:
labels.append(f"{prev + 1}-{b}")
prev = b
labels.append(f"{prev + 1}+")
return labels
def bucket_of(days: int, bounds: list[int], labels: list[str]) -> str:
if days <= 0:
return labels[0]
for i, b in enumerate(bounds):
if days <= b:
return labels[i + 1]
return labels[-1]
def load_aging(path: Path) -> list[dict]:
with path.open(newline="", encoding="utf-8-sig") as fh:
rows = list(csv.DictReader(fh))
if not rows:
sys.exit(f"No rows in {path}")
out = []
for i, r in enumerate(rows, start=1):
cust = clean(r.get("customer"))
out.append({
"row": i,
"customer": cust or "(NO CUSTOMER)",
"invoice_no": clean(r.get("invoice_no")) or f"(no number {i})",
"invoice_date": pdate(r.get("invoice_date")),
"due_date": pdate(r.get("due_date")),
"original_amount": d0(r.get("original_amount")),
"open_balance": d0(r.get("open_balance")),
"reported_bucket": clean(r.get("reported_bucket")),
"terms_days": dec(r.get("terms_days")),
"customer_type": clean(r.get("customer_type")),
"flags": [],
})
return out
def load_receipts(path: Path | None) -> dict:
out = defaultdict(lambda: {"amount": ZERO, "last": None, "count": 0})
if not path or not path.exists():
return out
with path.open(newline="", encoding="utf-8-sig") as fh:
for r in csv.DictReader(fh):
key = (clean(r.get("customer")).lower(), clean(r.get("invoice_no")).lower())
a = out[key]
a["amount"] += d0(r.get("amount"))
a["count"] += 1
d = pdate(r.get("receipt_date"))
if d and (a["last"] is None or d > a["last"]):
a["last"] = d
return out
def is_round(v: Decimal) -> bool:
a = abs(v)
if a < ROUND_MIN:
return False
for m in (Decimal("100000"), Decimal("50000"), Decimal("25000"),
Decimal("10000"), Decimal("5000"), Decimal("1000")):
if m >= a / Decimal(20) and a >= m and a % m == ZERO:
return True
return False
# ----------------------------------------------------------------------- tests
def analyse(rows, as_of: date, bounds, labels, receipts) -> dict:
misbucketed, cutoff, integrity = [], [], []
seen = defaultdict(list)
for x in rows:
inv_d = x["invoice_date"]
if inv_d is None:
x["days"] = None
x["recomputed_bucket"] = "(no invoice date)"
integrity.append(f"{x['customer']} / {x['invoice_no']}: no invoice date - "
f"cannot be aged")
else:
x["days"] = (as_of - inv_d).days
x["recomputed_bucket"] = bucket_of(x["days"], bounds, labels)
terms = x["terms_days"]
if terms is None and x["due_date"] and inv_d:
terms = Decimal((x["due_date"] - inv_d).days)
x["terms_used"] = terms
x["days_past_terms"] = (x["days"] - int(terms)) if (x["days"] is not None
and terms is not None) else None
if x["reported_bucket"] and x["recomputed_bucket"] != "(no invoice date)":
if clean(x["reported_bucket"]).lower() != x["recomputed_bucket"].lower():
x["flags"].append(f"MIS-BUCKETED: report says '{x['reported_bucket']}', "
f"recomputed from invoice date {inv_d} at {x['days']} "
f"days is '{x['recomputed_bucket']}'")
misbucketed.append(x)
if inv_d and inv_d > as_of:
x["flags"].append(f"invoice dated {inv_d}, AFTER the as-of date {as_of} - "
f"cutoff error overstating revenue and receivables")
cutoff.append(x)
if inv_d and x["due_date"] and x["due_date"] < inv_d:
x["flags"].append(f"due date {x['due_date']} precedes invoice date {inv_d}")
cutoff.append(x)
if x["open_balance"] > ZERO and x["original_amount"] > ZERO \
and x["open_balance"] > x["original_amount"]:
x["flags"].append(f"open balance {x['open_balance']:,.2f} exceeds the original "
f"invoice {x['original_amount']:,.2f}")
integrity.append(f"{x['customer']} / {x['invoice_no']}: open balance exceeds "
f"original amount")
if x["open_balance"] == ZERO:
x["flags"].append("zero open balance carried in the aging")
integrity.append(f"{x['customer']} / {x['invoice_no']}: zero balance row in "
f"the aging - clutters the report and distorts item counts")
if x["customer"] == "(NO CUSTOMER)":
integrity.append(f"Row {x['row']} ({x['invoice_no']}): no customer")
if is_round(x["open_balance"]) and x["days"] is not None and x["days"] > bounds[0]:
x["flags"].append("round-number balance on an aged account - confirm this is "
"a real invoice rather than an estimate or placeholder")
seen[(x["customer"].lower(), x["invoice_no"].lower())].append(x["row"])
key = (x["customer"].lower(), x["invoice_no"].lower())
rec = receipts.get(key)
x["subsequent_amount"] = rec["amount"] if rec else ZERO
x["subsequent_last"] = rec["last"] if rec else None
if receipts and x["open_balance"] > ZERO and x["days"] is not None \
and x["days"] > bounds[-1] and not rec:
x["flags"].append("aged beyond the oldest bucket with NO subsequent receipt - "
"a valuation question, and possibly an existence one")
for (c, inv), rowlist in seen.items():
if len(rowlist) > 1:
integrity.append(f"Duplicate invoice number for one customer: {inv} on rows "
f"{rowlist}")
for x in rows:
if x["row"] in rowlist:
x["flags"].append(f"duplicate invoice number within customer "
f"(rows {rowlist})")
return {"misbucketed": misbucketed, "cutoff": cutoff, "integrity": integrity}
def by_customer(rows, labels) -> list[dict]:
agg = {}
for x in rows:
c = agg.setdefault(x["customer"], {
"customer": x["customer"], "debits": ZERO, "credits": ZERO, "net": ZERO,
"buckets": {l: ZERO for l in labels}, "oldest_days": None,
"oldest_past_terms": None, "items": 0,
"customer_type": x["customer_type"], "flags": set(),
})
c["items"] += 1
if x["open_balance"] >= ZERO:
c["debits"] += x["open_balance"]
else:
c["credits"] += x["open_balance"]
c["net"] += x["open_balance"]
if x["recomputed_bucket"] in c["buckets"]:
c["buckets"][x["recomputed_bucket"]] += x["open_balance"]
if x["days"] is not None and x["open_balance"] > ZERO:
if c["oldest_days"] is None or x["days"] > c["oldest_days"]:
c["oldest_days"] = x["days"]
if x["days_past_terms"] is not None and x["open_balance"] > ZERO:
if c["oldest_past_terms"] is None or x["days_past_terms"] > c["oldest_past_terms"]:
c["oldest_past_terms"] = x["days_past_terms"]
if x["customer_type"] and x["customer_type"].lower() in (
"related party", "related-party", "employee", "affiliate", "intercompany"):
c["flags"].add(f"{x['customer_type']} - classification and disclosure matter, "
f"and rarely subject to the same credit discipline")
out = sorted(agg.values(), key=lambda c: c["net"], reverse=True)
return out
# -------------------------------------------------------------------- workbook
def hdr(ws, n, row=1):
for c in range(1, n + 1):
cell = ws.cell(row=row, column=c)
cell.fill, cell.font = HDR_FILL, HDR_FONT
cell.alignment = Alignment(vertical="center", wrap_text=True)
ws.row_dimensions[row].height = 30
def widths(ws, w):
for c, v in w.items():
ws.column_dimensions[get_column_letter(c)].width = v
def sheet_summary(wb, meta, tests, totals, labels, customers, allowance, overall, esc):
ws = wb.active
ws.title = "Tie-Out Summary"
widths(ws, {1: 4, 2: 52, 3: 18, 4: 16, 5: 62})
r = 1
def line(label, a=None, b=None, *, bold=False, size=11, fill=None, border=None,
note=""):
nonlocal r
c = ws.cell(row=r, column=2, value=label)
c.font = Font(bold=bold, size=size)
c.alignment = Alignment(wrap_text=True, vertical="top")
if fill:
c.fill = fill
for col, v in ((3, a), (4, b)):
if v is None:
continue
cc = ws.cell(row=r, column=col,
value=float(v) if isinstance(v, Decimal) else v)
if isinstance(v, Decimal):
cc.number_format = MONEY
cc.font = Font(bold=bold)
if border:
cc.border = border
if note:
n = ws.cell(row=r, column=5, value=note)
n.font = Font(italic=True, size=9)
n.alignment = Alignment(wrap_text=True, vertical="top")
r += 1
line("ACCOUNTS RECEIVABLE AGING TIE-OUT", bold=True, size=14)
r += 1
for k, v in meta.items():
ws.cell(row=r, column=2, value=k).font = Font(bold=True)
ws.cell(row=r, column=3, value=v)
r += 1
r += 1
v = ws.cell(row=r, column=2,
value="TIED OUT - the aging agrees to the control account and every "
"bucket recomputes"
if overall else
"NOT TIED OUT - see the failing test(s). The aging drives the allowance "
"and credit decisions, so a wrong aging matters even when the total "
"agrees.")
v.font = OK_FONT if overall else BAD_FONT
if not overall:
v.fill = BAD_FILL
r += 2
for t in tests:
line(f"TEST {t['num']} - {t['name']}", bold=True, size=12, fill=SUB_FILL)
if t.get("skipped"):
line(" " + t["note"], fill=WARN_FILL)
r += 1
continue
for label, val in t["lines"]:
line(" " + label, val)
if t.get("is_count"):
line(" Items failing", int(t["difference"]), bold=True, border=TOP,
note=t.get("note", ""))
else:
line(" Difference", t["difference"], bold=True, border=TOP,
note=t.get("note", ""))
c = ws.cell(row=r - 1, column=3)
c.font = OK_FONT if t["passed"] else BAD_FONT
if not t["passed"]:
c.fill = BAD_FILL
r += 1
line("AGING PROFILE (recomputed)", bold=True, size=12, fill=SUB_FILL)
total_debits = totals["debits"]
for l in labels:
amt = totals["buckets"][l]
pct = (amt / total_debits * 100) if total_debits else ZERO
ws.cell(row=r, column=2, value=" " + l)
c1 = ws.cell(row=r, column=3, value=float(amt))
c1.number_format = MONEY
c2 = ws.cell(row=r, column=4, value=float(pct))
c2.number_format = PCT
r += 1
r += 1
line("CONCENTRATION", bold=True, size=12, fill=SUB_FILL)
top5 = customers[:5]
tot = totals["net"]
for c in top5:
share = (c["net"] / tot * 100) if tot else ZERO
ws.cell(row=r, column=2, value=f" {c['customer']}")
cc = ws.cell(row=r, column=3, value=float(c["net"]))
cc.number_format = MONEY
cp = ws.cell(row=r, column=4, value=float(share))
cp.number_format = PCT
if share >= 20:
cp.fill = WARN_FILL
r += 1
top5_share = (sum((c["net"] for c in top5), ZERO) / tot * 100) if tot else ZERO
line(" Top five as a share of total AR", None, top5_share, bold=True,
note="Concentration changes the risk profile independently of the aging. A book "
"where one customer dominates belongs in the allowance discussion.")
ws.cell(row=r - 1, column=4).number_format = PCT
r += 1
if allowance is not None:
line("ALLOWANCE ANALYSIS", bold=True, size=12, fill=SUB_FILL)
line(" Allowance for doubtful accounts", allowance)
pct_total = (allowance / total_debits * 100) if total_debits else ZERO
line(" As a percentage of gross receivables", None, float(pct_total))
ws.cell(row=r - 1, column=4).number_format = PCT
past = totals["past_terms"]
line(" Balances past terms", past)
cov = (allowance / past * 100) if past else None
if cov is not None:
line(" Allowance coverage of balances past terms", None, float(cov),
note="Not a conclusion. Compare to history and to the specific "
"customers driving the exposure.")
ws.cell(row=r - 1, column=4).number_format = PCT
r += 1
if esc:
line("ESCALATE", bold=True, size=12, fill=BAD_FILL)
for e in esc:
line(" " + e)
r += 1
r += 1
line("Prepared by / date: __________________ ____________", bold=True)
line("Reviewed by / date: __________________ ____________", bold=True)
def sheet_detail(wb, rows, labels):
ws = wb.create_sheet("Re-aged Detail")
heads = ["Customer", "Invoice", "Invoice date", "Due date", "Terms (d)",
"Original", "Open balance", "Days outstanding", "Days past terms",
"Bucket per report", "Bucket recomputed", "Agrees",
"Subsequent receipts", "Last receipt", "Flags"]
ws.append(heads)
hdr(ws, len(heads))
ordered = sorted(rows, key=lambda x: (0 if any(f.startswith("MIS-BUCKETED")
for f in x["flags"]) else 1,
-(x["days"] or 0)))
for x in ordered:
agrees = ""
if x["reported_bucket"]:
agrees = "OK" if clean(x["reported_bucket"]).lower() == \
x["recomputed_bucket"].lower() else "MIS-BUCKETED"
ws.append([x["customer"], x["invoice_no"], x["invoice_date"], x["due_date"],
float(x["terms_used"]) if x["terms_used"] is not None else None,
float(x["original_amount"]), float(x["open_balance"]),
x["days"], x["days_past_terms"],
x["reported_bucket"] or None, x["recomputed_bucket"], agrees,
float(x["subsequent_amount"]) if x["subsequent_amount"] else None,
x["subsequent_last"], "; ".join(x["flags"])])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
row[2].number_format = DATEF
row[3].number_format = DATEF
row[13].number_format = DATEF
for i in (5, 6, 12):
row[i].number_format = MONEY
if row[11].value == "MIS-BUCKETED":
row[11].font, row[11].fill = BAD_FONT, BAD_FILL
if isinstance(row[6].value, (int, float)) and row[6].value < 0:
row[6].fill = WARN_FILL
if row[14].value:
row[14].fill = WARN_FILL
ws.freeze_panes = "C2"
ws.auto_filter.ref = f"A1:O{max(ws.max_row, 2)}"
widths(ws, {1: 30, 2: 14, 3: 12, 4: 12, 5: 9, 6: 14, 7: 15, 8: 15, 9: 14,
10: 17, 11: 18, 12: 14, 13: 18, 14: 13, 15: 72})
def sheet_customers(wb, customers, labels, total_net):
ws = wb.create_sheet("By Customer")
heads = ["Customer", "Type", "Items", "Gross debits", "Gross credits", "Net balance",
"% of AR", "Oldest days", "Oldest days past terms"] + labels + ["Flags"]
ws.append(heads)
hdr(ws, len(heads))
for c in customers:
share = (c["net"] / total_net * 100) if total_net else ZERO
ws.append([c["customer"], c["customer_type"] or None, c["items"],
float(c["debits"]), float(c["credits"]), float(c["net"]),
float(share), c["oldest_days"], c["oldest_past_terms"]]
+ [float(c["buckets"][l]) for l in labels]
+ ["; ".join(sorted(c["flags"]))])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in (3, 4, 5):
row[i].number_format = MONEY
row[6].number_format = PCT
for i in range(9, 9 + len(labels)):
row[i].number_format = MONEY
if isinstance(row[4].value, (int, float)) and row[4].value:
row[4].fill = WARN_FILL
if isinstance(row[6].value, (int, float)) and row[6].value >= 20:
row[6].fill = WARN_FILL
if row[len(heads) - 1].value:
row[len(heads) - 1].fill = WARN_FILL
ws.freeze_panes = "B2"
w = {1: 32, 2: 18, 3: 7, 4: 16, 5: 16, 6: 15, 7: 10, 8: 12, 9: 20}
for i in range(len(labels)):
w[10 + i] = 14
w[len(heads)] = 70
widths(ws, w)
def sheet_list(wb, title, header, rows, fmt, empty, footer=""):
ws = wb.create_sheet(title)
ws.append(header)
hdr(ws, len(header))
for r in rows:
ws.append(r)
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i, f in fmt.items():
if i < len(row):
row[i].number_format = f
if ws.max_row == 1:
ws.cell(row=2, column=1, value=empty)
elif footer:
rr = ws.max_row + 2
c = ws.cell(row=rr, column=1, value=footer)
c.font = Font(italic=True)
c.alignment = Alignment(wrap_text=True, vertical="top")
ws.freeze_panes = "A2"
widths(ws, {i: 20 for i in range(1, len(header) + 1)} | {1: 32,
len(header): 70})
# ------------------------------------------------------------------------ main
def main() -> int:
ap = argparse.ArgumentParser(description=__doc__)
ap.add_argument("--aging", required=True)
ap.add_argument("--out", required=True)
ap.add_argument("--gl-balance")
ap.add_argument("--as-of", required=True)
ap.add_argument("--buckets", default="30,60,90",
help="comma-separated day boundaries; use the client's own, "
"not an assumed 30/60/90")
ap.add_argument("--allowance")
ap.add_argument("--subsequent-receipts")
ap.add_argument("--client", default="")
ap.add_argument("--force", action="store_true")
args = ap.parse_args()
as_of = pdate(args.as_of)
bounds = [int(b) for b in args.buckets.split(",") if b.strip()]
if not bounds:
sys.exit("--buckets must contain at least one boundary.")
labels = bucket_labels(bounds)
rows = load_aging(Path(args.aging))
receipts = load_receipts(Path(args.subsequent_receipts)
if args.subsequent_receipts else None)
res = analyse(rows, as_of, bounds, labels, receipts)
customers = by_customer(rows, labels)
total_net = sum((x["open_balance"] for x in rows), ZERO)
debits = sum((x["open_balance"] for x in rows if x["open_balance"] > ZERO), ZERO)
credits = sum((x["open_balance"] for x in rows if x["open_balance"] < ZERO), ZERO)
buckets = {l: ZERO for l in labels}
for x in rows:
if x["recomputed_bucket"] in buckets:
buckets[x["recomputed_bucket"]] += x["open_balance"]
past_terms = sum((x["open_balance"] for x in rows
if x["days_past_terms"] is not None and x["days_past_terms"] > 0
and x["open_balance"] > ZERO), ZERO)
totals = {"net": total_net, "debits": debits, "credits": credits,
"buckets": buckets, "past_terms": past_terms}
gl = dec(args.gl_balance)
allowance = dec(args.allowance)
tests = []
if gl is not None:
tests.append({"num": 1, "name": "Aging total agrees to the GL control account",
"lines": [("Aging total per report", total_net),
("GL control account balance", gl)],
"difference": total_net - gl,
"passed": total_net == gl,
"note": "" if total_net == gl else
"Usual causes: unposted cash, a journal entry made directly "
"to the control account (invisible in the subledger by "
"definition), or a subledger cut on a different date."})
else:
tests.append({"num": 1, "name": "Aging total agrees to the GL control account",
"skipped": True, "passed": True, "lines": [], "difference": ZERO,
"note": "NOT PERFORMED - no GL control account balance supplied"})
cust_sum = sum((c["net"] for c in customers), ZERO)
bucket_sum = sum(buckets.values(), ZERO)
tests.append({"num": 2, "name": "Buckets foot to customer totals and to the report",
"lines": [("Sum of recomputed buckets", bucket_sum),
("Sum of customer net balances", cust_sum),
("Aging total", total_net)],
"difference": (bucket_sum - total_net) + (cust_sum - total_net),
"passed": bucket_sum == total_net and cust_sum == total_net})
mis = res["misbucketed"]
mis_val = sum((x["open_balance"] for x in mis), ZERO)
reported_any = any(x["reported_bucket"] for x in rows)
if reported_any:
tests.append({"num": 3,
"name": "Every bucket recomputes from the invoice date",
"lines": [("Invoices with a reported bucket",
Decimal(len([x for x in rows if x["reported_bucket"]]))),
("Mis-bucketed invoices", Decimal(len(mis))),
("Value mis-bucketed", mis_val)],
"difference": Decimal(len(mis)), "is_count": True,
"passed": not mis,
"note": "" if not mis else
"The total can agree to the GL while the aging is wrong. "
"A mis-aged book has been driving the allowance and the "
"credit decisions."})
else:
tests.append({"num": 3, "name": "Bucket recomputation", "skipped": True,
"passed": True, "lines": [], "difference": ZERO,
"note": "NOT PERFORMED - the aging carried no reported_bucket "
"column, so there is nothing to compare the recomputation "
"against. Recomputed buckets are used throughout."})
masked = [c for c in customers if c["credits"] < ZERO and c["debits"] > ZERO]
tests.append({"num": 4, "name": "Credit balances shown gross, not netted",
"lines": [("Gross debits", debits), ("Gross credits", credits),
("Net", total_net),
("Customers with offsetting credits",
Decimal(len(masked)))],
"difference": abs(credits), "passed": True,
"note": "Netting hides both numbers. A customer with a large open "
"invoice and a large unapplied credit shows as a small current "
"balance; both figures matter."})
cut = res["cutoff"]
tests.append({"num": 5, "name": "Cutoff - no invoices dated after the as-of date",
"lines": [("Invoices failing cutoff", Decimal(len(cut))),
("Value", sum((x["open_balance"] for x in cut), ZERO))],
"difference": Decimal(len(cut)), "is_count": True,
"passed": not cut,
"note": "" if not cut else
"An invoice dated after period end in the aging overstates "
"revenue and receivables in the same stroke."})
integ = res["integrity"]
tests.append({"num": 6, "name": "Internal integrity of the aging detail",
"lines": [("Findings", Decimal(len(integ)))],
"difference": Decimal(len(integ)), "is_count": True,
"passed": not integ})
if allowance is not None:
tests.append({"num": 7, "name": "Allowance reasonableness (informational)",
"lines": [("Allowance balance", allowance),
("Gross receivables", debits),
("Balances past terms", past_terms)],
"difference": ZERO, "passed": True,
"note": "Reported for judgement, not pass/fail. Compare to history "
"and to the specific customers driving exposure. A static "
"allowance against a deteriorating book is a judgement that "
"needs documenting."})
else:
tests.append({"num": 7, "name": "Allowance reasonableness", "skipped": True,
"passed": True, "lines": [], "difference": ZERO,
"note": "NOT PERFORMED - no allowance balance supplied"})
esc = []
if gl is not None and total_net != gl:
esc.append(f"The aging does not agree to the GL control account by "
f"{total_net - gl:,.2f}. Nothing built on the aging is reliable until "
f"this is resolved. Ask for the control account detail and read every "
f"manual journal entry posted directly to it - those bypass the "
f"subledger by definition.")
if mis:
esc.append(f"{len(mis)} invoice(s) totalling {mis_val:,.2f} are in the wrong "
f"ageing bucket. The aging the client has been using for the allowance "
f"and for credit decisions is wrong.")
if cut:
esc.append(f"{len(cut)} invoice(s) fail the cutoff test.")
old_credits = [x for x in rows if x["open_balance"] < ZERO
and x["days"] is not None and x["days"] > bounds[-1]]
if old_credits:
esc.append(f"{len(old_credits)} credit balance(s) totalling "
f"{sum((x['open_balance'] for x in old_credits), ZERO):,.2f} aged "
f"beyond the oldest bucket. Likely a customer overpayment or double "
f"billing - potentially a refund liability rather than a receivable "
f"offset, and possibly unclaimed property. Confirm the applicable "
f"dormancy rules rather than assuming them.")
related = [c for c in customers if c["flags"]]
if related:
esc.append(f"{len(related)} customer(s) appear to be related parties, affiliates, "
f"or employees within trade AR - a classification and disclosure "
f"matter.")
if receipts:
noreceipt = [x for x in rows if x["open_balance"] > ZERO
and x["days"] is not None and x["days"] > bounds[-1]
and x["subsequent_amount"] == ZERO]
if noreceipt:
esc.append(f"{len(noreceipt)} aged balance(s) totalling "
f"{sum((x['open_balance'] for x in noreceipt), ZERO):,.2f} have no "
f"subsequent receipt. An old balance since paid is a collection "
f"timing issue; one with no subsequent activity is a valuation "
f"issue and possibly an existence one.")
overall = all(t["passed"] for t in tests)
meta = {
"Client": args.client or "(not stated)",
"As-of date": str(as_of),
"Bucket boundaries (days)": args.buckets,
"Invoices in aging": len(rows),
"Customers": len(customers),
"Subsequent receipts supplied": "yes" if receipts else "no",
"Prepared": datetime.now().strftime("%Y-%m-%d %H:%M"),
}
# ---- console
print("=" * 76)
print("AR AGING TIE-OUT")
print("=" * 76)
print(f"Client : {meta['Client']} As of: {as_of} Buckets: {args.buckets}")
print(f"Invoices {len(rows)} customers {len(customers)}")
print()
for t in tests:
if t.get("skipped"):
print(f"Test {t['num']}: NOT PERFORMED - {t['note']}")
continue
st = "PASS" if t["passed"] else "*** FAIL ***"
if t.get("is_count"):
print(f"Test {t['num']}: {st:<14} {int(t['difference']):>6} item(s) "
f"{t['name']}")
else:
print(f"Test {t['num']}: {st:<14} diff {t['difference']:>15,.2f} "
f"{t['name']}")
print()
print(f"Gross debits {debits:>16,.2f} gross credits {credits:>14,.2f} "
f"net {total_net:>16,.2f}")
print("Aging profile (recomputed):")
for l in labels:
amt = buckets[l]
pct = (amt / debits * 100) if debits else ZERO
print(f" {l:<10} {amt:>16,.2f} {pct:>6.1f}%")
print(f"Past terms {past_terms:>16,.2f}")
if mis:
print(f"\nMIS-BUCKETED INVOICES ({len(mis)}, {mis_val:,.2f}):")
for x in mis[:12]:
print(f" {x['customer'][:24]:<24} {x['invoice_no']:<12} "
f"{x['open_balance']:>13,.2f} report '{x['reported_bucket']}' vs "
f"recomputed '{x['recomputed_bucket']}' at {x['days']}d")
if integ:
print(f"\nINTEGRITY FINDINGS ({len(integ)}):")
for p in integ[:12]:
print(f" ! {p}")
if esc:
print("\nESCALATE:")
for e in esc:
print(f" ! {e}")
if not overall and not args.force:
print("\n" + "=" * 76)
print("WORKBOOK NOT WRITTEN.")
print("A correct total does not make a correct aging. Resolve the failing test(s)")
print("before relying on the aging for the allowance or for credit decisions.")
print("=" * 76)
return 1
wb = Workbook()
sheet_summary(wb, meta, tests, totals, labels, customers, allowance, overall, esc)
sheet_detail(wb, rows, labels)
sheet_customers(wb, customers, labels, total_net)
sheet_list(wb, "Exceptions", ["#", "Finding"],
[[i + 1, p] for i, p in enumerate(integ)], {},
"None - no integrity findings.")
sheet_list(wb, "Credit Balances",
["Customer", "Invoice", "Invoice date", "Days", "Credit balance",
"Recomputed bucket"],
[[x["customer"], x["invoice_no"], x["invoice_date"], x["days"],
float(x["open_balance"]), x["recomputed_bucket"]]
for x in sorted((x for x in rows if x["open_balance"] < ZERO),
key=lambda y: y["open_balance"])],
{2: DATEF, 4: MONEY},
"No credit balances.",
footer="Credit balances are separated from the debit aging entirely. An "
"aged credit is usually an overpayment or a double billing, and may "
"be a refund liability rather than a receivable offset.")
if receipts:
sheet_list(wb, "Subsequent Receipts",
["Customer", "Invoice", "Open balance", "Days", "Subsequent receipts",
"Last receipt", "Status"],
[[x["customer"], x["invoice_no"], float(x["open_balance"]), x["days"],
float(x["subsequent_amount"]), x["subsequent_last"],
"collected after period end" if x["subsequent_amount"] > ZERO
else "NO subsequent receipt"]
for x in sorted((x for x in rows if x["open_balance"] > ZERO),
key=lambda y: -(y["days"] or 0))],
{2: MONEY, 4: MONEY, 5: DATEF},
"No subsequent receipts supplied.",
footer="Subsequent collection is the strongest evidence of "
"collectibility short of a confirmation.")
wb.active = 0
out = Path(args.out)
if not overall:
out = out.with_name(out.stem + " [DOES NOT TIE]" + out.suffix)
out.parent.mkdir(parents=True, exist_ok=True)
wb.save(str(out))
print(f"\n{'TIED OUT' if overall else 'DOES NOT TIE (forced)'}. Workbook: {out}")
return 0 if overall else 1
if __name__ == "__main__":
sys.exit(main())
