Cash flow template: what to include and how to build one in a spreadsheet

A cash flow template layout for ecommerce brands that hold stock: the rows to include, the formulas, and the mistakes that make a spreadsheet go stale.

Illustration for Cash flow template: what to include and how to build one in a spreadsheet

The short answer: a good cash flow template has one column per week and four blocks of rows: money in, money out, one-off payments, and the opening and closing balance. The closing balance of each week becomes the opening balance of the next. For a brand that holds stock, add a separate block for supplier orders, because that is usually the biggest payment you make.

This page gives you the layout so you can build it in Excel or Google Sheets in about an hour. For a finished set of numbers, see the cash flow forecast example.

The layout

Use one column per week for 13 weeks. Rows, top to bottom:

BlockRows
Opening balanceOpening bank balance (week 1 is today's actual balance)
Money inShopify payouts, marketplace payouts, wholesale invoices, other income
Regular money outAd spend, software, fulfilment and shipping, refunds and chargebacks, rent and other fixed costs
One-off paymentsPayroll, VAT or sales tax, loan repayments, owner drawings
Stock ordersSupplier deposit, supplier balance, freight and duties, each on its real due date
Net movementMoney in minus all money out for the week
Closing balanceOpening balance plus net movement
Minimum balanceYour safety floor, with a flag when the closing balance falls below it

The formulas

You only need three.

  • Net movement: total money in minus total money out for that week.
  • Closing balance: opening balance plus net movement.
  • Next week's opening balance: equal to this week's closing balance.

Add a conditional format that turns the closing balance red when it falls below your minimum. Set the minimum to at least one week of running costs plus one delayed payout.

Rules that make the template useful

  1. Use cash dates, not invoice dates. A supplier invoice that arrives today but is due in 45 days belongs 45 days out.
  2. Split every supplier order into its real payments. Deposit on order and balance on shipping are two lines on two dates.
  3. Separate payouts by channel. Shopify Payments and marketplaces settle on different schedules.
  4. Keep an input area. Put average weekly sales, ad spend and lead times in one place, so you change a number once rather than in thirteen columns.
  5. Copy the sheet for a slow-sales scenario. Reduce payouts by 20% and compare the lowest week in each version.
  6. Update it weekly. Overwrite last week's forecast with what actually cleared, and add a new week at the end.

Where spreadsheets go wrong

  • They go stale. The moment a supplier changes a date or a payout lands late, the sheet is wrong until someone fixes it.
  • Stock is typed in by hand. Reorder dates and quantities live in a different file, so the biggest payments in the forecast are guesses.
  • One scenario only. A single forecast hides how close the low week is.

If these are your problems, size your orders first with the reorder point calculator and the stock order calculator, then enter the payments in the template. The 13-week method explains how to keep it current.

FAQ

What should a cash flow template include? Opening balance, money in, regular costs, one-off payments, stock orders, net movement, closing balance and a minimum-balance flag, with one column per week.

How many weeks should it cover? 13 weeks is a common choice for ecommerce: long enough to see a supplier order cycle and a tax payment, short enough to stay reliable.

Is there a difference between a cash flow template and a cash flow forecast? The template is the empty structure. The forecast is the template filled with your expected payments and receipts.

Can I use my accounting software instead? Accounting software records income and costs when they are earned or incurred. A cash flow template records when money actually moves, which is the question that decides whether you can pay a bill on a given day.

Filed underForecastingCash Flow
Rory McLaughlan
Written by

Rory McLaughlan

Runs a Shopify store selling mystery football shirt boxes across the UK, EU and US. Built Cushion after years of joining up a cashflow sheet, a stock sheet and a sales forecast in his head every Sunday night.

See whether the next reorder leaves enough cash

Cushion puts your cashflow, stock and sales forecast on one screen, with read-only connections to your store, your books and your bank.