---
title: "AR aging report with a collections call list | Oplema"
description: "Build an AR aging report that ranks who to call first by days late and payment history, in an interactive example with fictional invoices and payments."
url: "https://oplema.com/use-cases/ar-aging"
updated: "2026-11-04"
---

# 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](https://oplema.com/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](https://oplema.com/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.
   | Invoices | Rows |
   | --- | --- |
   | With a payment | 41 |
   | With no payment | 28 |
2. ### 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+ |
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%.
   | Customer | Paid of what's due | Label |
   | --- | --- | --- |
   | Cedar Goods | 50% | Paying around an invoice |
   | Fern Home | 0% | 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 late | Invoices | Balance |
   | --- | --- | --- |
   | Any overdue | 23 | £53,112 |

## Move the date and the list changes

The flow ends in a [Vista](https://oplema.com/vista) dashboard. Change Reporting date to 3 July and watch Harbour Tea in the chart.

[Skip the interactive dashboard](https://oplema.com/use-cases/ar-aging#dashboard-end)

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](https://oplema.com/connectors).

Tarn & Tin's founder, Tom, asks [which products earn most](https://oplema.com/use-cases/contribution-margin).

How we built it

### Invoices

69 rows from your billing system.

Required columns: `invoice_id` `customer_id` `customer` `invoice_date` `due_date` `invoice_total`

### Payment allocations

45 rows, each linked to one invoice.

Required columns: `invoice_id` `payment_date` `amount`

### See the steps run

Select Join and open Output Left: those are the 28 unpaid invoices, INV-1013 among them.

[Skip the interactive flow](https://oplema.com/use-cases/ar-aging#workflow-end)

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.

[Try it on your exports](https://oplema.com/editor?path=%2F%3Fopen%3Dstrata) [Read the docs](https://oplema.com/docs)
