AR Forecast
Corporate Finance Financial Model (Free Excel Download)
Project customer collections, ageing buckets, credit terms, and bad-debt exposure to improve cash visibility and manage receivables before they become a problem.
professionals from Deloitte
Used by professionals from
About this model
An AR forecast translates a monthly billings plan into a 12-month projection of the accounts-receivable balance and the cash inflow from customers. This template lays the full mechanic on six sheets: an Assumptions sheet with a panel of eight customers, each with payment terms in days and a 12-month billings plan, plus opening AR, the four bucket cutoffs that map terms days to a collection-timing bucket (collected same month / +1 / +2 / +3), and the DSO and AR-intensity traffic-light thresholds; a Customer Master sheet that pulls each customer's terms and annual billings, computes its % of panel and DSO contribution, and labels its timing bucket; a Collection Schedule sheet with a 12x12 matrix that routes each month's billings to the month they are collected based on customer bucket, with a post-period column for billings whose collection lands beyond the 12-month window; an AR Schedule sheet that rolls opening AR, billings, collections, and closing AR through 12 months with an implied DSO each month; and a Dashboard sheet with weighted DSO, peak AR balance, AR intensity at peak, closing AR at M12, annual billings and collections, and the collection-timing distribution by bucket.
The collection routing uses a SUMPRODUCT against the customer bucket labels, so a single edit to a customer's terms days or to a bucket cutoff reshapes the entire collection matrix and the AR balance path. Weighted DSO is computed properly - each customer's share of total annual billings is multiplied by its terms days and summed - so high-billings customers dominate the headline number the way they would in a real cash forecast. The model maintains the AR identity (opening + billings - collections = closing) at every month, and the post-period spill column ensures column sums tie to the underlying billings.
CFOs, FP&A teams, controllers, and treasury managers use this template for cash-in timing (drive the operating-cash leg of a 13-week forecast off the AR Schedule collections row), working-capital sizing (read AR intensity at peak to size how much customer financing the business is carrying), and customer-terms renegotiation cases (flex one customer's terms days in Assumptions and quantify the cash impact on weighted DSO and peak AR before walking into the negotiation). Bad debt and write-offs are intentionally not modelled - real AR has a write-off rate, but the trade-off is interpretability and one-edit responsiveness over the false precision of a per-customer recovery calendar. This is the AR-side mirror of the ap-forecast template; pair them to bridge to a full net-working-capital build.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the AR Forecast
- Customer panel with terms (days) and 12-month billings plan per customer
- Customer Master with annual billings, % of panel, weighted-DSO contribution, and timing bucket
- 12x12 collection matrix routing each month's billings to the month they are collected
- Post-period spill column capturing collections deferred past M12
- 12-month AR roll-forward: opening AR, billings, collections, closing AR, implied DSO
- Peak AR balance, the month it occurs, and AR intensity (AR / monthly billings) at peak
- Collection timing distribution: % of annual billings by bucket (+0 / +1 / +2 / +3 months)
- Dashboard with DSO and AR-intensity traffic-light status against user-set thresholds
AR Forecast: 12-Month Accounts Receivable Timing Model
An AR forecast translates a monthly billings plan into expected cash collections and AR balances. This template shows how customer terms, a collection timing matrix and a 12-month roll-forward work together.
It is designed for treasury and CFO use to understand when cash arrives, where collection risk sits, and how scenarios change the picture.
How Customer Terms and Billings Drive the Panel
The model starts with a customer panel on the Assumptions sheet. Each customer has a set of active terms, selected through a scenario toggle that chooses between Base, Stretch and Tight definitions.
- Annual billings are spread across a monthly billings plan. Those two inputs alone define the steady-state view: the weighted-average DSO is calculated as each customer's share of panel billings multiplied by its active terms.
- Customer metadata adds practical detail such as early-pay discount percentage, standard terms, priority tier, currency, FX rate and expected bad-debt percentage. Bucket cutoffs then sort each customer into a collection timing bucket, from current month through to three months out or later.
This setup means changing a single term or a monthly billings figure ripples through the whole forecast, which is useful when evaluating customer negotiations or a shift in sales mix.
Routing Billings to Collection Months
The collection schedule is a 15-by-13 matrix that routes billings to the month they are expected to be collected. Historical rows cover the three months before the forecast, and forecast rows cover the twelve months ahead.
- A bucket logic assigns each customer's billings to a column based on the difference between the collection month and the billing month. Opening receivables are also drained into the first four months through a runoff curve that should sum to 100 percent.
- Columns are totalled to give collections by month, with any amounts falling beyond month twelve shown as post-period spill. The matrix is upper-triangular, meaning billings never collect before they are billed.
This structure makes the timing of cash receipts explicit rather than burying it in a single average collection period.
The AR Roll-Forward and DSO Views
The AR schedule produces a twelve-month roll-forward: opening balance plus billings minus collections minus write-offs equals closing balance each month. The identity is tested with a small tolerance.
- Write-offs are calculated per customer from billings multiplied by the bad-debt percentage. The schedule also shows three DSO measures: a revenue-basis DSO that divides closing AR by annual revenue scaled to a daily rate, a this-month implied DSO using the current month's billings, and a trailing-three-month DSO that uses the last three months of billings.
- These views often diverge because long-terms customers have late-year billings that spill beyond the twelve-month window, which can reduce the trailing DSO numerator. The dashboard highlights the gap and traffic-lights it, so users can see whether the difference is structural or a sign of something changing.
Using the Dashboard, Sensitivity and Checks
The dashboard summarises the forecast: lowest cash month, peak AR and its month, AR intensity, closing balance at month twelve, write-offs, customer concentration through HHI and top-three share, discount APR versus WACC with action labels, FX exposure, past-due ageing buckets and priority tiers. A sensitivity sheet lets you flex the most uncertain inputs.
- One grid varies DSO and billings growth to show closing AR; another varies two major customers' terms to show peak AR. A checks sheet runs identity tests on each month's roll-forward, scenario validity, runoff sums and cross-footing.
- The model is meant for AR-side working capital planning and does not cover capex, payables or factoring. Its value is in showing how collection timing and customer behaviour affect cash, not in delivering a single point forecast.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is an AR forecast?+
An accounts-receivable (AR) forecast translates a monthly billings plan into a month-by-month projection of the AR balance and the cash inflow from customers. It is the AR side of a working-capital forecast and the input that drives the operating-cash leg of a 13-week cashflow or 3-statement projection.
How are customer terms turned into collection timing?+
Each customer's terms in days are mapped to one of four buckets via user-set cutoffs on the Assumptions sheet: same month, +1 month, +2 months, or +3 months. The model uses a SUMPRODUCT against the customer bucket labels on the Customer Master sheet to route each month's billings into the right collection column.
Why does the dashboard show collections deferred past M12?+
Customers with longer terms can have billings in the last few months of the forecast whose collection lands beyond the 12-month window. The collection matrix has a post-period column that captures this spill so the totals tie and you can see how much of the late-year billing rolls over into the next year.
How is weighted DSO computed here?+
Weighted DSO is the sum of each customer's share of total annual billings multiplied by that customer's terms days. So customers with bigger billings get more weight, and the headline DSO reflects the panel's actual cash-timing centre of gravity rather than a flat average across customers.
Does it model bad debt or write-offs?+
No - this template assumes every billed dollar is eventually collected. Real AR has a write-off rate; users who need that should layer a haircut on top of collections or extend the model with a recovery rate column per bucket.
Can I add more customers?+
Yes. Extend the customer block on Assumptions, add the corresponding row on Customer Master, and extend the named ranges for terms, billings, and buckets to cover the new rows. The collection matrix and AR Schedule will pick up the additions automatically through the named ranges.
Have more financial modelling questions? Contact us
Related templates
AP Forecast
12-month accounts-payable timing forecast with vendor terms and DPO.
Working Capital Model
Model cash conversion cycle with DSO, DIO, and DPO to forecast working capital needs.
Cashflow Model
Detailed monthly and annual cash flow projections with sweep and liquidity mechanics.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
