How to Build a Cash Flow Tracker in Google Sheets

Build a cash flow tracker as a 13-week grid with one column per week, Monday through Sunday. Each column holds beginning cash, cash coming in, cash going out, net cash flow, and ending cash, and each week’s ending cash becomes the next week’s beginning cash. Start from a real bank balance, enter when you expect money to arrive and leave, and the sheet estimates your balance week by week for the next quarter. It shows which week looks tight while you still have time to act.

Why a Profitable Month Can Still Leave You Short of Cash

If your books use accrual accounting, your profit and loss statement records revenue when you earn it, and your bank account changes only when money moves. (Cash-basis books record revenue when it arrives, but cash timing still drives the balance.) The gap between profit and cash is where most cash problems start:

• A customer invoice counts as revenue when you earn it and may not be paid for 30, 45, or 60 days.
• Inventory and materials can leave your account before the cost shows up in your books, depending on how your books treat inventory.
• Loan principal payments and owner draws reduce your cash, and neither is an operating expense on the P&L.
• Payroll, payroll taxes, and other business tax payments go out on fixed dates that have nothing to do with when customers pay. They need dated entries in the tracker; however, your books record them.

Accounting software tells you what already happened. A cash flow tracker looks forward and estimates your balance from the timing you enter.

It also makes collections visible. The most reliable fix for slow payers is contact before an invoice is late: set up receivables so reminders go out, or someone talks to the customer, so you know what will lag and aim to keep invoices from drifting past 60 days. The tracker shows what’s left after you’ve done that.

Why 13 Weeks

Thirteen weeks is roughly one quarter, and it’s a common horizon in professional cash forecasting. It’s far enough out to see a problem while you can still act on it. The nearer weeks are easier to estimate and easier to correct each week; the further ones are rougher, which is why you refresh the sheet every week. Weekly columns match how cash moves: payroll, rent, and vendor payments land on specific days within specific weeks.

Set Up the Sheet

If you’d rather start from a finished version, copy the template, which has the layout, formulas, and color rules already built with made-up example numbers. To build it yourself, open a blank Google Sheet. Put labels in column A and the 13 weeks in columns B through N. Each column covers one week from Monday through Sunday. Enter every amount as a positive number; the formulas do the subtracting. Use these rows:

Row	Column A label	What goes in each week
1	Week starting	The Monday date
2	Beginning cash	Last week’s ending cash
3	Customer payments	Invoices you expect to be paid that week
4	Card and online sales	Deposits you expect from your processor
5	Other cash in	Anything else that lands in the bank
6	Total cash in	Sum of rows 3 to 5
7	Payroll and payroll taxes	Wages, taxes, and benefits
8	Rent and utilities
9	Vendors and inventory	Bills you plan to pay that week
10	Loan and tax payments	Principal, interest, and business tax payments due
11	Owner draws and discretionary purchases	Money you can choose to move
12	Total cash out	Sum of rows 7 to 11
13	Net cash flow	Cash in minus cash out
14	Ending cash	Beginning cash plus net cash flow

Then enter these formulas:

1. B1: type the date of this week’s Monday. In C1 enter =B1+7 and copy it across to N1.
2. B2: type your bank balance as of the start of that Monday, meaning the balance after Sunday’s transactions have cleared. In C2 enter =B14 and copy it across to N2. That link carries each week’s ending cash into the next week.
3. B6: =SUM(B3:B5)
4. B12: =SUM(B7:B11)
5. B13: =B6-B12
6. B14: =B2+B13
7. Copy B6 and B12 through B14 across to column N.

Type your minimum cash level in cell A17, with the label “Minimum cash” in A16. The next section explains it.

If you start in the middle of a week, use your current bank balance in B2 and enter only the money that moves after that moment in the first column. Counting a transaction that has already cleared would count it twice.

Fill In Cash In by When the Money Lands

Enter each receipt in the week the money reaches your bank, not the week you send the invoice. If a customer on 30-day terms usually pays on day 45, put the payment in the week that contains day 45. Use how each customer actually pays; their terms only show when they’re supposed to.

For card and online sales, look at the last eight to thirteen weeks of deposits in your bank export and use a typical week, adjusted for anything you know is coming. The business data you already have includes those exports. For retainers and recurring billing, use the billing date plus however long the money takes to settle.

When you’re unsure about a payment, put it a week later. An early payment costs you nothing, while an assumed one that doesn’t show up can hide a shortfall.

Fill In Cash Out by When It Leaves

List each payment in the week it will leave the account. Include the items that don’t appear as operating expenses on the P&L: loan principal and owner draws. Those are the ones that make a profitable business feel short. Business tax payments and payroll taxes belong in the grid on their due dates, however your books record them.

Rows 10 and 11 keep two kinds of payments apart. Row 10 holds payments you owe on a schedule: loan payments and tax payments. Payroll and payroll taxes in row 7 and rent in row 8 are also fixed. You generally can’t move any of these on your own. A vendor bill in row 9 moves only if the vendor agrees. Row 11 holds the money you can choose to delay, such as an owner draw or a purchase you can postpone. When a week turns red, row 11 is the first place to look.

Set a Minimum Cash Level and Let Color Warn You

