🎁 Free Excel Template

13-week AR collection forecast template: turn your aging into expected cash.

A free Excel model that takes your open invoices, customer by customer, and returns the cash you can expect each week for the next 13 weeks — with overdue invoices flagged, your AR aged, other sales channels on their own collection lag, and a weekly AR roll-forward. Built by experienced finance professionals.

📥 Get your free EasePro Template

Experienced finance professionals
Invoice by invoice, 13 weeks
Excel for Microsoft 365, 2021 or later
No credit card required

Output — Dashboard, sample data

AR outstanding at the start date

$2,832,120

Overdue

30.4%

Aged 1–30 / 31–60 days

$967,784 / $753,175

Aged 61–90 / 90+ days

$725,682 / $385,479

Expected receipts, 13 weeks

$3,893,608

Lowest receipts week

Week 10

$148,703 expected, against a peak of $584,818 in week 3.

Expected receipts by week ($000)

From existing AR Other sales channels Lowest week

Sample output: 249 invoices across 80 customers, forecast week by week

What's inside the file

Eight sheets. One input sheet — everything else is calculated.

You type into one sheet. The model schedules every invoice, ages your book, and builds the 13-week receipts forecast and dashboard from it.

Start

Cover and instructions

An index linking to every sheet, a check on each one that reads OK or Error, and a four-step guide to filling the model in.

Input

I1 — Input

Three tables: company name and forecast start date; up to six sales channels outside your invoiced AR; and up to 500 open invoices with customer, amount, invoice date, due date and an optional expected receipt date.

What it gives you back

Output

O1 — Dashboard

AR outstanding, overdue %, the four aging buckets, your top 15 customers by amount owed, the 13-week receipts table and charts — plus the receipt mix for any week you pick from a dropdown.

Output

O2 — 13-week forecast

Receipts week by week, split between existing AR and each other channel, and an AR roll-forward: opening AR, new credit sales, collections, closing AR.

Calculation

C1, C2, C3 — the schedules

Every customer’s receipts placed in the week they are expected, each channel’s sales and collections on its own lag, and an invoice-level aging report.

Inside the template

Due date in. Expected date out — when you know better.

Every invoice carries a due date and an optional expected receipt date. Leave it blank and the model uses the due date. Fill it in — because the customer always pays twelve days late, or has told you their payment run — and the cash moves to the week it will really land. Overdue invoices are flagged against the forecast start date, with the days overdue counted for you.

I1 — Input · Table C, customer receivables

Pioneer Trading Ltd. · INV-10001

Amount · due 21-Nov6,279.15

Expected receipt · blank, so due date used21-Nov

Lakeside Labs LLC · INV-10003

Amount · due 24-Aug6,374.49

Expected receipt · entered by you16-Oct

Overdue at start · daysYes · 8

Bright Labs LLC · INV-10011

Amount · due 31-Jul6,913.78

Overdue at start · daysYes · 32

You type here Calculated by the model

Three rows from the sample's 249 — same colour convention as the file

The problem this solves

Your aging says $2.8 million is owed. It doesn’t say which week it arrives.

An AR aging report tells you how old each invoice is. That is useful, and it is not a forecast. It does not tell you that $486,548 of it is due to land in week three while week ten brings in $35,033 — and the difference between those two weeks is what decides whether payroll is comfortable or tight.

Expected to arrive inside the 13 weeks$2,595,688

Overdue, with no expected date yet$131,045

Expected after week 13$105,386

AR outstanding at the start date$2,832,120

Sample from the file. The first three lines add to the total exactly ($2,832,119.88).

So the model works from dates instead of buckets. Each invoice goes into the week it is expected, customer by customer, using the date you know rather than the date on the invoice where you have one. Overdue invoices are not quietly assumed to arrive: until you give one an expected date, it stays in AR. In the sample that is 19 invoices worth $131,045 — exactly the list worth chasing first. We walked through why the expected date matters more than the due date in how to forecast cash receipts accurately.

Thirteen weeks is the window because it is far enough ahead to act on and close enough to name real invoices (why thirteen). It is the receipts half of a weekly, 90-day cash flow forecast — the half that most forecasts get wrong.

How the model works

Six things it does that an aging report can’t.

All of it runs from the one input sheet. Nothing to wire up, and every sheet checks itself.

Expected date over due date

Enter an expected receipt date and the model uses it instead of the due date; leave it blank and the due date is used. That one column is how a forecast stops being optimistic. Why it matters →

Overdue, flagged and counted

Every invoice past its due date at the forecast start is marked overdue, with the days counted. The dashboard shows overdue as a share of total AR.

Aging in four buckets

1–30, 31–60, 61–90 and 90+ days, by invoice date, invoice by invoice. The buckets are checked against total AR, so nothing falls through.

Sales beyond your AR

Up to six channels — cash sales, wholesale, marketplace, dealers, B2B, partnerships — each with a first-week amount, a weekly increase and its own days to collect.

An AR roll-forward every week

Opening AR, plus new credit sales, less collections, equals closing AR — for each of the 13 weeks, so you can see the book shrink or grow.

Checks on every sheet

Each sheet reports OK or Error, and the cover summarises them. If receipts stop reconciling to your invoices, you will know before you rely on the number.

Before you start

What to gather before you open the file.

Most of it is one export from your accounting system. The rest is a short conversation with whoever talks to your customers.

From your accounting system

AR aging detail, by invoice — customer, invoice number, amount, invoice date and due date
Up to 500 open invoices fit on the input sheet
A forecast start date — usually the Monday you run it

From your team

