📎 This article includes 1 downloadable practice file ↓
Cash comes from collections, not sales. A weekly receivables review, who owes what and since when, is one of the most useful reports a finance team runs.
Per-invoice columns
Outstanding: =[@Amount] - [@Received]
Days past due: =MAX(0, AsOn - [@[Due date]])
Bucket: =IF([@Outstanding]=0,"Paid",IF([@[Days past due]]=0,"Not due",IF([@[Days past due]]<=30,"1-30",IF([@[Days past due]]<=60,"31-60",IF([@[Days past due]]<=90,"61-90","90+")))))Age from the due date, and use a fixed as-on date cell rather than TODAY() so the month-end report doesn’t change later. The debtors ageing report covers bucket totals in detail.
Headline numbers
Total outstanding: =SUM(T[Amount]) - SUM(T[Received])
Over 60 days: =SUMPRODUCT((AsOn - T[Due date] > 60) * (T[Amount] - T[Received]))
Customer outstanding: =SUMIFS(T[Amount], T[Customer], A2) - SUMIFS(T[Received], T[Customer], A2)
Collection efficiency %: =SUM(T[Received]) / SUM(T[Amount])
DSO (approx.): =Outstanding / Billed in period × days in periodFollow-up list
Filter invoices with Days past due > 0 and Outstanding > 0, add the customer’s contact, and conditional-format 90+ in red. In Excel 365: =SORT(FILTER(T, (T[Outstanding]>0)*(T[Days past due]>0)), 5, -1).
Where people go wrong
| Mistake | Effect |
|---|---|
| Ageing the invoice amount, not the balance | Part-paid invoices overstated |
| Unapplied receipts ignored | Customers look worse than they are |
| TODAY() in a month-end report | Numbers drift |
Practice
Download the workbook below. Build each figure in the yellow column of the Practice sheet; the Check column turns green when it matches, and the Answers sheet has a working formula for every task.
📎 Practice files for this article
- 📗Practice workbook36 invoices with 45-day credit and part payments, as on 30-Sep-2026.⬇ XLSX · 16 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.