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?

InvoiceCustomerDays lateBucketPaid on itBalance
INV-1003Fern Home13290+£0£2,957
INV-1013Cedar Goods11190+£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.

CustomerCurrent1-30 days31-60 days61-90 days90+ daysTotal
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 total30%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.

  1. 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.

    InvoicesRows
    With a payment41
    With no payment28
  2. Work out balance, days late and bucket.

    One Formula block does all three.

    Formula

    INV-1013, Cedar Goods.

    BalanceDays lateBucket
    £3,38411190+
  3. 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%.

    CustomerPaid of what's dueLabel
    Cedar Goods50%Paying around an invoice
    Fern Home0%Not paying
  4. 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 lateInvoicesBalance
    Any overdue23£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.

Skip the interactive dashboard

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.

CallAsk
1. Fern HomeCan they pay? Have we stopped supplying them, or just stopped billing?
2. Kestrel Audio, Dune TravelCan they pay, and should the next order ship?
3. Cedar GoodsWhat's wrong with INV-1013 and INV-1034?
4. Harbour TeaWhat's wrong with INV-1012?
Not yet: Iris OpticsUsual 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.

Skip the interactive flow

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

ColumnExpression
Balanceround(invoice_total - paid_to_date, 2)
Days latedate_diff('day', due_date, CAST(params.as_of AS DATE))
Paid sharesum(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 payingcustomer_paid_share = 0
Barely payingcustomer_paid_share < 0.25
Paying around an invoicemax(CASE WHEN paid_to_date > 0 THEN invoice_date END) OVER (PARTITION BY customer_id) > invoice_date
Call orderCASE 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.