Articles

How do you forecast collections from an AR aging?

A collection curve for each aging bucket, applied to the balance at week 0, and why days sales outstanding is too blunt to forecast with.

To forecast collections from an aging, apply a collection curve to each aging bucket: the share of the bucket's balance expected in week 1, week 2 and so on, older buckets paying slower and less. In the worked case receivables of $32,320,000 at week 0 bring $3,500,000 in week 1 and $29,618,000 over 13 weeks; invoices not yet issued bring the other $24,361,425.

Worked in full in The 13-Week Cash Flow Handbook by Julian R. Sterling, with every figure reproduced in five free workbooks.Get the DRM-free PDF, $19.99 →

Why the aging, not days sales outstanding

Days sales outstanding compresses the whole ledger into one average. The case company, a fictional manufacturer, has receivables of $32,320,000 against $50,960,000 of invoices over the 13 weeks before week 0, so DSO is $32,320,000 / ($50,960,000 / 91) = 57.7 days. That number cannot tell you that a current invoice and an invoice 120 days past due behave differently, or that one large customer pays in eight weeks whatever its terms say. The aging can, because it keeps each balance in its bucket and each customer on its own line.

Step 1: a curve for each bucket

A collection curve says what share of a bucket's balance at week 0 arrives in each week that follows. It is measured, not assumed: take the agings of the last few months and see what each bucket actually paid, week by week. The share collected falls with age, and the curve of the oldest bucket never reaches 100 percent.

Fictional case, aging at week 0, dollars
BucketBalance at week 0Week 1 shareWeek 1 cashCollected by week 13Share
Current (not yet due)$21,300,0008%$1,704,000$20,874,00098%
1 to 30 days past due$6,600,00020%$1,320,000$6,138,00093%
31 to 60 days past due$2,520,00015%$378,000$2,016,00080%
61 to 90 days past due$1,000,0008%$80,000$500,00050%
Over 90 days past due$900,0002%$18,000$90,00010%
Total$32,320,000$3,500,000$29,618,00092%

The calculation for one cell is a single multiplication: the 31 to 60 day bucket holds $2,520,000, its curve takes 15 percent in week 1, so it brings $2,520,000 x 15% = $378,000. In a spreadsheet, the curve sits in its own input row, and the forecast cell reads =ROUND(balance*INDEX(curve,1,week),0), so a slower bucket is one input to change.

The trap. The over 90 day bucket holds $900,000 and the case expects $90,000 of it in 13 weeks. A forecast built on DSO would collect it like any other balance. The difference is cash that is in the borrowing base nowhere and in the forecast only if someone forgets how old it is.

Step 2: customers who pay on their own clock

Large customers get their own line, because their payment habit is worth more than the average. Measure each one's lag from invoice to cash over the last 26 weeks. In the case, Dunstrey Mechanical Supply, the largest customer at 22 percent of invoices, is on 45-day terms but pays eight weeks after the invoice; Fellowmere Plumbing Distributors, on 60 days, pays after nine.

Step 3: new invoices, net of credit memos

From the order book and expected new orders, each week's invoices are split by customer and reduced by credit memos (3.5 percent of invoices in the case) before they turn into cash. Dunstrey's invoices of week 1 total $880,000; credit memos take $30,800; the lag of eight weeks puts $849,200 in week 9. Over the 13 weeks, receipts from new invoices add up to $24,361,425, and total receipts to $53,979,425.

Step 4: check the curves every week

Once actuals come in, compare each bucket's collections with its curve. A bucket that pays slower for two weeks running is a curve to re-measure, not a timing variance to wait out. The checks that keep the build honest are simple: the aging ties to the receivables on the borrowing base certificate, no bucket collects more than it holds, and every invoice is collected once, credited, or still open at the end of the horizon.

Questions people ask

How do you forecast cash collections from accounts receivable?

Split the receivables at week 0 by aging bucket, apply to each bucket a collection curve measured from past agings, and add receipts from new invoices after each customer's payment lag. In the case, $32,320,000 of receivables bring $29,618,000 in 13 weeks.

What is a collection curve?

The share of an aging bucket's balance at week 0 that is collected in each following week. It is measured from past agings and falls with the age of the bucket.

Can you forecast collections with DSO?

As a cross-check only. DSO averages the whole ledger (57.7 days in the case) and cannot tell a current invoice from one 120 days past due.

How do credit memos affect the collections forecast?

They reduce the invoice before it becomes cash. In the case, $880,000 of invoices less $30,800 of credit memos gives a receipt of $849,200.

Read the whole case

This article is one step of The 13-Week Cash Flow Handbook. The book follows one company through seventeen weeks: receipts from the aging, the borrowing base every Sunday, the variance report, the roll, five levers and a DIP budget in the conditional, with every figure a live formula in the free workbooks.

Paperback PDF edition, $19.99 Book page and free files

Free: the 13-week cash flow, ABL availability and variance template in Excel.

Also on this site

Case figures computed by the book's model; the company is fictional and every balance, rate and covenant level is a teaching assumption. Figures checked on 17 November 2026.