How to Build a 12-Month Cash Flow Forecast in Google Sheets

Most small businesses fail not because they're unprofitable — but because they run out of cash. A profitable business can still go under if income and expenses don't line up on a monthly basis. A 12-month cash flow forecast in Google Sheets lets you see cash crunches 3, 6, or 9 months before they happen — giving you time to act instead of scramble. Here's exactly how to build one.

Cash Flow vs. Profit: Why the Difference Matters

Profit is revenue minus expenses on paper. Cash flow is the actual movement of money in and out of your bank account. These two numbers are often very different — and it's the gap between them that kills businesses.

Common scenarios where a profitable business has a cash flow problem:

• Net-30/60 invoicing: You deliver $20K of work in January, but the client doesn't pay until March. Your P&L shows income in January; your bank account doesn't see it for 60 days.
• Seasonal revenue: A landscaping company earns 70% of revenue in five months. The other seven months still have overhead — insurance, equipment loans, staff.
• Inventory-heavy businesses: You buy $15K of inventory in August for holiday sales in November. Three months of cash is tied up before it comes back as revenue.
• Growth: Expanding often requires spending more before earning more — new hires, equipment, marketing — creating temporary cash shortfalls even when the business is profitable long-term.

A business cash flow spreadsheet shows you when money moves, not just if it moves. That timing visibility is what allows you to plan.

The Structure of a 12-Month Cash Flow Forecast

Your cash flow forecast in Google Sheets uses a simple structure: rows for income and expense categories, columns for each month, and a running balance row at the bottom. Here's the complete layout:

Tab Structure

Keep the spreadsheet clean with these tabs:

• Assumptions — Growth rates, seasonal factors, payment terms, key variables
• 12-Month Forecast — Main forecast with columns Jan–Dec
• Actuals — What actually happened (update monthly as you close each month)
• Variance — Forecast vs. Actual comparison (identifies where your assumptions were off)
• Scenarios — Best case, base case, and worst case versions

Forecasting Your Cash Inflows

Cash inflows include everything that brings money into the business. For most small businesses, this means revenue — but also loans drawn, owner contributions, and asset sales.

Revenue Forecasting Methods

Method 1: Historical Trend (Best for Established Businesses)

Use 12–24 months of historical revenue data. Calculate average monthly revenue, identify seasonal patterns, and apply a growth rate. Example: if June has historically been 140% of your average month, forecast June at 140% of your projected average.

Formula in Google Sheets: =AVERAGE(PreviousJune1, PreviousJune2) * GrowthFactor

Method 2: Pipeline Method (Best for Service Businesses)

Build inflows from your sales pipeline. List current active clients/contracts and expected payment amounts by month. Add projected new business based on your average close rate and lead volume. This method is more accurate for businesses with known contracts.

Method 3: Unit-Based Projection (Best for Product Businesses)

Forecast units sold × price per unit. Build in seasonal adjustments. Account for payment terms (if you invoice, add collection lag).

Accounting for Payment Terms

If your clients pay on Net-30 terms, the revenue you earn in April hits your bank account in May. In your forecast, shift income from the month earned to the month collected. Create a collection formula:

April Cash Collected = March Revenue × 0.80 + April Revenue × 0.20

(Assumes 80% of monthly revenue collected the following month, 20% paid immediately — adjust to your actual payment patterns.)

Mapping Your Cash Outflows

Outflows are every dollar leaving your business. The discipline here is being comprehensive — missing a category creates a forecast that looks better than reality.

Handling Irregular and Annual Expenses

Annual expenses (insurance renewals, license fees, equipment purchases) are easy to forget until they hit. List every recurring expense with its payment frequency in your Assumptions tab, then use a formula to place them in the correct month. For quarterly payments (like estimated taxes), enter them in the four correct months and leave the other months at zero.

The Running Balance: Your Most Important Number

The closing cash balance row — the running balance — is the single most important output of your cash flow forecast. It tells you:

• How much cash you'll have at the end of each month
• When cash gets dangerously low — before it happens
• Whether you need financing and how much
• Whether you can invest in growth safely

The Running Balance Formula

Closing Balance (Month N) = Opening Balance (Month N) + Net Cash Flow (Month N)

