Finance AI Skill
Aging Analysis Ar Ap
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, colle...
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
- 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)- 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)- 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- 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)- 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- 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- 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 accountsIntegration 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 termsConversation 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?"