How to Forecast Sales in Google Sheets

Building a reliable sales forecast is one of the most powerful things you can do for your business, but it often feels like you need a data science degree to get started. Thankfully, that's not the case. You can create a surprisingly accurate and useful sales forecast using a tool you already have: Google Sheets. This article will walk you through several methods, from simple to more advanced, to help you predict future sales and make smarter business decisions.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Why Bother with a Sales Forecast?

Before jumping into the formulas, let’s quickly touch on why this is worth your time. A solid sales forecast helps you:

• Manage Cash Flow: Know when money is coming in so you can plan expenses.
• Allocate Resources: Make informed decisions about hiring, inventory management, and marketing spend.
• Set Realistic Goals: Create data-backed targets for your sales team instead of just picking a number that sounds good.
• Track Performance: Compare your actual sales against your forecast to see if you're on track and adjust your strategy if you're not.

In short, it transforms your planning process from guesswork into a strategic exercise.

Step 1: Gather and Prepare Your Historical Sales Data

No forecasting model can work without good data. The old saying "garbage in, garbage out" is especially true here. Before you can look forward, you need to look back.

What Data Do You Need?

At a minimum, you need a record of your historical sales over a consistent time period. This could be daily, weekly, or monthly. Monthly data is often the best starting point for strategic forecasting as it smooths out tiny daily fluctuations.

Ideally, you should have at least 12-24 months of data. The more history you have, the more reliable your forecast will be, as it can better identify trends and seasonal patterns.

Graphed

Still Building Reports Manually?

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

Watch the Demo

How to Structure Your Data

Open a new Google Sheet and create a clean, simple table. Your data needs to be organized logically for the formulas to work. A simple two-column setup is perfect:

• Column A: Date (e.g., Month, Week Ending)
• Column B: Sales (e.g., Revenue, Units Sold)

Here are some crucial tips for data preparation:

• Be Consistent: Make sure your time intervals are uniform. Don't mix weekly and monthly data. If you use month-end dates, use them for every entry (e.g., 1/31/2023, 2/28/2023, 3/31/2023).
• Keep it Clean: The 'Sales' column should contain only numbers. Remove any currency symbols, commas, or text notes, as these will cause errors in your formulas. You can use Google Sheets' formatting options to display the currency symbol without altering the underlying numerical value.
• Fill in Gaps: If you had a month with zero sales, enter a '0'. Don’t leave the cell blank. Blank cells can break your calculations.

Your prepared data should look something like this:

Method 1: The Simple Moving Average

A moving average forecast is one of the simplest methods and works well for businesses without strong seasonal trends. The idea is to average the sales of the last few periods to predict the next one. This helps smooth out random highs and lows to identify the underlying trend.

How to Calculate a 3-Month Moving Average

Let's use a 3-month moving average as an example. This means your forecast for April will be the average of sales from January, February, and March.

1. Add a new column next to your Sales column and label it "3-Month Moving Average Forecast."
2. In the cell next to your fourth data point (e.g., C5, if your data starts in row 2), enter the following formula. This will average the sales from the previous three months (B2, B3, and B4).

=AVERAGE(B2:B4)

1. Click the small blue square (the fill handle) in the bottom-right corner of the cell and drag it down to the end of your data. This automatically copies the formula, adjusting the cell references for each row.

The number in the "Forecast" column is your prediction for that month, based on the performance of the three prior months. To forecast the next period beyond your historical data, you'd just take the average of the last three actual sales figures.

Pros: Very easy to implement and understand. Cons: Lags behind trends. If your sales are growing rapidly, a moving average forecast will always be too low. It also doesn't account for seasonality.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Linear Trend Forecasting

If your sales are generally trending up or down over time, a linear trend forecast is a better choice. This method finds the "line of best fit" through your historical data and extends it into the future. It’s more sophisticated than a moving average but still straightforward to implement in Google Sheets using the FORECAST function.

Step-by-Step Guide to Using the FORECAST Function

1. Create a "Period" Column: The FORECAST function needs a numerical x-axis. First, insert a new column between 'Date' and 'Sales'. Call it "Period" (or "Time Period"). Number your periods sequentially starting from 1. This turns your dates into a simple number line that the formula can understand.
2. Add a "Forecast" Column: Create a fourth column and name it "Linear Forecast." This is where your results will go.
3. Enter the FORECAST Formula: Let's say your "Period" numbers are in column B and your "Sales" data is in column C. To forecast the sales for the next period (Period 13 in our example), click on an empty cell and enter:

=FORECAST(B14, C$2:C$13, B$2:B$13)

Let's break down this formula:

• B14: This is the 'x' value, or the future time period you want to forecast (in this case, Period 13).
• C$2:C$13: This is the range of your known_data_y, which is your historical sales data. We use dollar signs ($) to lock the range, so it doesn't shift when we drag the formula.
• B$2:B$13: This is the range of your known_data_x, your historical time periods (1-12). We also lock this range.

You can extend this by adding more future periods (14, 15, 16) in your Period column and dragging the formula down. The function will project sales for each of those future periods.

Step 3: Visualize Your Sales Forecast

Numbers in a spreadsheet are useful, but a visual chart makes your forecast much easier for you and your team to understand. A line chart is perfect for comparing your historical data against your forecast.

How to Create a Forecast Chart

1. Select Your Data: Highlight all three columns: Date, Actual Sales, and your Forecasted Sales.
2. Insert the Chart: Go to the menu and click Insert > Chart.
3. Choose the Right Chart Type: Google Sheets will likely suggest a line chart, which is what you want. You should now see two lines: one for your actual sales history and another showing the projected trend line extending into the future.
4. Customize for Clarity: Click on the chart to open the Chart editor on the right. Give your chart a clear title like "Monthly Sales Forecast." Ensure your axes are labeled correctly. You might also want to change the color of the forecast line to distinguish it from historical data.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Adjusting for Seasonality (a Simplified Approach)

What if your business is seasonal? For example, a pool supply company sells far more in summer than in winter. A simple linear forecast will miss these predictable peaks and valleys.

While full-blown seasonal forecasting models (like Holt-Winters exponential smoothing) can get very complex, you can create a simplified adjustment. The basic idea is to:

1. Calculate a Baseline: Get an initial forecast using a simple method like the linear trend forecast we just covered. This gives you your general growth trend.
2. Find Your Seasonal Index: Look at your historical data to see how each month typically performs relative to the annual average. For instance, you might find that December sales are consistently 150% of the monthly average, while February sales are only 70%. This percentage is your "seasonal index."
3. Apply the Index: Multiply your baseline forecast for each month by its corresponding seasonal index. This adjusts the straight-line forecast up or down to reflect expected seasonal patterns.

This approach gives you a much more nuanced projection by layering seasonal patterns on top of your overall growth trend.

Final Thoughts

Building a sales forecast in Google Sheets turns raw historical data into a strategic roadmap for the future. By prepping your data, choosing a method like moving averages or the FORECAST function, and visualizing the result, you can make more proactive, data-driven decisions that guide your business growth.

While Google Sheets is an excellent starting point, the process of manually exporting data from platforms like Shopify or Salesforce, cleaning it, and updating your formulas can become a recurring task. At Graphed, we automate this entire process. Connect your data sources in a few clicks, and our AI can instantly build real-time sales dashboards and forecasts for you — just by asking in plain English. Instead of spending hours in spreadsheets, you get live, interactive visualizations that are always up-to-date, allowing you to focus on acting on the insights, not just finding them.