sheetsmith

Templates › Business and freelance

Cash Flow Forecast

Made for small businesses worried about the next few months.

Enter your expected money in and money out by category, and see the closing bank balance for each of the next twelve months, including the worst month. The summary carries a chart that redraws itself as you type.

Tabs
4
Columns
24
Fill themselves in
26

£7one payment, yours to keep

  • Opens in Excel, Google Sheets and Numbers
  • Delivered straight away
  • No macros, nothing to install
  • Change it free before you buy

Payment is handled by Stripe. Your card details go to them and never to us. Sales are final, which is why every tab, column and calculation is shown on this page first.

The workbook's summary and the table behind it
A row typed into the real file. Every figure that changes is the spreadsheet's own arithmetic.
The summary page, every figure calculated from the tables
The chart, drawn from the totals in the table beside it
What is inside: the tabs, the columns and what fills itself in
Where you type, and the shaded columns that work themselves out
The guide sheet that opens first and explains the file
Everything included in the download

Everything inside, before you pay

This is the whole design: every tab, every column and how each calculated one is worked out. White cells are yours to fill in; the shaded ones work themselves out and are locked so a formula cannot be overwritten by accident. There is room for 275 rows.

Starting Position

Your bank balance today, and the level you want to be warned about. Room for 1 rows.

ColumnType
Balance In The Bank Nowcurrency
Balance As Atdate
Low Balance Warning Levelcurrency
Notestext

Cash Items

One row per thing you expect to come in or go out. Set the amount for a single month, then say which months it runs from and to. Room for 250 rows.

ColumnTypeWorked out as
Itemtext
In Or Outchoice (list)
Categorychoice (list)
Amount Per Monthcurrency
First Monthchoice (from Monthly Forecast › month_label)
Last Monthchoice (from Monthly Forecast › month_label)
Start KeynumberIF(ISBLANK([amount]), "", IFERROR(INDEX([Monthly Forecast!month_key], MATCH([start_month], [Monthly Forecast!month_label], 0)), 0))
End KeynumberIF(ISBLANK([amount]), "", IFERROR(INDEX([Monthly Forecast!month_key], MATCH([end_month], [Monthly Forecast!month_label], 0)), 999999))
Months In ForecastnumberIF(ISBLANK([amount]), "", COUNTIFS([Monthly Forecast!month_key], ">=" & [start_key], [Monthly Forecast!month_key], "<=" & [end_key]))
Total Over ForecastcurrencyIF(ISBLANK([amount]), "", ROUND([amount] * [months_applied], 2))
Notestext

Monthly Forecast

One row per month. The money in, money out and closing balance are worked out from your cash items. Room for 24 rows.

ColumnTypeWorked out as
Month Startingdate
MonthtextIF(ISBLANK([month_start]), "", TEXT([month_start], "MMM YYYY"))
Month KeynumberIF(ISBLANK([month_start]), "", YEAR([month_start]) * 12 + MONTH([month_start]))
Money IncurrencyIF(ISBLANK([month_start]), "", SUMIFS([Cash Items!amount], [Cash Items!direction], "Money in", [Cash Items!start_key], "<=" & [month_key], [Cash Items!end_key], ">=" & [month_key]))
Money OutcurrencyIF(ISBLANK([month_start]), "", SUMIFS([Cash Items!amount], [Cash Items!direction], "Money out", [Cash Items!start_key], "<=" & [month_key], [Cash Items!end_key], ">=" & [month_key]))
Net For MonthcurrencyIF(ISBLANK([month_start]), "", [income] - [outgoings])
Opening BalancecurrencyIF(ISBLANK([month_start]), "", SUM([Starting Position!opening_balance]) + SUMIFS([Monthly Forecast!net], [Monthly Forecast!month_key], "<" & [month_key]))
Closing BalancecurrencyIF(ISBLANK([month_start]), "", SUM([Starting Position!opening_balance]) + SUMIFS([Monthly Forecast!net], [Monthly Forecast!month_key], "<=" & [month_key]))
Notestext

Forecast Summary

The headline answers: where the balance ends up, and which month is tightest.

MeasureWorked out as
Balance in the bank nowSUM([Starting Position!opening_balance])
Months in the forecastCOUNT([Monthly Forecast!month_key])
Total money in over the forecastSUM([Monthly Forecast!income])
Total money out over the forecastSUM([Monthly Forecast!outgoings])
Net change over the forecastSUM([Monthly Forecast!net])
Balance at the end of the forecastIFERROR(INDEX([Monthly Forecast!closing_balance], MATCH(MAX([Monthly Forecast!month_key]), [Monthly Forecast!month_key], 0)), 0)
Worst monthIFERROR(INDEX([Monthly Forecast!month_label], MATCH(MIN([Monthly Forecast!closing_balance]), [Monthly Forecast!closing_balance], 0)), "")
Lowest closing balanceMIN([Monthly Forecast!closing_balance])
Shortfall to cover in the worst monthIF(MIN([Monthly Forecast!closing_balance]) < 0, ABS(MIN([Monthly Forecast!closing_balance])), 0)
Months ending below zeroCOUNTIF([Monthly Forecast!closing_balance], "<0")
Months ending below your warning levelCOUNTIF([Monthly Forecast!closing_balance], "<" & SUM([Starting Position!low_balance_alert]))
Average money in each monthIFERROR(SUM([Monthly Forecast!income]) / COUNT([Monthly Forecast!month_key]), 0)
Average money out each monthIFERROR(SUM([Monthly Forecast!outgoings]) / COUNT([Monthly Forecast!month_key]), 0)
Tightest month for spendingIFERROR(INDEX([Monthly Forecast!month_label], MATCH(MAX([Monthly Forecast!outgoings]), [Monthly Forecast!outgoings], 0)), "")
Committed outgoings each monthSUMIF([Cash Items!direction], "Money out", [Cash Items!amount])