Components of a Decomposition Forecast of Stock Prices with Excel

Abstract

This paper presents a decomposition forecast of stock prices using time series of weekly stock price data as implemented in Excel. The following decomposition components are presented, analyzed, and interpreted including a moving average, a trend, a periodic function, and two shock variables including a triangular shock variable and a level change. The results of the individual components are compared and a discussion of each component’s efficiency is provided. The trend component is statistically significant over the forecast time. The moving average component displays a bi-modal error distribution over varying spans of the moving average and forecast periods. The first mode coincides with random walk behavior with an optimal span and forecast period of one. The second mode is more interesting and applicable for investing beyond the short-term with an optimal spans and forecast periods beyond 75 weeks. The periodic sine function well captures the typical U.S. business cycle of 4–5 years and significantly improves model performance. Finally, the significant outliers remaining from the decomposition are diagnosed and modeled with a triangular shock variable for the bust and recovery associated with the 2008 financial crisis. The model presented does a good job of decomposing the analytical components in forecasting stock prices and provides a useful illustration of Excel methods.

Citation

Keller, C.M. (2014), "Components of a Decomposition Forecast of Stock Prices with Excel", Advances in Business and Management Forecasting (Advances in Business and Management Forecasting, Vol. 10), Emerald Group Publishing Limited, Leeds, pp. 3-17. https://doi.org/10.1108/S1477-407020140000010023