Basic Cash Flow Template in Excel: Build and Use It

How-to basics · Updated November 21, 2026

A basic cash flow template in Excel lists when cash is expected to come in and go out, then calculates the net change and ending cash balance for each period. Set up one column per month, enter opening cash and cash transactions by category, and use SUM formulas to total inflows and outflows; the layout and formulas below make a reusable monthly template in Microsoft Excel for Microsoft 365 on Windows.

What a basic cash flow template shows

A cash flow statement or forecast focuses on cash movement during a period, rather than simply whether a business recorded a profit. It can help a small business, household, project team, or nonprofit anticipate when cash may be tight, plan upcoming payments, and compare expected activity with what actually happened. The template here is a practical planning worksheet, not a substitute for formal financial statements or accounting records.

The central calculation is straightforward: cash at the start of a period, plus cash received, less cash paid, equals cash at the end. That ending balance becomes the next period’s opening balance. Record a transaction when cash is expected to arrive or leave, not merely when an invoice is issued or an expense is recognized. This distinction matters when customers pay later or bills are due on a different date from when they are incurred.

Suggested worksheet layout

Use column A for labels and columns B through M for January through December. Put the year in a separate cell or include it in each month heading so that the worksheet remains clear when copied for another year. Arrange the rows as follows:

  • Row 4: month headings, beginning with January in B4.
  • Row 5: opening cash balance.
  • Rows 7–14: cash inflows, such as customer receipts, sales paid immediately, grants, owner contributions, or other receipts.
  • Row 15: total cash inflows.
  • Rows 19–24: cash outflows, such as payroll, rent, supplier payments, taxes, loan payments, and other operating or one-time costs.
  • Row 25: total cash outflows.
  • Row 27: net cash flow for the month.
  • Row 28: ending cash balance.

Leave blank rows between sections for readability, or add categories that suit your situation. Keep the labels specific enough to make the totals useful. For example, separate payroll from contractor payments if those costs have different timing or need to be monitored separately. Avoid placing a receipt and a payment in the same row: separate inflow and outflow sections make it easier to see what is driving a change in cash.

Build the template step by step

  1. Open a blank workbook and rename the first worksheet Cash Flow. In A4, enter Month, then place January through December across B4:M4. You can type the month names directly or enter a date and apply a month display format.
  2. Enter the row labels listed above in column A. Put an opening cash balance in B5 based on the cash available at the start of January. If this is a forecast, use a reasonable starting figure from your records; if it is an actual report, reconcile it to the relevant bank or cash balance.
  3. Enter the amounts for each month in the applicable category rows. Use positive numbers for both receipts and payments, because the separate totals and subtraction formula will handle the direction of cash movement. Leave a cell blank or enter zero when a category has no activity.
  4. Add formulas for monthly totals, net cash flow, and ending balances. After entering the January formulas, copy the applicable formulas across the remaining month columns, checking that the references move to the correct month.
  5. Format the headings and amounts, then review the worksheet with a simple test case before relying on it. Confirm that the next month’s opening balance agrees with the prior month’s ending balance.

In Excel for Microsoft 365 on Windows, select the amount cells and use Home > Number to apply a currency or accounting format. Use Home > Conditional Formatting to highlight negative ending balances if that helps spot a projected shortfall. These ribbon options are also available in Excel for Mac, although the placement or presentation can differ slightly by platform; Excel for the web uses a more compact ribbon.

Core formulas with a worked example

For January, assume the template uses rows 7–14 for receipts, row 15 for total inflows, rows 19–24 for payments, row 25 for total outflows, row 27 for net cash flow, and row 28 for ending cash. With January receipt entries in B7:B14, enter this in B15:

=SUM(B7:B14)

For an example, if the receipt entries total $15,000, the formula returns $15,000. This total includes the amounts entered in the listed receipt rows; a blank cell contributes nothing.

With January payment entries in B19:B24, enter this in B25:

=SUM(B19:B24)

If those payments total $12,000, the formula returns $12,000. Record payment values as positive amounts in these rows; do not enter them as negatives when using this layout.

With total inflows in B15 and total outflows in B25, enter this in B27:

=B15-B25

Using the example totals, $15,000 minus $12,000 gives net cash flow of $3,000. A positive result means cash increased during the month before considering any opening balance; a negative result means cash decreased.

With the January opening balance in B5 and January net cash flow in B27, enter this in B28:

=B5+B27

If opening cash is $10,000 and net cash flow is $3,000, the ending balance is $13,000. For February, with January’s ending balance in B28, enter this in C5:

=B28

That returns $13,000 as February’s opening balance in the example. Repeat the monthly total and balance formulas in each following column. You can fill across by selecting the formula cell and dragging its fill handle to the right, or copy and paste it; relative references adjust for each column. If you use a forecast and want a fixed opening amount for a particular month, type that amount instead, but understand that doing so breaks the automatic roll-forward.

Forecasting timing, not just totals

A monthly template is useful for a high-level view, but monthly totals can hide short-term cash gaps. A business may receive $20,000 near the end of a month while payroll and rent are due at the beginning. The month could end with a healthy balance even though cash was insufficient on an earlier date. When timing is important, make a second worksheet with weekly or daily columns, or list expected transactions by date and summarize them by week.

