Net 30 Is a Lie
👋 Hi, it’s CJ Gustafson and welcome to Mostly Metrics.
Tutorial number four in our series on Claude and Excel. (Haven’t installed Claude for Excel yet? Here’s the 5-minute setup.)
A couple weeks back my CFO buddy Brian Weisberg reminded me how important collections timing is for a typical B2B software company. Even if you’re profitable on the P&L, it’s very possible to be cash poor on a working capital basis. This problem is exacerbated if you have venture debt and need to maintain a minimum cash balance, and a whale or two of a customer deciding to drag their feet on an annual payment trips you into default.
So forecasting near-term cash is more about predicting when money actually enters and leaves the building rather than when deals actually close (a sticky wicket of a problem).
As someone who used to deal with Fortune 500 Auto suppliers as his customers, the answer is almost never what’s printed on the invoice. Even if your contract says Net 30, your customer hasn’t paid in thirty days since the Obama administration. They pay in fifty-five, sometimes seventy.
Net 30 is a lie, and yet we keep telling it to our forecast.
So I built a fake company called Meridian Software. And I recorded myself building a full 13-week rolling cash forecast for it, live, from eight messy data sources, using Claude and Excel.
What’s really cool about having AI to help with this exercise is we can incorporate signals from multiple areas of the company to make the forecast as realistic as possible. AI is great at synthesizing data points (that sometimes even disagree with each other) from places like bank accounts, payroll files, AR open invoices, AR collections history (how does this customer actually behave?), AP vendors, and a whole bucket of other random outflows that tend to slip through the cracks (don’t forget about D&O insurance, or that management team offsite you gotta pay for!)
Below the line, readers get:
The full build video, start to finish
The exact Claude prompts
The raw data file, all eight messy tabs
The finished model
Snapshot of final forecast
You’ll have everything to try this yourself.
Here’s the full walkthrough.
And here’s everything behind it.
What Claude built
I started from a fake company with data scattered across eight tabs: a NetSuite invoice export with terms entered five different ways, a duplicate invoice, a credit memo, twelve months of collections history, a hand-built contracts database, AP and vendor agreements, a payroll calendar with a sneaky three-payroll July, and a pile of one-timers all landing in the same ugly week. Claude turned it into six tabs:
Assumptions. The levers plus the payment-behavior cadence table Claude pulled from the collections history. That table drives the whole model.
Collections. Every receivable with two computed cash dates side by side, nominal terms and actual behavior.
Disbursements. Every payable, payroll run, and one-timer, mapped to the day cash actually moves.
13-Week Forecast. Beginning cash, collections, disbursements, net change, ending cash, chaining week to week. Weeks 1-2 are actuals, 3-13 roll up from the tabs above, and the $500K covenant sits on its own line with an OK/BREACH flag that trips on its own.
Variance (Roll). The actual-versus-forecast tie-out that teaches the rolling discipline.
The “wow” moment
On nominal terms, Meridian cruises. The lowest its cash ever gets is $646K, comfortably above the floor, every single week. If that’s the forecast you carry into your CFO’s office, you tell her you’re in great shape and you go get a sandwich.
Now run it on real payment behavior. Week nine is the cliff: roughly $800K leaves in one week, the third July payroll stacking up against a tax installment, the D&O premium, and August rent and benefits all at once. On nominal terms, Northwind’s $480K invoice shows up that same week and catches it. But Northwind doesn’t pay in thirty days. It pays in fifty-eight. So that money isn’t there in week nine. It lands in week thirteen.
That gap is the whole story. Cash bleeds through weeks ten and eleven with nothing big coming in, and in week eleven Meridian goes straight through the covenant, bottoming at $449K in week twelve before Northwind finally pays. Profitable company, clean P&L, sitting in technical default on its loan.
It’s the Wile E. Coyote thing. On real behavior you already ran off the cliff in week nine. You just don’t fall until you look down, and looking down is week eleven. The bridge Meridian needed to not breach was $51,000, against $700K of untouched revolver sitting right there. The fix was a layup. The only reason it becomes a crisis instead of a Tuesday is that on nominal terms you never see it coming.
If you want this running for real instead of rebuilt by hand every Monday, that’s the kind of job a platform like Abacum is built for, running it continuously off your live actuals. Brian’s whole architecture was Claude for the data, a system to run it.
How to try this yourself
Same workflow as always.
Open it in Excel, fire up the Claude sidebar, and follow along.
Step 1: Let Claude audit the data and learn how people actually pay
Open Claude desktop (or the sidebar in Excel), paste in the Open Invoices and Collections History tabs, and type:
I'm building a 13-week rolling cash forecast. I've got data from all over: a NetSuite invoice export, twelve months of collections history, a contracts database I built by hand for my biggest accounts, our AP aging plus vendor agreements, a payroll calendar, and a list of lumpy one-time payments. I also have a $500K minimum-cash covenant on my revolver that I cannot breach.
Before we build anything in Excel, do two things.
First, audit this data. Tell me what's dirty, what's duplicated, what's missing, and what decisions I need to make.
Second, and this is the important one: look at my collections history and work out how each customer ACTUALLY pays versus the terms on their invoice. I don't want to forecast collections on nominal Net 30. I want to forecast them on real behavior. Give me an average days-to-pay per customer and flag anyone who pays well off terms.
Claude will catch the duplicate invoice, the name variants, the blank terms, and the credit memo, and it’ll hand you back a days-to-pay number for every customer. Then make your calls.
Step 2: Make the decisions
Here’s what Claude flagged and how I answered:
Duplicate invoice? → Drop it. One Initech receivable, not two. The dupe would’ve had you collecting $176K from a customer that owes you $88K.
Blank and inconsistent terms? → Default anything blank or weird to Net 30.
Credit memo? → Apply the Hooli credit as a reduction in week 1.
Forecast on terms or behavior? → Behavior. This is the whole point. Use the days-to-pay table, not the invoice.
The long tail? → Model the whales one by one off their real cadence. For the dozens of small monthly accounts, assume one flat tops-down number per week. I used $35K.
Step 3: The build prompt
Then I had Claude desktop help me write a clean prompt to feed its brother in Excel. This is the one I prepped:
Build a 13-week rolling cash forecast starting Monday, June 1, 2026.
Collections: use the cadence table we just built so each receivable lands on the customer's ACTUAL days-to-pay, not nominal terms. Split collections into three lines: open AR, contract whales, and a tops-down long tail at $35K/week.
Disbursements: pull from the AP, payroll calendar, and one-time tabs. Split into three lines: payroll and comp, opex and AP, and taxes and one-time. Map every item to the week the cash actually moves.
Structure each week as: beginning cash, total collections, total disbursements, net change, ending cash. Beginning cash chains from the prior week's ending. Weeks 1 and 2 are actuals; I'll give you those numbers.
Add a $500K minimum-cash covenant line, a headroom line, and an OK/BREACH flag.
Then build a second view: ending cash two ways, side by side, on nominal terms and on actual behavior. Chart both against the covenant floor.
Finally, give me headline cells: minimum cash, the first week I breach, and how big a revolver draw I'd need to stay above the line
When it asks for the weeks 1-2 actuals, hand it these: beginning cash $2,350,000; week 1 collections $71K, payroll $312K, rent and benefits $90.5K; week 2 collections $168K, AP runs $71.2K.
Step 4: Check the output
Things to spot-check:
Collections — Northwind should land in week 13 on behavior but week 9 on nominal. That gap is the whole model. Globex should land at 70 days, not 30.
13-Week Forecast — Weeks 1-2 should tie to your actuals within a few thousand dollars. The BREACH flag should trip in week 11 on the behavior view and never on nominal.
Headline cells — Min cash $449K behavior, $646K nominal. First breach week 11. Revolver bridge needed: $51K.
Step 5: Run the scenario live
Once the model’s clean, this is the fun part. After the breach shows up, I asked Claude something I didn’t script:
I breach the covenant in week 11. Walk me through my options to avoid it.
The answer will vary, but it’ll surface the real levers: draw the revolver, lean on Northwind to pay early, push the tax installment a few days, slow-walk a vendor. Watching it reason through a fix in real time is the part that would’ve been a 45-minute scramble by hand.
The payment-behavior cadence table
Here’s the days-to-pay table from Meridian. Swap in your own customers and run the same model on your real book.
Globex: terms Net 30, pays 70 (chronic, +40)
Northwind: terms Net 30, pays 58 (severe, and it’s the whale)
Cyberdyne: terms Net 45, pays 52
Wonka: terms Net 30, pays 38
Soylent: terms Net 30, pays 36
Fabrikam: “due on receipt,” pays 34 (the terms are fiction)
ACME: terms Net 15, pays 22
Umbrella / Pied Piper / Vandelay: Net 30, pay 33-34
Initech / Stark: Net 30, pay 31 (on time)
Contoso: Net 45, pays 46 (on time)
The model levers: beginning cash $2.35M, covenant floor $500K, long-tail collections $35K/week.
What I’d do differently on your real data
Pull your own collections history first. Before you build a single cell. My cadences are made up. Yours are sitting in your billing system right now, and the gap between what you assume and what’s real is the size of the surprise waiting for you.
Your covenant might be tested differently. Meridian’s is a simple minimum-cash floor. Yours might be a fixed-charge coverage ratio or a borrowing-base calc. Model the actual test in your credit agreement, not a generic one.
Mid-quarter contracts. I forecast future invoices off a hand-built contracts database. If you’ve got a real CPQ or billing system, point the model at that instead.
The tail deserves a second look. I used one flat weekly number. If your small accounts are seasonal or lumpy, build the tail with a little more shape.
Next time we’re building something else. Open to requests.
CJ
