Excel Cash Flow Template Generator

About this template

A profit and loss statement tells you whether the business makes money; a cash flow forecast tells you whether it will have money on the day the rent is due. Businesses fail on the second question far more often than the first, which is why this sheet is built around balances rather than profit: what you start the month with, what comes in, what goes out, and what you finish with.

It is for anyone who needs to see cash by month a year ahead - a business planning hires or stock, a freelancer with lumpy income, a household with an irregular pay pattern, or someone putting a forecast in front of a lender. You give it an opening balance and your expected inflows and outflows per month; it gives you the closing balance every month and the lowest point of the year.

With the default lines the layout is: row 3 the header, row 5 the Opening balance. Rows 8 to 10 are cash in lines and row 11 Total cash in. Rows 14 to 20 are cash out lines and row 21 Total cash out. Row 23 is Net cash flow and row 24 Closing balance. Beneath the table, a Lowest closing balance cell shows the smallest closing balance across the months, which is the figure that tells you whether the plan survives.

Only the first month's opening balance is typed. Every later month opens with the previous month's closing balance, by formula, so the balance chain runs through the whole year and a change in March flows through to December on its own. The Total column sums each line across the year and shows the year's opening and closing balances.

How to use it

1. Enter the opening balance for the first month - The cash you actually have on the first day of the forecast - bank balance plus cash, less anything already committed. It goes in the cream cell on the Opening balance row under the first month; every other month's opening balance is a formula.
2. Fill in the expected inflows and outflows - Positive numbers in the cream cells, one per line per month, timed for when the cash moves rather than when the invoice is dated. A sale invoiced in March and paid in May is May cash. Leave a cell blank when nothing is expected.
3. Read the closing balance row - Closing balance is opening plus net cash flow. A negative closing balance is a month the plan cannot pay for; the Lowest closing balance cell under the table tells you the worst month without scanning the row.
4. Move money between months to fix a low point - Because the balance chain is live, you can test a change immediately: delay a purchase a month, bring a receipt forward, add a loan line. The closing balances downstream update as you type.
5. Replace forecast with actual as months pass - Overwrite a month's estimates with what actually happened and the rest of the year re-forecasts from the true balance. Keep a copy of the original forecast on another sheet if you want to compare later.
6. Add a line without breaking the totals - Insert the row inside a section - between two existing lines - and the section's SUM widens to include it. Then copy the Total column formula from the row above.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =SUM(B8:B10) - Total cash in for the first month: the inflow lines added together. Total cash out in row 21 uses the same shape over the outflow lines.
• =B11-B21 - Net cash flow in row 23: total cash in minus total cash out. Positive when the month adds to your cash, negative when it draws it down.
• =B5+B23 - Closing balance in row 24: the opening balance plus the net cash flow for the month.
• =B24 - The second month's opening balance, in C5: simply the first month's closing balance. Every month after the first opens this way, which is what chains the balances through the year.
• =SUM(B8:M8) - The Total column on a line item: that inflow or outflow summed across the twelve months. The Total column's opening balance is the year's opening balance and its closing balance is that plus the year's net cash flow, which equals the final month's closing balance.
• =MIN(B24:M24) - Lowest closing balance, below the table: the smallest closing balance across the months. If this is negative, the plan runs out of cash in the month that holds it.

Customising it

• Highlight negative months: select the Closing balance row and use Conditional Formatting > Highlight Cells Rules > Less Than 0 with a red fill, so a shortfall shows without reading the numbers.
• Add a minimum cash buffer: put the buffer figure in a spare cell and add a row with =B24-$B$28 to show headroom above it each month.
• Forecast by week for a tight period: generate a three-month version and relabel the columns as weeks; the formulas do not care what the column headers say.
• Show the month-end balance as a chart: select the Closing balance row with its month headers and Insert > Line Chart. The Data is already one series in a row.
• Add a VAT or sales tax line under outflows timed for the quarter it is paid, which is one of the most common surprises in a first forecast.