Excel can use past data to project possible future results with the Forecast feature. It’s a very simple version of statistical ‘modelling’.
The Forecast function will let you predict future values and data trends, such as consumer trends, future sales and or inventory needs, based on seasonality.
Here’s a simple guide on how to use the Forecast feature. Although the data below looks fairly complicated, Excel makes it pretty easy to create in just a few clicks.
How to use Forecast Sheet
First, simply gather your historically time-based data in Excel, in this example we’ve put together a few random values and dates.
Next, use the mouse to select the data, then go to Data | Forecast | Forecast Sheet
This will bring up a preview of your chart.
From here, you can select between a line or column chart, and adjust the Forecast End date.
Customise Options
By selecting the arrow next to Options, there are extra features you can use or adjust to customise your forecast.
Forecast Start
Choose the start date of the prediction. If the start date is before the end of historical data, the forecast uses the data only prior to that date.
Confidence Interval
This refers to a range of values so defined that there is a specified probability that the value of a parameter lies within it. It helps you figure out the accuracy of the prediction. The default is set to 95%, but you can adjust the percentage or remove the Confidence interval from the graph completely.
Seasonality
Seasonality is a measurement of how long (how many points) the seasonal pattern lasts. A yearly sales cycle, for example, with one point representing a month, has 12 points of seasonality. Setting Manually allows you to choose a number overriding the automatic detection.
It’s important to note when setting seasonality manually, to avoid a value less than 2. Excel won’t be able to identify the seasonal components when the seasonality isn’t high enough for the algorithm to detect… so it’ll revert to a linear trend.
Timeline Range
Here you can adjust your timeline range. You need to match this range with the Values Range.
Values Range
Here you can change your value range. You need to match this range with the Timeline Range.
Fill Missing Points Using
In order to fill in missing data points, Excel uses Interpolation, as long as less than 30 percent of the data points are missing, the missing point will be filled in by the weighted average of its neighbouring points. To treat the missing points as zeros instead, click Zeros in the list.
Aggregate Duplicates Using
Excel will average the values if your data has several entries with the same timestamp. Select the calculation you wish from the list to use another method, such as a median or count.
Include Forecast Statistics
You may check this box if you want additional forecast statistics to be included in a new worksheet. By doing so, a table of statistical information will appear based on the FORECAST.ETS.STAT function and includes smoothing coefficients measures (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE).
We like adding the Forecast Statistics because it gives you the chance to edit the modelling to account for events Excel can’t predict.
Formulas used in Forecast Sheet
If you choose to include Statistics, once you’ve created the Forecast Sheet, a table will appear including the following columns:
Date/Timeline: The historical time-based data series.
Historical Values Column: The list of corresponding value series
Forecast Values Column: Forecasted value series calculated using the formula =FORECAST.ETS
=FORECAST.ETS (target_date, values, timeline, [seasonality], [data_completion], [aggregation])
Confidence Interval Columns: If selected, two columns will represent the confidence interval. This is calculated using the formula =FORECAST.ETS.CONFINT
=FORECAST.ETS.CONFINT (target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])
Adjusting the Forecast
Once Excel has made its forecast, feel free to adjust it for things Excel can’t possibly know. For example, change future sales figures for known events (open/close stores, special sales etc). Manually tweak the values in the Forecast column. The confidence bound, stats and chart will all adjust accordingly, like this.
