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:

text

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

Usage#

bash

# 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:

TabContentsAR SummaryAging snapshot by bucket, key metrics, concentration riskAging DetailInvoice-level list: customer, date, due date, balance, bucketCollection PrioritySorted action list: HIGH/MEDIUM/LOW/MONITOR with recommended actionsPayment PatternsAvg days to pay per customer, vs. terms, reliability ratingBad Debt ReservePercentage-of-aging reserve calc + suggested journal entryDSO AnalysisCurrent and rolling 3-month DSO, monthly revenue detailCDC LogChanges since last run: improved / deteriorated / new / cleared

Collection Priority Logic#

PriorityCriteriaRecommended ActionHIGH90+ days past due OR balance > $5KEscalate / demand letter / write-off reviewMEDIUM61-90 days OR balance > $2.5KFollow-up callLOW31-60 daysSend email reminderMONITORCurrent or 1-30 daysStandard review next cycle

Bad Debt Reserve Rates (Percentage-of-Aging)#

BucketRateCurrent1%1-303%31-6010%61-9025%90+50%

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#

bash

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.