Cash collection forecast

Purpose

Produce a dated weekly forecast of customer cash receipts from open invoices and documented collection evidence. The forecast explains why each receipt is placed in a week and shows the exposure that remains uncertain.

Scope

Cover open AR invoices, unapplied credits, disputed amounts, customer payment history, written payment promises, and the prior forecast. Allocate expected receipts to the reporting weeks and compare them with the prior version.

Excluded: posting cash, changing invoice status, contacting customers, and deciding credit terms.

Data basis

• AR aging with invoice number, customer, due date, open amount, currency, dispute flag, and salesperson.
• Payment-history table with invoice settlement date and amount; collection log with promise date, promised amount, and confidence.
• Credit-note register, prior collection forecast, and the selected forecast start date.

Result

A receipts sheet with invoice-level week, amount, basis, confidence, and citation, plus a memo showing weekly totals, forecast movement, overdue exposure, and unresolved promises.

Quality criteria

• The invoice population reconciles to the AR aging open balance after documented exclusions.
• Every predicted receipt cites an invoice row, payment-history row, promise, or explicit assumption.
• Disputed and credited amounts are excluded only with a cited amount and reason.
• Weekly totals use one currency treatment and reconcile to invoice-level rows.

Instructions

Use invoice due date as the starting point. Move a receipt to the promise week only when the collection log identifies the invoice, amount, and date. Use observed customer payment lag when no promise exists; state the lookback window and never treat a dispute as collectible until its undisputed amount is identified. Separate committed, behavior-based, and assumption-based receipts. Flag any weekly shortfall against the company liquidity threshold defined in Adapt before use. Compare the new forecast with the prior forecast by week and invoice, preserving additions, removals, and date shifts.

Adapt before use

• Map AR, payment-history, dispute, credit-note, and collection-log columns.
• Set the payment-history lookback period, confidence labels, currency conversion rule, and liquidity threshold.
• Add the prior forecast format and define which promise statuses count as committed.

Process detail

Define the receipt population

Identify the forecast start date, thirteen forecast weeks or the configured horizon, and every open invoice with a non-zero balance.

Data basis: AR aging invoice number, customer, due date, open amount, currency, and status columns.

Result: An invoice census with opening balance and eligibility status.

Acceptance criterion: Count of eligible invoice balances reconciles to the AR aging population after cited exclusions.

Exception: Exclude no row silently; mark duplicate invoice numbers or missing due dates as unresolved.

Apply credits and disputes

Reduce each invoice by linked credit notes and separate disputed from undisputed balances.

Data basis: Credit-note register, dispute log, and AR aging invoice identifiers and amounts.

Result: Collectible balance by invoice with credit and dispute components.

Acceptance criterion: No collectible balance is negative and total reductions equal linked credit-note amounts.

Exception: Retain an unlinked credit or dispute as an open exception rather than allocating it by guesswork.

Assign receipt weeks

Place each collectible invoice amount into a forecast week using promises, payment lag, or due-date rules.

Data basis: Collection commitments, payment-history settlement dates, invoice due dates, and company placement rule.

Result: Invoice-level receipt week, amount, basis, and confidence.

Acceptance criterion: Every non-zero forecast row has exactly one week and one named basis.

Exception: Use an uncertainty bucket when the date evidence cannot support a week.

Reconcile with the prior forecast

Match invoice rows to the prior forecast and classify amount changes, date shifts, new items, and removed items.

Data basis: Current invoice forecast, prior forecast invoice number, week, and amount columns.

Result: Movement table explaining weekly forecast changes.

Acceptance criterion: Movement categories sum to current total minus prior total for every week.

Exception: Do not force a match when invoice identifiers conflict; list both records.

Prepare the collections view

Aggregate committed, behavior-based, and assumption-based receipts by week and rank overdue balances by customer.

Data basis: Invoice forecast, customer master, aging bucket, and confidence fields.

Result: Receipts sheet and cash-collection memo with weekly totals and top exposures.

Acceptance criterion: The memo totals equal the sheet and every ranked exposure has an invoice citation.

Exception: Show a zero or empty week explicitly and explain any currency aggregation limit.

Document what could not be assessed

Record missing promises, absent payment history, unlinked credits, disputed balances, and unsupported week assignments.

Data basis: Exception log and all intermediate reconciliation tables.

Result: Open-points section in the memo with affected invoice and evidence gap.

Acceptance criterion: Every open point names an invoice or source row and its amount where known.

Exception: State that no open points were found only after checking the complete invoice census.