The 13-week cash flow forecast is the most important spreadsheet in a lot of companies and one of the worst maintained. It is the model that tells you whether you make payroll, whether you trip a covenant, whether the board conversation next month is about growth or about survival. And in most finance teams it is rebuilt, by hand, every Monday morning, by someone who would rather be doing almost anything else.
We have now pulled apart dozens of these models across companies of very different sizes. The failure modes are remarkably consistent. Here are the five we see every time, and what to do instead.
1. It forecasts revenue, not cash
This is the original sin. The model starts from the P&L (projected bookings or revenue) and assumes it converts to cash on a fixed lag. "We bill it, they pay in thirty days."
They do not pay in thirty days. Collections are lumpy, your DSO drifts up quietly when nobody is watching, and your three biggest customers each pay on their own schedule regardless of your terms. A revenue-derived cash forecast is smooth and confident and wrong in exactly the weeks it matters.
The fix: forecast collections off the accounts-receivable aging, not revenue off the income statement. Take what is actually outstanding, apply realistic payment timing by customer or cohort, and let the big accounts have their own line. Revenue tells you what you earned. The AR aging tells you when the cash shows up, which is the only thing a cash forecast is supposed to answer.
2. Nobody reconciles the forecast to actuals
Ask to see last week's forecast next to what actually happened, and in most shops you get a blank stare. The model gets rebuilt forward every week, but the prior version is never scored against reality. So the same optimistic collection assumptions, the same forgotten outflows, the same biases repeat indefinitely. The forecast never learns because no one ever checks it.
The fix: before you touch next week's numbers, lock last week's actuals beside the forecast you made and explain every material variance. This takes twenty minutes and it is the single highest-leverage habit in the whole process. A forecast you grade is a forecast that gets better. One you never look back at is just a guess you retype.
3. The roll-forward is manual, so it breaks
Each Monday someone deletes the oldest week, adds a new column on the right, and re-points a thicket of formulas by hand. Do this fifty times a year and the errors are not a risk. They are a certainty. A reference that quietly points one column off. A SUM that stops including the new week. A hardcoded number where a formula used to be. The model degrades a little every week, and the degradation is invisible until the day it produces a number that is badly wrong.
The fix: the roll-forward should be automatic. Whatever tool you use, the act of advancing the window by a week should not require a human to rewire formulas. If you are staying in Excel, that means a structure where the period is a parameter and nothing gets deleted or re-pointed by hand. The goal is that moving forward in time changes one input, not forty.
4. It produces a single line with no scenarios
A forecast that says "$1.2M at week thirteen" is close to useless for making a decision, because the decision is never about the expected case. It is about the downside. What happens to cash if your two largest receivables slip three weeks? If a deal you were counting on pushes? A single point estimate hides exactly the question you are running the model to answer.
And the endpoint is the wrong thing to watch anyway. Covenant breaches and missed payroll do not happen at week thirteen. They happen at the trough, the lowest cash balance somewhere in the middle of the period. A model that only shows you where you land tells you nothing about the cliff you might walk off on the way there.
The fix: run at least a base and a downside, and surface the minimum cash balance across the whole window, not just the ending balance. The number that should be on the first slide is "how low does cash get, and in which week," with a realistic downside next to it.
5. Lumpy outflows get smoothed into a run-rate
Payroll, debt service, quarterly taxes, the annual insurance premium, seasonal swings in payables. These are the items that actually break cash forecasts, and they are the ones most often buried in a smoothed average. The model spreads $X of "operating outflow" evenly across the weeks and completely misses that this is a three-paycheck month, or that the tax payment and the debt service land in the same week, or that a big annual bill is about to hit.
The fix: pull the large, known, non-recurring outflows out of the run-rate and place them in the specific weeks they actually occur. The lumpy stuff is not noise to be averaged away. It is the entire reason some weeks are tight and others are fine, and a forecast that smooths it is blind to the only weeks worth worrying about.
The real problem is the tool
Notice that none of these five are sophisticated. They are not modeling errors; they are consequences of doing a recurring, structured task in a tool that has to be rebuilt by hand every week. Excel is not the villain. It is extraordinary for building the logic once. It is a terrible place to operate a living forecast that has to advance every week, reconcile against actuals, and run more than one scenario without somebody quietly breaking a formula.
The teams that have gotten past this stopped treating the 13-week forecast as a spreadsheet they rebuild and started treating it as something that should update itself: live data feeding collections off the actual AR aging, an automatic roll-forward, scenarios on demand, and last week's forecast scored against what really happened, every week, without a person retyping any of it. Once the rebuild goes away, so do most of the five mistakes, because most of them were never really judgment errors. They were the cost of doing the same hard thing by hand, fifty-two Mondays a year.