Cash at risk in a spreadsheet, step by step.

One sheet of history, one sheet of open balances.

Export two files from your accounting system into one workbook. On a sheet named Closed: invoice ID, customer key, invoice date, due date, payment date, amount. On a sheet named Open: the same columns without payment date. Use at least a year of closed invoices where possible, and keep customer keys identical across both sheets—the whole method joins on them.

Leave out names, emails, phone numbers, and notes. They add privacy risk and no predictive value here.

One helper column defines everything downstream.

On the Closed sheet, add two columns. With due date in D and payment date in E:

was_late  =IF(E2>D2, 1, 0)
days_late =MAX(0, E2-D2)

Make sure both columns are real dates, not text—ISNUMBER(D2) should return TRUE. Agree on the definition with the team first: some businesses ignore delays under a grace period, and disputed invoices may deserve their own category. Whatever rule you choose, apply it to every historical row before going further.

Summarize how each customer actually pays.

On a third sheet, list unique customer keys, then compute for each one (customer key in A2, Closed data in columns B, G, H):

invoice_count  =COUNTIF(Closed!B:B, A2)
late_count     =SUMIF(Closed!B:B, A2, Closed!G:G)
late_rate      =late_count / invoice_count
typical_delay  =AVERAGEIFS(Closed!H:H, Closed!B:B, A2, Closed!H:H, ">0")

Also compute the portfolio-wide late rate once: =AVERAGE(Closed!G:G). You now have, for every customer, how often they pay late and by how many days when they do.

Two invoices are not a track record.

A customer with two invoices, one late, is not “a 50% late payer”—you simply lack evidence. Blend each customer’s rate toward the portfolio rate in proportion to how little history they have. With late_count in C, invoice_count in B, and the portfolio rate in a cell named port_rate:

blended_rate = (C2 + 2*port_rate) / (B2 + 2)

The constant 2 acts like two phantom invoices at the portfolio’s typical behavior. A customer with 40 real invoices barely moves; a customer with one invoice lands close to the portfolio average. This one formula prevents most of the silly conclusions a naive spreadsheet produces.

Multiply, rank, and add an expected date.

On the Open sheet, look up each invoice’s customer stats and compute three columns (amount in F, customer key in B, due date in D):

late_chance   =VLOOKUP(B2, Customers!A:F, 6, FALSE)   // blended_rate
cash_at_risk  =F2 * late_chance
expected_date =D2 + late_chance * VLOOKUP(B2, Customers!A:E, 5, FALSE)

Sort descending by cash at risk. The top of that list is your collections queue: the invoices where size and likelihood of delay combine into the largest threat to this month’s cash. Grouping expected_date by week (a pivot table works) gives a rough forecast of when the open balance should actually arrive.

What the spreadsheet version cannot do.

1. It is untested. The blended rate is a sensible rule, but nothing here checks it against newer invoices it has not seen. A method can look reasonable and still rank worse than the portfolio average.
2. It sees one signal. Invoice size relative to the customer’s norm, payment terms, seasonality, and recent behavior changes all carry information a single late rate ignores.
3. It goes stale. Every refresh means re-exporting, re-pasting, and hoping no formula range slipped.

When the spreadsheet becomes the bottleneck, the free PaidWhen workspace runs the same idea properly: it learns from your closed invoices, tests itself on newer ones it did not train on, tells you whether the estimate beats the simple late-rate rule, and builds the ranked queue and weekly cash timeline automatically—entirely in your browser.