how to build a 13-week rolling cashflow model to time seasonal stock purchases and avoid emergency borrowing

I’m going to walk you through how I build a practical 13-week rolling cashflow model that helps you time seasonal stock purchases and avoid emergency borrowing. I use variations of this with my small retail and hospitality clients because it’s short-term, action-focused and forces decisions around when to buy stock so you aren’t surprised by a cash shortfall in week 10.

Why 13 weeks?

Thirteen weeks (roughly a quarter) is long enough to capture seasonality and ordering lead times, but short enough to keep forecasts realistic. It’s a horizon that encourages weekly action: if a number moves, you respond immediately. For most small businesses this timescale is ideal for planning stock purchases tied to promotion periods, supplier lead times and payroll cycles.

The core structure I use

My models are deliberately simple. Each week you want to track:

Opening cash balance

Receipts (sales, other income)

Payments (cost of goods sold, supplier invoices, wages, rent, tax, other overheads)

Net weekly cashflow

Closing cash balance

At the bottom I add helpful rows for committed purchases (stock orders you’ve placed but not yet paid), planned purchases you might place, and a running forecast of available cash after those commitments. That lets you see whether a planned bulk stock order is affordable or whether you need to phase purchases.

How I forecast receipts (sales)

I don’t aim for perfect sales forecasts — I aim for realistic, evidence-based ones:

Start with last year’s weekly sales for the same period if available, adjusted for known changes (pricing, promotions, new channels).

Overlay recent trends: if sales have grown 10% over the last 13 weeks, apply that rate rather than blindly using last year.

Split sales by cash timing. For example, card sales typically settle in 1–3 days with Stripe or Square, while Bacs/CHAPS or invoice payments settle on variable terms (30 days). If you use Xero/QuickBooks, export receivable ageing to estimate when invoices turn into cash.

Account for upcoming marketing or events that will shift sales by adding a one-off or ramped increase in the specific weeks.

How I forecast payments

Think in terms of when cash is leaving, not when a cost is incurred. Common payments include:

Supplier invoices (COGS / stock). Map these to the supplier’s payment terms — 7, 14, 30 days, etc.

Wages, PAYE, NIC (weekly, fortnightly or monthly).

Rent, utilities, subscriptions (monthly).

VAT and corporation tax (quarterly or annual) — include staged payments for upcoming VAT returns.

For stock specifically, include two rows:

Stock ordered (commitment) — the amount you’ve agreed to buy and when it will likely be shipped.

Stock payment — the cash out when the supplier invoice becomes due.

Sample 13-week layout

Here’s a simplified layout you can reproduce in Excel or Google Sheets. Replace the sample numbers with your own.

Row	Week 1	Week 2	…	Week 13
Opening balance	£10,000	£9,200	…	£X
Receipts (sales)	£6,000	£5,500	…	£Y
Supplier payments (stock)	£4,000	£3,800	…	£Z
Wages	£1,200	£1,200	…	£1,200
Other overheads	£600	£600	…	£600
Net weekly cashflow	=Receipts−Payments		…
Closing balance	=Opening + Net		…
Committed stock orders	£2,500	£0	…	£X
Available cash after commitments	=Closing − Committed stock		…

How to use the model to time seasonal stock purchases

Once the model is populated, look at the following:

Cash runway — how many weeks until your closing balance hits your minimum threshold (your safety buffer)?

Committed vs planned orders — if you plan to place a large seasonal order, add it to the “planned” row to see when it will hit your cash.

Supplier lead time — work backwards from the week you need the stock available for sale. If lead time is 4 weeks and you need stock in week 10, place the order in week 6 and ensure payment terms align with your cashflow.

By running “what if” scenarios (e.g. sales 10% higher or supplier payment delayed 14 days), you can choose whether to:

Phase a big purchase into two smaller orders across different weeks.

Ask the supplier for extended payment terms or partial upfront payment.

Use short-term finance (overdraft or invoice finance) only when the model shows unavoidable shortfalls — and for the shortest possible period.

Triggers and rules I recommend

Turn the model into decision rules so it becomes operational, not just informational:

If available cash after commitments drops below your safety buffer (£X), postpone non-essential stock purchases.

If closing balance is above X for two consecutive weeks, release planned promotional stock orders.

When committed stock payments exceed Y% of expected receipts in a week, split the order or negotiate terms.

Common mistakes to avoid

From my experience working with micro businesses, these are the pitfalls that break the usefulness of a 13-week model:

Confusing sales with cash — track when funds actually land in the bank, not just invoice dates.

Using one static projection and never updating it — the model must be updated weekly with actuals.

Ignoring seasonality and promotions — a big sale week can drain stock and create procurement pressure; plan for it.

Relying on a single “average” week — averages hide peaks and troughs. Use weekly granularity.

Tools and automation tips

I usually build the first version in Google Sheets so the client can edit and we can collaborate live. Once a pattern is established, you can automate parts of it:

Connect Xero or QuickBooks to pull bank receipts and supplier bills to auto-populate actuals (tools like Causal or Float integrate well for cashflow visualisation).

Use bank rules or export bank statements to reconcile weekly receipts quickly.

Keep a simple dashboard row that shows weeks-to-buffer and whether any planned order breaches the threshold — a visual flag makes decisions faster.

How I review the model each week

Every Monday I update the previous week’s actual bank balance, receipts and payments, then re-run the forecast for the next 13 weeks. If a planned stock order pushes a week into red, I email the supplier to renegotiate or split the order. If sales are beating forecast, I review whether to accelerate replenishment to avoid stockouts. These small weekly habits prevent panic borrowing and give you headroom to negotiate better supplier terms.

If you’d like, I can share a simple Google Sheets template that mirrors this layout, with the formulae already set up. Tell me what accounting software you use (Xero, QuickBooks, Sage, or none) and I’ll tailor the template so it’s quick to populate with your actuals.