Home > Forum Home > Managing Supply Chain and Inventory > Seasonality Forecasting with Time Series Decomposition
| Share |

Forum Topic | Post Reply Login |

## Seasonality Forecasting With Time Series Decomposition | Rate this: (4/5 from 2 votes) |

Forecasting time series data for inventory management or any other analysis often removes volatility that is inherent in the historical data. For simple analysis this can be acceptable to identify the linear trend for predicted values in the future. More specific predictions, however, often require the prediction of variances such as seasonality and other period specific nuances that can affect decision making. Such cases include cash flow and budget planning in business scenarios. Common period volatility can be identified within periodic data through time series decomposition which can then be applied to the forecast results to then predict with embedded risk dynamics. One solution within the built in Excel statistical functions is the FORECAST.ETS.SEASONALITY function; however little control is given on the algorithm applied to seasonality calculations. An alternative approach is to apply a VBA function that allows control for periodicity and outlier thresholds to replicate historical fluctuates that are likely to repeat in future periods within the forecast. To facilitate automating the periodic variation quantification, the function can detect the frequency of the time series data as either daily, weekly, monthly, quarterly or annual from the dates. This could also, of course be overridden with modification to the function variables. [Download this Excel seasonal forecasting solution] The following VBA function takes the following arguments: - arrData - A 2-dimensional array of dates and values for the historical data.
- per_for - The number of periods to forecast.
- thres - A threshold to ignore outliers that have a multiple of more than this amount compared to other same time category observations. A value of 3 would ignore any observations that have a magnitude of more than 3 times the average variation compared to other time category observations of the same periodicity. Use zero to ignore or a very low number such as 0.0001 to remove time decomposition from the forecast.
- fmeth - A string as either Linear, 2nd Poly, 3rd Poly or Exponential to define how the base forecast method is defined before applying seasonal variation.
VBA Code:
Coefficients and intercept values for the base forecast method are returned from a helper function which could also be used independently to classic forecasting of time series or non-time series data. VBA Code:
We can apply this function as a user defined function formula in Excel by selection the ranges of dates and values and specifying the number of periods to forecast and threshold multiplier. Note that the number of cells corresponding to the number of forecast periods need to be then selected and the formula entered as an array formula by using Control-Shift-Enter. The VBA for the user defined function is: VBA Code:
| |||||

Excel Business Forums Administrator | |||||

Posted by Excel Helper on |

There are currently no replies to the "Seasonality Forecasting with Time Series Decomposition" topic of the Excel Help Forum for Managing Supply Chain and Inventory.Post Reply |

Find relevant Excel templates and add-ins for Seasonality Forecasting with Time Series Decomposition in the Excel Business Solutions Directory |