I've spent years looking at aging reports from the other side, after the accounts in the far right column were already given up on. Here's how I'd build one with AI today, and what I'd do with what it shows.
Step 1: Start with a clean export
AI is only as good as the file you give it. Export open invoices from your accounting system (QuickBooks, Xero, NetSuite, Sage Intacct, Business Central or whatever you run) with one row per open invoice and these columns:
- Customer name
- Invoice number
- Invoice date
- Due date
- Open amount
- Last contact date (from your CRM, email or collections notes)
- Dispute status (none, open, resolved)
- Customer state or country
Pick one as-of date, usually the period end, and use it on every row. Tie the total open amount back to your AR control account before you do anything else. If the totals don't match, fix that first.
A note on data. Check your company's AI policy before you upload customer data to any tool. Many teams replace customer names with an ID for the AI step and map them back afterward.
Step 2: Let AI write the formulas
You don't need to remember Excel syntax. Describe your columns and ask for the formula. Here is an example prompt that works in ChatGPT or Copilot in Excel:
I have an Excel table named AR with columns Customer, Invoice, InvoiceDate, DueDate, OpenAmount, LastContact, DisputeStatus and State. The as-of date is in cell B1. Add columns for Days Past Due (zero if not yet due), Aging Bucket (Current, 1-30, 31-60, 61-90, 91-120, Over 120), and Days Since Last Contact. Give me the formulas and explain each one.
You'll get something close to this (example formulas, test them on your own file):
- Days Past Due:
=MAX(0, $B$1 - [@DueDate]) - Aging Bucket:
=IFS([@DaysPastDue]=0,"Current",[@DaysPastDue]<=30,"1-30",[@DaysPastDue]<=60,"31-60",[@DaysPastDue]<=90,"61-90",[@DaysPastDue]<=120,"91-120",TRUE,"Over 120") - Days Since Last Contact:
=IF([@LastContact]="", "No contact logged", $B$1 - [@LastContact])
Split the old balances into 91-120 and Over 120 instead of one "90+" bucket. That's where the story usually is.
Step 3: Add the flags most aging reports leave out
A standard aging report tells you how old a balance is. It doesn't tell you whether anyone is working it. Add these columns and your report becomes a worklist.
Stopped working flag. Mark any account that is past 90 days and hasn't had a logged contact in 60 days or more. Example formula: =IF(AND([@DaysPastDue]>90, OR([@LastContact]="", $B$1-[@LastContact]>60)), "Stopped", ""). Adjust the thresholds to your own terms. The point is to see the balances that went quiet on your side, not just the customer's.
Dispute status. A disputed invoice and an ignored invoice need different handling. Keep disputes in their own column so they don't get chased like a simple late payment, and so they don't hide in the 90+ bucket forever.
Customer concentration. Ask for each customer's open balance as a percent of total AR, and each customer's 90+ balance as a percent of total 90+. If two or three customers make up most of your oldest bucket, that's a different conversation than a long tail of small accounts.
In-statute check. Every state and country sets a time limit for collecting a business debt, and the clock usually runs from the due date or the last payment or acknowledgment. Add a column with the limit for each customer's state, and flag anything getting close. See our B2B statute of limitations by state page for a starting point, and confirm the rule for your contracts.
Days sales outstanding (DSO). Ask AI to calculate DSO for the period and compare it to last quarter. DSO tells you the trend. The flags above tell you which accounts are driving it. More on that in how to reduce DSO without hiring more AR staff.
Step 4: Turn it into a dashboard and a summary
Once the columns are in place, ask for the views your CFO or owner actually reads. Example prompts:
- "Build a PivotTable of open amount by customer and aging bucket, sorted by Over 120 balance, largest first."
- "Make a stacked column chart of total AR by aging bucket for the last six month-ends."
- "List every account flagged Stopped, with balance, days past due, days since last contact and dispute status."
- "Write a five-sentence summary of our AR position for the weekly cash meeting. Use only the numbers in the table and say which accounts changed buckets since last week."
Copilot in Excel, ChatGPT's data analysis, Power BI and most modern AR platforms can all do this. Whichever you use, check the totals against your ledger. AI can misread a column or round a number, and it doesn't know which customer just promised a check.
Step 5: Decide what happens to each flagged account
This is the step most reports skip. An aging report that gets prettier every month but has the same names in the Over 120 column isn't helping. Sort your flagged accounts into groups:
Keep working it in-house when
- The customer is still buying from you.
- They paid recently or made a partial payment.
- A dispute is actively moving toward a fix.
- Sales has a live reason to handle the relationship.
Hand it off when
- It's flagged Stopped: nobody has worked it in 60+ days.
- Reminders and calls have gone unanswered.
- It was reserved, written off or returned by a prior agency.
- It's still inside the statute of limitations.
A third group is accounts that are outside the statute, were settled or released, or were discharged in bankruptcy. Those come off the worklist. For more on keeping customers you want to keep, see a controller's guide to stopped accounts, and for the write-off decision, should you write off or recover receivables over 120 days old?
What to do with the accounts your report flags
The accounts in the "hand it off" column are what we call abandoned receivables. They're still on the report, or they were written off last year, but nobody is actively working them. That's usually not a judgment call. Your team is busy with current invoices, and the old ones fall to the bottom.
If you've stopped working it, we'll work it.
Zindo recovers abandoned B2B receivables with an escalating sequence of demand letters on attorney letterhead. Here's how it works:
- You approve every message. Nothing reaches your customer that you haven't cleared. Customers you want to keep stay with your team.
- Debtors pay you directly. Funds never sit with Zindo.
- 25% after collection. No retainer and nothing upfront. If we don't collect, you don't pay. See what contingency receivables recovery costs.
- $100,000 minimum in B2B receivables you've stopped working, per company. No per-claim minimum.
- Light to start. The same six fields your AI report already has: debtor name, invoice amount, invoice and due date, last activity, any dispute or payment history, and the contact on file. No data room or systems access.
- Recovery Dashboard. Every account, step and payment, so the results show up in your next aging report too.
What the results look like
- More than 50% recovered on in-statute accounts.*
- $12M+ recovered for a single client.
- Files worked since 2018, in 18 countries.
- One anonymized company placed $211,816 in stopped accounts. Zindo recovered $125,000, or 59%.
More in our case studies.
, counting only accounts within the applicable statute of limitations. Prior results do not guarantee a similar outcome.
The Dare
You have two choices with the accounts your report flags. Leave them where they are, or send one file and let us show you what it's worth. You pay nothing unless we collect.
or call (830) 483-8443.