See the tight week coming.
A free 13-week cash-flow forecast in Excel. Put in your balances and it shows which week cash runs short, and by how much, while there's still time to fix it.
Set your floor. See the red weeks.
This is the sample forecast for Harbor Lane Supply, a fictional distributor. It starts with $168,000 in the bank. Slide the floor to the least cash you'd want on hand. Any week that ends below it turns red.
2 weeks end below $50,000 (weeks 11, 13). Lowest: week 11, $43,100. Gap to close: $6,900.
At a $50,000 floor, week 11 dips to $43,100 after a $38,000 estimated tax payment, and week 13 dips again. The gap is $6,900, and the owner sees it ten weeks out. Collecting a couple of the oldest invoices would cover it. So would moving one inventory order back a week.
What you get
A 13-week forecast
For every week: the cash you start with, what's likely to come in, what has to go out, and what's left at the end.
A floor you set
Pick the least cash you want in the bank. Any week that ends below it turns red, and the sheet shows how big the gap is.
A working-capital check
How many days your cash sits in unpaid invoices, inventory and vendor bills, plus the two ratios a lender looks at first.
What it counts
Money coming in
- Invoices customers already owe, grouped by age. Newer ones are expected sooner and in full. Older ones are spread over later weeks and only partly counted. For invoices more than 90 days old, the default is 30 percent.
- New sales, starting after your normal collection time. If customers take about six weeks to pay, new sales show up as cash from week 7.
- One-off items you add, like an equipment sale or a tax refund.
Money going out
- Vendor bills you already owe, oldest first.
- New purchases, on your usual payment timing.
- Payroll on your real pay dates, weekly or every two weeks.
- Loan and lease payments on their due dates.
- Rent, utilities and the rest of your regular overhead.
- One-off items like estimated tax payments or owner draws.
You can change every assumption: the collection percentages, the timing, the floor.
How long each dollar is tied up
Days in inventory plus days to collect, minus the days you take to pay vendors, is how many days your own cash has to cover.
70 days in inventory plus 40 days to collect minus 35 days to pay vendors equals 75 days for Harbor Lane.
Each day has a dollar value. For Harbor Lane, collecting one day faster frees about $13,000. Holding one day less inventory frees about $8,450, and so does paying vendors one day later.
The sheet also shows how much of what customers owe you is more than 60 days old (8.7 percent for Harbor Lane), and the same for what you owe vendors (3.0 percent). These ranges are common rules of thumb. Your industry and your lender may use different ones.
Two ratios lenders check first
- Current ratio 3.41
- Short-term assets ÷ short-term bills. The usual comfort range is 1.5 to 2.5, so 3.41 can mean cash is parked in inventory that could be working.
- Quick ratio 1.81
- Cash and receivables ÷ short-term bills, leaving out inventory. Harbor Lane sits inside the usual 1.0 to 2.0 range.
Get the blank workbook
Five tabs: How To Use, Inputs, 13-Week Forecast, Working Capital and Data Log. Every number you can type over is in blue. It's a standard .xlsx file, so there are no macros to enable.
Or have us run it every week
We pull four reports from QuickBooks Online: receivables aging, payables aging, profit and loss, and balance sheet.
The forecast tool only reads. It can request those four reports and nothing else, and it's built to refuse any attempt to change your books.
Your assumptions carry over. Pay dates, loan payments and one-off items stay put, so each update only swaps in fresh numbers.
We go over the red weeks with you and agree on which fix to make first.
A forecast is an estimate based on your books and the assumptions in the sheet. It isn't a guarantee of future results, and it isn't tax, legal or investment advice. Harbor Lane Supply is a fictional company. QuickBooks is a trademark of Intuit Inc.