Accounts Receivable Cash Flow Forecast Template (13-Week)

Build a 13-week accounts receivable cash flow forecast template by mapping every open invoice from your aging report into weekly collection buckets: current, 30, 60, and 90+ days, each with its own collection percentage. You need your AR aging report, a spreadsheet, and about an hour. The result shows the week cash runs short while there is still time to act.

Why does a cash flow forecast built only from AR beat a P&L-based forecast for the next 13 weeks?

Your P&L tells you what you earned. It says nothing about when the cash lands. On a $5M services business, a $400K receivable balance can sit 60+ days out while payroll, materials, and equipment payments hit weekly, so a profit-positive month can still bounce a check. An AR-driven forecast starts from open invoices, maps each to a collection week using age buckets, and shows the gap before it happens.

A P&L-based forecast also carries accrual noise: revenue recognized on progress billing you have not yet invoiced, depreciation, prepaid expenses. None of that moves cash in the next 13 weeks. Xero's own guidance on cash flow forecasting separates cash position from reported profit for exactly this reason (Xero).

If you want the weekly rhythm that keeps this forecast current rather than rebuilt each month, that is what we run at Founderwright.

What does the accounts receivable cash flow forecast template look like?

The template is one spreadsheet with a 13-week grid across the top and row blocks down the side. Each column is a week ending on a fixed date. Each row is either money coming in from open invoices or money going out. The whole thing fits on one screen, which matters when you are the finance department and you need to see the cash gap before it hits, not after.

The weekly columns (13 weeks)

Column A holds row labels. Columns B through N are Week 1 through Week 13, each headed by the week-ending date. Freeze the first column and the header row so you can scroll without losing your place. Week 1 starts Monday after you pull your AR aging report.

The row blocks: collections, disbursements, net cash, ending balance

Four blocks, always in this order:

