A 13 week cash flow forecast Excel model tracks expected cash receipts and payments by week. Start with available cash, add forecast receipts, subtract forecast payments, and carry each closing balance into the following week. This direct-method structure helps a business identify potential cash shortages, test collection delays, and decide when management needs to investigate financing or adjust payment timing.
What a 13-week cash flow forecast shows
A 13-week forecast is a short-term liquidity model covering roughly one quarter. Unlike an income statement, it records when cash is expected to enter or leave the bank account, not when revenue or expenses are recognized under accounting rules.
The forecast has four essential components:
- Opening cash: Cash available at the beginning of the week.
- Cash receipts: Customer collections and other expected cash inflows.
- Cash payments: Payroll, supplier payments, rent, taxes, debt payments, and other outflows.
- Closing cash: Opening cash plus receipts minus payments.
The 13-week horizon is detailed enough for weekly treasury decisions but short enough to estimate individual receipts and payments. Its main limitation is uncertainty: later weeks usually depend on assumptions rather than confirmed transactions. The model should therefore be updated regularly rather than treated as a fixed prediction.
Set up the 13 week cash flow forecast Excel structure
Create columns for labels, Week 1 through Week 13, and optional notes. Use actual week-ending dates rather than labels alone so users know exactly which transactions belong in each period.
A practical row structure is:
- Opening cash
- Customer receipts
- Other receipts
- Total receipts
- Payroll
- Supplier payments
- Rent and occupancy costs
- Taxes
- Debt service
- Other payments
- Total payments
- Net cash flow
- Closing cash
- Minimum cash requirement
- Cash headroom or shortfall
Keep assumptions separate from calculations. For example, maintain schedules for customer invoices, supplier due dates, and payroll dates on supporting worksheets. This makes the forecast easier to audit and update.
Customer collection timing often depends on days sales outstanding, or DSO, which estimates how long customers take to pay. The explanation of DSO and its formula provides useful context, although a weekly cash model should use invoice-level due dates when reliable details are available.
Enter direct-method receipts and payments
The direct method forecasts identifiable cash transactions. For receipts, begin with open customer invoices and assign each expected collection to a week. Then add cash sales, tax refunds, asset-sale proceeds, or other inflows only when there is a reasonable basis for their timing.
For payments, use payroll calendars, supplier aging reports, lease schedules, tax deadlines, and debt agreements. An annual budget may help identify recurring costs, but it should not simply be divided by 52. A weekly model needs actual payment timing. If you are also learning how budgets connect with forecasts, see this overview of the annual operating plan process.
Avoid including noncash accounting entries such as depreciation. They affect reported profit but do not create a current-period bank payment. Also exclude transfers between the company’s own included bank accounts unless your model forecasts each account separately.
Excel formulas and a worked calculation
Assume column B contains Week 1, column C contains Week 2, and the following rows are used: opening cash in row 5, receipt categories in rows 6 and 7, total receipts in row 8, payment categories in rows 9 through 12, total payments in row 13, net cash flow in row 14, and closing cash in row 15.
- Total receipts in B8:
=SUM(B6:B7) - Total payments in B13:
=SUM(B9:B12) - Net cash flow in B14:
=B8-B13 - Closing cash in B15:
=B5+B14 - Week 2 opening cash in C5:
=B15
The following synthetic example is hypothetical and uses USD. It assumes no borrowing, interest, taxes outside the listed payments, or restrictions on the use of cash.
| Cash flow line | Week 1 | Week 2 | Week 3 | Week 4 |
|---|---|---|---|---|
| Opening cash | USD 50,000 | USD 30,000 | USD 48,000 | USD 23,000 |
| Total receipts | USD 30,000 | USD 45,000 | USD 25,000 | USD 60,000 |
| Total payments | USD 50,000 | USD 27,000 | USD 50,000 | USD 34,000 |
| Net cash flow | USD -20,000 | USD 18,000 | USD -25,000 | USD 26,000 |
| Closing cash | USD 30,000 | USD 48,000 | USD 23,000 | USD 49,000 |
For Week 1, the calculation is USD 50,000 opening cash + USD 30,000 receipts − USD 50,000 payments = USD 30,000 closing cash. That USD 30,000 becomes Week 2 opening cash. Apply the same formulas through Week 13.
If management sets a hypothetical minimum cash requirement of USD 25,000, calculate headroom as closing cash minus USD 25,000. Week 3 headroom would be USD 23,000 − USD 25,000 = USD -2,000, identifying a potential shortfall that needs investigation.
Model delayed-payment and collection scenarios
A base case alone can conceal timing risk. Duplicate the forecast into a downside scenario, then change only the assumptions being tested. Preserve the base worksheet so the effect remains visible.
Suppose a hypothetical USD 45,000 customer receipt expected in Week 2 moves to Week 4. Week 2 receipts fall from USD 45,000 to zero, while Week 4 receipts rise from USD 60,000 to USD 105,000. With all other assumptions unchanged, closing cash becomes USD 3,000 in Week 2, USD -22,000 in Week 3, and USD 49,000 in Week 4.
The final Week 4 balance is unchanged because the receipt was delayed rather than lost. However, the negative interim balance exposes a liquidity problem. This is why cumulative quarterly cash flow can look acceptable while weekly cash availability does not.
You can drive the scenario with an input cell instead of typing over formulas. If the delay amount is in cell B20, the downside Week 2 customer receipt could equal =Base!C6-$B$20, while Week 4 could equal =Base!E6+$B$20. Add controls preventing a delayed amount from exceeding the original receipt.
Timing problems may lead a company to evaluate borrowing, but debt availability, cost, covenants, and repayment terms must be assessed separately. For broader context on cash flows supported by dedicated financing arrangements, review how project finance works.
Add reconciliation checks and update controls
A forecast becomes useful when it is rolled forward consistently. At each update, replace the completed week’s forecast with actual receipts and payments, investigate material differences, and add a new week at the end to retain a 13-week horizon.
Add these checks to reduce spreadsheet errors:
- Cash roll-forward check: Closing cash − opening cash − receipts + payments should equal zero.
- Opening balance check: Each week’s opening cash should equal the previous week’s closing cash.
- Bank reconciliation: The first opening balance should agree to reconciled bank cash, adjusted for accounts included or excluded from the model.
- Completeness check: Supporting invoice and payment schedules should reconcile to forecast category totals.
- Scenario check: A timing shift should reduce one week and increase another by the same amount unless the scenario assumes a partial or failed collection.
For example, place =B15-(B5+B8-B13) in a check row. The result should be zero. A nonzero value indicates that the closing balance does not match the cash roll-forward formula.
Reconciliation does not prove that the assumptions are correct. It proves only that the workbook is internally consistent. Review overdue invoices, disputed payments, one-time obligations, and unusually large transactions with the people responsible for them.
Frequently asked questions
Why does a 13-week forecast use the direct method?
The direct method shows expected receipts and payments by timing and category. That transaction-level visibility is more useful for short-term liquidity management than starting with accounting profit and adjusting it indirectly.
How often should a 13-week cash forecast be updated?
Many businesses update it weekly, but the appropriate frequency depends on cash volatility and decision needs. A company under liquidity pressure may monitor key balances more frequently while still maintaining weekly forecast columns.
Should accounts receivable equal forecast customer receipts?
No. Accounts receivable is the amount customers owe at a point in time, while forecast receipts represent the portion expected to be collected during specific weeks. Collection assumptions should consider due dates, payment history, and known disputes.
Can closing cash be negative in Excel?
Yes, but it signals that the assumptions require more cash than the model makes available. In practice, management would need to investigate timing changes, available borrowing, additional funding, or reductions in payments before that week occurs.
What is the biggest limitation of a 13-week forecast?
Its accuracy depends on the quality and freshness of timing assumptions. Formula checks can detect mechanical errors, but they cannot ensure that customers will pay or that every obligation has been captured.