--- name: aging-analysis-ar-ap description: Perform aging analysis on accounts receivable and accounts payable to assess collection risk, optimize payment timing, manage cash flow, and identify potential bad debt. Use when users need AR aging reports, AP aging analysis, days past due breakdown, collection priority lists, payment scheduling based on cash position, bad debt provision calculations, or vendor payment prioritization. Triggers on phrases like "AR aging", "AP aging", "aging report", "days past due", "collection priority", "bad debt provision", "payment schedule AP", "overdue invoices", "aging bucket", "allowance for doubtful accounts", "payment prioritization", or related aging analysis queries. --- # Aging Analysis (AR/AP) Systematically analyze the age distribution of receivables and payables to assess collection risk, optimize payment timing, forecast cash flow, and make informed credit and liquidity decisions. ## Workflow 1. **Generate AR Aging Report** - Pull and structure AR aging data: ``` AR AGING REPORT TEMPLATE ════════════════════════════════════════ Customer | Current | 1-30 | 31-60 | 61-90 | 91-120 | 121+ | Total ───────────────────┼──────────┼───────┼────────┼────────┼─────────┼────────┼──────── Acme Corp | $120,000 | $15K | $8K | $5K | $0 | $0 | $148K Beta Industries | $85,000 | $0 | $0 | $42K | $18K | $12K | $157K Gamma LLC | $0 | $35K | $0 | $0 | $0 | $0 | $35K Delta Co | $50,000 | $0 | $22K | $10K | $5K | $3K | $90K Epsilon Inc | $0 | $0 | $0 | $0 | $0 | $85K | $85K ───────────────────┼──────────┼───────┼────────┼────────┼─────────┼────────┼──────── Total | $255K | $50K | $30K | $57K | $23K | $100K | $515K % of Total | 49.5% | 9.7% | 5.8% | 11.1% | 4.5% | 19.4% | 100% DSO calculation: (Total AR / Total Credit Sales) × Days in Period Current DSO: 52 days (industry benchmark: 35 days) ``` - Calculate aging metrics: ``` AR AGING METRICS ════════════════════════════════════════ → % Current: 49.5% (target: >60%) → % Past Due: 50.5% (target: <40%) → % >60 days: 35.0% (target: <15%) → % >90 days: 23.9% (target: <10%) → Concentration: Top 5 customers = 82% of overdue AR → Trend: % past due increasing 3 consecutive months (RED FLAG) ``` 2. **Generate AP Aging Report** - Pull and structure AP aging data: ``` AP AGING REPORT TEMPLATE ════════════════════════════════════════ Vendor | Current | 1-30 | 31-60 | 61-90 | 91-120 | 121+ | Total ───────────────────┼──────────┼───────┼────────┼────────┼─────────┼────────┼──────── Supplier A | $80,000 | $0 | $0 | $0 | $0 | $0 | $80K Supplier B | $60,000 | $25K | $10K | $5K | $0 | $0 | $100K Supplier C | $0 | $0 | $40K | $20K | $10K | $5K | $75K Supplier D | $45,000 | $0 | $0 | $0 | $0 | $0 | $45K Utility Provider | $3,000 | $2K | $0 | $0 | $0 | $0 | $5K ───────────────────┼──────────┼───────┼────────┼────────┼─────────┼────────┼──────── Total | $188K | $27K | $50K | $25K | $10K | $5K | $305K AP Metrics: → Average days payable outstanding (DPO): 42 days → % Past Due: 30.8% (target: <20%) → Early payment discounts available: $4,200 (if paid within discount window) → Late fee exposure: $1,800 (if not resolved in 14 days) ``` 3. **AR Aging Analysis and Action Planning** - Prioritize collection actions: ``` AR COLLECTION PRIORITY MATRIX ════════════════════════════════════════ Priority 1 — CRITICAL (>120 days, large balances): → Epsilon Inc: $85K >120 days (likely bad debt) · Action: Engage collections agency or legal · Assess charge-off vs. continued collection ROI · Document all collection efforts for write-off → Beta Industries: $30K >91 days · Action: Collections manager call + formal demand letter · Offer structured payment plan (3 months) · Place credit hold on all open orders Priority 2 — HIGH (61-90 days): → Beta Industries: $42K 61-90 days → Delta Co: $10K 61-90 days → Others: $5K · Action: Collections team review, phone call · Investigate root cause (dispute? cash flow issue?) · Escalate to account manager if relationship-sensitive Priority 3 — MODERATE (31-60 days): → Delta Co: $22K 31-60 days → Acme Corp: $8K 31-60 days · Action: Automated reminder email · Include payment link and statement Priority 4 — WATCH (1-30 days past due): → Acme Corp: $15K 1-30 days → Gamma LLC: $35K 1-30 days · Action: Payment reminder at day 7 and day 14 · Monitor for continued aging ``` 4. **Bad Debt Provision Calculation** - Calculate allowance for doubtful accounts: ``` ALLOWANCE FOR DOUBTFUL ACCOUNTS ════════════════════════════════════════ Method 1: Aging Schedule Method (Most Common) Aging Bucket | Balance | Estimated % Uncollectible | Provision ─────────────────┼───────────┼───────────────────────────┼────────── Current | $255,000 | 1% | $2,550 1-30 days | $50,000 | 3% | $1,500 31-60 days | $30,000 | 10% | $3,000 61-90 days | $57,000 | 25% | $14,250 91-120 days | $23,000 | 50% | $11,500 121+ days | $100,000 | 75% | $75,000 ─────────────────┼───────────┼───────────────────────────┼────────── Total | $515,000 | | $107,800 Existing allowance balance: $85,000 Adjustment needed: $107,800 - $85,000 = $22,800 additional provision DR Bad Debt Expense $22,800 CR Allowance for Doubtful Accounts $22,800 Method 2: % of Sales Method → Bad debt expense = Credit sales × Historical bad debt % → Simpler but less precise (doesn't reflect current AR quality) ``` 5. **AP Aging Analysis and Payment Optimization** - Optimize payment timing: ``` AP PAYMENT OPTIMIZATION ════════════════════════════════════════ Category A — PAY IMMEDIATELY (Past due or discount expiring): → Supplier B: $25K 1-30 days past due (late fee risk) → Supplier C: $40K 31-60 days past due (relationship risk) → All invoices within discount window: Pay to capture $4,200 discount (Discount rate: 2/10 net 30; $210K eligible; ROI on early payment: 36% annualized) Category B — PAY ON DUE DATE (Current, within terms): → Supplier A: $80K (due in 18 days) → Supplier D: $45K (due in 25 days) → Utility: $3K (due in 5 days) → Hold until due date to maximize cash on hand → Schedule payment batches to align with cash position Category C — NEGOTIATE EXTENSION (Past due, cash constrained): → Supplier C: $35K >61 days (strategic vendor) → Contact to negotiate payment plan → Prioritize: $20K this month, $15K next month → Document agreement Category D — DISPUTE / HOLD (Questioned charges): → Any AP with open dispute flags → Hold payment pending resolution → Document dispute and timeline ``` 6. **Cash Flow Impact Analysis** - Model AR/AP aging impact on cash: ``` CASH FLOW FORECAST FROM AGING ════════════════════════════════════════ Week 1 Inflows (collections): → Current AR due this week: $65,000 → Past due collections (estimated): $20,000 → Total expected inflow: $85,000 Week 1 Outflows (payments): → AP due this week: $35,000 → Past due payments (committed): $20,000 → Total expected outflow: $55,000 Net Week 1 Cash Flow: +$30,000 Sensitivity: → If collections are 20% slower: Inflow = $68K, Net = +$13K → If payments must be accelerated: Outflow = $75K, Net = +$10K → Combined stress: Inflow = $68K, Outflow = $75K, Net = -$7K ``` 7. **Trend Analysis and Early Warning Indicators** - Monitor aging trends over time: ``` AGING TREND DASHBOARD ════════════════════════════════════════ AR Aging Trend (Last 6 Months): Month | DSO | % Current | % Past Due | % >60 Days | Bad Debt % ──────────┼──────┼───────────┼────────────┼────────────┼─────────── Jan | 45 | 58% | 42% | 18% | 0.8% Feb | 47 | 55% | 45% | 22% | 0.9% Mar | 49 | 53% | 47% | 27% | 1.0% Apr | 51 | 50% | 50% | 31% | 1.2% May | 52 | 49.5% | 50.5% | 35% | 1.3% Early Warning Signals: ⚠ DSO trending up (5 consecutive months) ⚠ % Current below 50% threshold ⚠ Bad debt provision increasing quarter-over-quarter ⚠ Top customer (Beta Industries) has 35% of overdue in >91 days bucket Recommended Actions: → Tighten credit policy for new customers → Review and reduce credit limits for deteriorating accounts → Implement stricter automated dunning at day 14 → Executive review of top 5 overdue accounts ``` ## Integration Points - **ERP**: SAP, Oracle, NetSuite, Dynamics (AR/AP sub-ledger reports) - **CRM**: Salesforce, HubSpot (customer relationship context for collections) - **Credit Bureaus**: D&B, Experian (customer credit score updates) - **Payment Platforms**: ACH, wire transfer systems, payment portals - **BI/Reporting**: Power BI, Tableau (aging dashboards) - **Accounting Software**: QuickBooks, Xero (smaller businesses) - **Collections Software**: HighRadius, Rootware, Versapay ## Edge Cases & Handling ``` EDGE CASES ═══════════════════════════════════════ 1. Write-Off Decision → When to write off: Customer bankrupt, unreachable, or cost of collection > recovery → Charge-off entry: DR Allowance, CR Accounts Receivable → Tax implications: Bad debt deduction requires evidence of worthless debt → Recovery after write-off: Reverse write-off + record collection 2. Related-Party / Intercompany Aging → Intercompany AR/AP should net to zero at consolidation → Aging of intercompany balances indicates process issues → Resolution: Monthly intercompany reconciliation 3. Disputed Invoices in Aging → Segregate disputed amounts from clean aging → Track dispute aging separately (resolution timeline) → Don't apply standard collection actions to disputed amounts 4. Seasonal Business AR Patterns → AR naturally builds before peak season → Adjust aging expectations by season → Pre-position collections resources for post-peak period 5. Vendor Relationship Management (AP) → Strategic vendors: Prioritize payment even when cash-tight → Non-critical vendors: Negotiate extended terms proactively → Track vendor satisfaction: Payment timeliness impacts pricing and terms ``` ## Conversation Examples ### AR Aging Crisis ``` User: "Our AR aging report just came in and $200K is past 90 days. That's 40% of our overdue balance. What's the plan?" AI: "$200K past 90 days is a serious cash flow and credit risk. Here's your immediate action plan: Step 1 — Triage (Today) → Pull the detailed 90+ aging report: customer, invoice number, amount, invoice date, last contact date → Segregate into: · Active disputes (hold collection actions) · Known financial distress (expedite charge-off assessment) · Uncontacted overdue (immediate collections action) · Cooperative but slow (offer payment plans) Step 2 — Collections Sprint (This Week) → Collections manager calls all 90+ accounts (not just emails) → Offer structured payment plans for accounts that want to pay but can't → Send formal demand letters to non-responsive accounts → Place credit holds on all accounts >60 days overdue Step 3 — Bad Debt Assessment (This Week) → For accounts >120 days with no response: Assess charge-off likelihood → Calculate required bad debt provision adjustment → Engage collections agency for top 3 unresponsive accounts Step 4 — Process Fix (Next 30 Days) → Review credit policies: Why did these accounts get this far? → Implement automatic credit holds at 60 days → Add DSO and aging metrics to executive dashboard → Monthly aging review meeting with Sales and Finance Share the aging detail and I'll help you prioritize the collection actions." ``` ### AP Payment Prioritization ``` User: "We have $305K in AP but only $150K in cash. How do we prioritize payments this week?" AI: "Cash-constrained payment prioritization — here's your framework: Priority 1 — Pay immediately (non-negotiable): → Past-due invoices with late fees or penalties → Critical vendors (production materials, payroll service, IT infrastructure) → Invoices within early payment discount window (if discount value > cost of capital) → Regulatory/government obligations (taxes, compliance fees) Priority 2 — Pay on due date (manage timing): → Current invoices not yet due — hold cash as long as terms allow → Schedule payments by due date, front-load only when necessary Priority 3 — Negotiate (cash preservation): → Contact vendors with >60-day past-due balances → Propose payment plans: "We can pay $X this month, $Y next month" → Most vendors prefer predictable partial payment over silence Priority 4 — Disputed / Hold: → Any questioned charges — hold pending resolution → Communicate timeline to vendor Quick calculation: → Past due AP: $75K (must pay) → Current AP due this week: $35K (should pay) → Total committed outflow: $110K → Cash available: $150K → Remaining after committed: $40K buffer If your weekly operating expenses exceed $40K, you need to: → Negotiate 1-2 week extension with Priority 3 vendors → Accelerate AR collections (call top 5 overdue customers) → Consider short-term financing (line of credit, factoring) Want me to help you build a payment priority schedule?" ```