Cash-Flow Forecasting: Spreadsheet Template

TL;DR

A 13-week rolling cash flow forecast is the most-used working-capital tool for an SME.

A 13-week rolling cash flow forecast is the most-used working-capital tool for an SME. It is the basis for every working-capital decision — when to draw down the bank line, when to defer a supplier payment, when to delay a hire, when to push customers for collection. The template is simple — open with the bank balance, project weekly inflows and outflows, end with the projected closing balance. Update weekly with actuals. This post is the template and the discipline.

Why 13 Weeks

The 13-week horizon is the right balance:

• Long enough to see the working-capital cycle (collections + payables + inventory).
• Short enough to project with reasonable accuracy.
• Aligned with quarterly financial reporting.
• Aligned with the GST / TDS / advance tax payment calendar.

For a seasonal business, a 26-week horizon may be better. For a stable business, a 4-week horizon is enough. For most SMEs, 13 weeks is the sweet spot.

The Template Structure

A simple Google Sheet or Excel file with these columns:

Column	Header
A	Line item (description)
B–N	Week 1 to Week 13 (dates)
O	Total (13-week period)
P	Actual (filled in weekly with the actual bank movement)
Q	Variance (O - P)

Rows:

Opening bank balance (Week 1)

The starting balance from the bank statement — typically the closing balance as of the date the forecast is created. This becomes the opening balance for Week 1.

Inflows

• Customer collections (projected by week, based on receivables aging).
• GST refund (if any).
• Interest on FD (if any).
• Loan disbursement (if any).
• Equity infusion (if any).
• Other receipts.

Outflows

• Supplier payments (projected by week, based on payables aging).
• Payroll (gross salary + employer PF + ESI + PT).
• Rent + utilities.
• GST payment (monthly / quarterly, based on filing cycle).
• TDS payment (monthly / quarterly).
• Income tax advance payment (quarterly — 15 June, 15 September, 15 December, 15 March).
• Loan EMI (principal + interest).
• Capital expenditure (projected by week).
• Professional fees (CA / lawyer / consultant).
• Other expenses.

Closing bank balance (Week 13)

The projected balance at the end of Week 13. This becomes the opening balance for Week 14 (the next rolling forecast).

The Discipline

Step 1 — Project from contracts, not from history

The inflows are projected from the receivables aging — every customer’s outstanding invoice with the expected collection date. Don’t project “we usually collect ₹10 lakh per week” — project by customer, by invoice, by due date.

The outflows are projected from the payables aging — every supplier’s outstanding bill with the planned payment date. Plus the calendar items — payroll every month-end, rent on the 1st, GST on the 20th, TDS on the 7th, advance tax on the 15th.

Step 2 — Update weekly

Every Friday (or every Monday for the upcoming week), update the forecast:

• Replace the projected numbers for the week that just ended with the actuals (from the bank statement).
• Re-project the upcoming weeks based on the latest collections and payments schedule.
• Flag any week where the projected closing balance is below the minimum cash threshold (typically 2–4 weeks of operating expenses).

Step 3 — Identify the triggers

For each week with a projected shortfall, identify the trigger:

• A large customer payment slipped from Week 4 to Week 6.
• A large supplier invoice is due in Week 5 that wasn’t in the prior forecast.
• An income-tax advance payment of ₹15 lakh is due in Week 7.
• A seasonal downturn in collections.

For each trigger, identify the mitigation:

• Push the customer for collection (call / email / in-person).
• Negotiate the supplier payment date.
• Draw down the bank line for the gap.
• Defer the capital expenditure.
• Pause a non-critical hire.

Step 4 — Communicate to the bank

If the projected shortfall requires a bank line drawdown, communicate to the bank 4–6 weeks before the shortfall. Banks take 2–4 weeks to process a drawdown. Last-minute requests are typically rejected.

Step 5 — Roll the forecast

Every Friday (or every Monday), shift the forecast window — drop the week that just ended, add a new week at the end. The forecast remains a 13-week forward look at all times.

The Common Pitfalls

Pitfall 1 — Confusing accrual P&L with cash flow

The P&L shows revenue when the invoice is raised. The cash flow shows the collection. The two diverge. A profitable quarter can be a cash-negative quarter. The cash flow forecast must be on cash basis — actual bank movements.

Pitfall 2 — Forgetting the calendar items

