A 13-week cash forecast: how can you see in advance when the money in the bank account will run out?

Ravshan, the owner of the "Archa mebel" workshop, looks at the report at the end of every month and feels at ease: there are orders, and the annual result is a profit. But one day the bank returns the payment order for wages: there is not enough money in the bank account. The monthly report does not show such a day in advance, because it totals by month. Money, however, lives by weeks and days.

There is a simple tool for this problem: the 13-week cash forecast. 13 weeks is roughly one quarter. This period is long enough to see a danger in advance, and short enough that the figures are not yet guesses but rest on actual contracts and invoices.

All amounts are hypothetical, compiled by the author, in million soum. Tax payments are not included to keep the table simple; in practice be sure to add their amounts and due dates.

How is the forecast built?

The table consists of one row for each week. The columns: 1. Opening balance for the week: cash on hand (5010) and money in the settlement account (5110) together. For the first week, today's actual balance is taken. 2. Receipts: money coming from customers. Here it is not the sales amount that is entered but the week in which the money arrives: if the furniture is delivered in week 2 and the customer pays in week 4, the receipt is entered in week 4. 3. Payments by group: materials, wages, rent and other fixed payments, loan, large purchases. 4. Closing balance for the week = opening balance + receipts − payments. It becomes the opening balance of the next week.

Ravshan's conditions: - opening balance 60; - materials are paid to the supplier every week (from 30 to 55); - wages of 70 every two weeks; - workshop rent of 25 each in weeks 1, 5, 9 and 13; - bank loan repayments of 40 each in weeks 4, 8 and 12; - a new milling machine is planned to be bought in week 5 for 60; - orders increase from week 7: receipts grow under signed contracts.

In which week does the money run short?

For the quarter, total receipts are 1 315 and total payments 1 250 (materials 550, wages 420, rent 100, loan 120, machine 60). In other words, by the end of the quarter cash grows from 60 to 125. In a monthly or quarterly summary everything looks fine.

The weekly table, however, shows something else: from week 4 to week 8 the balance is in the minus for five weeks in a row, with the lowest point in week 6 at −135. The reason is three events falling at the same time: weeks with few orders, a loan repayment and the purchase of the machine. In real life there is no such thing as a "negative balance": it means unpaid wages, a delayed loan repayment or a supplier's shipment that has been stopped.

How can the gap be closed?

Because Ravshan drew up the forecast in week 1, he has four weeks in hand. He makes four decisions: 1. He moves the machine purchase from week 5 to week 11. Orders increase from week 7 anyway, and that is exactly when the machine will be needed. 2. He asks a major customer for an advance. Of the 145 due in week 8, the customer agrees to pay 30 in advance in week 4. 3. He reaches an agreement with the supplier. He postpones 20 of the materials payment in week 4 to week 7. 4. He collects an overdue receivable. The 30 expected in week 9 is paid by an old debtor in week 6.

Now no week goes into the minus. Most importantly: the balance at the end of the quarter is still 125. Ravshan did not earn or save a single extra soum, he only changed the timing of the money.

But the 5 in week 6 is a very thin cushion: if one customer delays payment by two days, it is minus again. That is why Ravshan discusses a small credit line or overdraft facility with the bank in advance. Even if it is not needed, it is easier to agree on it in advance than on the day the money runs out. Its cost and terms should be checked against the bank's actual offer.

How should the forecast be kept up to date?

A forecast is not a document drawn up once and put on the shelf. Every Monday: - last week's actual figures are entered in place of the forecast; - the table moves one week forward, and a new week 13 is added at the end; - the difference between forecast and actual is analysed.

Ravshan's first three weeks:

Over three weeks receipts came in 9 below the forecast, that is, about 3.6% less. This is not a large error, but its direction matters: if every quarter customers pay later than promised, the receipts column should be filled in more cautiously. With payments it is the opposite: wages, rent and loan repayments are known almost exactly, so do not understate them.

Practical tips

• Enter receipts not by sales but by each customer's usual payment discipline: if someone always pays 10 days late, his money is also entered 10 days late.
• Enter large payments (fixed assets, annual insurance, loan repayments) in the table first: gaps most often appear because of them.
• Set a minimum cushion for each week (for example, equal to two weeks' wages) and highlight in colour the weeks in which the balance falls below this limit.
• The forecast does not replace the financial statements: it is an internal management tool. The actual movement of cash is shown at year end in the statement of cash flows.