Cash Flow Statement & Forecast in Excel

Q: What is the basic cash flow formula in Excel?

`Closing balance = Opening balance + Total inflows − Total outflows`, with each period's closing balance carried into the next period's opening cell with `=`.

Q: Direct or indirect method — which should an SMB use?

For day-to-day cash management, the **direct** method (list actual inflows and outflows) is simpler and clearer. The indirect method, which starts from net profit, is mainly for the formal audited cash flow statement.

Q: Is the template free?

Yes — free to download, no sign-up. ---

Last updated: 27 June 2026 · Structure, formulas and a free template included.

A cash flow statement in Excel tracks the money actually moving in and out of your business — opening balance, plus inflows, minus outflows, equals closing balance — period by period, while a cash flow forecast projects those same lines forward so you can see your runway and spot a shortfall before it happens. The core formula is one running line: each period's closing cash becomes the next period's opening cash.

Key takeaways

The master equation: Closing = Opening + Inflows − Outflows, carried forward each period.
Cash flow ≠ profit: profit is on paper; cash flow is money in the bank. A profitable business can still run out of cash.
A rolling forecast projects collections and payments forward 4-13 weeks to reveal shortfalls early.
Drive it from your real data — collections from the receivables tracker, payments from the payables tracker.
SUMIFS by month or week totals inflows and outflows by category automatically.
A negative projected closing balance is your earliest, clearest warning to act — chase, delay, or arrange finance.

Fact box. A cash flow statement is historical — it records cash that has already moved. A cash flow forecast is forward-looking — it projects expected inflows and outflows. SMBs need both: one to reconcile, one to plan.

What is a cash flow statement?

A cash flow statement records the actual cash that entered and left your business over a period, starting from the bank/cash opening balance and ending at the closing balance. Unlike a profit & loss statement, it ignores accruals — a ₹5 lakh sale on credit shows up only when the customer pays, not when you invoice.

For most Indian SMBs a simple direct cash flow view — group inflows (collections, other income) and outflows (purchases, salaries, rent, GST, EMIs, tax) — is more useful day to day than the formal three-section statement (operating/investing/financing) used in audited accounts.

What is the cash flow formula?

Every cash flow sheet is built on one carried-forward line:

Line	Formula
Opening balance	(last period's closing)
+ Total inflows	`=SUM(inflow rows)`
− Total outflows	`=SUM(outflow rows)`
= Closing balance	`=Opening + Inflows − Outflows`

The key link: this period's closing balance is the next period's opening balance. Set the first opening balance manually (your current bank balance), then each subsequent opening is = the previous closing. That single chain is what makes a multi-period sheet or forecast work.

Fact box. The cash flow identity is Closing Cash = Opening Cash + Inflows − Outflows. Carrying each closing balance forward as the next opening balance is the only "trick" a cash flow forecast needs.

How do I build a cash flow statement in Excel?

Lay periods (months or weeks) across the columns and line items down the rows:

Row 1 — period headers: Jan, Feb, Mar… or Week 1, Week 2…
Opening balance row: type your real bank balance in the first cell; in the next cell put = the previous column's closing.
Inflow rows: customer collections, other income, loans received, GST refunds.
Total inflows: =SUM() of the inflow rows.
Outflow rows: supplier payments, salaries, rent, utilities, GST paid, TDS, EMIs, tax.
Total outflows: =SUM() of the outflow rows.
Closing balance: =Opening + Total Inflows − Total Outflows.
Copy the column across all periods — the chain carries itself forward.

To total actual transactions by category from a transaction log, use SUMIFS on month and category, e.g. =SUMIFS(Amount, Month, "Jun", Category, "Salaries").

How do I forecast cash flow (and find a shortfall)?

A forecast is the same sheet, filled with expected figures for future periods:

Project collections from your receivables due dates — sum balances due in each future week from the AR tracker.
Project payments from your payables due dates and fixed costs (salaries, rent, EMIs, GST/TDS due dates).
Carry the closing balance forward as before.
Watch the closing-balance row. Apply conditional formatting: red whenever the projected closing goes negative — that's a shortfall week.
Roll it weekly: each week, replace one forecast with actuals and add a new week at the end (a "rolling 13-week" forecast).

⚠️ A negative projected closing balance is an early-warning, not a verdict. It means: act now — accelerate collections, delay non-urgent payments, or arrange a working-capital limit — before the week arrives.

A free cash flow statement and forecast template is available to download, no sign-up — enter your opening balance and the closing chain builds itself.

How Ankeshan helps: Ankeshan projects your cash position inside Excel from your real invoices and bills — expected collections and payments roll into a week-by-week forecast, and it flags shortfall weeks before they hit. It's launching soon; join the waitlist.

Frequently asked questions

What is the difference between cash flow and profit? Profit is revenue minus expenses on an accrual basis — it can include money you haven't collected yet. Cash flow is the actual money in your bank. A profitable business can still fail if cash doesn't arrive in time.

What is a rolling cash flow forecast? A forecast that always looks a fixed number of periods ahead — commonly 13 weeks. Each week you drop the oldest week, replace forecasts with actuals, and add a new week at the far end, so you always see roughly a quarter ahead.

What is the basic cash flow formula in Excel? Closing balance = Opening balance + Total inflows − Total outflows, with each period's closing balance carried into the next period's opening cell with =.

Direct or indirect method — which should an SMB use? For day-to-day cash management, the direct method (list actual inflows and outflows) is simpler and clearer. The indirect method, which starts from net profit, is mainly for the formal audited cash flow statement.

Is the template free? Yes — free to download, no sign-up.

Sources

Microsoft Excel function reference — SUM, SUMIFS, TODAY: support.microsoft.com.
General accounting practice for cash flow statements and forecasting.

General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.