AR Collections & Aging Analysis Skill

What This Does

Runs the AR Collections & Aging Analysis pipeline (scripts/pipelines/ar-collections.py) to produce a controller-level accounts receivable workbook from QBO data.

Produces:

1. AR aging bucketed into Current | 1-30 | 31-60 | 61-90 | 90+ days
2. Aging metrics: total AR, weighted average days outstanding, concentration risk
3. Collection priority scoring per customer (HIGH / MEDIUM / LOW / MONITOR)
4. Recommended collection actions per customer
5. Bad debt reserve (percentage-of-aging method)
6. Payment pattern analysis from GL history
7. DSO: current period and rolling 3-month
8. CDC: which customers improved or deteriorated since last run
9. Excel workbook (7 tabs)

When to Use

Use when:

• Client asks for AR aging report, collections status, or DSO
• Monthly close includes AR review
• Need to know who owes money and what to do about it
• Bad debt reserve needs to be calculated for month-end
• Auditors or investors request AR aging schedule

Do NOT use when:

• Client SOP says AR is not applicable (pipeline exits gracefully — no report needed)
• Client collects at point of sale (e.g., SB Paulson / Willo Salons)
• Running for a non-QBO client (no data source)
• User wants a P&L or balance sheet (use pl-deep-analysis or client-dashboard)

SOP Gate

The pipeline automatically checks the client SOP before pulling any data:

• sb-paulson → exits gracefully with explanation (POS collection, no AR)
• Unknown slugs → checks SOP markdown for AR-disabled signals, defaults to AR-applicable

To add a new client's AR status, update CLIENT_AR_CONFIG in the pipeline, OR add these markers to their clients/{slug}/sop.md:

**AR Aging:** ❌ Not applicable (POS collection)

Usage

# Standard run — as of end of month
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31

# With custom output directory
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --out ~/Desktop/reports

# Skip GL pull (faster, no payment pattern analysis)
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --skip-gl

# QBO sandbox
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --sandbox

# Client with no AR — exits gracefully
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31

Output

Default location: reports/ar-collections/ar-collections_{slug}_{as-of}.xlsx

Tabs:

Collection Priority Logic

Bad Debt Reserve Rates (Percentage-of-Aging)

DSO Formula

• Current DSO = (AR Balance ÷ Current Period Revenue) × Days in Period
• Rolling DSO = AR Balance ÷ (3-Month Revenue ÷ 90 days)

CDC Cache

Cached at: .cache/ar-collections/{slug}.json

Each run saves customer balances and worst buckets. Next run computes:

• Improved — balance decreased or bucket moved earlier
• Deteriorated — balance increased or bucket moved later
• New — first appearance in AR
• Cleared — balance went to zero (collected)

Requirements

pip install openpyxl
# Node.js QBO client must be auth'd
node bin/qbo info {slug}  # from your QBO integration directory

Related Pipelines

• pl-deep-analysis.py — GL drill-down, P&L variance, accrual proposals
• client-dashboard.py — KPI dashboard (includes DSO as a KPI)
• bank-reconciliation.py — Bank rec (not AR-specific)
• budget-vs-actual.py — BvA (revenue-side context for AR)

Clients

Configure AR applicability per client in CLIENT_AR_CONFIG or via clients/{slug}/sop.md.