Table of Contents
Creating a sales forecast in Excel is a simple process that requires the user to first enter their data into an Excel spreadsheet. The user then has to create formulas that will calculate the forecasted data. The user should also create a chart that will best graphically represent the forecasted data. Finally, the user should save the spreadsheet. Following these steps will allow the user to create an accurate and useful sales forecast in Excel.
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:
- 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.