The ending balance row is the one you read first. If any week goes negative, that is your cash gap, and the collections block above it tells you which customer or bucket caused it. This grid is the same structure our system builds and maintains for you each week (https://founderwright.com/platform), so the spreadsheet is both the training ground and the fallback.

How do you bucket open invoices by age to predict weekly collections?

Pulling the aging report

Run your AR aging report as of today, straight from Xero or QuickBooks, and export it to a spreadsheet. Keep every open invoice on its own row with the customer, invoice date, due date, balance, and original amount. A summary aging report that shows only totals per customer will not work, because you need invoice-level dates to place each dollar into a collection week. If you have more than a few hundred open invoices, a simple pivot table by age bucket is enough to start.

Assigning each bucket to collection weeks

DSO varies widely by industry, so your collection assumptions should start from your own history, not a benchmark. That said, it helps to know where you stand: working-capital studies such as The Hackett Group's Working Capital Survey publish DSO medians by industry, and operators in construction and distribution typically run several weeks above the all-industry median, which means the 60 and 90+ buckets carry real weight in your forecast. The mechanics are simple: sort every invoice into current, 1-30, 31-60, 61-90, and 90+ days past due, then assign each bucket a collection lag. Current invoices mostly pay in weeks 1-3 of your grid, the 30-day bucket lands in weeks 2-5, the 60-day bucket in weeks 4-8, and the 90+ bucket spreads across weeks 6-13 or drops into a "collection risk" line if it looks doubtful.

Age the buckets off the due date, not the invoice date, because terms like net-60 already push payment out and aging from the invoice date will double-count that lag. This one mapping step is what turns a static aging report into a weekly cash picture, and it is the same discipline we apply when we take over the finance function for owner-led operators through our fractional CFO work. Once each bucket sits on a week, you can stress the whole grid by shifting a bucket's lag and watch the cash gap move before it hits your bank balance.

What collection assumptions should you use for current, 30, 60, and 90+ day invoices?

Starting assumptions and how to tune them

Start conservative: 85% of current invoices, 70% of 30-day, 45% of 60-day, and 15% of 90+ day buckets collect within the 13-week window. These are illustrative defaults, not benchmarks. Calibrate from your own history: pull 12 weeks of actual receipts, match them to the invoice ages when they were issued, and compute your real collection rate per bucket. If your 30-day bucket actually collects at 82%, use 82%, not the default. Recalibrate monthly, and bias downward after a large customer starts slipping.

A worked example with $2.4M of open AR

Say your aging shows $1.0M current, $700K at 30 days, $450K at 60 days, and $250K at 90+. Applying the defaults predicts roughly $850K current, $490K at 30, $202K at 60, and $38K at 90+ — about $1.58M collected over 13 weeks, with $820K at risk. That gap is the number to act on. The tuning step matters more than the starting percentages, and a weekly maker-checker review keeps the assumptions honest — the same rhythm we run inside Founderwright.

How do you handle partial payments, disputes, and slow-paying customers in the forecast?

A forecast is only as good as its worst assumption. Three situations break the standard DSO buckets, and each needs a manual adjustment.

Customer-level overrides

Some customers always pay in 75 days no matter what the invoice says. For those, override the bucket math with a known pay pattern: assign the invoice to the week the customer actually pays, not the week the aging suggests. Keep a short list of these accounts and their real payment behavior. Ten customers driving 60% of your AR is common, and ten rows of overrides beats pretending the average applies to everyone.

Dispute and retention holds

Move disputed invoices out of the collection weeks entirely until the dispute clears, then re-enter them at a realistic week. On construction work, retainage is standard practice, commonly held at around 5-10% of each progress payment per state lien and contract norms (AGC of America), so forecast only the released portion. If overrides like these are eating your Fridays, that is the workload a fractional CFO or an owner-led finance system absorbs.

How do you adapt the template for construction and contractor progress billing?

Progress billing rows

Progress billings break the invoice-per-job assumption. Instead of one row per invoice, add a row per approved billing application, with the approval date as your bucket anchor. Contractors bill on a schedule, so collections follow the approval-to-payment cycle rather than invoice age alone. Track each application's status: submitted, approved, or paid. If approvals lag, your collections lag, and the forecast should show that gap week by week.

Retainage as a separate bucket

Retainage never follows normal payment terms. It sits until the job reaches substantial completion, so pull it out of your age buckets entirely and forecast it in the week you expect to bill and release it. If you have $80,000 in retainage across five jobs, that is a separate line, not a 90+ day invoice. The same logic applies to pay-when-paid clauses: a subcontractor's receivable to you is only collectible once the owner pays, so map those rows to the owner's payment schedule, not your invoice date.

How do you reconcile the forecast against Xero or your accounting system each week?

A forecast that drifts from your books stops being a forecast and becomes a guess. A 20-minute Monday routine keeps the two tied together.

The weekly update checklist

Pull the open-invoice report from Xero (see Xero's central documentation) and paste it over last week's data in the template. Mark invoices as paid, partially paid, disputed, or written off, and let the age buckets recalculate. Then roll the horizon forward: drop the week that just closed, add a new week 13 at the end. Re-check any customer whose payment behaviour changed last week and adjust their collection assumption before anything else.

Variance tracking: forecast vs. actual collections

Next to each week, record what you forecast for collections and what actually landed. A running variance column tells you whether your assumptions are too generous, too cautious, or fine for most customers but wrong for two or three. If actuals run under forecast three weeks in a row, cut your collection percentages by bucket and rebuild the out-weeks. The point is not a perfect forecast; it is a forecast whose error you can see and correct while there is still time to chase the cash.

What decisions should you make differently once you can see the AR cash gap by week?

The forecast turns vague worry into week-specific decisions. If week 7 shows a $60k gap, you know a collections push in weeks 4-6 closes it, so you call the three largest 60-day accounts before the gap arrives, not after.

Hiring gets timed against the same view. A tech start date that lands in a thin-cash week gets pushed two weeks or paired with a collections push first. Capex works the same way: a truck or machine purchase moves to the week after your strongest collections, or you schedule the draw against your line before the trough, not during it.

Owner draws stop being guesswork. You draw in surplus weeks and skip in deficit weeks, and you can show a lender or partner exactly why.

The discipline: every week, pick one gap and one action against it. That is the weekly operating rhythm the forecast exists to support.

FAQ

Can I get this as an XLS download, and does it work with Numbers on a Mac or in Xero?

Yes, the grid opens in Excel, Google Sheets, or Numbers with no changes: it is just row labels, 13 dated columns, and four calculated row blocks. Xero does not host the template itself; you pull the invoice-level aging report out of Xero and paste it into the sheet each Monday.

How is a 13-week AR forecast different from a monthly cash flow forecast template?

Granularity and purpose. A monthly template smooths collections into one lump, which hides a gap that opens and closes inside a single month. The 13-week grid maps invoices to specific weeks, so a $60k shortfall in week 7 is visible and fixable with a collections push in weeks 4-6.

What DSO should I aim for in a service business?

Aim to beat your own history, not an industry median. Pull 12 weeks of actual receipts, match them to invoice ages, and compute your real collection rate per bucket. Services generally run tighter DSO than construction or distribution, but a falling DSO matters more than any benchmark number you compare against.

How far ahead should I forecast new invoices that have not been billed yet?

Cover the full 13 weeks with a separate unbilled line. Use your billing cadence: if you invoice monthly on completed work, add rows for work finishing this month landing as invoices in weeks 1-4. Keep them distinct from the aging-based collections so you can see forecast error from each source separately.

Is a UK or NZ version of the template different from a US version?

Structurally, no. The 13-week grid, age buckets, and collection percentages work identically. What changes is terminology and calendar: VAT or GST collected on invoices belongs in a separate row from the receivable itself, since you remit it rather than keep it. Week-ending dates simply follow your local fiscal calendar.