FP&A software forecasts working capital and DSO (days sales outstanding) by applying collection and payment timing to revenue and expenses, ideally at customer and vendor level. DSO is the output calculated from those assumptions. When payment behavior changes, finance can revise the affected customer assumptions and refresh the forecast without rebuilding the entire model.
Contents
- How do I model receivables and payables timing in a forecast?
- How do I forecast receipts and DSO from customer-level data?
- What drives DSO variance month to month?
- What breaks in a cash forecast when customer payment behavior changes?
- How do I separate gross sales from refunds in a cash forecast?
How do I model receivables and payables timing in a forecast?
Apply timing assumptions to each revenue and expense stream: when it is billed, when it is due, and when it is paid in practice. Receivables timing turns revenue into receipts. Payables timing turns expenses into payments.
Net working capital is current assets minus current liabilities; operating working capital is often modeled through receivables, inventory and payables. Forecast closing receivables from invoicing and cash receipts, inventory from purchases and usage, and payables from purchases and payments. Then reconcile changes in those balances to the cash-flow forecast.
Separate billing from recognition first. A customer billed annually in advance pays cash before revenue is recognized. A customer billed monthly in arrears pays after. A services firm billing on milestones may recognize revenue months before it can invoice. Each pattern produces a different receivables profile from the same revenue.
Then apply payment behavior. Terms say net 30, but customers pay on their own schedules. If invoices in one segment are consistently paid 12 days after their net 30 due dates, a 42-day expected collection interval may be a useful starting assumption, adjusted for open-invoice aging and known exceptions.
Payables mirror the same logic. Vendors with net 45 terms are often paid on a weekly payment run, so payment dates cluster on run days. Some bills, such as payroll taxes and rent, cannot move. Others, such as discretionary vendor payments, can be timed to manage cash. DPO (days payable outstanding) then follows from the forecast payables balance, the same way DSO follows from receivables.
A common error is using the same days for every stream. In an illustrative case, a company applied 45-day collection to all revenue, including credit card sales that settle in two days. Receivables were overstated by about $600,000, and forecast cash was too low by the same amount, which made the business look tighter than it was.
How do I forecast receipts and DSO from customer-level data?
Forecast receipts from open invoices and expected billings by customer, using each customer’s payment history. Then calculate DSO from the forecast receivables balance. Use the resulting DSO to check the forecast.
Granular data improves accuracy where it matters. Where receivables are concentrated among a few customers, forecast those accounts individually, with their actual payment patterns and any known disputes. Group the rest into segments with shared collection curves.
An illustrative example shows the method. A company has $3.2 million in open receivables. Its ten largest customers hold $1.9 million. Two of them pay reliably on the due date, five pay about two weeks late, and three pay in irregular batches tied to their own month-end. Forecasting those ten one by one, and applying a segment curve to the remaining $1.3 million, gives a weekly receipts forecast that explains itself when a payment moves.
DSO then follows. Using an illustrative 90-day convention, forecast month-end trade receivables of $3.0 million divided by $6.0 million of trailing 90-day credit sales, multiplied by 90, gives about 45 days of DSO. Use a consistent sales basis and period length. If forecast DSO rises, reconcile invoice timing, credit-sales mix, disputed balances and customer payment behavior before attributing the change to slower collections.
Keep customer-level overrides from the account team. A collections lead who knows a customer is waiting for a new purchase order adds information no history provides.
What drives DSO variance month to month?
DSO can change because of invoice timing, customer mix, large invoices, the denominator used in the calculation, or payment behavior. Slower collections are only one of these causes. DSO is a ratio, so it moves when revenue moves even if every customer pays exactly as before.
Billing timing is one possible cause. If a large annual renewal is invoiced on the last day of the month, receivables jump while that month’s revenue barely changes, and DSO spikes. When the customer pays the next month, DSO falls again, with collection behavior unchanged throughout.
Customer mix also matters. A month with more revenue from slow-paying segments, such as public sector or large enterprise customers, raises DSO even when each segment pays at its normal pace.
Calculation method matters too. DSO based on one month of credit sales can swing more than DSO based on a trailing quarter. The count-back method, which works backward through recent months of sales until it covers the receivables balance, gives a different figure from a simple average. Pick one method, document it and use it consistently.
Each month, decompose the DSO change into billing timing, mix and collection behavior. In an illustrative decomposition, DSO rose from 44 to 51 days. Four days came from a $900,000 renewal invoiced on the 30th, two from mix, and one from slower payment. The one-day collection-behavior component warrants a closer review with collections, while the other components need different explanations.
What breaks in a cash forecast when customer payment behavior changes?
A change in payment behavior breaks the collection curves and every receipt forecast built on them. A forecast built on historical payment patterns assumes customers keep paying the way they did. When a large customer slows down, or a whole segment stretches terms, receipts fall below forecast and the gap compounds each week.
The early signals appear in the data before they appear in cash. Partial payments, payments that skip the oldest invoices, requests for new terms and disputes on invoices that were never disputed before are all signs. A forecast that tracks only totals misses them.
Consider an illustrative distributor whose largest retail customer began paying in 60 days, up from 30, with no formal change to its terms. For three weeks, receipts were $410,000 below forecast. The forecast assumed the delay was temporary and kept the old curve, so the shortfall rolled forward each week until treasury drew on the revolver.
Datarails Cash connects bank balances and transactions with cash forecasting inputs, helping finance compare observed receipts with planned cash flows. Where invoice and customer data are integrated and reconciled, analysts can investigate which expected receipts have not arrived. FinanceOS®, the governed data foundation beneath Datarails, supports connected financial data and drill-down analysis; customer-level matching still depends on the available source records and configuration.
When behavior changes, review the affected invoices and update that customer or segment’s collection assumption. Keep the previous forecast version so the team can distinguish delayed receipts from a change in the underlying sales plan.
How do I separate gross sales from refunds in a cash forecast?
Forecast refunds and dispute-related debits as separate cash-flow lines with their own timing, and keep gross sales on a line of their own. Treat chargebacks as part of the dispute process where applicable, so the same debit is not counted twice. Refunds often leave weeks after the sale. Chargebacks can arrive months later. Netting them hides both the timing and the trend.
Card disputes have a long tail. Stripe explains that dispute windows and the timing of funds debited from a merchant balance depend on payment method, network and dispute status. Finance should model disputes and any associated fees using the processor’s current rules and the merchant’s own dispute history. A spike in sales can lead to disputes in later periods.
An illustrative example: an online retailer sells $2.0 million in November. Historical rates suggest refunds of 6 percent, mostly within 30 days, and chargebacks of 0.4 percent spread over the following four months. The forecast shows $2.0 million of gross receipts, about $120,000 of refunds in December and early January, and about $8,000 of chargebacks spread from December through March, roughly $2,000 a month.
Payment processors complicate this further. Payouts arrive net of fees, refunds and disputes. Use the processor’s transaction-level data to forecast each component, since it shows charges, refunds, fees and disputes separately. Where processor-level feeds such as Stripe and the relevant transaction fields are connected, finance can reconcile gross charges, refunds, fees and disputes to net deposits. Confirm the available fields and matching rules for the specific integration before relying on automated classification.
The overview of cash forecasting tools covers how these feeds fit together, and the review of cash management solutions compares the options.
Start with last quarter’s DSO movement
Decompose last quarter’s DSO movement into billing timing, mix and collection behavior. If most of the change reflects billing timing, investigate the underlying invoices before attributing the movement to collections performance. A Datarails demo shows what that looks like on your own data.
