Consulting | Excel Examples

Forecasting Techniques

Keywords: Forecasting, Trend line, Linear Regression, Statistics, Sales, Time Series

Description: Example of applying Excel's forecasting features to the process of forecasting sales. This method provides the user with a method of eliminating seasonal trend and the R^2 statistic to compare the fit of various shape curves to the adjusted data.

Tip: You can use Excel's built-in forecasting features to create a sales forecast. Use the Average formula and use moving averages to estimate seasonal indices.

Adjust the actual data with the seasonal indices and plot the resulting data. Use Excel's trendline feature to add a best fit line to the chart. This workbook contains an example of a linear and polynomial sales trend. You can compare trendline R^2 values to find the best fit. R^2 is percent of variation explained by trendline -- Higher R^2 values are best -- 1=perfect fit.

After finding the best fit, use the trendline formula and seasonal indices to forecast future periods.

Guide to the Example:
1) Enter Sales in column B of Input Data Sheet
2) Recreate trendline on chart in Forecast sheet - Activate chart and click on Insert Trendline button. Choose best fitting trendline (linear, ploynomial, etc.) by comparing R^2 value.
3) Replace formula in cell E38 with formula from trendline and copy down column E -- see examples.

Download File: Click Here
Tip: Click on the link above and choose "Save" to download the xls workbook to your hard drive or "Open" to open the workbook inside of your browser.

Last modified: May 21, 1996