How to forecast in Excel: linear and non-linear forecasting methods

Arranging data

In your Excel worksheet, enter two data series into adjacent columns:

• Time series - date or time entries that are observed sequentially at a regular interval like hourly, daily, monthly, yearly, etc.
• Data values series - corresponding numeric values that will be predicted for future dates.

It is important that your time series have equal intervals between the data points. For example, you can supply weekly intervals with values on every Monday, monthly intervals with values on the 1st day of every month, etc.

In theory, your time series can miss up to 30% of data points or have several entries with the same date or time, and Excel will still do the forecast correctly. However, it is recommended to summarize your data to get more accurate predictions.

In this example, we will try to forecast sales for the next few years based on the following historical data. Please pay attention that column A contains dates (the 1st of every month) in a custom format that displays only the month and year. However, these are fully-functional dates, not text values.

Creating a forecast sheet

With the two data series in place, carry out the following steps to build a forecasting model:

1. Select both data series. In most cases, it is sufficient to select just one cell in any of your series, and Excel picks up the rest of the data automatically.
2. Go to the Data tab > Forecast group and click the Forecast Sheet button.
   
3. The Create Forecast Worksheet window shows a forecast preview and asks you to choose:
   • Graph type: line (default) or column chart
   • End date for forecasting
4. When done, click the Create button.
   

Excel immediately creates a new sheet containing a table with your original and predicted values as well as a chart that visually represents this data.

Here's how our Excel sales forecast looks like:

To find out how the predictions are calculated, click on one of the future values in your forecast worksheet and investigate the formula. Or check out Exponential smoothing forecast formulas for a quick overview.

Customizing Excel forecast

If you'd like to change any of the default options of your forecast, click Options in the lower-left part of the Create Forecast Worksheet window and configure the following settings the way you need:

Forecast Start - the start date for forecasting. You can either select a date from the date picker or type it directly in the box.

• If your data is seasonal, it is recommended to start a forecast before the last historical point.
• To see how well the predictions match the known values, pick a date before the end of the historical data. In this case, only data prior to the start date will be used for forecasting (this back-testing method is also known as hindcasting).

Confidence Interval - a range in which the predictions are expected to fall. On the line chart, it is represented by the two finer lines on each side of the forecast line; on the column chart - by the error bar values.

Confidence interval can help you understand the forecast accuracy. A smaller interval indicates more confidence for a specific point. The default level is 95%, meaning that 95% of future points are expected to fall within the range.

You can check and uncheck the Confidence Interval box to show or hide it. And you can change the default value by using the up or down arrows.

Seasonality - the length of the seasonal pattern in which regular and predictable data fluctuations occur. For example, in a yearly pattern where each data point represents a month, the seasonality is 12.

Excel identifies the seasonal cycle automatically but also allows you to set it manually. When Excel is unable to detect seasonality (usually, with less than 2 cycles of historical data), the predictions revert to a linear trend.

Include Forecast Statistics - additional statistical information on the forecast. Check this box if you want Excel to generate a table of additional statistics such as smoothing constants (Alpha, Beta, Gamma) and error metrics (MASE, SMAPE, MAE, RMSE). All these values are calculated by using the FORECASE.ETS.STAT function.

Timeline Range - the range used for your timeline series. By default, it includes all dates in your source table, but you can change it here.

Values Range - the range used for your value series. It should match the Timeline Range.

Fill Missing Points Using - controls how missing points are handled. By default, Excel uses the Interpolation approach where the missing points are filled based on the weighted average of neighboring points. Alternatively, you can select Zeros to treat the missing points as zero values.

Duplicate Aggregates Using - determines how multiple values with the same timestamp are calculated. The default option is the average, but you can pick any other calculation method from the list, e.g. Median, Max or Min.

Forecasted values (FORECAST.ETS function)

The future values are calculated with the FORECAST.ETS function, which has the following syntax:

For the detailed explanation of each argument, please check out Using FORECAST.ETS function in Excel.

For our sample forecast sheet, Excel has created this formula:

=FORECAST.ETS(A31, $B$2:$B$30, $A$2:$A$30, 1, 1)

Where:

• A31 is the target date
• $B$2:$B$30 is the data values range
• $A$2:$A$30 is the time series range
• 1 in the 4^th argument (seasonality) - tells Excel to detect seasonality automatically.
• 1 in the 5^th argument (data completion) - tells Excel to complete missing points as the average of the neighboring points.
• The 6^th argument (aggregation) is omitted, which means that multiple values with the same time stamp are to be aggregated using AVERAGE.

Confidence interval (FORECAST.ETS.CONFINT function)

To return a confidence interval for the forecast value at a specified date, the FORECAST.ETS.CONFINT function is used.

For our sample data set, the confidence interval can be calculated with this formula:

=FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)

Where:

• A31 is the target date
• $B$2:$B$30 is the data values range
• $A$2:$A$30 is the time series range
• 0.95 - the confidence level is equal to 95%.
• 1 in the 5^th argument (seasonality) - automatic detection of seasonality.
• 1 in the 6^th argument (data completion) - missing points are completed based on the average of the neighboring points.
• 7^th argument omitted (aggregation) - aggregate multiple data values with the same time stamp by using the AVERAGE function.

In the automatically created Forecast Sheet, Excel does not output the confidence interval value. Instead, it uses the FORECAST.ETS.CONFINT function in combination with the forecast value to calculate the Confidence Bounds, provided the Confidence Interval box is checked in the Options section.

To get the lower bound, you subtract the confidence interval from the forecasted value:

=C31 - FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)

To get the upper bound, you add the confidence interval to the forecasted value:

=C31 + FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)

Where C31 is the forecasted value returned by FORECAST.ETS.