How to Create a Sales Forecast in Excel (Step-by-Step)

The following step-by-step example shows how to create a sales forecast in Excel.

Step 1: Create the Data

First, let’s create a dataset that shows the total sales made by some company during 18 consecutive months:

Note: In order to use the forecast functionality in the next step, make sure that each of your dates are at evenly spaced intervals. For example, the dates in the dataset above are each one month apart.

Step 2: Create a Forecast

Next, highlight the cells in the range A1:B19 and then click the Data tab along the top ribbon and click Forecast Sheet within the Forecast group:

In the new window that appears, click the Options dropdown arrow near the bottom to specify the Forecast Start and Forecast End dates, then click Create to automatically create a forecast:

A forecast will automatically be generated for the future dates that you specified, along with 95% confidence interval limits:

Here’s how to interpret the output:

The forecasted sales for 7/1/2021 are 172.518 and the 95% confidence interval for this forecast is [159.9, 185.14].
The forecasted sales for 8/1/2021 are 179.12 and the 95% confidence interval for this forecast is [162.14, 196.11].

And so on.

A line chart with the forecasted values is also produced automatically:

forecast sales in Excel

Here’s how to interpret the lines:

The dark blue line represents the historical sales values.
The dark orange line represents the forecasted sales values.
The light orange lines represent the 95% confidence limits for the forecasted sales values.

Step 3: Modify the Forecast (Optional)

When creating the forecast, you can choose to display the results in a bar graph instead of a line graph by clicking the bars icon in the top right corner: