Cash Collection Prioritizer

Goal

Generate a data-driven, ranked action list of overdue invoices, tailored dunning email templates for each priority band, and an executive AR dashboard so collectors focus on the right customers first.

Inputs to gather

• Company name (identifies context for all outputs).
• Latest AR aging data (customer, invoice ID, amount, currency, due date, days past due – needed to score aging and exposure).
• Latest CRM customer-health data (engagement score, open tickets, renewal date & ACV, churn-risk flag – needed to adjust scores for health and renewal timing).
Before doing any work, ask the user for these inputs in ONE message. Skip anything they already provided. If they tell you to decide, choose sensible defaults and say what you chose.

Workflow

1. Parse the AR aging data into a clean table named “Clean_AR” with columns: Customer Name, Invoice ID, Invoice Amount, Currency, Days Past Due, Original Due Date.
2. Parse the CRM health data into a clean table named “Clean_CRM” with columns: Customer Name, Engagement Score (0-100), Open Ticket Count, Renewal Date, Renewal ACV, Churn Risk (Low/Med/High).
3. Identify missing or inconsistent fields needed for scoring; output a “Data_Issues” note and ask the user for any critical gaps. Pause for their reply before continuing.
4. Join Clean_AR and Clean_CRM on Customer Name to create “Joined”.
5. For each invoice calculate:
   • Aging_Score = Days Past Due ÷ 90 (capped at 1.2)
   • Dispute_Risk_Score = min(Open Ticket Count ÷ 5, 1)
   • Renewal_Weight = 1.2 if Renewal Date is within 120 days, else 0.8
   • Health_Adjust = 1 – (Engagement Score ÷ 100)
   • Collection Priority Score = (Aging_Score × 0.5 + Dispute_Risk_Score × 0.2 + Health_Adjust × 0.3) × Renewal_Weight
   • Priority Band: Critical (≥1), High (0.7–0.99), Medium (0.4–0.69), Low (<0.4)
6. Sort Joined by Collection Priority Score descending and output the scored table.
7. Select the top 20 invoices or all Critical & High band invoices (whichever is larger) and build “Action_List” with: Customer, Invoice ID, Amount, Days Past Due, Priority Band, Recommended Action (Call CFO / Escalate to CSM / Standard Reminder / Hold due to dispute).
8. Group remaining invoices by Priority Band; show counts and total exposure in “Backlog_Summary”.
9. Draft four personalized dunning-email templates—one per Priority Band—with subject lines and two-paragraph bodies using tokens {{Customer_Name}}, {{Invoice_ID}}, {{Amount}}, {{Days_Past_Due}}, {{Renewal_Date}}. Tone: firm yet friendly.
10. Compile an executive dashboard:
    • Total AR exposure and weighted-average Days Past Due
    • Exposure and invoice counts per Priority Band
    • Top 5 customers by exposure with their scores
    • Outstanding data-quality issues
    • 2–3 strategic action recommendations
11. Review all outputs against specifications; if any gap or unresolved data issue remains, request clarification. Otherwise, confirm completion.

Output

Deliver a single report containing:

1. Clean_AR table, Clean_CRM table, Data_Issues note (if any).
2. Scored “Joined” table with priority columns, sorted highest to lowest.
3. “Action_List” section followed by “Backlog_Summary”.
4. Four labeled dunning email templates (Critical, High, Medium, Low).
5. Executive dashboard bullet list.
   Length: concise but complete; tables in markdown pipe format, emails and dashboard in clear labeled sections.