Cash Flow Forecast Template in Excel for a Small Business: Layout, Formulas and Example

Cash flow vs profit: what is the difference?

Profit counts income when you earn it and costs when you incur them. Cash flow counts money when it actually moves in or out of the bank. A business can be profitable and still run short of cash, and a forecast is how you see that coming.

Three things drive the gap for most small businesses:

• Payment timing. Invoiced revenue is profit today and cash next month (or later).
• Capital purchases. A vehicle or piece of equipment is cash out now, but only a slice of it (depreciation) hits profit each year.
• Loan principal. Interest is an expense; principal repayments are not, yet they leave the bank every month.

So "what is better, cash flow or profit?" is the wrong contest. Profit says whether the business model works. Cash says whether you can pay next Friday's bills. You need both. I build spreadsheet math; I am not an accountant, and nothing here is financial or tax advice.

Check a month's profit first (free calculator)

Before forecasting cash, know the month's profit. Enter revenue, direct costs and operating expenses below; the calculator returns gross and operating profit and margins.

For the example business, April looks like this on the P&L. The figures are assumptions for illustration.

April earns $7,000.00 of operating profit. Now watch what the bank sees.

Worked example: a 6-month cash flow forecast

The example business invoices every job, collects 20% at the job and the other 80% the following month, pays direct costs (25% of revenue) and $11,000.00 of overhead in the month they occur, repays $800 of loan principal monthly and buys $6,000 of equipment in March. December's invoices were $15,000. All of these are assumptions.

How April's cash in is built: 20% of April's $24,000 ($4,800) plus 80% of March's $20,000 ($16,000) = $20,800. Cash out is $6,000 direct costs + $11,000 overhead + $800 loan principal = $17,800. Closing is $3,100 + $20,800 - $17,800 = $6,100.

March is the month to notice. It showed $4,000 of profit, yet the equipment purchase pulled cash from $7,500 down to $3,100. One late-paying customer that month and the business could not have covered payroll.

Reconciling profit and cash: where did the money go?

Profit minus cash change always equals the items that are on one statement but not the other. Over the six months the example shows $27,000 of profit but only $10,600 more cash.

Growing businesses feel this most: higher sales mean more money sitting in unpaid invoices. That is why a P&L alone can look great while the bank account gets tighter. The companion guide on a profit and loss template in Google Sheets builds the P&L side of this pair.

How to create a cash flow forecast in Excel step by step

Lay out months across columns and cash categories down rows, split into cash in and cash out. Link each opening balance to the prior closing balance so the whole sheet updates when one number changes.

To chart it, select the closing-balance row with the month headers and insert a line chart. A line that dips toward zero is the early warning the forecast exists to give.

What does a 13-week cash flow forecast look like?

A 13-week forecast is the same layout with weekly columns instead of monthly ones: one quarter, week by week. It is used when cash is tight enough that the timing within a month matters, for example when payroll lands every other Friday and a large customer pays on the 30th.

• Use it for the next quarter; keep the 12-month version for planning.
• Enter known payments on their actual week (rent on week 1, loan on its due week).
• Enter collections by customer when a few customers make up most revenue.
• Each Monday, replace last week's forecast with actuals and roll a new week on the end.

The formulas are identical; only the column headers change from months to week-start dates.

Common cash flow forecast mistakes

The most common mistake is forecasting cash in on the invoice date. If your customers pay in 30 days, a forecast that books revenue as cash the same month will overstate every month's balance while sales grow.

• Forgetting annual and quarterly bills. Insurance, tax payments and software renewals that arrive once a year cause the surprises. Put them in their actual month.
• Copying profit into the cash sheet. Start from cash movements, not from the P&L bottom line.
• Leaving out loan principal and equipment. Neither is on the P&L, both leave the bank.
• No minimum balance line. Add a row with the lowest balance you are comfortable holding and highlight any month below it.
• Never updating. A forecast is useful for about a month. Replace forecasts with actuals as they happen.

If the forecast shows the business is short every month, the issue may be price or volume, not timing. The break-even calculator for small business guide shows how many jobs cover your overhead.

Free cash flow template vs a paid tracker

Free templates from banks, government business sites and Microsoft's gallery give you a solid monthly layout. What they usually lack is the link to your P&L, so profit and cash live in separate files and never reconcile.

The small business P&L and cash flow tracker is $14.99 one-time and works in Excel and Google Sheets. It tracks revenue, COGS, gross profit and margin, operating expenses and net profit, plus break-even revenue and a running cash position. It is also in the $49 Complete Toolkit of seven templates. The cash flow template page has a 20-second demo.

Step-by-step: Cash Flow Forecast Template in Excel for a Small Business: Layout, Formulas and Example

1. Set the starting balance. Enter the actual bank balance on the first day of the forecast as the first opening balance.
2. Forecast sales and collections. Enter expected invoiced revenue per month, then convert it to cash collected using your customers' real payment timing.
3. List every cash payment. Enter direct costs, payroll, rent, overhead, loan payments, tax payments and equipment in the month they leave the bank.
4. Compute net and closing cash. Net cash = total in - total out. Closing = opening + net. Link each opening balance to the previous closing.
5. Find the low point. Use MIN on the closing-balance row and compare it to your minimum safe balance. Plan financing or timing changes before that month.
6. Roll it forward. Each month, replace the forecast with actuals and add a new month at the end.

Related guides