Pick a floor: the lowest balance you’re comfortable seeing. One starting point is enough to cover two payroll runs. Set yours by how quickly you could collect or borrow if you needed to. Enter the number in A17.

Then add two color rules to the Ending cash row:

1. Select B14:N14 and open Format > Conditional formatting.
2. Choose “Custom formula is” and enter =B14<$A$17. Set the fill to red.
3. Add another rule with =AND(B14>=$A$17, B14<1.5*$A$17). Set the fill to yellow.

Red means projected cash at the end of the week is below your floor. Yellow means it’s within 50% above it.

What the Colors Can’t See

These rules check each week’s ending balance only. A week can end above your floor and still dip below it in between. Say a week starts at $34,000 and your floor is $30,000. Payroll takes $15,000 out on Tuesday, and an $18,000 customer payment arrives on Friday. The Tuesday balance is $19,000, which is $11,000 under the floor, but the week ends at $37,000 and shows yellow rather than red. The weekly color does not reveal how low cash fell on Tuesday.

For any yellow week, and any week with a large payment before a large receipt, list the dated payments and receipts for that week on a separate tab, or build a day-by-day view, before you schedule payments. The weekly grid shows you where to look.

A Worked Example

The numbers below are made up. A business starts week 1 with $42,000 in the bank and sets its minimum at $30,000. It pays $15,000 in payroll and taxes every other week, and rent of $8,000 comes out in weeks 1 and 5.

	Wk 1	Wk 2	Wk 3	Wk 4	Wk 5	Wk 6
Beginning cash	$42,000	$34,000	$43,000	$34,000	$39,000	$24,000
Total cash in	$18,000	$15,000	$13,000	$14,000	$12,000	$11,000
Total cash out	$26,000	$6,000	$22,000	$9,000	$27,000	$5,000
Net cash flow	-$8,000	$9,000	-$9,000	$5,000	-$15,000	$6,000
Ending cash	$34,000	$43,000	$34,000	$39,000	$24,000	$30,000

Week 5 turns red. Payroll and rent land in the same week while cash in is the lowest it’s been, and the balance ends $6,000 under the minimum. Standing in week 1, that’s four weeks of notice. Weeks 1 through 4 and week 6 are yellow, and week 6 sits exactly on the floor.

Four weeks is enough to do something, and the sheet shows what each change does. Asking a vendor to move a $4,000 bill from week 5 to week 6 lifts week 5 to $28,000, which is still $2,000 short. Week 6 falls to the same $30,000 as before, because the bill was delayed, not removed. Pairing the delay with a $2,000 customer payment that you pull in from week 6 to week 5 brings week 5 to exactly $30,000. Nothing in the P&L would have flagged this.

Update It Every Week

Pick Monday morning. It takes about 15 minutes, and Monday is when last week is over, and your opening balance is clear.

1. Duplicate the tab and rename the copy with the date. You’ll use the copies later to compare what you forecast with what happened.
2. In the live tab, delete the column for the week that just ended (right-click column B, then Delete column). The dates, beginning cash, and ending cash rows will show #REF! across the sheet until you finish the next step.
3. Type this week’s Monday date in B1 and the bank balance at the start of that Monday in B2. The errors clear, and every week recalculates.
4. Copy the last column into the next one to add a new week 13. The color rules come along with the pasted column. Then update its amounts.
5. Revise the next four to eight weeks with whatever you’ve learned: paid invoices, new bills, changed plans.
6. Look at the colors. If a week is red or yellow, pick one action and a date to take it.

If you update midweek, don’t delete the current week. Change the amounts you now know and leave the structure alone.

Mistakes That Make a Cash Flow Tracker Useless

• Using invoice dates instead of payment dates. This is the most common one. It makes the forecast look healthier than your bank account will.
• Leaving out cash that never touches the P&L. Loan principal and owner draws are the usual ones.
• Counting a transaction twice. If you start from today’s bank balance, leave out the money that has already moved.
• Not updating it. A tracker you refresh every week is worth more than a detailed one you rebuilt once.
• Treating the forecast as exact. Round to the nearest hundred. The goal is to see which week gets tight, not to predict the penny.

How Long to Stay in a Spreadsheet

Stay in Google Sheets until it stops being enough. A sheet like this handles a small business well, and Google Sheets supports several people editing the same file, so shared access alone is no reason to leave. Look at other tools when you hit a specific problem the sheet doesn’t solve, such as pulling bank and accounting transactions in automatically across many accounts or entities, or meeting review and control requirements that a shared spreadsheet can’t. Until then, a disciplined sheet you update every week will do more than software you don’t use.

Color alone won’t email you. If you want a message when a week goes red, that’s a separate setup. It needs Google’s conditional notifications, which are available only on certain work or school accounts, or a short Apps Script, and both have limits.

Frequently Asked Questions

Does a cash flow tracker replace my accounting software?

No. Accounting software records what happened and produces your P&L and balance sheet. The tracker looks forward at cash timing. You use your accounting records to fill it in.

How accurate does the forecast need to be?

Accurate enough to tell you which weeks are tight. Round amounts, put uncertain receipts later, and update weekly. The weeks closest to today will be the most reliable.

Do I need a template?

No. The layout above takes about 45 minutes to build, and building it yourself means you know what every row does. If you’d rather not, copy the template and replace the example numbers with yours.