GST payment, TDS payment, advance tax, PF / ESI deposit, professional tax deposit — these are calendar-driven and must be in the forecast. Missing them creates a “surprise” outflow that wasn’t budgeted.

Pitfall 3 — Optimistic collection projections

A “we usually collect ₹10 lakh per week” projection is a guess. The receivables aging shows what is due when. If the receivables aging shows ₹6 lakh in Week 3, the inflow for Week 3 is ₹6 lakh — not ₹10 lakh.

Pitfall 4 — Not flagging the small shortfalls

A weekly shortfall of ₹50,000 is small. But two such shortfalls in the same month are ₹1 lakh — meaningful. Flag every week with a projected shortfall, even if small.

Pitfall 5 — Not updating after a major event

A new funding round, a new contract win, a key customer payment delay, a tax demand — all these change the cash flow. Update the forecast immediately after any major event.

The Cash Flow Forecast vs the Budget

The budget is the annual plan — revenue, expenses, profit — projected for the year. The cash flow forecast is the 13-week forward look at bank movements. The two are related but distinct:

• The budget is for strategy — capital allocation, hiring plan, marketing spend.
• The cash flow forecast is for operations — collections, payments, bank line management.

A business can hit its budget but miss its cash flow forecast (high P&L profit, low cash conversion). The two should be reconciled monthly — actual bank movements vs actual P&L for the month.

The Template — A Worked Example

For a small SaaS company with ₹50 lakh MRR (Monthly Recurring Revenue):

Item	Week 1	Week 2	Week 3	Week 4	Week 5	…	Week 13
Opening balance	30,00,000	32,50,000	35,00,000	36,00,000	35,50,000	…	38,00,000
Customer collections	12,00,000	11,50,000	11,00,000	12,50,000	11,50,000	…	12,00,000
GST refund	0	0	0	0	0	…	0
Interest on FD	0	0	0	25,000	0	…	0
Total inflows	12,00,000	11,50,000	11,00,000	12,75,000	11,50,000	…	12,00,000
Payroll	8,50,000	0	0	8,50,000	0	…	8,50,000
Rent + utilities	1,50,000	0	0	0	0	…	0
Supplier payments	0	8,00,000	9,00,000	4,00,000	4,00,000	…	4,00,000
GST payment	0	0	0	0	5,00,000	…	0
TDS payment	0	0	0	75,000	0	…	75,000
Loan EMI	50,000	0	0	0	0	…	0
Professional fees	0	1,00,000	0	0	0	…	0
Total outflows	10,50,000	9,00,000	9,00,000	13,25,000	9,00,000	…	13,25,000
Net cash flow	1,50,000	2,50,000	2,00,000	(50,000)	2,50,000	…	(1,25,000)
Closing balance	31,50,000	35,00,000	36,00,000	35,50,000	38,00,000	…	36,75,000

The forecast is on a simple Excel / Google Sheet. The weekly update takes 15–30 minutes.

The Tools

• Google Sheets / Excel — the simplest, most flexible. The template above is built in 15 minutes.
• Tally Prime / Zoho Books — the cash flow report is built-in. The projection requires manual setup.
• QuickBooks — the cash flow forecast is a paid add-on.
• Float — a cash flow forecasting tool built on Xero / QuickBooks. Paid subscription.
• Pulse — a free cash flow forecasting tool for Xero.

For most SMEs, Google Sheets / Excel is sufficient. The discipline matters more than the tool.

The Single Most Important Advice

Run the 13-week rolling forecast every Friday. Update it with the actuals. Flag every week with a projected shortfall. Communicate to the bank 4–6 weeks before a shortfall. The discipline pays off in working capital — no surprise shortfalls, no panic bank calls, no emergency rate-cutting.

When to Get Help

For a growing business, the cash flow forecast should be a CFO function. The weekly update, the variance analysis, the bank line management, the customer collection prioritisation are all CFO tasks.

We routinely set up and maintain cash flow forecasts for clients. Our virtual accounting and bookkeeping services cover the forecast template, the weekly update, and the bank communication. Share your current cash flow process on WhatsApp for a no-charge assessment.

For the P&L counterpart, see our Read a P&L statement guide. For the bookkeeping methodology, see our Bookkeeping basics guide.

Sources

• No specific regulatory source — internal best practice.
• See related posts for the broader accounting and bookkeeping frameworks.