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:
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:
The blue bars represent the historical sales values and the orange bars represent the forecasted sales values.
Note that the confidence interval for each sales forecast will be represented with vertical error bars along the top of each of the orange bars.
The following tutorials explain how to perform other common operations in Excel:
How to Calculate an Exponential Moving Average in Excel
How to Find Weighted Moving Averages in Excel
How to Calculate a Cumulative Average in Excel