Opening Balance (Month N+1) = Closing Balance (Month N)

Set up the first month manually (Opening Balance = current bank balance), then create formulas that chain each month into the next. This creates a rolling projection that automatically recalculates when you update any assumption.

The Minimum Cash Threshold

Set a minimum cash balance in your Assumptions tab. Most small businesses should maintain at least 1–2 months of operating expenses in cash as a buffer. Add a row below your closing balance that shows:

=IF(ClosingBalance < MinimumCash, "⚠️ BELOW MINIMUM", "✓ OK")

Add conditional formatting to turn the cell red when the balance falls below your minimum. This creates an automatic early warning system.

Tracking Actuals vs. Forecast

A forecast you never compare to reality is just a budget you ignore. The Actuals tab is where you record what actually happened each month, and the Variance tab shows the difference.

Monthly Update Routine

1. At month-end, enter actual inflows and outflows into the Actuals tab
2. The Variance tab automatically calculates Forecast vs. Actual for each line item
3. Identify which categories were materially off (±15% or more)
4. Update your Assumptions tab to reflect better estimates for future months
5. The revised assumptions automatically update your remaining month forecasts

The Variance Analysis Table

Scenario Planning: Best/Worst Case

Your base case forecast is your best estimate. But running a business means dealing with uncertainty. Scenario planning builds versions of your forecast under different assumptions so you know what happens if revenue comes in 20% below plan or expenses spike unexpectedly.

Three Scenarios to Model

• Best Case: Revenue 15–20% above base, expenses on plan. Shows maximum cash position — useful for planning growth investments.
• Base Case: Your realistic forecast. The main version you manage against.
• Worst Case: Revenue 20–30% below base, expenses 10% above plan. Shows the scenario you need to have a plan for — when do you run out of cash? What decisions do you need to make before that happens?

Build scenarios by creating three copies of your Forecast tab, each with different assumption inputs. A summary tab shows all three side by side — specifically the closing balance for each month across all scenarios.

Reading Your Forecast: Red Flags and Action Signals

A cash flow forecast is only useful if you act on what it tells you. Here are the warning signs to watch for and what to do about each:

Red Flag: Closing Balance Drops Below 1 Month Expenses

What it means: You have very little buffer. One unexpected expense or slow month could put you in the red.
Action: Draw on your line of credit now (before you need it). Accelerate collections. Defer discretionary spending. Have a cash conversation with your accountant.

Red Flag: Net Cash Flow Negative for 3+ Consecutive Months

What it means: Your business is burning cash consistently. This is manageable temporarily (seasonal businesses, growth phase) but needs a clear reversal point.
Action: Identify the top three expense categories and cut or defer. Accelerate revenue — price increase, new offer, push for faster payments.

Red Flag: Significant Revenue Concentration in 1–2 Months

What it means: Seasonal business — high months fund the rest of the year. If the big months underperform, the whole year suffers.
Action: Model downside scenarios for your peak months specifically. Build cash reserves during peak season to fund off-season operations.

FAQ: Cash Flow Forecasting in Google Sheets

How far out should I forecast?

12 months is the minimum for planning purposes. Many established businesses maintain an 18–24 month rolling forecast. The further out you go, the less precise the numbers — but even rough 18-month projections highlight structural issues that a 3-month view misses.

How often should I update my forecast?

Enter actuals monthly at minimum. Review and re-forecast quarterly — updating assumptions for the next 3–6 months based on current business conditions. When major changes occur (big new client, significant expense, economic shift), update immediately.

Should my forecast include owner salary?

Yes. If you're taking a salary or owner's draw, it's a cash outflow in your forecast. Many self-employed business owners underestimate their own compensation — your forecast should include your planned draw even if you sometimes take less. Planning for your own pay is part of running a real business.

What's the difference between a cash flow forecast and a P&L?

A P&L (income statement) shows revenue, expenses, and profit for a period — often on an accrual basis. A cash flow forecast shows when cash actually moves — accounting for payment terms, timing of large expenses, and financing activity. Both are necessary; they answer different questions.

Related reading: Freelance Income & Expense Tracker Guide · Rental Property ROI Calculator in Google Sheets · Home Bakery Business Planner in Google Sheets