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
- Aging metrics: total AR, weighted average days outstanding, concentration risk
- Collection priority scoring per customer (HIGH / MEDIUM / LOW / MONITOR)
- Recommended collection actions per customer
- Bad debt reserve (percentage-of-aging method)
- Payment pattern analysis from GL history
- DSO: current period and rolling 3-month
- CDC: which customers improved or deteriorated since last run
- 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:
CODEBLOCK0
Usage
CODEBLOCK1
Output
Default location: INLINECODE4
Tabs:
| Tab | Contents |
|---|
| AR Summary | Aging snapshot by bucket, key metrics, concentration risk |
| Aging Detail |
Invoice-level list: customer, date, due date, balance, bucket |
| Collection Priority | Sorted action list: HIGH/MEDIUM/LOW/MONITOR with recommended actions |
| Payment Patterns | Avg days to pay per customer, vs. terms, reliability rating |
| Bad Debt Reserve | Percentage-of-aging reserve calc + suggested journal entry |
| DSO Analysis | Current and rolling 3-month DSO, monthly revenue detail |
| CDC Log | Changes since last run: improved / deteriorated / new / cleared |
Collection Priority Logic
| Priority | Criteria | Recommended Action |
|---|
| HIGH | 90+ days past due OR balance > $5K | Escalate / demand letter / write-off review |
| MEDIUM |
61-90 days OR balance > $2.5K | Follow-up call |
| LOW | 31-60 days | Send email reminder |
| MONITOR | Current or 1-30 days | Standard review next cycle |
Bad Debt Reserve Rates (Percentage-of-Aging)
3% |
| 31-60 | 10% |
| 61-90 | 25% |
| 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: INLINECODE5
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
CODEBLOCK2
Related Pipelines
- -
pl-deep-analysis.py — GL drill-down, P&L variance, accrual proposals - INLINECODE7 — KPI dashboard (includes DSO as a KPI)
- INLINECODE8 — Bank rec (not AR-specific)
- INLINECODE9 — BvA (revenue-side context for AR)
Clients
Configure AR applicability per client in CLIENT_AR_CONFIG or via clients/{slug}/sop.md.
AR Collections & Aging Analysis 技能
功能说明
运行AR收款与账龄分析管道（scripts/pipelines/ar-collections.py），从QBO数据生成控制器级别的应收账款工作簿。
产出内容：
- 1. AR账龄分组：当前 | 1-30天 | 31-60天 | 61-90天 | 90天以上
- 账龄指标：总AR、加权平均逾期天数、集中度风险
- 每位客户的收款优先级评分（高/中/低/监控）
- 每位客户的建议收款行动
- 坏账准备（账龄百分比法）
- 总账历史付款模式分析
- DSO：当期及滚动3个月
- CDC：自上次运行以来客户状况改善或恶化情况
- Excel工作簿（7个标签页）
使用时机
适用场景：
- - 客户要求提供AR账龄报告、收款状态或DSO
- 月度结账包含AR审核
- 需要了解谁欠款及如何处理
- 月末需要计算坏账准备
- 审计师或投资者要求提供AR账龄表
不适用场景：
- - 客户SOP规定AR不适用（管道优雅退出——无需报告）
- 客户在销售点收款（如SB Paulson / Willo Salons）
- 为非QBO客户运行（无数据源）
- 用户需要损益表或资产负债表（请使用pl-deep-analysis或client-dashboard）
SOP门控
管道在拉取任何数据前自动检查客户SOP：
- - sb-paulson → 优雅退出并附带说明（POS收款，无AR）
- 未知slug → 检查SOP markdown中AR禁用信号，默认为AR适用
要添加新客户的AR状态，请更新管道中的CLIENTARCONFIG，或在clients/{slug}/sop.md中添加以下标记：
AR账龄： ❌ 不适用（POS收款）
使用方法
bash
标准运行——截至月末
python3 scripts/pipelines/ar-collections.py --slug <客户slug> --as-of 2026-03-31
自定义输出目录
python3 scripts/pipelines/ar-collections.py --slug <客户slug> --as-of 2026-03-31 --out ~/Desktop/reports
跳过GL拉取（更快，无付款模式分析）
python3 scripts/pipelines/ar-collections.py --slug <客户slug> --as-of 2026-03-31 --skip-gl
QBO沙箱
python3 scripts/pipelines/ar-collections.py --slug <客户slug> --as-of 2026-03-31 --sandbox
无AR客户——优雅退出
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31
输出
默认位置： reports/ar-collections/ar-collections{slug}{as-of}.xlsx
标签页：
| 标签页 | 内容 |
|---|
| AR汇总 | 按分组账龄快照、关键指标、集中度风险 |
| 账龄明细 |
发票级列表：客户、日期、到期日、余额、分组 |
| 收款优先级 | 排序行动列表：高/中/低/监控及建议行动 |
| 付款模式 | 每位客户平均付款天数、与条款对比、可靠性评级 |
| 坏账准备 | 账龄百分比法准备金计算 + 建议会计分录 |
| DSO分析 | 当期及滚动3个月DSO、月度收入明细 |
| CDC日志 | 自上次运行以来的变化：改善/恶化/新增/结清 |
收款优先级逻辑
| 优先级 | 标准 | 建议行动 |
|---|
| 高 | 逾期90天以上或余额>5K美元 | 升级处理/催款函/核销审核 |
| 中 |
逾期61-90天或余额>2.5K美元 | 跟进电话 |
| 低 | 逾期31-60天 | 发送邮件提醒 |
| 监控 | 当前或1-30天 | 下周期标准审核 |
坏账准备率（账龄百分比法）
3% |
| 31-60天 | 10% |
| 61-90天 | 25% |
| 90天以上 | 50% |
DSO公式
- - 当期DSO = (AR余额 ÷ 当期收入) × 期间天数
- 滚动DSO = AR余额 ÷ (3个月收入 ÷ 90天)
CDC缓存
缓存位置：.cache/ar-collections/{slug}.json
每次运行保存客户余额和最差分组。下次运行计算：
- - 改善 — 余额减少或分组提前
- 恶化 — 余额增加或分组延后
- 新增 — 首次出现在AR中
- 结清 — 余额归零（已收款）
要求
bash
pip install openpyxl
Node.js QBO客户端必须已认证
node bin/qbo info {slug} # 从您的QBO集成目录
相关管道
- - pl-deep-analysis.py — 总账钻取、损益差异、应计建议
- client-dashboard.py — KPI仪表板（包含DSO作为KPI）
- bank-reconciliation.py — 银行对账（非AR专用）
- budget-vs-actual.py — 预算与实际对比（AR的收入侧背景）
客户
通过CLIENTARCONFIG或clients/{slug}/sop.md为每位客户配置AR适用性。
