Forecast accounts receivable using DSO

To forecast accounts receivable using DSO, multiply forecast daily credit sales by an explicit days-sales-outstanding assumption. That gives a projected balance; use the receivables rollforward to check the collections it implies. A DSO projection supports the balance sheet, while individual invoice dates determine when cash can actually arrive.

Set the balance convention first

Forecast closing AR = Forecast credit sales / Days in period × Assumed closing-balance DSO

Implied collections = Opening AR + Credit sales − Forecast closing AR

The second equation assumes no write-offs, credit memos, foreign exchange movements or other adjustments. Add those separately when they exist.

Using efficiency ratios alongside sales forecasts to project receivables is a standard working capital approach, described in CFA Institute's forecasting guidance. The critical choice is which balance the ratio represents.

The CFA Institute financial ratio list defines receivables turnover using average receivables and DSO through that turnover. Reversing an average-balance ratio gives average AR. It does not automatically give closing AR.

Here the planning assumption uses closing trade receivables divided by that month's daily credit sales. Calibrate historical DSO the same way. For the broader definition and historical calculation, use days sales outstanding; the AR turnover ratio covers the turnover relationship.

Build the forecast from your exports

Start with the latest closed AR aging report, the credit-sales forecast and recent monthly ledger balances. Keep customer, invoice date, due date, currency, original invoice amount, open balance and applied payment date in the supporting files. Reconcile the opening aging total to the trade receivables control account before projecting anything.

Exclude cash sales and unrelated receivables. Decide how sales taxes, credit memos and allowances enter both sales and AR so their bases match. Keep a separate adjustment column if they cannot be aligned directly.

A US distributor has opening receivables of $1,200,000 at October 31, 2026. It sets a closing-balance DSO assumption of 42 days. These monthly sales inputs deliberately imply even daily sales within each month; the DSO approach remains an approximation when sales rates change.

In Excel, place those columns in A:F with the first forecast in row 2. Daily sales and closing AR are:

D2 = B2/C2
F2 = D2*E2

Fill down. If separate date cells contain the period start and end, calculate inclusive calendar days with:

=DAYS(EndDate,StartDate)+1

Microsoft documents DAYS as the difference between two dates; adding one includes both boundary dates. Covirage is not affiliated with Microsoft.

Keep the assumption visible instead of burying it inside a formula. Finance owns the projection, and the AR team should challenge the timing assumption against customer payment evidence.

Prove the projection with the collections bridge

Add opening AR in column G, implied collections in H and a balance check in I. Set G2 to the reconciled opening balance and G3 to F2, then fill G3 down.

H2 = G2+B2-F2
I2 = G2+B2-H2-F2

The full-window row uses the first opening balance and last closing balance. Balances are not additive. The check is $1,200,000 + $2,915,000 − $2,855,000 = $1,260,000; all figures are exact dollars, with no rounding required.

A zero check proves arithmetic consistency, not payment feasibility. Compare implied collections with the open invoices and expected receipts. For weekly payment timing, use the invoice collection approach in the cash flow forecast guide.

Compare the forecast with actual balances

When November closes, actual credit sales are $960,000 and closing AR is $1,408,000. Separate the two drivers before asking collections to explain the whole difference.

Actual closing-balance DSO is 1,408,000 / 960,000 × 30 = 44 days. The extra two days explain $64,000 at actual daily sales of $32,000. Actual implied collections are $1,200,000 + $960,000 − $1,408,000 = $752,000.

The sales-first bridge assigns the interaction to the DSO step. Keep that order consistent. Then investigate the residual by customer: the aging of accounts receivable method helps distinguish current invoices from old overdue balances.

Where it goes wrong

• Mixing average and closing balances. A historical average-balance DSO cannot be inserted into a closing-balance formula without conversion.
• Treating DSO as a payment schedule. Seasonal sales, month-end invoicing and large customer payments can break the daily-sales approximation. Use invoice timing for near-term liquidity decisions.
• Calling every reduction a collection. Write-offs, credit memos and receivables sales also reduce AR. Reconcile them separately to cash receipts.
• Assuming improvement without evidence. Check the intended DSO change against customer terms, disputes and specific collection actions before putting it in the forecast.

Check your receivables projection in Covirage

Bring your credit-sales forecast, AR aging and payment export to Covirage, which maps the uploaded columns. Its tools compute and check the projected balances, actual DSO and collections bridge; the external AI model explains the differences with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See how this supports FP&A reporting.