How to Create a Sales Forecast in Excel

Creating an accurate sales forecast doesn't require a data science degree or expensive, complex software. You can build a powerful and reliable forecast using a tool you already know and use: Microsoft Excel. This article will walk you through exactly how to do it, covering different methods and providing step-by-step instructions to turn your historical data into an actionable plan for the future.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Sales Forecast and Why Does It Matter?

A sales forecast is an estimate of future sales revenue over a specific period - be it a month, a quarter, or a year. It's more than just a guess, it's a data-informed prediction that helps guide critical business decisions. For many companies, it's the single most important report for steering the ship.

Without a reliable forecast, you're essentially flying blind. Here’s why it’s so important:

• Smart Resource Planning: A forecast tells you what to expect, helping you make informed decisions about inventory management, staffing, and operational needs. Will you need to hire more sales reps? Stock up on a particular product? A good forecast has the answers.
• Budgeting and Cash Flow Management: An accurate revenue prediction is the foundation of any business budget. It helps you manage cash flow, allocate funds for marketing or product development, and ensure financial stability.
• Setting Realistic Goals: It helps you set achievable quotas for your sales team. Goals based on historical data and current pipeline are far more motivating than numbers picked out of thin air.
• Informing Strategy: Is a particular product line taking off? Is a new market showing promise? Your forecast can reveal trends that inform your broader business, marketing, and sales strategies.
• Building Investor Confidence: If you're seeking funding, a well-researched sales forecast demonstrates that you have a deep understanding of your business and market, building confidence with potential investors.

First, Gather the Right Data

Your forecast is only as good as the data you feed it. Before you open a blank Excel sheet, you need to collect and organize the right information. "Garbage in, garbage out" is a reality here.

Here’s what you'll typically need:

• Historical Sales Data: This is your most valuable asset. Collect as much as you can, preferably at least two years of monthly sales data. The more history you have, the better you’ll be able to identify patterns, growth trends, and seasonality.
• Sales Pipeline Data: For many B2B companies, this is essential. Export your open deals from your CRM (like Salesforce or HubSpot). You'll want the deal value, current stage in the sales process, sales rep, and expected close date.
• Qualitative Input from Your Team: Data can't tell you everything. Your sales reps are on the front lines. They know which deals feel solid and which ones are a long shot, regardless of what the CRM stage says. Their input adds a crucial layer of context.
• External Factors: Consider what's happening outside your business. Think about seasonality (e.g., higher sales during the holidays), upcoming marketing campaigns, economic trends, or changes in the competitive landscape.

Organize this data in a clean Excel table. A simple structure works best. For historical data, you'll want columns like Month and Sales Revenue. For pipeline data, have columns for Deal Name, Deal Value, Sales Stage, Probability, and Expected Close Month.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Sales Forecasting Methods in Excel

There are several ways to model a forecast in Excel, ranging from simple to more complex. The best approach depends on your business model and the data you have available. Here are three popular and effective methods you can implement today.

Method 1: Moving Average Forecasting

A moving average smooths out random fluctuations in your sales data to show you the underlying trend. It's a great starting point if your sales are relatively stable without extreme seasonal swings.

How to build it in Excel:

1. Set up your spreadsheet with your historical data. Let's say Column A is "Month" (ex: Jan-23) and Column B is "Sales."
2. Decide on your time period. A 3-month moving average is common. To get the first average, go to the cell next to your third month of data (e.g., C4) and enter the formula:

=AVERAGE(B2:B4)

1. Drag this formula down the column. Each cell will now show the average of the sales from the previous three months. Your final calculation is your baseline forecast for the next period.

While simple, this method only looks backward and might not be sensitive enough if your business is growing quickly or has predictable busy/slow seasons.

Method 2: Linear Regression Model (Using FORECAST.LINEAR)

If your sales have shown a consistent growth trend over time, a linear regression forecast can be very effective. This method finds the "line of best fit" through your historical data points and projects it into the future. It sounds complicated, but Excel's FORECAST.LINEAR function does all the heavy lifting for you.

How to build it in Excel:

1. Organize your data with three columns: "Period" (1, 2, 3, etc.), "Month," and "Sales." The numerical "Period" column is what we'll use for the function.
2. Let’s say you have 12 months of data in cells A2:C13. You want to forecast the 13th period.
3. In the cell for your forecast (e.g., C14), enter the following formula:

