The P&L Smiled. The Bank Account Was Screaming.
A profitable business can still run out of cash. Discover how late-paying customers distort forecasts and how Excel exposes the payment gap.
There Is a Hole Between Your Revenue and Your Bank Account
Look at these two lines. The first shows monthly revenue, rising confidently across the screen, as though the business has finally discovered the secret to effortless growth.
The second shows the bank balance, staggering downward like somebody descending a staircase in the dark.
Do you see the growing space between them?
That divergence is a warning. To measure the collection gap, follow the invoices themselves: how much of each cohort remains unpaid?
It is where unpaid invoices live, where optimistic cash forecasts go to die, and where otherwise healthy businesses discover that accounting profit cannot pay Friday’s wages.
Consider an illustrative consulting firm with a familiar problem. Its management team celebrates a profitable month, approves several new expenses, and then discovers that the expected money has not arrived.
The spreadsheet was not missing the invoices. It was missing the time.
The Lie
Our imaginary consultancy records $100,000 in revenue during April, against $70,000 of expenses.
The accounting report shows $30,000 in profit, assuming those are all the relevant recognized revenues and expenses.
Beautiful.
Then the cash forecast makes one quiet, catastrophic assumption: all $100,000 invoiced in April will arrive in April.
This chart looks polite, but it is lying to us.
Come, open the receivables export.
Some customers settle quickly. Others take sixty days. A few regularly drift toward ninety, often after three reminder emails and a mysterious silence from accounts payable.
The invoices may all be valid, but they do not all represent immediate liquidity.
Suppose historical payment behavior for this particular group of invoices suggests the following collection pattern:
| Collection period | Expected receipts |
|---|---|
| April | $40,000 |
| May | $35,000 |
| June | $25,000 |
| Total | $100,000 |
These are illustrative expectations, not guaranteed payments.
Only $40,000 arrives during April. Meanwhile, suppose the firm must actually pay $70,000 in expenses that month.
If it began April with $20,000 in cash, the simplified calculation becomes:
$20,000 + $40,000 − $70,000 = −$10,000
That is a projected funding shortfall of $10,000, before considering any other receipts or payments.
The business must find financing, collect faster, or delay an outgoing payment before the cash is exhausted.
Yet the profit report still shows $30,000.
Neither statement is necessarily incorrect. The dishonesty begins when we pretend they answer the same question.
Profit measures economic performance. Cash determines whether the business can meet its obligations.
The Truth
The solution is not another average payment-delay figure. The solution is to inspect the distribution.
Let us follow the invoices out of the accounting system and into the bank.
First, separate the whales from the barnacles
Not all customers behave alike.
One client pays a modest invoice nine days after receiving it. Another signs enormous contracts, demands ninety-day terms, and routinely adds three more weeks to the wait.
The second customer looks magnificent in the sales report. In the cash forecast, that customer may be a very expensive source of uncertainty.
This is why I dislike a single company-wide collection average.
The average hides the shape.
Export invoice-level data into Excel, including customer, invoice date, due date, amount, payment status, and actual bank-cleared date.
Then segment the customers by observed behavior:
- Fast payers: Customers who regularly settle within agreed terms.
- Slow payers: Customers who repeatedly pay after the due date.
- Uncertain payers: Customers with disputed invoices, irregular payment histories, or prolonged overdue balances.
These are working categories, not permanent judgments. Customers can move between them as new evidence arrives.
And do not confuse invoice count with invoice value. A hundred tiny invoices paying promptly may matter less to cash flow than one enormous invoice delayed by a month.
Next, measure the actual waiting time
Suppose invoice dates are in column C and bank-cleared payment dates are in column F.
For fully paid invoices, calculate days to payment:
=IF(F2="","",F2-C2)
Now inspect the distribution by customer, not just the overall mean.
Who consistently pays within fifteen days? Who clusters around sixty? Which customers produce the long, uncomfortable tail?
For unpaid invoices, track overdue days separately using the due date in column D:
=IF(F2="",MAX(0,TODAY()-D2),0)
There is an important trap here. Calculating payment speed only from completed payments excludes customers whose money has not arrived at all.
That can make slow-paying customers look safer than they really are.
Keep outstanding balances visible, especially those already beyond their usual payment window.
Replace Invoice Dates With Collection Windows
Now comes the useful part.
Rather than assuming every invoice turns into cash immediately, build a monthly collection schedule based on historical payment patterns, current invoice aging, and known customer circumstances.
Our April invoice cohort, for example, produces an expected $40,000 in April, $35,000 in May, and $25,000 in June.
But real businesses have multiple invoice cohorts overlapping. April may also receive payments from February and March, while new invoices create additional future obligations to collect.
Add the expected receipts from every cohort into a single monthly cash view, alongside payroll, rent, taxes, supplier payments, debt obligations, and other anticipated outflows.
At the bottom of the workbook, calculate:
=OpeningCash+ExpectedReceipts-ExpectedPayments
Follow that ending cash balance across the next several months.
Do you see the valley forming in May? That is the point where delayed receipts and fixed expenses collide.
Now stress-test the forecast. What happens if the slow-paying customer group takes another fifteen days? What if a large invoice is disputed? What if only part of an overdue balance is ultimately collected?
Do not assign false precision to uncertain dates. Use realistic collection windows, update assumptions when evidence changes, and keep a conservative scenario alongside the baseline.
These two views follow only the illustrative April invoice cohort. Receipts remain expectations, not confirmed deposits. The zero revenue entries mean this cohort contributes no further recognized revenue in May or June; they say nothing about other sales.
| Month | Cohort revenue | Expected receipts | Still uncollected |
|---|---|---|---|
| April | $100,000 | $40,000 | $60,000 |
| May | $0 | $35,000 | $25,000 |
| June | $0 | $25,000 | $0 |
The same collection schedule, followed across time:
APRIL MAY JUNE
Invoice cohort: $100,000
Collect $40,000 -----------> Collect $35,000 --> Collect $25,000
Uncollected: $60,000 $25,000 $0The collection gap is the unpaid balance of this cohort: $60,000 after April, then $25,000 after May. It closes in June only if these expected receipts arrive. Add other invoice cohorts and all outflows separately before forecasting the bank balance.
The Bank Balance Gets the Final Word
A forecast that recognizes revenue but ignores payment behavior is only telling half the story.
The missing half is not buried in complicated mathematics. It is waiting in the invoice export, in the aging report, and in those stubborn little clusters of customers who always seem to pay next month.
Once we make those patterns visible, cash shortages become easier to anticipate, even when their exact timing remains uncertain.
We can negotiate payment terms earlier, follow up before invoices become seriously overdue, and avoid committing cash that has not yet arrived.
The goal is not to make the forecast look reassuring. It is to make the danger visible while there is still time to respond.
An invoice is a promise. A bank deposit is evidence. Never draw them as the same line.
FAQs
Why can a profitable business run out of cash?
Profit and cash follow different clocks. Under accrual accounting, revenue can be recognized before customers pay, while payroll and supplier payments may come due immediately.
How do I forecast late customer payments in Excel?
Export invoices and bank-cleared payment dates, group customers by historical payment behavior, then schedule expected receipts by month rather than assuming every invoice is paid on time.
What should I track besides average days to pay?
Track unpaid invoice aging, payment-delay distributions, customer-level behavior, disputed balances, and the proportion of invoice value collected within each time period.