Seasonality Forecasting with Time Series Decomposition
Originally published: 28/06/2020 20:40
Publication number: ELQ-89022-1
View all versions & Certificate

Seasonality Forecasting with Time Series Decomposition

Data forecasting with analysis of periodic variation in predicted data projections.

The seasonal forecasting Excel template uses time series decomposition to identify replicating variations within automatically calculated common period types. Data periodicity is identified as either daily, weekly, monthly or annual by analyzing the different between time periods. Common periods are then compared within the historical input time series to quantify average variation for each common period in relation to others. These variations are used to make desired projection while embedding the periodic risk volatilities. This approach is applicable to seasonal forecasting as well as to determine potential risk with forward looking financial models.

Forecasts can be easily changed with different parameters and visualized before numerical forecast data is extracted for modelling purposes.

A threshold variable can be used to control outliers whereby abnormal observations are removed from the analysis based on the magnitude cutoff defined. The base forecast method for which variation is applied on can be defined as either linear, 2nd or 3rd order polynomial or exponential. The VBA code is open and freely available for modification to specific modeling purposes. The code includes generic functions for the time series decomposition routine as well as a helper function for defining coefficients and intercept values for the base forecast method options. This function can be used independently. A user defined function is also available for creating seasonal forecasts via an array formula directly within Excel.

This Best Practice includes
1 Excel file

Business Spreadsheets offers you this Best Practice for free!

download for free

Add to bookmarks


Further information

Create forecasts with identified seasonality or risk variation within projections.

Financial data projections where repetitive variation such as seasonality is inherent.

Forecasting data where there is no discernible repetitive variation.

4.5 / 5 (4 votes)

please wait...