Blog · Forecast and pipeline · Finance and FP&A teams
Test customer collections, sales receipts and payment dates together to find a liquidity shortfall. A worked weekly example shows the formulas, the minimum balance and the checks behind each scenario.
Cash flow scenario analysis tests whether dated customer receipts and business payments leave enough cash under different assumptions. The decision figure is the lowest usable balance against an agreed buffer, together with the date it occurs. A positive closing balance at the end of the forecast does not prove that every earlier payment can be made.
Start from a reconciled cash flow forecast. This analysis adds timing stresses and combined assumptions to that baseline. For the separate operating-profit method, see scenario analysis in Excel; a profit scenario needs additional collection and payment schedules before it becomes a liquidity scenario.
Agree the legal entity, bank accounts, forecast start date and usable opening cash. Reconcile that balance to bank records and identify restricted amounts separately. Do not count an undrawn facility as cash already in the account.
Next, agree the minimum operating buffer and the period detail needed for the decision. The buffer is a management assumption, not an accounting balance. Record who approved it and what payments it is meant to protect.
Gather the receivables aging, expected collection dates, supplier payment schedule, payroll dates, tax payments and approved capital spending. Retain invoice IDs and payment references. A scenario can then change a date or an amount without losing the original row.
Useful receipt columns are invoice ID, customer, expected receipt date, amount, scenario and evidence. Payment rows need the equivalent supplier or commitment ID and expected payment date. Keep actual cash movements distinct from forecast movements so closed periods cannot be changed by a new assumption.
Consider an illustrative US distributor with $40,000 usable opening cash and a $20,000 minimum buffer. Its next three weekly periods have the following base plan. Payments are positive outflow amounts, so the balance formula subtracts them.
Closing cash = Opening cash + Receipts − Payments
Minimum headroom = Lowest closing cash − Required buffer
| Scenario | Week | Opening cash (USD) | Receipts (USD) | Payments (USD) | Closing cash (USD) |
|---|---|---|---|---|---|
| Base | 1 | 40,000 | 50,000 | 60,000 | 30,000 |
| Base | 2 | 30,000 | 60,000 | 55,000 | 35,000 |
| Base | 3 | 35,000 | 65,000 | 60,000 | 40,000 |
| Collection delay | 1 | 40,000 | 30,000 | 60,000 | 10,000 |
| Collection delay | 2 | 10,000 | 60,000 | 55,000 | 15,000 |
| Collection delay | 3 | 15,000 | 65,000 | 60,000 | 20,000 |
| Combined stress | 1 | 40,000 | 30,000 | 65,000 | 5,000 |
| Combined stress | 2 | 5,000 | 54,000 | 55,000 | 4,000 |
| Combined stress | 3 | 4,000 | 58,500 | 60,000 | 2,500 |
The collection-delay case moves one $20,000 invoice from week one to week four. It remains receivable; it is not written off. Although week four is outside this display, it must remain in the working schedule.
The combined stress keeps that delay and changes two further assumptions. Weeks two and three contain receipts from newly completed cash-on-delivery sales; those sales fall 10%, reducing receipts by $6,000 and $6,500. A supplier also requires a $5,000 payment in week one instead of week four. Other payments remain fixed because management has not approved immediate cost reductions.
All dollar calculations are exact. The percentage reduction applies only to the specified cash-on-delivery receipts, not to every open receivable.
For one scenario block in Excel, use A for the weekly start date, B for opening cash, C for receipts, D for payments, E for closing cash and F for headroom. Put the buffer in H1. The first closing balance, next opening balance and headroom are:
=B2+C2-D2
=E2
=E2-$H$1
The second formula belongs in B3; fill the balance formulas through the remaining weekly rows. For this three-week block, calculate the minimum balance and minimum headroom with:
=MIN(E2:E4)
=MIN(E2:E4)-$H$1
Microsoft's MIN documentation confirms that the function returns the smallest number. It also explains that empty cells in references are ignored, so a missing forecast row must be flagged rather than accepted as a completed period.
To assemble receipts from dated rows, put the scenario name in H2. If the Receipts sheet has dates in C, amounts in D and scenario labels in E, weekly receipts in C2 are:
=SUMIFS(Receipts!$D$2:$D$100,Receipts!$C$2:$C$100,">="&$A2,Receipts!$C$2:$C$100,"<"&($A2+7),Receipts!$E$2:$E$100,$H$2)
This uses the multiple-criteria structure in Microsoft's SUMIFS documentation. Use a matching payment formula against the payment schedule. Covirage is not affiliated with Microsoft.
| Scenario | Lowest closing cash (USD) | Week of lowest balance | Minimum headroom (USD) |
|---|---|---|---|
| Base | 30,000 | 1 | 10,000 |
| Collection delay | 10,000 | 1 | -10,000 |
| Combined stress | 2,500 | 3 | -17,500 |
Both stressed cases first breach the buffer in week one. The combined case needs $17,500 of additional cash or reduced outflows to restore the buffer at its lowest displayed point. That is different from the amount needed merely to avoid a negative balance.
The base case has $175,000 receipts and $175,000 payments, so $40,000 opening cash also becomes $40,000 closing cash. The collection-delay case has $155,000 receipts and the same $175,000 payments, producing $20,000 closing cash.
Combined receipts are $142,500 and payments are $180,000. The independent check is $40,000 plus $142,500 minus $180,000, equaling $2,500. A second bridge starts with base closing cash: $40,000 minus the delayed $20,000, minus lost receipts of $12,500, minus accelerated payments of $5,000 also equals $2,500.
The finance owner should assign the collection action to credit control, payment discussions to procurement and funding decisions to treasury or the CFO. Recalculate approved actions with their actual dates. Use incremental cash flow analysis for the cash effect of a specific proposed action, rather than treating the entire scenario difference as that action's benefit.
Bring a sample of your own receivables, ledger and dated scenario schedules to Covirage. Covirage maps the columns; its tools compute and check the supplied receipt, payment and cash comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring the rows behind your lowest cash balance.
See FP&A reporting for the supporting approach.
A sensitivity changes one input while other assumptions stay fixed. A scenario combines changes that could occur together, such as slower sales and delayed collections. Both are useful, but the scenario must explain why its assumptions belong together and how they change dated receipts or payments.
Use periods short enough to reveal the funding decision. Monthly planning can support a longer horizon, but a payroll or supplier payment may require weekly or daily detail. Extend the schedule far enough to show receipts and payments that move beyond the initial period.
No. Profit and cash reflect different timing and movements. A company can record profitable sales before customers pay, while inventory purchases, equipment and debt principal consume cash. Review the cause of the shortfall before assuming that a change in operating margin will resolve it.
Management should set it from payment commitments, uncertainty and access to usable funding. Label it as a policy input rather than a universal benchmark. Test available cash against the buffer at the dates obligations fall due, and keep restricted cash outside the usable balance.