# AR Forecast A 12-month accounts-receivable timing forecast: customer panel with terms (days) and a monthly billings plan, weighted-average DSO, a 12x12 collection matrix routing each month's billings to the month they are collected, a 12-month AR roll-forward (opening / billings / collections / closing) with implied DSO each month, and a dashboard with peak-AR and timing-distribution status. - Canonical: https://finamodel.com/templates/ar-forecast - Excel download: https://finamodel.com/templates/ar-forecast.xlsx - Category: Corporate Finance - Model type: Operating model - Difficulty: Intermediate - Audiences: CFOs & FP&A, Founders & operators, CFOs, FP&A teams, Controllers, Treasury - Tags: accounts receivable, dso, working capital, customer terms, cash forecast ## Overview 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's included - 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 - Weighted-average DSO across the panel weighted by annual billings - 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 - Customer Master with annual billings, % of panel, weighted DSO contribution, and timing bucket per customer - 12x12 collection matrix routing each month's billings to the month they are collected based on customer bucket - 12-month AR roll-forward: opening AR, billings, collections, closing AR, implied DSO each month - Weighted-average DSO across the customer panel weighted by annual billings - Peak AR balance and the month it occurs, plus AR intensity (AR / monthly billings) at peak - Collection timing distribution: % of annual billings collected same month / +1 month / +2 months / +3 months ## 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. ## Built for cash-in timing When the question is "when do we actually collect from our customers?", an AR forecast is the cleanest answer. This template feeds the operating-cash leg of a 13-week cashflow forecast, a 3-statement model, or a runway projection without you having to stitch collection timing together by hand. ## Designed for one-edit responsiveness Every customer term, bucket cutoff, threshold, and monthly billing line is a named-range or named-cell input. Flex one number and the collection matrix re-routes, the AR balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real customer terms have daily nuance and real AR has bad debt; this template uses a four-bucket model with full recovery because the trade-off favours interpretability over false precision. The bucket cutoffs are user-set inputs, so you can tighten or loosen them to match how your customers actually pay. ## Built for cash-in timing When the question is "when do we actually collect from our customers?", an AR forecast is the cleanest answer. This template feeds the operating-cash leg of a 13-week cashflow forecast, a 3-statement model, or a runway projection without you having to stitch collection timing together by hand. ## Designed for one-edit responsiveness Every customer term, bucket cutoff, threshold, and monthly billing line is a named-range or named-cell input. Flex one number and the collection matrix re-routes, the AR balance path updates, and the dashboard status flips - no formula rewrites. ## Honest about timing precision Real customer terms have daily nuance and real AR has bad debt; this template uses a four-bucket model with full recovery because the trade-off favours interpretability over false precision. The bucket cutoffs are user-set inputs, so you can tighten or loosen them to match how your customers actually pay. ## Workbook structure ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: customer panel, monthly billings, opening AR, bucket cutoffs, thresholds. - 8 customers with terms (days) and a 12-month billings plan per customer - Opening AR balance - Three bucket cutoff days (same-month, +1 month, +2 month) - DSO and AR-intensity traffic-light thresholds (green / amber) ### Customer Master Per-customer terms, annual billings, weighted-DSO contribution, and timing bucket. - One row per customer - Annual billings via SUM across the 12 monthly named ranges - % of panel and DSO contribution (% of panel times terms days) - Bucket label via nested IF on the user-set cutoffs ### Collection Schedule A 12x12 matrix routing each month's billings to the month they are collected. - Row r = billing month, column c = collection month - Cell (r, c) computed via SUMPRODUCT against customer bucket labels - Post-period column captures collections deferred past M12 - Column sums feed the AR Schedule collections row ### AR Schedule 12-month AR roll-forward with implied DSO each month. - Opening AR rolls from previous month's closing - Billings per panel from the Assumptions monthly named ranges - Collections pulled from the Collection Schedule column totals - Closing AR = opening + billings - collections - Implied DSO = closing AR / monthly billings x 30 ### Dashboard Headline metrics with traffic-light status and collection-timing distribution. - Weighted-average DSO with On track / Watch / Stretched status - Peak AR balance and the month of peak - AR intensity at peak with On track / Watch / Heavy status - Annual billings, in-period collections, and post-period spill - Bucket distribution: % of annual billings collected +0 / +1 / +2 / +3 months ### Cover Workbook overview, sheet legend, and tab-colour key for navigation. - Title and scope framing - Sheet-by-sheet purpose summary - Tab-colour legend ### Assumptions Every driver in one sheet: customer panel, monthly billings, opening AR, bucket cutoffs, thresholds. - 8 customers with terms (days) and a 12-month billings plan per customer - Opening AR balance - Three bucket cutoff days (same-month, +1 month, +2 month) - DSO and AR-intensity traffic-light thresholds (green / amber) ### Customer Master Per-customer terms, annual billings, weighted-DSO contribution, and timing bucket. - One row per customer - Annual billings via SUM across the 12 monthly named ranges - % of panel and DSO contribution (% of panel times terms days) - Bucket label via nested IF on the user-set cutoffs ### Collection Schedule A 12x12 matrix routing each month's billings to the month they are collected. - Row r = billing month, column c = collection month - Cell (r, c) computed via SUMPRODUCT against customer bucket labels - Post-period column captures collections deferred past M12 - Column sums feed the AR Schedule collections row ### AR Schedule 12-month AR roll-forward with implied DSO each month. - Opening AR rolls from previous month's closing - Billings per panel from the Assumptions monthly named ranges - Collections pulled from the Collection Schedule column totals - Closing AR = opening + billings - collections - Implied DSO = closing AR / monthly billings x 30 ### Dashboard Headline metrics with traffic-light status and collection-timing distribution. - Weighted-average DSO with On track / Watch / Stretched status - Peak AR balance and the month of peak - AR intensity at peak with On track / Watch / Heavy status - Annual billings, in-period collections, and post-period spill - Bucket distribution: % of annual billings collected +0 / +1 / +2 / +3 months ## Features - **Bucket-based collection timing:** Customer terms (in days) are mapped to a four-bucket timing model: collected same month, +1 month, +2 months, or +3 months. Cutoff days for each bucket are user-set named-range inputs, so a single edit to one cutoff cell reshapes the entire collection matrix. - **12x12 collection matrix:** Each row is a billing month; each column is a collection month. Cell (r, c) shows the dollars from month r's billings that get collected in month c, with a post-period spill column for billings whose collection lands beyond M12. Column sums feed the AR Schedule collections row directly. - **Weighted DSO not flat-averaged:** Weighted-average DSO is computed as the sum of each customer's annual-billings share times that customer's terms days, so big-billings customers dominate the headline number the way they would in a real cash forecast. No equal-weight averaging. ## Use cases - **Cash-in timing for treasury:** Take a budgeted billings plan, push it through customer terms, and read the month-by-month cash inflow off the AR Schedule collections row. Pairs directly with a 13-week cashflow forecast or a runway model. - **Working-capital sizing for fundraising:** AR intensity at peak (AR / monthly billings) sizes how much customer financing the business carries; compare that against the AP side from the ap-forecast template to bridge to net working-capital investment. - **Customer-terms renegotiation case:** Flex one customer's terms days in Assumptions, watch the weighted DSO move, the bucket flip, the collection matrix re-route, and the peak AR balance respond. Quantifies the cash impact of a terms negotiation before you walk into the room. ## Frequently asked questions ### 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. ## Related templates - [AP Forecast](https://finamodel.com/templates/ap-forecast) - [Working Capital Model](https://finamodel.com/templates/working-capital-model) - [Cashflow Model](https://finamodel.com/templates/cashflow-model)