=FORECAST.LINEAR(A14, B$2:B$13, $A$2:$A$13)

Where:

• A14 is the future period you want to forecast (13 in this case).
• B$2:B$13 is your range of known sales numbers.
• $A$2:$A$13 is your range of historical time periods.

Excel will project the sales for period 13 based on the historical trend. You can drag this formula down to forecast several future periods.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 3: Forecasting with Seasonality (Using FORECAST.ETS)

Many businesses have seasonal cycles - think retail during the holidays, landscaping in the spring, or B2B software sales at the end of a quarter. The FORECAST.ETS function in Excel is specifically designed to handle this. It considers both trend and seasonality, making it one of the most accurate built-in forecasting tools.

How to build it in Excel:

1. Your data must be structured with consistent time intervals. Let’s use Column A for "Date" (e.g., 1/1/23, 2/1/23, 3/1/23) and Column B for "Sales."
2. Let's say your historical data runs to row 25 (2 years of monthly data). To forecast the next month, in cell B26, use this formula:

=FORECAST.ETS(A26, $B$2:$B$25, $A$2:$A$25, 12, 1)

Where:

• A26 is the target date for your forecast.
• $B$2:$B$25 is your range of historical sales values.
• $A$2:$A$25 is your timeline of dates.
• 12 tells Excel there's a 12-month seasonal cycle.
• 1 tells Excel to automatically fill in missing data points.

This will give you a forecast that respects the seasonal patterns present in your historical data.

Building a Practical Sales Pipeline Forecast

Historical methods are great, but what about the deals you're actively working on right now? A pipeline forecast is a bottom-up approach based on the nitty-gritty of your current deals. It's especially useful for B2B sales teams.

The core concept is to weight each deal's value by its likelihood to close. For example, a $10,000 deal in a late "Proposal Sent" stage (60% probability) adds $6,000 to your forecast.

How to build it in Excel:

1. Export Your CRM Data: Pull a report of all open opportunities from your CRM and get it into an Excel table. You'll need columns for Deal Name, Deal Value, and Sales Stage.
2. Assign Probabilities: If your CRM doesn't automatically assign a close probability to each stage, create a small reference table in your sheet (e.g., Prospecting = 10%, Qualified = 30%, Proposal Sent = 60%, Negotiation = 80%).
3. Calculate the Weighted Value:
4. Sum It Up: Your forecast is the sum of the Forecasted Value column. You can use SUM or SUMIFS functions to organize further, e.g., by expected close month or sales rep.

Many companies combine a top-down historical method (like FORECAST.ETS) with this bottom-up pipeline forecast for increased accuracy.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Tips for Better Accuracy

Building the model is only half the battle. Maintaining and improving its accuracy is an ongoing process.

• Don't Run on Autopilot: Revisit and update your forecast regularly, at least monthly. As new sales data comes in, your forecast model will become more accurate.
• Track Forecast vs. Actual: Add a column to track your actual sales results next to your forecasted numbers. Calculating the variance ((Actual - Forecast) / Actual) will show you where your model is weak so you can refine your assumptions.
• Use Multiple Models: Don't rely on just one method. Compare your pipeline forecast against a linear regression. If they tell vastly different stories, it's a sign you need to dig deeper into your assumptions.
• Talk to Your Team: Your sales team's gut feeling is a valuable data point. A weekly "deal review" where reps give their honest assessment of top opportunities can help you adjust probabilities and produce a more realistic forecast.

Final Thoughts

Building a sales forecast in Excel doesn't need to be intimidating. By gathering clean data and using simple functions like MOVING AVERAGE, FORECAST.LINEAR, or the more powerful FORECAST.ETS, you can move from guesswork to a data-driven strategy for your business. The process gives you a clear vision of the future, empowering you to make smarter decisions about everything from hiring to inventory.

While Excel is incredibly versatile, the process of constantly exporting data from your sales CRM, marketing platforms, and payment systems can become a time-consuming manual task. We built Graphed to remove that friction completely. You can connect sources like Salesforce, Shopify, and Google Analytics in a few clicks, then create and update live sales dashboards and forecasts automatically using simple prompts. Instead of spending hours wrangling CSVs, you get back time to focus on what the numbers mean and how to grow your business.