AR aging report with a collections call list
Fern and Cedar each have a 90+ day invoice with £0 paid. Cedar has paid half its bills; Fern has paid none.
The company and data are fictional.
Fern Home paid £0 of £15,104 due
- INV-1003: Unpaid and late
- INV-1015: Unpaid and late
- INV-1016: Unpaid and late
- INV-1038: Unpaid and late
- INV-1039: Unpaid and late
- INV-1046: Unpaid and late
Cedar Goods paid £6,420 of £12,755 due
- INV-1013: Unpaid and late
- INV-1032: Paid
- INV-1033: Part-paid
- INV-1034: Unpaid and late
- INV-1037: Paid
- INV-1049: Paid
- Paid
- Part-paid
- Unpaid and late
- Not due yet
Same bucket (a range of days late), nothing paid. Would you make the same call?
| Invoice | Customer | Days late | Bucket | Paid on it | Balance |
|---|---|---|---|---|---|
| INV-1003 | Fern Home | 132 | 90+ | £0 | £2,957 |
| INV-1013 | Cedar Goods | 111 | 90+ | £0 | £3,384 |
How to decide who to chase first on an AR aging report
An AR aging report shows what customers owe, grouped by how late it is. Priya Nair chases unpaid invoices at Tarn & Tin, a drinkware maker. On June 30 she has one morning of calls and 23 overdue invoices. Which call first?
Why largest and oldest first fails
Most guides say to start with the oldest, largest balances.
Tarn & Tin's aging report, June 30. Over 60 days is shaded.
| Customer | Current | 1-30 days | 31-60 days | 61-90 days | 90+ days | Total |
|---|---|---|---|---|---|---|
| Fern Home | - | - | £6,347 | - | £8,757 | £15,104 |
| Cedar Goods | - | - | - | £2,951 | £3,384 | £6,335 |
| Kestrel Audio | £3,815 | - | £3,179 | - | £5,934 | £12,928 |
| Harbour Tea | - | £3,102 | - | £1,471 | £2,074 | £6,647 |
| Dune Travel | £1,346 | £5,816 | - | £1,532 | £347 | £9,041 |
| Iris Optics | £6,057 | £3,676 | - | - | - | £9,733 |
| Five others | £11,034 | £4,001 | £540 | - | - | £15,575 |
| Total | £22,252 | £16,596 | £10,066 | £5,954 | £20,496 | £75,364 |
| Share of total | 30% | 22% | 13% | 8% | 27% | 100% |
Over 60 days, Cedar owes the second most, but it has paid half its bills. Asking if it can pay is the wrong question. Sort by total and Iris comes third, yet most of its balance isn't due. Priya needs to know what else each customer has paid.
How to build the report from two exports
Priya builds a flow in Strata, Oplema's browser app for data prep, from her invoice and payment exports. It matches payments, then labels each invoice. Oplema's AI assistant, Elara, can help write the bucket formula.
Match payments to invoices.
Total payments up to the reporting date, the day the balances are counted, and keep unpaid invoices.
Filter, Summarize, Join, Union
INV-1013 has no payment, so it stays with the unpaid.
Invoices Rows With a payment 41 With no payment 28 Work out balance, days late and bucket.
One Formula block does all three.
Formula
INV-1013, Cedar Goods.
Balance Days late Bucket £3,384 111 90+ Check what else the customer paid.
Cedar is paying around an invoice: nothing paid on this one, but a later one was paid.
Formula
Not paying means nothing paid of what's due; barely paying, under 25%.
Customer Paid of what's due Label Cedar Goods 50% Paying around an invoice Fern Home 0% Not paying Put the list in call order.
A Filter keeps what's overdue and a Sort orders it, with Cedar behind Fern, Kestrel and Dune.
Filter, Sort
The list on June 30.
Minimum days late Invoices Balance Any overdue 23 £53,112
Move the date and the list changes
The flow ends in a Vista dashboard. Change Reporting date to 3 July and watch Harbour Tea in the chart.
Loading the interactive dashboard. The figures below don't depend on it.The interactive dashboard didn't load. The figures below don't depend on it.
Harbour paid INV-1058 on July 2, so Priya shouldn't chase it. The total still rises as other invoices go overdue.
Who to call and what to ask
Priya's phone script: possible losses first, fixable queries next. The order is her judgment.
| Call | Ask |
|---|---|
| 1. Fern Home | Can they pay? Have we stopped supplying them, or just stopped billing? |
| 2. Kestrel Audio, Dune Travel | Can they pay, and should the next order ship? |
| 3. Cedar Goods | What's wrong with INV-1013 and INV-1034? |
| 4. Harbour Tea | What's wrong with INV-1012? |
| Not yet: Iris Optics | Usual reminder only |
Each label suggests what to ask. It doesn't say why a customer hasn't paid. To rerun the list, load new exports or read invoices and payments through the Xero or QuickBooks connector.
Tarn & Tin's founder, Tom, asks which products earn most.
How we built it
Invoices
69 rows from your billing system.
Required columns: invoice_idcustomer_idcustomerinvoice_datedue_dateinvoice_total
Payment allocations
45 rows, each linked to one invoice.
Required columns: invoice_idpayment_dateamount
See the steps run
Select Join and open Output Left: those are the 28 unpaid invoices, INV-1013 among them.
Loading the interactive flow. The steps above don't depend on it.The interactive flow didn't load. The steps above don't depend on it.
Left to right: the two exports, one reporting date read by the filter and balance formula, then the stages and the call list. 44 of the 45 payments fall by the reporting date.
The formulas in this flow
| Column | Expression |
|---|---|
| Balance | round(invoice_total - paid_to_date, 2) |
| Days late | date_diff('day', due_date, CAST(params.as_of AS DATE)) |
| Paid share | sum(paid_to_date) OVER (PARTITION BY customer_id) / NULLIF(sum(CASE WHEN days_past_due >= 0 THEN invoice_total ELSE 0 END) OVER (PARTITION BY customer_id), 0) |
| Not paying | customer_paid_share = 0 |
| Barely paying | customer_paid_share < 0.25 |
| Paying around an invoice | max(CASE WHEN paid_to_date > 0 THEN invoice_date END) OVER (PARTITION BY customer_id) > invoice_date |
| Call order | CASE WHEN customer_paid_share = 0 THEN 1 WHEN customer_paid_share < 0.25 THEN 2 WHEN pattern = 'No later payment' THEN 4 ELSE 3 END |
What the data can't show
A missing purchase order, a wrong amount or a delivery query could each explain Cedar. The data can't say which.
Kestrel has paid 20.5% of what's due and did pay a later invoice, so it could read either way. Ask both questions on that call.
Building the list from the payments table gives £4,019 and loses all of Fern, because an invoice with no payment never appears there.
We wrote these patterns into the sample, so test the method on your own data.
Definitions
- Outstanding
- Invoice total minus payments dated on or before the reporting date, including VAT.
- Days late
- Days past due: calendar days from the due date to the reporting date.
- Current
- A balance due on or after the reporting date.
- Any overdue
- On the dashboard, one day or more late. A balance due on the reporting date is current.
- Not paying
- The customer has paid nothing of what has fallen due.
- Barely paying
- The customer has paid something, but under a quarter of what has fallen due.
- Paying around an invoice
- Nothing paid on this invoice, but the same customer has paid a later one.
- Part-paid remainder
- This invoice has a payment and a balance left.
- No later payment
- Nothing paid on this invoice or any later one, from a customer who has paid at least a quarter of what's due.
- How it ties out
- Invoiced £159,332.36 minus paid £83,968.69 leaves £75,363.67. Current includes £5,887.72 due on the day. 1 customer has nothing outstanding.
FAQ
- How do you read an aging report?
- Read the bucket totals first, to see how much is late and how late. Then read across the customers holding the oldest buckets, and check what each has paid before you decide what to ask.
- My accounting system already has an aging report. Why add this?
- If its report answers your questions, use it. The flow adds a label on each invoice, steps you can inspect and repeat, and room for data it leaves out, such as CRM owners.
- Where does my data go?
- With Run local it stays in your browser while it runs, but the project still syncs to Oplema Cloud, including rows stored in its blocks, even for guests. Connector traffic passes through Oplema's servers. Anyone who can view a published dashboard can read its input rows.
Build this call list from your exports
Rerun it after each payment run. Local runs are free.