An accounts receivable forecast template should preserve invoice facts, timing evidence, weekly assumptions, confidence, ownership, and observed outcomes. The point is not to force the aging total into four columns. It is to show which open invoices may turn into cash in each of the next four weeks, why each timing assumption exists, and which exceptions need action.
Intuit describes the A/R Aging Summary as a customer-balance view and the A/R Aging Detail as the transaction-level view. Start with the detail data so that every assumption remains tied to an invoice. Intuit also tells users to review AI-assisted forecasts before making decisions, a useful rule for any forecast rather than only an automated one.
The source-fact columns
Copy facts from the authorized source without silently repairing them:
- Customer and invoice identifier
- Invoice date and due date
- Original amount, open amount, and any visible credit
- Report date and source-row reference
- Days past due or aging bucket
Keep these fields separate from analyst notes. If a balance, due date, or application looks wrong, flag it for review instead of overwriting the source value.
The evidence columns
Add fields that explain what is known after the invoice was issued:
- Latest meaningful follow-up date
- Payment commitment and the date it was made
- Dispute, approval, billing, or documentation blocker
- Internal owner
- Next action and next review date
A due date is a ledger fact. A promised date is collection evidence. A disputed invoice is a blocker. They should not share one ambiguous notes cell.
The four-week assumption columns
Use one expected collection week per invoice: week 1, week 2, week 3, week 4, or unassigned. Add a confidence label such as supported, tentative, or unconfirmed. The label should describe the evidence behind timing, not a manufactured probability.
For each assigned invoice, record a short reason. Examples include a recent specific commitment, an established payment cadence that has been checked, or a resolved approval step. If no credible timing evidence exists, keep the invoice unassigned. An honest gap is more useful than false precision.
Then summarize the assigned open amounts by week. Keep unassigned and blocked amounts outside the expected-week totals so the review can see how much of the aging remains unresolved.
The outcome and variance columns
After each week closes, record the observed result without rewriting the original assumption:
- Actual receipt date and amount, when observed
- Variance from the assigned week
- Reason the timing changed, if known
- Revised assumption and reviewer
This creates a learning record. It shows whether misses came from stale promises, unresolved disputes, missing ownership, bad source data, or another observed cause. It does not turn one miss into a benchmark or a prediction about every customer.
A compact template layout
Use four sections in one table: source facts, evidence, forecast assumption, and outcome. Freeze the identifiers and source facts. Make owner, next action, and review date visible beside the expected week. Add summary rows for each week, blocked invoices, and unassigned invoices.
Excel controls that keep the file reviewable
In Excel, keep the imported source rows on a protected input sheet and use a separate review table keyed by a stable invoice identifier. Avoid copying and pasting adjusted balances over the source export. A reviewer should be able to trace every expected-week amount back to the exact source row and report date.
Use validation lists for forecast state and confidence labels. Use formulas to summarize `Week 1` through `Week 4`, `later`, `blocked`, and `unassigned` without deleting rows. Add control cells that compare the sum of all review states with the imported open-balance total and flag duplicate invoice keys. Treat a failed control as a stop condition rather than hiding it with a manual plug.
The workbook should also display its snapshot date, receipt cutoff, version, and reviewer. Save a locked copy before rolling forward. Excel is the container; the evidence model and reconciliation controls are what make the template useful.
Add explicit week-boundary fields
Observed searches for an accounts receivable forecast template often hide a basic ambiguity: what dates belong to Week 1? Add a start date and end date for each forecast week, plus the reporting time zone and snapshot cutoff. A date should map to exactly one weekly, later, or unassigned state.
Microsoft's cash-flow forecasting documentation distinguishes invoice, due-date, payment-term, and payment-delay inputs. Keep those source and model dates separate from the template's display buckets. The Week 1 boundary guide provides the roll-forward rule and cutoff checks.
The related invoice collection assumptions template gives more detail on evidence and confidence rules. The four-week AR forecast guide explains how to run the weekly review.
After the week closes, use the forecast variance review to compare locked invoice assumptions with observed receipts. Set the cash-receipt cutoff before that comparison so later postings do not rewrite the prior outcome.
What this template should answer
A finance owner should be able to answer five questions quickly: what may arrive, when it may arrive, what evidence supports the timing, what is blocked, and who owns the next step. If an Excel template only rearranges aging buckets, it is still an aging report.
AR Pulse applies this structure to a customer-provided QuickBooks receivables export and produces a ranked cash-action list and four-week collections forecast. It does not require QuickBooks credentials, contact customers, or promise collection outcomes. View the one-page example to see the intended decision layer.