Excel Business Template for Cash Flow: How to Build One

How-to basics · Updated November 22, 2026

Yes, you can build a practical cash flow template in Excel using a simple layout: list cash inflows and outflows by month, then use formulas like =SUM() and =B8-B20 to calculate net cash flow and ending balance. The fastest way is to start with a free Microsoft template (File > New, search “cash flow”), but a custom sheet gives you full control over categories and forecasting. Below is everything you need to create, customize, and troubleshoot your own.

What a Cash Flow Template Actually Tracks

A cash flow statement answers one question: where did cash come from and where did it go during a period? Unlike a profit and loss statement, it only records actual money movement—not invoices sent or expenses accrued. Your template should have three main sections:

  • Cash inflows: customer payments, loan proceeds, owner investments, interest received.
  • Cash outflows: rent, payroll, supplier payments, loan repayments, taxes, utilities.
  • Net cash flow: inflows minus outflows for each period, plus a running ending balance.

Most small businesses run a 12-month forecast with monthly columns, starting with an opening balance. The ending balance of one month becomes the opening balance of the next—this is the core logic your formulas must handle.

How to Build a Custom Template Step by Step

Open a new workbook in Excel for Microsoft 365. Use columns A for categories, B through M for January to December, and N for annual totals. Here’s a layout that works:

  1. Row 1: Title “Cash Flow Forecast” in A1, merge A1:N1.
  2. Row 3: Headers—A3 “Item”, B3 “Jan”, C3 “Feb”, … M3 “Dec”, N3 “Total”.
  3. Rows 4-8: List inflow categories (e.g., Sales, Other income) in A4:A6, then a subtotal row.
  4. Rows 9-15: List outflow categories (e.g., Rent, Payroll, Supplies) in A9:A13, then a subtotal row.
  5. Rows 16-18: Net cash flow, opening balance, and closing balance.

Assume your inflow categories are in A4:A6 and outflow categories in A9:A13. Enter this formula in B7 for total inflows: =SUM(B4:B6). In B14 for total outflows: =SUM(B9:B13). Net cash flow in B16: =B7-B14. Opening balance in B17 is your starting cash—type it manually. Closing balance in B18: =B17+B16. Then for February, set C17 to =B18 (the prior month’s closing balance). Copy the formulas across columns C to M, and the template works.

For annual totals in N, use =SUM(B4:M4) for each row. To make it look professional, select the range and apply Home > Format as Table, then use Home > Conditional Formatting > Highlight Cells Rules to flag negative closing balances in red.

Choosing Between Free Templates and Building Your Own

Microsoft offers a built-in “Cash Flow Statement” template (File > New, search “cash flow”). It’s a good starting point for a monthly statement but often lacks forecasting features. For a forward-looking budget, you’ll likely want to build your own or modify the free one. Consider these factors:

  • Time horizon: A 12-month forecast is standard; if you need weekly, adjust columns.
  • Detail level: Do you need separate rows for each customer or just one “sales” line?
  • Integration: Do you want to link to your income statement or bank exports? Use Power Query (Data > Get Data) to import bank CSV files.
  • Ease of use: A custom sheet with named ranges and data validation (Data > Data Validation) prevents entry errors.

For most small businesses, a hybrid works best: start with a free template, then add rows and formulas as needed. If you’re not comfortable with formulas, the template saves time, but you must understand its logic to trust the output.

Common Problems and How to Fix Them

Cash flow templates fail for predictable reasons. Here are the most frequent issues and their solutions:

  • Negative ending balance not caught: Use conditional formatting to highlight any cell in the closing balance row that is below zero. Select B18:M18, then Home > Conditional Formatting > Highlight Cells Rules > Less Than, enter 0.
  • Double-counting transfers: If you move money between your business and personal accounts, decide whether it’s an inflow or outflow. Never list both sides unless it’s a loan.
  • Forgetting non-monthly expenses: Add a row for annual insurance or quarterly taxes, and use a formula like =IF(MONTH(B$3)=7,1200,0) to only show the amount in July.
  • Formulas not copying correctly: If you use absolute references like $B$4 when you meant relative, totals will be wrong. Double-check by clicking a cell and reviewing the formula bar.
  • Opening balance errors: Ensure the opening balance for month 2 references the prior closing balance, not a manual entry that can become stale.

If you see a #REF! error, you deleted a row that a formula referenced. Undo with Ctrl+Z (Mac: Cmd+Z) and re-check your ranges.

Comparing Cash Flow Templates to Paid Software

Excel templates are free and flexible, but they lack automation. Accounting software like QuickBooks or Xero automatically pulls transactions from your bank and generates cash flow reports. The trade-off: cost and learning curve. Excel is ideal if you want to forecast scenarios manually, such as “what if sales drop 20%?”—you can copy your sheet and change a few numbers. Paid tools are better if you need real-time data and don’t want to maintain formulas. A middle ground: use Excel for monthly forecasting and your accounting software for historical statements.

Costs and Safety Considerations

Excel itself costs money (Microsoft 365 subscription or one-time purchase), but the template is free. No extra licensing is needed. For safety, always keep a backup of your workbook—use File > Save As to store a copy on OneDrive or a local drive. Never share a workbook that contains sensitive financial data without password protection (File > Info > Protect Workbook). If you use macros (e.g., to clear data), remember that macros cannot be undone with Ctrl+Z, so save a copy before running any VBA. Also, be aware that Excel formulas are not a substitute for professional accounting advice; for tax or loan decisions, consult a qualified accountant.

Alternatives to Excel for Cash Flow

If Excel feels too manual, consider Google Sheets (free, cloud-based, similar formulas) or dedicated cash flow apps like Float or Pulse. These tools connect to your accounting software and update automatically. However, they often have monthly fees and less flexibility. For a one-person business, a simple Excel template is often sufficient. For a growing company with multiple revenue streams, a paid tool saves hours. The choice depends on your comfort with spreadsheets and how often you need to update the forecast.

Frequently Asked Questions

How do I calculate net cash flow in Excel?

Subtract total outflows from total inflows for the period. If inflows are in B7 and outflows in B14, use =B7-B14. This gives you the net change in cash for that month.

What’s the difference between cash flow and profit?

Profit is revenue minus expenses on an accrual basis; cash flow is actual money received and paid. You can be profitable but run out of cash if customers pay late or you prepay expenses.

Can I use Excel for a 12-month cash flow forecast?

Yes. Set up 12 columns for months, enter your best estimates for inflows and outflows, and use formulas to calculate running balances. Update it monthly with actuals to improve accuracy.

How do I handle seasonal sales in my template?

Enter different amounts for each month manually, or use a formula that references a seasonality factor. For example, if December sales are 150% of average, put that number in the December column.

Is there a free cash flow template in Excel?

Yes. Open Excel, go to File > New, and search “cash flow.” Microsoft provides several free templates that you can download and modify. They are safe to use, but always review formulas before relying on them.

What if my cash flow is negative for several months?

That’s a warning sign. Use your template to test scenarios: increase collections, delay purchases, or arrange a line of credit. The template helps you see exactly when you’ll hit zero, so you can act early.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.