Expected payment dates for your larger and overdue invoices
Sales that never sit on your AR — cash, marketplace, dealers — with typical weekly amounts
How many days each of those channels takes to turn into cash

Not sure how late a customer really pays? Work it out from their last six invoices. And for the full list of forecast inputs and where each one lives, see the cash flow forecast data checklist.

How to use it

How to use the AR collection forecast template: four steps.

Then refresh it weekly: update the invoice list, move any expected dates that have changed, and roll the start date forward.

General inputs

On I1_Input, Table A: your company name and the forecast start date. The 13 weeks run from that date.

Other sales channels

Table B: each channel outside your invoiced AR, its sales in the first week, the weekly increase you expect, and the days it takes to collect.

Customer invoices

Table C: paste in your open invoices. Add an expected receipt date wherever you know better than the due date — overdue invoices first.

Read the dashboard

On O1_Dashboard, pick a week from the dropdown. Start with the lowest receipts week and the overdue list, then work back to the customers behind them.

Is this for you?

Is this AR template right for you? Honestly.

This template is for you if:

You invoice customers on terms and want to know which week the money actually arrives.
You want receipts forecast customer by customer, not as an average of your AR days.
You need to see overdue invoices, aging and customer concentration in one place.
You can export an AR aging detail from your accounting system.

This is not for you if:

You need payroll, rent and suppliers forecast too — this covers receipts only. The 13-week cash flow template gives a high-level view of the whole.
You run Excel 2019 or earlier, or Google Sheets — the model relies on functions they do not support.
You carry more than 500 open invoices, or need multi-currency or multi-entity AR.
You only need this Monday’s pay-or-chase decision — that is a one-week question. Use the Weekly Cash Decision Calculator instead.

This is a self-serve model. If you would rather have your AR forecast built from your ledger and kept current every week, talk to us.

Go further

Use it with the rest of your cash flow.

This template forecasts receipts in detail. For the rest of your cash flow, these are the best places to start — a high-level weekly template, a tool for this week’s decisions, and the guides behind them.

13-Week Cash Flow Template → A high-level weekly view of your whole cash flow — receipts and payments — for the quarter, with your lowest cash week flagged. Weekly Cash Decision Calculator → This week only: who to pay, what to chase, and what can wait. Forecasting cash receipts → Real payment habits, committed versus probable receipts, and checking accuracy each week. A direct forecast, built from $80,000 → Receipt by receipt and payment by payment, to the week that turns out tight. Where did my cash go? → DSO, DPO and the cash conversion cycle — and how to get paid faster. Cash Flow Management Services → We build the forecast on your numbers, run it weekly with your team, then hand it over.

Frequently Asked

AR collection forecast template questions, answered honestly.

What format is it, and which version of Excel do I need?

It is an Excel (.xlsx) workbook with eight sheets and no macros. It uses XLOOKUP and Excel’s dynamic array functions — FILTER, SORTBY and UNIQUE — which are available in Excel for Microsoft 365, Excel 2021 or later, and Excel for the web. Excel 2019 and earlier do not have them, and Google Sheets does not support all of them, so the schedules will not calculate there.

How many invoices and sales channels does it handle?

Up to 500 open invoices on the input sheet, across as many customers as you have, and up to six sales channels outside your invoiced AR. The sample file carries 249 invoices across 80 customers.

What is the difference between the due date and the expected receipt date?

The due date is what the invoice says. The expected receipt date is when you actually expect the money — because the customer always pays late, pays in a fixed weekly run, or has told you a date. Leave the expected date blank and the model uses the due date; fill it in and the cash moves to the week it will really land. How to forecast cash receipts accurately covers how to work out that date.

What happens to overdue invoices that have no expected date?

They are not assumed to arrive. An overdue invoice’s due date is already behind the forecast start, so until you give it an expected date it stays in AR rather than being counted as cash. In the sample that is 19 invoices worth $131,045. That keeps the forecast cautious: those are the invoices to chase, and they should not count as cash until someone has a date for them.

How is the aging calculated?

Invoice by invoice, from the invoice date to the forecast start date, into four buckets: 1–30, 31–60, 61–90 and 90+ days. The buckets are checked against total AR, so they always add back to what is outstanding.

Can it include sales that are not on my AR yet?

Yes. Table B takes up to six channels — cash sales, marketplace, dealers and so on — each with its first-week sales, a weekly increase, and the days it takes to collect. Sales collected later than the week they are made become new AR in the weekly roll-forward, and are collected in the week their lag puts them.

How is this different from the 13-week cash flow template?

The 13-week cash flow template forecasts your whole cash position — receipts and payments — from averages such as your AR days. This one goes deeper on receipts only: invoice by invoice, customer by customer, with aging, overdue flags and expected dates. Use this one to get receipts right, and the 13-week template for a high-level view of the whole cash flow.

Is it really free? What do you ask for?

Yes, free. You sign in with Google once and answer a few short questions about your business, so we know who is using it. No credit card.

What if I want this built and run for my business every week?

EasePro’s Cash Flow Management Services build the receipts forecast from your own ledger and customer payment history, keep it current every week, and tie it into a full 13-week cash flow forecast.

Want your AR forecast built from your ledger and kept current every week?

A receipts forecast is only as good as its expected dates, and those change every week. EasePro's Cash Flow Management Services build it from your own AR and customer payment history, run the weekly update with your team, and tie it into a full 13-week cash flow forecast.

See Cash Flow Management Services → Book a free 30-min call →

Founders from global advisory firms, supported by an in-house trained team of finance professionals. Big Four-grade depth with our own standards for accuracy and data security.