Introduction
The following post contains a basic template for forecasting of revenue based on time series. The actual data inside the file can indeed be adjusted by the user based on their needs, including the periods, however, the formulas may need to be adjusted.
Basic methods included
I. Simple Average Method
Simple forecasting method using the average of the past values as an expectation for the projections. The data should not exhibit any trends, seasonality, or other systemic patterns.
II. Moving Average Method
Simple forecasting method using the average of the past X values (e.g., past 4 quarters) as an expectation for the projections. The data should not exhibit any trends, seasonality, or other systemic patterns. The averaged period should be adjusted to better fit the actual data to improve predictive quality, i.e., minimize the average squared error.
III. Trend Fitting Method
Simple forecasting method based linear regression. Excel has a built in function to display the line of best fit including the formula.
IV. Holt’s Exponential Smoothing
This method uses a weighted average of past data, where the weights decrease exponentially for older observations (based on Simple Exponential Smoothing) and also captures trends in the data, adjusting the forecast by considering both the recent level and the trend of the series. After updating the data, it is necessary to re-run the Solver add-in.
V. Winters Exponential Smoothing
This method extends Holt’s Exponential Smoothing by also considering seasonal impacts; it adjusts the forecast based on the level, trend, and seasonality of the data series. After updating the data, it is necessary to re-run the Solver add-in.
Attachment
Conclusion
This basic template can be used for forecasting in Excel. However this template is not intended to replace more rigorous forecasting methods used by the professionals.