A small business can look profitable on paper and still run short of cash.
The reason is timing. A customer may accept an invoice today and pay in 30 days, while payroll, rent, tax and suppliers are due much sooner. A bank balance tells you where you are now. It does not tell you where you are heading.
Google Sheets is a practical place to solve this because the calculations are visible, the workflow is flexible and the forecast can be shared with the people who help run the business. The weak point is usually the input process: repeated bank exports, inconsistent columns and a forecast that quietly becomes stale.
This guide shows how to build a small-business cash-flow tracker that updates its actual transactions automatically, keeps future assumptions separate and highlights a potential cash shortfall before it becomes urgent.
Current bank actuals
Supported transactions and balances flow into dedicated raw-data tabs on a schedule.
A rolling 13-week view
Expected customer receipts and business payments are placed in the week cash should actually move.
Visible cash headroom
Each week shows projected closing cash, the minimum operating buffer and any expected shortfall.
A short weekly review
Actual results replace settled assumptions, delayed items move forward and recurring forecast errors become visible.
Start by separating actual cash from expected cash
A reliable cash-flow model has two different kinds of information:
- Actuals: transactions and balances that have already been posted by the bank.
- Forecasts: cash movements that have not happened yet, such as invoices expected to be collected, payroll, tax, subscriptions, rent, inventory purchases and loan repayments.
Do not mix them in one editable table. Bank data should remain traceable to its source. Forecast assumptions should remain editable and explainable.
What can be automated and what still needs an owner
| Feature | Automated actuals | Controlled forecast |
|---|---|---|
| Posted bank transactions | Not an assumption | |
| Current account balances | Used as a verified opening point | |
| Customer receipts | Visible after the money arrives | |
| Payroll, tax and suppliers | Visible after payment | |
| Scenarios and minimum cash buffer |
Use five tabs, not one giant spreadsheet
Keep each part of the workflow responsible for one job. This makes the model easier to audit and less likely to break when new rows arrive.
Recommended Google Sheets structure
| Tab | Purpose | Who changes it |
|---|---|---|
| Accounts | Latest supported balances, account labels, currency and whether each account belongs in operating cash. | Feed plus controlled settings |
| Transactions | Append-only posted cash movements with stable transaction and account identifiers. | Automated feed |
| Cash_Plan | Expected receipts and payments that have not settled yet. | Owner or finance team |
| Forecast_13W | Opening cash, weekly inflows, outflows, closing cash, buffer and headroom. | Formulas |
| Dashboard | The few measures needed for weekly decisions and follow-up. | Formulas and charts |
The raw-data tabs are controlled by the feed. The planning and reporting tabs are controlled by the business.
Set up the automatic bank-data layer
BankSync can send supported transactions and balances to Google Sheets while allowing the spreadsheet to remain the working model. Availability, history and update timing depend on the connected institution, region and underlying data source.
Connect BankSync to the cash-flow workbook
Create the workbook and raw-data tabs
Create Accounts and Transactions first. Keep formulas, dashboards and manual notes out of the ranges that the feed will control.
Connect the relevant business accounts
Include the operating accounts needed for the cash position. Keep separate legal entities and personal accounts out of the model unless there is an explicit, documented reason to include them.
Choose Google Sheets as the destination
Authorise the correct Google workspace and select the cash-flow workbook.
Map the source fields
Map date, amount, description, merchant, account, currency, category and transaction ID into stable columns.
Choose an appropriate schedule
Daily is enough for many small businesses. More frequent schedules may suit active cash monitoring when the plan and connected source support them.
Inspect the initial sync
Check date formats, debit and credit signs, currencies, account names, available history and duplicate handling before building reporting formulas.
Protect the raw ranges
Restrict casual editing and keep manual classifications in separate columns or mapping tables so a refresh does not overwrite business context.
Review the BankSync Google Sheets workflow.
Build the Transactions tab as an append-only actuals layer
A cash-flow tracker needs a stable record of what actually happened. Keep the source table boring and predictable. Build summaries, charts and forecasts elsewhere.
Recommended Transactions columns
| Column | Purpose |
|---|---|
| Date | Places the cash movement in the correct day and week. |
| Account | Identifies the bank or card account that supplied the row. |
| Currency | Prevents unlike currencies from being added without an explicit conversion rule. |
| Description | Preserves the bank's original transaction text. |
| Merchant or counterparty | Provides a cleaner reporting label where available. |
| Amount | Uses one consistent sign convention: positive for cash in and negative for cash out. |
| Category | Groups recurring cash movements such as payroll, tax, rent, suppliers and customer receipts. |
| Cash flow class | Separates operating, investing, financing and owner-related movements where useful. |
| Cash treatment | Marks the row Include or Exclude for consolidated cash-flow calculations. |
| Transaction ID | Supports deduplication together with the source account. |
| Review | Creates an exception queue for uncertain categories or unusual movements. |
| Notes | Stores business context outside the provider-controlled fields. |
Provider-controlled fields should remain separate from manual review and planning fields.
Decide how cards and transfers should behave
Internal transfers do not create or consume cash for the business as a whole. Mark both sides as Exclude from consolidated inflow and outflow totals while keeping them visible for account reconciliation.
Credit cards need an explicit rule. For a cash-only forecast, the payment from the bank account is the cash outflow; counting every card purchase and the later card repayment would double-count it. You may still analyse card purchases on a separate expense view.
Build a Cash_Plan tab for future movements
The bank feed cannot know about an invoice that has not been paid, next month's payroll, a quarterly tax payment or a supplier order that has not settled. Put those items in a separate planning table.
Recommended Cash_Plan columns
| Column | Example | Purpose |
|---|---|---|
| Expected date | 2026-09-04 | The date cash is realistically expected to move. |
| Description | August client invoice | A clear operational label. |
| Counterparty | Acme Pty Ltd | Supports collection and supplier follow-up. |
| Direction | Inflow or Outflow | Controls which forecast line receives the amount. |
| Amount | 12500 | Positive amount used by the weekly formulas. |
| Status | Expected | Change to Received, Paid or Cancelled when the assumption is resolved. |
| Confidence | Committed, likely or uncertain | Makes optimistic assumptions visible rather than hidden. |
| Owner | Chris | Identifies who should confirm or chase the item. |
| Notes | Customer normally pays 7 days late | Records the reasoning behind timing and amount. |
Amounts are stored as positive values; Direction determines whether each item is an inflow or outflow.
Build a rolling 13-week forecast
A weekly 13-week horizon is a practical default for many operating businesses: close enough to identify near-term pressure, but long enough to see payroll cycles, tax dates, supplier commitments and collection delays. Use a different horizon when the business cycle requires it.
Set up these columns in Forecast_13W:
Forecast_13W layout
| Column | Meaning |
|---|---|
| Week starting | The first day of each seven-day forecast period. |
| Opening cash | Verified available operating cash at the start of the week. |
| Actual inflows | Included positive transactions posted during the week. |
| Actual outflows | Absolute value of included negative transactions posted during the week. |
| Expected inflows | Cash_Plan inflows still marked Expected for the week. |
| Expected outflows | Cash_Plan outflows still marked Expected for the week. |
| Closing cash | Opening cash plus inflows less outflows. |
| Minimum buffer | The cash floor the business does not want to cross. |
| Headroom | Closing cash less the minimum buffer. |
Assume the following columns:
Transactions: A Date, D Amount, G Cash treatment.Cash_Plan: A Expected date, C Direction, D Amount, E Status.Forecast_13W: A Week starting, B Opening cash, C Actual inflows, D Actual outflows, E Expected inflows, F Expected outflows, G Closing cash, H Minimum buffer, I Headroom.
Use the formulas below in row 2 and copy them down.
=SUMIFS(Transactions!$D:$D,Transactions!$A:$A, ">="&$A2,Transactions!$A:$A, "<"&$A2+7,Transactions!$D:$D, ">0",Transactions!$G:$G, "Include")
=-SUMIFS(Transactions!$D:$D,Transactions!$A:$A, ">="&$A2,Transactions!$A:$A, "<"&$A2+7,Transactions!$D:$D, "<0",Transactions!$G:$G, "Include")
=SUMIFS(Cash_Plan!$D:$D,Cash_Plan!$A:$A, ">="&$A2,Cash_Plan!$A:$A, "<"&$A2+7,Cash_Plan!$C:$C, "Inflow",Cash_Plan!$E:$E, "Expected")
=SUMIFS(Cash_Plan!$D:$D,Cash_Plan!$A:$A, ">="&$A2,Cash_Plan!$A:$A, "<"&$A2+7,Cash_Plan!$C:$C, "Outflow",Cash_Plan!$E:$E, "Expected")
Forecast_13W!G2=B2+C2-D2+E2-F2Forecast_13W!I2=G2-H2Forecast_13W!B3=G2
Avoid mixing currencies and entities
Do not add unrelated legal entities or different currencies into one cash total without explicit consolidation rules. A practical approach is one forecast per entity and currency, then a separate management summary. If conversion is necessary, record the exchange rate and date used rather than hiding the assumption inside a formula.
Put only decision-useful measures on the dashboard
A dashboard should answer what needs attention, not display every available chart.
Useful cash-flow dashboard measures
Available operating cash
Included cash balances after excluding restricted, personal or separately reserved accounts.
Lowest projected balance
The lowest weekly closing balance in the current forecast horizon.
First buffer-breach date
The first week in which projected cash falls below the agreed minimum.
Expected receipts in 30 days
Customer and other inflows still marked Expected in the next 30 days.
Expected payments in 30 days
Payroll, tax, suppliers, rent, debt and other outflows expected soon.
Forecast-to-actual variance
The difference between what was expected and what actually settled, by week and major category.
Run a short cash review every week
Automation removes repetitive transaction entry. It does not remove the need to review assumptions. A consistent 15-minute process is usually more useful than a complex model that no one maintains.
The weekly review
Reconcile the actuals
Confirm that recent transactions, balances, signs and account labels look complete and reasonable.
Resolve settled plan items
Mark expected receipts and payments as Received or Paid once the matching cash movement appears.
Move delayed items
Change the expected date when a customer, supplier or internal decision shifts the timing.
Review the lowest cash week
Check the size, date and cause of the projected low point rather than only today's balance.
Assign collection actions
Give overdue or high-impact customer receipts a clear owner and follow-up date.
Explain material variance
Record whether a difference came from timing, amount, classification or a missing assumption.
Common cash-flow automation mistakes
Treating an invoice as cash
Revenue and issued invoices do not improve the bank balance until the customer pays. Forecast the expected receipt date.
Counting transfers as operating activity
Moving money between owned accounts can inflate both inflows and outflows unless both sides are excluded.
Counting card purchases and repayments twice
Choose whether the model is showing cash movement or expense activity, then apply that rule consistently.
Forgetting tax, payroll and annual bills
Large periodic payments should be scheduled before they appear in the bank feed.
Using optimistic collection dates
Base expected receipts on customer behaviour, contractual terms and current information, not the date you hope to be paid.
Editing the raw transaction table
Manual totals and classifications inside feed-controlled ranges make refreshes fragile and the audit trail harder to follow.
Keep the spreadsheet secure
Protect both the financial connection and the Google Sheet receiving the data.
- Limit workbook access to people who need it.
- Avoid public-link sharing.
- Use multi-factor authentication on the connected Google account.
- Keep separate workbooks or access boundaries for different entities and clients.
- Review connected accounts, workspace members and departed users.
- Avoid placing credentials, authentication tokens or full account numbers in ordinary spreadsheet cells.
A secure bank connection does not make an openly shared spreadsheet secure.
Frequently asked questions
Small-business cash-flow tracker FAQs
The bottom line
The most useful cash-flow spreadsheet is not the one with the most charts. It is the one that starts with trustworthy actuals, keeps future assumptions visible and shows the first week the business may need to act.
Use BankSync to reduce the repetitive work of bringing supported transactions and balances into Google Sheets. Keep invoices, payroll, tax, supplier payments and scenarios in a separate plan. Then review the gap between forecast and actual cash every week.
That gives the business a clearer view of what is available now, what is likely to happen next and which receipt or payment deserves attention first.