To demonstrate Excel’s super-easy forecasting tool, we’ll use the last ten years of quarterly sales for Home Depot.
Home Depot’s fiscal year ends on January 31st.
I’ve included the quarter numbers to the left to make it easier for us to recognize the quarters, but we won’t be using those in the upcoming tools.
Create a Sales Forecast for Upcoming Quarters
Just like the title says, our task is to create a sales forecast for upcoming quarters.
When you receive a dataset like the one featured above, it’s a good idea to first plot it out on a line chart to get an idea of how the values increase and decrease over time.
Line charts are great when your driving grouping is based on the passage of time.
Highlighting the dates and sales values, create a quick line chart by selecting Insert (tab) -> Charts (group) -> Line Chart.
Notice the peaks and valleys. There is without a doubt some seasonality involved.
We can’t just plot a simple trendline to predict future quarters as a trendline wouldn’t consider the cyclicality of the historical data into consideration.
Featured Course
Power Excel Bundle
Enter the Excel Forecast Sheet Tool
The Forecast Sheet tool was introduced in Excel 2016 and resides in the Forecast group of the Data ribbon.
To use the Forecast Sheet tool, select the range of cells that contains the dates and historical sales, then click the Forecast Sheet button.
This presents us with a preview of the forecast output.
We can choose between a line chart or a column chart (upper right).
The blue lines/bars represent the ACTUAL values while the orange lines/bars represent the FORECAST values.
While the thicker orange line represents the FORECAST, the thinner orange lines represent the upper and lower confidence bounds.
In a bar chart rendition, the upper and lower confidence bounds are represented as Vertical Error Bars.
If your Confidence is set to 95% (this is Excel’s default setting), then 95% of your future data points should fall between the upper and lower bounds.
You can alter the forecasting end date by changing it in the Forecast End date picker (lower left).
Forecast Worksheet – Advanced Options
Clicking the Options button (lower left) will reveal a host of customization controls.
The following controls are commonly altered:
- Forecast Start – Sets the start date for forecast analysis. Setting this date before the last data point is a great way to check the accuracy of the tool as you can compare the forecast against actuals. This is referred to as “hindcasting”. Beware of setting this too early in the data, as fewer data points reduce the predictive accuracy of the tool. The more data you feed the tool, the greater the tools’ accuracy.
- Confidence Interval – The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). Confidence Interval can help you figure out the accuracy of the prediction. The greater the percentage, the more confidence in the prediction for the specific point.
- Seasonality – Seasonality is a number for the length (number of points) of the seasonal pattern. For example, in quarterly sales cycles, with each point representing a month, the seasonality is 4.
- Timeline Range – This is the range of cells that contain the date intervals.
- Values Range – This is the range of cells that contain the values.
- Fill Missing Points Using – If any gaps exist in the data, Excel uses interpolation. This means that missing points will be calculated as the weighted average of its neighboring points provided less than 30% of the data points are missing. If you wish to treat missing data points as 0 (zero), click Zeroes.
- Aggregate Duplicates Using – When your data contains multiple values with the same timestamp, Excel will average the values. You can select from a variety of other aggregation tools such as MEDIAN, COUNT, SUM, etc.
Featured Course
Excel Essentials for the Real World
Creating the Forecasting Sheet
Clicking CREATE in the Create Forecasting Worksheet dialog box produces a new sheet with two objects:
- A proper Excel Table containing the past and future dates, past sales, and future sales as forecast, lower, and upper confidence bounds.
- A chart that displays the table’s values in the previously described blue and orange lines/bars.
Adjusting the table of values will automatically adjust the lines/bars of the chart.
Examining the Underlying Forecasting Functions
Clicking any of the cells in the table that display forecasted values, we can see in the Formula Bar that Excel has created a series of formulas using the FORECAST.ETS function.
This function is used to predict future values by using an Exponential Triple Smoothing algorithm.
Without knowing very much at all about forecasting or statistics you were able to produce a seasonal forecast.
All you need is data with consistent steps between the data points. These steps can be years, quarters, months, days, etc.
The function can operate accurately when up to 30% of the data is missing.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
