An ageing report answers the credit controller’s daily question: who owes us money, and how late is it?

1. The invoice list

Columns: Customer, Invoice No, Invoice Date, Due Date, Amount, Received. Add:

Balance:        =E2-F2
Days overdue:   =MAX(0, $J$1-D2)          ' J1 = report date, e.g. =TODAY()

2. Buckets with a lookup table

A small table (L1:M5): 0 → Not due, 1 → 1–30, 31 → 31–60, 61 → 61–90, 91 → 90+. Then:

=IF(G2<=0, "Settled", VLOOKUP(H2, $L$1:$M$5, 2, TRUE))

Approximate-match VLOOKUP is ideal for bands — see why TRUE is right here.

3. Pivot by customer

Insert a pivot: Customer in rows, Bucket in columns (ordered Not due → 90+), Balance in values. Add a slicer for Salesperson if you have one.

4. Highlight what needs a call

• Red fill where days overdue > 90.
• Amber for 61–90.
• Sort customers by total 90+ balance.

5. Key metrics

=SUMIFS(G:G, I:I, "90+") / SUM(G:G)       ' share of receivables over 90 days
=SUM(G:G) / (Sales_last_90_days / 90)     ' DSO: days sales outstanding (approx.)