Debtor Tracking Spreadsheet Outline for Accounts Receivable

Core columns for a debtor register

The debtor register is the accounts receivable equivalent of the payment schedule — a single document that lists every outstanding invoice by customer, with enough detail to track collection progress. Recommended columns include: customer name, invoice number, invoice date, due date, original amount, amount outstanding, currency, contact name, and last action taken.

• Auto-calculate days overdue from the due date
• Use conditional formatting to highlight 30+, 60+ and 90+ day buckets
• Record every collection action (call, email, letter) with date and outcome
• Flag disputed invoices separately to avoid treating them as standard overdue debts

Ageing analysis — what the buckets tell you

Ageing buckets segment your debtors by how long their invoices have been outstanding. The current bucket (not yet due) is healthy; the 1–30 days bucket typically recovers with a simple reminder; the 31–60 days bucket warrants a second notice and a phone call. Invoices in the 61–90 days bucket require a formal final demand, and anything beyond ninety days should be reviewed for provision or referral to a collection agency.

Summarise the total value in each bucket on a separate dashboard tab. This gives the finance director an at-a-glance view of receivables risk without needing to scroll through every line.

Calculating Days Sales Outstanding

Days Sales Outstanding (DSO) measures how long on average it takes to collect payment after raising an invoice. It is calculated as: (total outstanding debtors ÷ total credit sales in the period) × number of days in the period. A rising DSO trend indicates that customers are paying more slowly, which puts pressure on working capital even if revenue is growing.

Track DSO monthly alongside your ageing summary. If DSO is drifting above your standard credit terms, investigate whether specific customers are systematically late and consider tightening their credit limits or terms.

Linking to collection actions

The most common weakness in debtor tracking is that the spreadsheet records what is owed but not what has been done about it. Add a dedicated action log tab, or at minimum a notes column, so that anyone covering the role can see immediately what the last contact was and what the customer committed to. This prevents duplicate chasing and avoids the embarrassment of escalating a debt that the customer has already agreed to settle.

Cross-reference disputed invoices with your late payment reminder letter sequence to ensure letters are only sent on undisputed balances.