Lesson 5.3 45 min
The 13-week cash forecast
A direct weekly forecast from billings, bills and one-off payments, with a minimum balance and what to do when it is breached.
A budget says what a year should look like. A cash forecast says whether the bank balance will stay above zero in each of the next few weeks. Thirteen weeks, a quarter, is long enough to see most obligations and short enough that the numbers are real.
The direct method
The forecast lists actual cash receipts and cash payments week by week, the direct method from lesson 1.5. It starts with the bank balance today.
Closing cash = Opening cash + Receipts − Payments
Each week's closing balance is the next week's opening balance.
Building the receipts
Receipts are not sales. They are sales from earlier weeks, when customers pay. The forecast needs a collection lag: how many weeks pass between billing and payment. Take it from history by looking at when recent invoices were actually paid, not the terms on the invoice. If customers on net 30 terms pay in six weeks, use six weeks.
Building the payments
List every payment under three groups.
- Regular and weekly: materials, fuel, subcontractors.
- Fixed and periodic: payroll on its pay dates, rent, vehicle loans and leases, insurance, software, usually monthly.
- One-off and irregular: tax instalments, equipment purchases, loan repayments, annual fees.
The one-offs are what the weekly view is for. A monthly budget can show a profit for the month while a one-off in week 6 empties the account for two weeks.
A minimum balance
Choose a minimum cash balance below which the business should not go, perhaps two weeks of payroll and fixed bills. Any week that closes below it is a signal to act while there is still time to act.
When the forecast shows a shortfall
There are four kinds of response, and they differ in cost.
- Speed up receipts: chase overdue invoices, bill sooner, ask for deposits.
- Slow down payments: negotiate time with suppliers, stagger payments to fall after receipts.
- Move or cut one-offs: delay the equipment purchase, ask the tax authority about a payment arrangement before the date, not after.
- Borrow: arrange the line or loan before it is needed. A lender is easier to persuade by a forecast showing a planned temporary gap than by an emergency request.
Update it weekly
A forecast is only worth keeping if it is updated. Each week, replace the first week's forecast with what actually happened, add week 14, and note where reality differed. Large gaps between forecast and actual show which assumption, the lag or the billing, is wrong.
Worked case
Hartley Plumbing, 13 weeks
Hartley Plumbing opens the quarter with $14,000 in the bank and wants never to go below $10,000. Customers have been paying about six weeks after billing, so each week's receipts equal the billings of six weeks earlier. Recent billings and the forecast for the next seven weeks are:
| Billings $ | |
|---|---|
| Week -5 | 12,800 |
| Week -4 | 12,100 |
| Week -3 | 11,000 |
| Week -2 | 9,600 |
| Week -1 | 9,000 |
| Week 0 | 10,200 |
| Weeks 1 to 7, forecast | 11,500, 12,000, 12,500, then 13,000 for four weeks |
The weekly payments are: materials $2,800 and fuel $490 every week; payroll of $8,770 every second week; rent, van payments, admin, insurance and software totalling $8,300 in weeks 1, 5, 9 and 13; a tax instalment of $7,500 in week 9; and a $12,000 purchase of new tools in week 6.
| Opening $ | Receipts $ | Payroll $ | Materials and fuel $ | Monthly bills $ | Tax and tools $ | Closing $ | |
|---|---|---|---|---|---|---|---|
| Week 1 | 14,000 | 12,800 | (3,290) | (8,300) | 15,210 | ||
| Week 2 | 15,210 | 12,100 | (8,770) | (3,290) | 15,250 | ||
| Week 3 | 15,250 | 11,000 | (3,290) | 22,960 | |||
| Week 4 | 22,960 | 9,600 | (8,770) | (3,290) | 20,500 | ||
| Week 5 | 20,500 | 9,000 | (3,290) | (8,300) | 17,910 | ||
| Week 6 | 17,910 | 10,200 | (8,770) | (3,290) | (12,000) | 4,050 | |
| Week 7 | 4,050 | 11,500 | (3,290) | 12,260 | |||
| Week 8 | 12,260 | 12,000 | (8,770) | (3,290) | 12,200 | ||
| Week 9 | 12,200 | 12,500 | (3,290) | (8,300) | (7,500) | 5,610 | |
| Week 10 | 5,610 | 13,000 | (8,770) | (3,290) | 6,550 | ||
| Week 11 | 6,550 | 13,000 | (3,290) | 16,260 | |||
| Week 12 | 16,260 | 13,000 | (8,770) | (3,290) | 17,200 | ||
| Week 13 | 17,200 | 13,000 | (3,290) | (8,300) | 18,610 | ||
| 13 weeks | 152,700 | (52,620) | (42,770) | (33,200) | (19,500) | 18,610 |
Receipts total $152,700 and payments $148,090 over the quarter, so Hartley ends week 13 with $18,610, above where it started. On the quarter the picture looks comfortable. By week it does not. The tool purchase in week 6 takes the balance to $4,050, which is $5,950 below the minimum, and weeks 9 and 10 are also below it, at $5,610 and $6,550, after the tax instalment and the monthly bills in week 9.
Testing the options
Delay the tools to week 10. Week 6 recovers, but the problem moves: by week 10 the tools, the week 9 tax instalment and the week 9 monthly bills have all been paid, and the balance is $6,550 either way, still under the minimum.
Customers pay two weeks later. If the collection lag stretches to eight weeks, receipts for the quarter fall by $6,000 and the lowest balance is $1,050 in week 10. This case assumes billings of $10,000 in each of the two weeks before the history shown. The business stays above zero, but with almost no room. The sensitivity that counts most for Hartley is how fast customers pay, so chasing invoices in weeks 1 to 4 is worth more than any other single action.
The decision Hartley needs to make is how to fund a gap of about $5,950 for three weeks. A temporary line arranged now costs far less than discovering the gap in week 6.
Template
13-week cash forecast workbook
A weekly forecast with inputs for the opening balance, minimum cash, billings history, collection lag, weekly and periodic payments and one-offs. Closing cash, the lowest week and the weeks below the minimum are calculated, and a flag column marks each breach. Hartley's quarter is loaded.
The full working file, 5-3-13-week-cash-forecast.xlsx, is in the Accounting and Finance Toolkit, Complete Track 1. The lesson above teaches the method and shows the worked numbers, so you can build it yourself in any spreadsheet.
Exercises
Work these, then open the answers
Find the first week in which Hartley's balance falls below $10,000, and by how much.
Answer
Week 6, which closes at $4,050 after the tools purchase. That is $5,950 below the minimum.
What changes in the forecast if Hartley moves the tool purchase to week 10 and customers keep to the six-week lag?
Answer
Week 6 recovers, and week 10 becomes the low point at $6,550, still below the minimum. It is the only week below it.
Explain why the collection lag should come from history and not from the terms on the invoices.
Answer
Terms say when payment is due, not when it arrives. Cash forecasts depend on when customers actually pay.
Check
4 questions
Sources
Sources
- Principles of Accounting, Volume 1: Financial Accounting, OpenStax, Rice University
- IAS 7 Statement of Cash Flows, IFRS Foundation