Bad debt analysis by customer, aging and write-off

Bad debt analysis shows which customer balances remain overdue, which were written off and how much was recovered afterward. Use invoice-level records to connect those outcomes, then compare customer segments and invoice cohorts on a consistent basis. An aging report alone cannot show whether a smaller overdue balance came from collection or a write-off.

Start with three different questions

The collections manager needs to know what is overdue now. The controller needs to know which approved write-offs reached the ledger. Finance planning needs to know whether comparable groups of invoices are producing different realized losses.

Those questions use related records but different denominators. Current overdue receivables are an outstanding balance. Write-offs during a month are transactions. A cohort loss rate follows a fixed population of invoices from issue through a stated observation window.

This is diagnostic work. It does not set an accounting allowance or make a credit decision. FASB's credit-loss guidance includes a trade-receivables aging example, but an accounting estimate requires its applicable assumptions and evidence; a historical write-off percentage is not automatically that estimate.

Build one invoice register from the exports

Bring the invoice register, cash applications, credit memos and approved write-off transactions. Match them using entity and invoice ID, rather than customer name alone. Aggregate multiple payment or write-off transactions to the invoice before joining them, or repeated rows will multiply the original invoice amount.

Use this column order in a worksheet called Register: A invoice ID, B customer, C segment, D issue date, E due date, F original amount, G cash applied, H credit memos, I gross write-offs, J open balance, K days overdue and L aging bucket. Store recoveries on previously written-off balances separately, linked to the original invoice.

For a US distributor at September 30, 2026, the financial columns are:

Calculate J2 and fill down:

=F2-G2-H2-I2

The proof is $290,000 = $140,000 + $5,000 + $40,000 + $105,000. Put a control cell below the table; it must return zero:

=SUM(F2:F5)-SUM(G2:I5)-SUM(J2:J5)

The $105,000 must also equal the gross trade receivables aging and the corresponding ledger balance. A write-off and a sales credit memo both reduce the open balance, but preserve separate transaction types because their causes differ.

Age only the balances still open

Put the reporting date in N1 with =DATE(2026,9,30). Days overdue in K2 uses the contractual due date, not the issue date:

=IF(J2=0,0,MAX(0,$N$1-E2))

Assign the bucket in L2:

=IF(J2=0,"Closed",IF(K2=0,"Current",IF(K2<=30,"1-30",IF(K2<=60,"31-60",IF(K2<=90,"61-90","91+")))))

Overdue exposure is $55,000: $30,000 + $25,000. The balance more than 60 days overdue is $25,000. Calculate those figures directly from the register:

=SUMIFS(J2:J5,K2:K5,">0")
=SUMIFS(J2:J5,K2:K5,">60")

Cedar's invoice is closed because of cash and a write-off. Calling that collection success would be wrong. Use days sales outstanding to measure collection time across the portfolio, with write-offs visible alongside it.

Compare losses on equally mature cohorts

Do not divide this month's write-offs by this month's new invoices and call it a customer loss rate. The write-offs may come from much older sales. Group invoices by issue month and segment, and give every invoice the same follow-up window.

The following separate example follows January 2025 invoices for their first 18 months after issue. The denominator is original credit invoice value after sales credit memos; each recovery is linked to an invoice in that cohort.

With sales in B, write-offs in C and recoveries in D, enter these formulas in E2 and F2:

=C2-D2
=IF(B2=0,"",E2/B2)

The combined rate is $31,000 / $700,000 = 4.43%, rounded to two decimal places. Calculate it from totals, not the average of the segment rates. Retailer losses warrant invoice review; the table alone does not establish whether disputes, customer failure or collection practices caused them.

Where it goes wrong

• Different follow-up windows. Comparing an older cohort's realized losses with a recent cohort hides losses that have not yet emerged.
• Recoveries detached from invoices. Subtracting an old recovery from a new cohort understates the new cohort's losses.
• Policy changes treated as customer changes. Faster write-off approval can move losses into an earlier reporting period. Document the change before interpreting the trend.
• Tax and reporting measures mixed. The IRS explains that a deduction for an uncollectible receivable generally requires prior inclusion in income. Keep the tax review separate from the operational analysis and the accounting allowance.

The controller owns the ledger tie and write-off classification. Collections owns dispute and contact evidence. Together they can explain the changes that a portfolio AR turnover ratio would otherwise compress into one number.

Analyze your receivables file in Covirage

Bring a sample of your own invoice, cash-application and write-off exports to Covirage; it maps the columns, and its tools compute and check aging, cohort losses and reconciliations. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring your file.

See Covirage for finance teams for the supporting finance context.