How to Create a Demand Forecast in Excel

Building a demand forecast can feel like a daunting task, but you already have a powerful tool to get it done: Microsoft Excel. Forget complex statistical software, with just your historical sales data and a few simple formulas, you can create a baseline forecast to guide your inventory, staffing, and marketing decisions. This guide will walk you through a few straightforward methods to start forecasting demand right in your spreadsheet.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is Demand Forecasting?

Demand forecasting is the process of using historical data to predict future customer demand for a product or service. Getting it right helps you make smarter business decisions. When you know roughly how much product you'll sell, you can avoid common pitfalls like overstocking and tying up cash in inventory, or understocking and missing out on sales because you ran out of a popular item.

An accurate forecast directly impacts:

• Inventory Management: Ordering the right amount of stock at the right time.
• Financial Planning: Creating realistic budgets and revenue projections.
• Resource Allocation: Making sure you have enough staff and materials to meet upcoming demand.
• Marketing Strategy: Planning promotions and campaigns around predicted peaks and lulls in sales.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 1: Gather and Prepare Your Data

The quality of your forecast depends entirely on the quality of your data. Before you can predict the future, you need a clear record of the past. For most businesses, this means gathering historical sales numbers.

Start by opening a new Excel sheet and creating two simple columns: one for the time period (e.g., Day, Week, or Month) and one for the quantity sold (e.g., Units Sold or Revenue).

Your data should look something like this:

Before moving on, take a few minutes to clean your data. This is a critical step that many people skip.

• Check for Consistency: Ensure your time periods are consistent. If you're forecasting by month, make sure every row represents a single month.
• Handle Missing Values: Did you have a data-entry hiccup and miss a month? If so, decide how to handle it. You could enter a zero, but a better approach might be to calculate the average of the months before and after the missing period to avoid creating an artificial dip.
• Remove Major Outliers (with caution): Did a one-time bulk order from a corporate client dramatically skew one month's sales? You might consider excluding that data point if it's not representative of typical consumer demand and isn't expected to happen again. Just be careful not to remove data that represents genuine fluctuations.

Step 2: Choose Your Forecasting Method in Excel

Excel offers several ways to forecast data, from simple averages to more complex statistical functions. We’ll cover three accessible methods you can use right away, starting with the simplest.

Method 1: The Moving Average

The moving average technique smooths out short-term fluctuations in your data to help you identify the underlying trend. It works by calculating the average of sales over a specific number of recent periods (e.g., the last three months). This is an excellent starting point if your sales are relatively stable without strong trends or seasonality.

How to Calculate It:

1. In your sales data spreadsheet, click on the cell next to your third data point (if you're doing a 3-month average). For our example, this would be cell C4.
2. Type the formula to average the first three months of sales. If your "Units Sold" are in column B, the formula would be:
3. Press Enter. Excel will calculate the average of the first three months.
4. Click back on the cell with the formula, grab the small square in the bottom-right corner (the fill handle), and drag it down to the last period with data. This automatically applies the moving average formula to the rest of your data.

To forecast the next period, simply extend the formula one more cell down. It will average the last three known data points to predict the upcoming month.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Method 2: Exponential Smoothing

Like the moving average, exponential smoothing also uses past data to forecast the future, but it gives more weight to the most recent data points. The idea is that more recent sales are often a better predictor of the immediate future than sales from a year ago. This method is best for data that doesn't have a clear trend or seasonal pattern.

While you can create a manual exponential smoothing formula, Excel has a built-in tool that makes it much easier. You just need to enable the Data Analysis ToolPak.

How to Enable the ToolPak:

1. Go to File > Options > Add-ins.
2. At the bottom of the window, you'll see a drop-down menu for "Manage." Select "Excel Add-ins" and click "Go."
3. In the pop-up box, check the "Analysis ToolPak" option and click "OK." You'll now have a "Data Analysis" button on your Data tab.

How to Use Exponential Smoothing:

1. Click the "Data Analysis" button on the Data tab.
2. Select "Exponential Smoothing" from the list and click "OK."
3. In the dialog box, configure the following:
4. Click "OK." Excel will automatically generate the forecasted values for you.

Method 3: Forecasting with Linear Regression (FORECAST.LINEAR)

If your sales data shows a clear trend (steadily increasing or decreasing over time), linear regression is your best bet. This method finds the best-fit straight line through your historical data and extends that line into the future to make a prediction.

Excel’s FORECAST.LINEAR function does all the heavy lifting for you.

How to Use It:

The function’s syntax looks like this: FORECAST.LINEAR(x, known_y's, known_x's)

• x: The future time period you want to forecast (e.g., month 13).
• known_y's: Your historical sales data (the "Units Sold" column).
• known_x's: Your historical time periods (the "Month" column).

Here’s how to apply it:

1. First, make sure your time periods are numerical (e.g., 1, 2, 3 instead of Jan, Feb, Mar).
2. Click the cell where you want your first forecast to appear (e.g., next to Month 13).
3. Enter the formula, using absolute references (the $ signs) for the historical data ranges so they don't shift when you drag the formula down. If we're forecasting for Month 13 (in cell A14), the formula would be:
4. Press Enter. Excel will calculate the forecasted sales for Month 13 based on the historical trend. You can drag this formula down to forecast for months 14, 15, and so on.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Visualize Your Forecast

Numbers in a spreadsheet are useful, but a visual chart makes your forecast much easier to understand. A simple line chart allows you to compare your historical performance with the forecasted trend at a glance.

How to Create a Forecast Chart:

1. Select all your data, including the headers: your time periods, historical sales, and your new forecast column.
2. Go to the Insert tab in Excel.
3. Click on the Line Chart icon and select "2-D Line."

Excel will instantly generate a chart that plots your actual sales against your forecasted numbers. This visualization helps you see how well the forecast matches the past trend and what to expect in the future if that trend continues.

Final Thoughts

Creating a demand forecast in Excel doesn't require a degree in statistics. By organizing your sales data and using built-in tools like moving averages or the FORECAST.LINEAR function, you can build a reliable model that gives you valuable insight for business planning. Remember, no forecast is perfect, the goal is to make a more informed guess that you can continuously refine over time.

Once you're comfortable with basic forecasting, the process can still become time-consuming, especially when you need to pull data from multiple sources like Shopify, Google Analytics, and your advertising platforms. With Graphed, we automate the hard parts. We connect to all your data sources so you can instantly turn raw numbers into live, updating dashboards and insights. Instead of manually exporting CSVs and updating spreadsheets, you can just ask a question like "Forecast my sales for the next quarter" and get a real-time answer in seconds.