How to Create a Cash Flow Forecast in Excel

Knowing how much cash is in your bank account today is easy, but knowing how much will be there next month or next quarter is a game-changer. That's what a cash flow forecast does: it gives you a clear view of your financial future, helping you spot potential shortfalls before they become emergencies. This guide will walk you through building a simple yet powerful cash flow forecast from scratch using Microsoft Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Cash Flow Forecast (and Why You Absolutely Need One)

A cash flow forecast is a plan that projects how much money will move in and out of your business over a specific period. It's not the same as a profit and loss statement, which includes non-cash items like depreciation. The forecast is only concerned with actual cash changing hands, which is the lifeblood of any business.

Why bother? Because winging it with finances is a recipe for disaster. A good forecast helps you to:

• Avoid running out of money. This is the big one. A forecast gives you an early warning if your expenses are set to outpace your income, giving you time to arrange a loan, cut costs, or push for more sales.
• Make smarter decisions. Can you afford to hire a new employee? Should you invest in that new marketing software? Your forecast provides the data to answer these questions confidently.
• Plan for growth. Knowing when you'll have surplus cash allows you to plan strategic investments, like buying more inventory for a big sales season or expanding your services.
• Secure funding. If you're seeking a loan or investment, a well-researched cash flow forecast shows lenders and investors that you have a solid understanding of your business's financial health.

Gathering Your Financial Data

Before you open Excel, you need to collect some key pieces of information. The more accurate your data, the more reliable your forecast will be. Spend some time gathering the following:

1. Opening Cash Balance

This is your starting point. Check all your business accounts (checking, savings, etc.) and add up the total cash you have on hand right now. This amount will be the opening balance for the first month of your forecast.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Cash Inflows (Money Coming In)

List all the sources of cash your business receives. Don't guess, use historical data as your guide. Look at your past sales reports, invoices, and bank statements.

• Sales Revenue: The main source for most businesses. If your sales vary seasonally, account for that. Map out your monthly sales projections using your past records.
• Payment Timelines: Be realistic about when you actually get paid. If you invoice clients on 30-day terms, don't forecast that cash for the current month. If 20% of your clients pay late, factor that in.
• Other Income: Include any other sources of cash, like loans from a bank, infusions of cash from investors, tax refunds, or sales of assets.

3. Cash Outflows (Money Going Out)

This is every expense that requires you to spend cash. Review your bank and credit card statements, payroll records, and supplier invoices from the last 6-12 months to build a comprehensive list.

• Fixed Costs: These are regular, predictable expenses that don't change much month to month. Examples include rent, salaries, insurance, software subscriptions, and loan repayments.
• Variable Costs: These costs fluctuate depending on your sales or business activity. They might include inventory purchases, raw materials, advertising spend, shipping fees, and sales commissions.
• One-Time Expenses: Note any planned major purchases, like new equipment, website development, or legal fees, and place them in the correct month on your forecast.

Setting Up Your Excel Spreadsheet: A Step-by-Step Guide

With your data gathered, it's time to build your forecast. Follow these steps to create a clean, functional template.

Step 1: Create a Timeline

Open a new Excel spreadsheet. In row 1, set up your time periods. Start in cell B1 and type "Month 1" (or a specific month like "January"). Use the fill handle (the small square in the bottom-right corner of the cell) to drag across the row to create "Month 2," "Month 3," and so on, for at least 12 months.

Step 2: List Your Opening Balance

In cell A2, type "Opening Balance." In cell B2, enter the total cash you have on hand right now. This is the amount you gathered earlier.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Detail Your Cash Inflows

Leave a row for spacing. In cell A4, type the heading "Cash Inflows." Below this, list your different income streams in column A (e.g., "Product Sales," "Service Revenue," "New Loan").

At the bottom of this list, in a cell like A8, type Total Cash Inflows. In cell B8, use the SUM formula to add up all your inflows for Month 1. The formula will look something like this:

=SUM(B5:B7)

You can then drag this formula across the row to apply it to all subsequent months.

Step 4: Detail Your Cash Outflows

Leave another blank row. In cell A10, type the heading "Cash Outflows." Just like with inflows, list all your categories of expenses in column A (e.g., "Salaries," "Rent," "Marketing Spend," "Inventory," "Software Tools").

At the bottom of this list, perhaps in cell A20, type Total Cash Outflows. In cell B20, use the SUM formula again to total all your outflows for Month 1:

=SUM(B11:B19)

Drag this formula across the row for all months.

Step 5: Calculate Net Cash Flow

Your net cash flow is the difference between your total inflows and outflows. Leave a blank row and in cell A22, type "Net Cash Flow."

In cell B22, subtract your total outflows from your total inflows. The formula is simple:

=B8-B20

Drag this formula across the row.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Calculate the Closing Balance

The closing balance is the grand finale for each month. It tells you your projected cash position at the end of the period. In a cell like A24, type Closing Balance.

In cell B24, add the net cash flow for the month to the opening balance. The formula is:

=B2+B22

Now, here's the most critical part that ties the entire forecast together. Next month's opening balance is this month's closing balance.

Go to cell C2 (the Opening Balance for Month 2) and simply make it equal to Month 1's closing balance. The formula is:

=B24

Drag this formula across row 2 for all the remaining months. Your spreadsheet is now dynamic - if you change an inflow in Month 1, the opening balance for Month 2 (and every subsequent month) will automatically update.

Tips for a More Accurate and Useful Forecast

A template is just a starting point. Use these tips to make your forecast a powerful tool for your business.

• Be Conservative in Your Estimates. It's human nature to be optimistic about sales and underestimate expenses. Fight this urge. It's better to forecast a tight month and be pleasantly surprised than to plan for a windfall that never comes.
• Create Multiple Scenarios. What happens if a major client pays late? What if your ad campaign costs double what you budgeted? Copy your Excel sheet into new tabs labeled "Best Case," "Worst Case," and "Most Likely." Adjusting the numbers will reveal potential vulnerabilities and opportunities.
• Plan for Seasonality. Nearly every business has an ebb and flow. If you run a retail store, your Q4 inflows might be massive, but Q1 could be very quiet. Use your historical data to model these peaks and troughs accurately.
• Review and Update Regularly. A forecast is a living document, not a "set it and forget it" task. At the beginning of each month, update it with your actual numbers from the previous month and adjust your future projections accordingly. The more you use it, the more accurate it becomes.

Final Thoughts

Creating a cash flow forecast in Excel puts you firmly in control of your company's financial health. By tracking the money moving in and out, you equip yourself to make proactive, data-informed decisions that will guide your business toward sustainable growth.

Once you've mastered your cash flow, connecting those financial outcomes to the marketing and sales activities that drive them is the next powerful step. Manually pulling data from your various platforms - like Shopify, QuickBooks, Google Ads, and Salesforce - into a master spreadsheet can be incredibly time-consuming. We built Graphed to make this easier. You connect your data sources once, and then you can ask questions in plain English like "show my revenue and marketing spend this quarter" to build live, real-time dashboards instantly, without a single formula.