Use realistic expected receipt dates rather than invoice dates, and expected payment dates rather than the dates a bill was entered. If a customer commonly pays late, a forecast that assumes payment on the invoice date may overstate available cash. For recurring transactions, enter the amounts in the months when cash is expected to move, and note any assumptions that may change, such as a one-time purchase or seasonal sales.

Do not confuse a loan payment with loan proceeds. Money received from a lender is a cash inflow; later repayments are outflows. Similarly, purchases of equipment, owner withdrawals, and tax payments may deserve their own rows because they can create substantial cash changes that are easy to miss inside a broad category.

Make the worksheet easier to read and maintain

Use consistent number formatting and clear labels. Currency formatting makes it easier to distinguish amounts from counts, while parentheses for negative values can make a cash deficit visible. You can use bold text or a fill color for total rows, but keep formatting restrained so input cells, formulas, and headings remain distinguishable. A note above the table can say whether figures are forecast, actual, or a mixture; mixing the two without labeling them is a common source of confusion.

Consider adding three practical fields alongside the monthly figures or on a companion sheet:

  • Source or assumption: where an estimate came from, such as a signed contract, an average of recent bills, or a rough estimate.
  • Status: whether the amount is planned, confirmed, or paid/received.
  • Last updated: when the forecast was revised, especially if several people maintain it.

Protecting formula cells can reduce accidental edits, but worksheet protection is not a replacement for keeping a backup. Save a separate copy before making major changes, and retain dated versions if the forecast is important. If multiple people use the workbook, agree on who changes assumptions and how actual transactions are recorded.

Common mistakes and how to avoid them

  • Entering expenses as negative values and subtracting them again: This can turn a payment into an increase. With the layout above, enter both inflows and outflows as positive amounts, then subtract total outflows from total inflows.
  • Using profit as if it were cash: Profit and cash flow can differ because of unpaid invoices, inventory, loan principal, equipment purchases, and timing. A profitable period can still have a cash shortfall.
  • Double-counting receipts: If a deposit is already included in customer receipts, do not also include it under another income category. Define categories so each transaction appears once.
  • Forgetting the roll-forward: Each month’s opening cash should normally equal the previous month’s ending cash. A mismatch may be an intentional adjustment, but it should be explained and checked.
  • Leaving out irregular costs: Annual insurance, tax installments, repairs, and equipment purchases may not occur every month, but omitting them can make the forecast misleading.
  • Over-trusting a forecast: Estimates change. Compare forecast amounts with actual bank activity regularly and revise future periods when new information arrives.

A negative ending balance is a warning in the model, not proof that a bank account will be overdrawn: credit availability, timing, and other accounts may affect the real position. Conversely, a positive monthly ending balance does not rule out a cash shortage within the month. Check actual account balances and payment dates before making decisions.

Basic template versus a detailed cash flow model

The simple monthly worksheet is quick to build and easy to explain. It is a good fit when the goal is to see broad monthly trends, track a limited number of categories, or learn how receipts and payments affect the balance. A more detailed model may separate operating, investing, and financing cash flows, distinguish confirmed transactions from estimates, or show multiple bank accounts. Those additions can answer more questions, but they also require more consistent data and upkeep.

For a household or very small project, fewer categories may be adequate: income, housing, utilities, food, transport, debt payments, savings transfers, and other spending. A business may need categories aligned with how it receives and spends cash. Do not copy a complex statement format simply because it looks authoritative; choose detail that supports a decision and can be maintained accurately. Formal reporting requirements vary, so use appropriate accounting support when the workbook must meet a reporting standard.

Frequently Asked Questions

How do I make a simple cash flow template in Excel?
Set up one column for each month, list an opening balance, cash receipt categories, payment categories, and totals. Calculate net cash flow as total receipts minus total payments, then add it to opening cash to get the ending balance. Carry that balance forward as next month’s opening cash.

Is there a free cash flow template in Excel?
Excel may offer workbook templates through its New screen, and Microsoft provides template resources that can change over time. Availability depends on the Excel version, account, and connected services. The layout in this guide can also be built in a blank workbook without downloading a template.

Should cash flow expenses be entered as positive or negative numbers?
In the layout described here, enter expenses and other cash outflows as positive values in their own section. The net cash flow formula subtracts the total outflow from total inflow. If you choose a different design that records payments as negative values, the calculation must be adjusted so the same payment is not subtracted twice.

What is the difference between cash flow and profit?
Profit is generally based on revenue and expenses recognized under the relevant accounting method, while a cash flow worksheet tracks receipts and payments as cash moves. For example, a sale made on credit can contribute to revenue before the customer pays. Loan principal repayment is a cash outflow but is not the same as an operating expense in a profit calculation.

How often should I update a cash flow forecast?
Update it often enough to reflect meaningful changes in expected receipts, bills, and payment dates. A weekly review may be useful when balances are tight or cash timing is volatile; a monthly review may be sufficient for a stable, high-level plan. Compare forecasts with actual activity and document important assumptions. This template is an organizational tool, not financial, tax, or legal advice; check actual lender terms with the lender and consult a qualified professional for advice on your circumstances.

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.