How Do You Use AR Aging to Forecast Cash Collections?
How to turn an AR aging report into a 13-week cash collection forecast in six simple steps — each one demonstrated on a worked example, with how to read it.
Key takeaways
- An AR aging report shows how old each invoice is. A cash collection forecast needs when it will be paid.
- Six steps get you from one to the other: tie out the aging, see what’s overdue, date each invoice, leave out what nobody can date, put the rest into weeks, and roll it forward.
- Overdue doesn’t mean “arriving next week”. An overdue invoice gets a date only when someone knows why it’s late.
- Every dollar on the aging ends up somewhere: in a forecast week, after the forecast, or still in AR with no date. The pieces must add back.
You’ve got the AR aging report open, and it says customers owe you a lot of money — some of it overdue. The question I usually get next: how do I turn this into a forecast of when the cash will actually come in?
Give every invoice on the aging the date you really expect the cash, leave out the ones nobody can date, and check that every dollar ends up somewhere. The aging buckets tell you how old an invoice is. They don’t tell you which week it will be paid.
That matters because the aging is easy to read as a forecast, and it isn’t one. A large 31–60 bucket could be next week’s cash, next month’s, or a problem.
This piece turns the AR aging report into an accounts receivable forecast — the collections half of cash flow forecasting — in six simple steps. How to work out when a customer really pays is in how to forecast cash receipts accurately.
The example we’ll use. Each step is demonstrated on the sample data in our free 13-week AR collection forecast template: a business with 249 open invoices across 80 customers, owing $2,832,120 on 1 September 2026. You can follow the steps with any spreadsheet; the template just does the arithmetic.
Step 1: Export the aging detail and tie it to the balance sheet
Export the accounts receivable aging detail — one line per invoice — and check that its total equals accounts receivable on your balance sheet at the same date.
Use the detail, not the summary: you can’t date a bucket total. For each invoice you need the customer, invoice number, amount, invoice date and due date.
Before you trust the total, clear unapplied customer payments and credit notes. They leave invoices looking open after the cash has already arrived, and you’d forecast that money twice. If the books are behind, fix them first.
Example. The sample’s aging detail totals $2,832,120.
How to read it. That number has to match the balance sheet. If it doesn’t, stop and find out why before going further — usually a report run on a different date, a filter left on, or a journal posted straight to AR with no invoice behind it.
Step 2: See what’s overdue — and how your aging is measured
Total the overdue invoices and look at the buckets. This tells you where to look in the next step — not when the cash will come.
An invoice is overdue when its due date has passed. Before reading the buckets, check how your report measures age: by days past due or by days since the invoice date. Some systems, Xero among them, use days past due; others use the invoice date. “31–60” means two different things depending on which.
Example. 69 invoices worth $862,355 are overdue — 30.4% of the total. Aged by invoice date, the book looks like this:
| Age, by invoice date | Amount |
|---|---|
| 1–30 days | $967,784 |
| 31–60 days | $753,175 |
| 61–90 days | $725,682 |
| 90+ days | $385,479 |
| Total | $2,832,120 |
How to read it. Take invoice INV-10003 to Lakeside Labs for $6,374. It’s 55 days old, so it sits in 31–60 here. But it’s only 8 days past due, so a days-past-due report would show it in 1–30. Neither is wrong; just know which one you’re reading. The overdue list is where step 3 starts.
Step 3: Give each invoice the date you expect the cash
Use the best evidence you have for each invoice: a date the customer has promised, then how that customer usually pays, then the due date. Start with your largest customers and every overdue invoice.
The due date is only the fallback. A customer who always pays two weeks late will pay two weeks late again, and an overdue invoice is not automatically next week’s cash. Which evidence should win when it conflicts is covered in due dates vs payment behavior.
You don’t need to do this for every small invoice. Usually the largest customers and the overdue list cover most of the money; the rest can stay on the due date.
Example. The largest overdue invoice, $81,581 to Frontier Labs, was due on 20 August and is now expected on 22 September — week 4. Lakeside Labs’ invoice, 8 days late, is expected on 16 October — week 7.
How to read it. Every date on an overdue invoice should have a reason behind it — a promise, a payment run, a resolved query. If nobody can say why the customer will pay that week, don’t guess. Leave it for step 4.
Step 4: Keep the invoices nobody can date out of the forecast
An overdue invoice with no expected date doesn’t go into any week. It stays in accounts receivable — and goes on your chase list.
This is what keeps a forecast honest. Putting an undated overdue invoice into next week, every week, shows cash that never comes. Disputed and doubtful invoices belong here too until there’s a date. Whether a doubtful invoice also needs a bad debt allowance in the books is a separate question for your accountant.
Example. 19 overdue invoices worth $131,045 have no date, so they stay in AR. The largest is $51,029 to Cedar Retail Group, 69 days overdue.
How to read it. This is the list to chase this week, largest first. It should be short. If it isn’t, the forecast is guessing about a lot of money.
Step 5: Put the dated invoices into weeks and check every dollar lands
Add up the dated invoices week by week, find the lowest week, and check that the pieces add back to the aging total.
Number the weeks from your start date. Anything dated beyond week 13 sits outside the forecast but is still owed. Receipts that never appear on the aging — card sales, cash sales, marketplace payouts — are forecast separately and added on top; where your receipts come from lists them.
Example. $2,595,688 of the aging is dated into the 13 weeks. With $1,297,920 from sales that aren’t on the aging, total receipts come to $3,893,608 — but they’re far from even:
| Week | Total receipts | From the aging |
|---|---|---|
| Highest — week 3 (15–21 Sep) | $584,818 | $486,548 |
| Lowest — week 10 (3–9 Nov) | $148,703 | $35,033 |
And every dollar lands somewhere, to the cent:
| Where the $2,832,119.88 went | Amount |
|---|---|
| Dated into weeks 1–13 | $2,595,688.15 |
| Dated after week 13 | $105,386.35 |
| Overdue, no date — still in AR | $131,045.38 |
| Total — matches the aging | $2,832,119.88 |
How to read it. The lowest week is the one to plan payments around — and to check which customers it depends on. If the pieces don’t add back to the aging, an invoice has been dropped or counted twice.
Step 6: Roll it forward every week
Each week, run a fresh aging and check that last week’s total, plus new invoices, less cash received and credits, equals this week’s. Then compare what you expected with what arrived.
The roll-forward catches what slips through: an unapplied payment, an invoice edited after it went out. The comparison catches the dates that were wrong. A customer that keeps paying later than you dated it needs its dates moved; an invoice that slipped with no reason needs a phone call. The full routine is the weekly actual-vs-forecast review.
Example. In week 1: $2,832,120 + $63,000 of new receivables − $217,254 collected = $2,677,866. With no new invoices to these customers, AR would fall to $358,112 by week 13.
How to read it. That fall is the limit of any forecast built from an aging: it only collects invoices you’ve already raised. Cash from invoices you haven’t raised yet has to come from your sales forecast — which is why the aging is refreshed every week, not built once.
The list I look at first isn’t the 90+ bucket — that’s usually already known. It’s the overdue invoices with no expected date. That’s where an invoice from a good customer quietly turns into a problem, and where one phone call still changes the date.
Start this week
Export the aging detail, tie it to the balance sheet, and date the invoices of your ten largest customers and every overdue one.
If you’d rather not build the spreadsheet, our free 13-week AR collection forecast template does the arithmetic in these steps, and comes with the sample data used above.
If you’d like a second pair of eyes on your aging, email me at anant@ease.pro. I hope you have a great day.
Want your AR aging turned into a forecast you can plan around?
Dating every invoice once is a few hours’ work. Keeping the dates current every week, and chasing the invoices that slip, is what changes your cash. If you’d like experienced finance professionals to build your collection forecast from your own aging and run it weekly with your team, that’s our cash flow work.
Frequently asked questions
What is an AR aging report?
An accounts receivable aging report lists the money customers owe you, grouped by how old each invoice is — usually 1–30, 31–60, 61–90 and over 90 days. Some systems age by days past the due date, others by days since the invoice date, so check which yours uses. Its total should equal accounts receivable on your balance sheet at the same date.
How do you forecast cash collections from an AR aging report?
Export the aging detail, check that its total ties to accounts receivable on the balance sheet, and clear unapplied payments and credits. Then give each invoice the date you expect the cash: a date the customer has promised, then how that customer usually pays, then the due date. Overdue invoices nobody can date stay out of the forecast until someone can. Group the dated invoices into weeks, check every dollar is accounted for, and roll it forward weekly.
Should overdue invoices go into next week's cash forecast?
Not automatically. Being overdue tells you an invoice is late, not when it will be paid. Give an overdue invoice a date only when you know why it's late and when it will be paid — a promise, a payment run, a fixed approval. Until then, leave it in accounts receivable and put it on your chase list.
What should I do with invoices over 90 days old?
Keep them out of the forecast unless there's specific evidence of a date, such as a signed payment plan. Keep them visible on your chase list, and decide with your accountant whether they need a bad debt allowance. Forecasting them into next week, every week, is how an unpaid invoice hides in a forecast.