Forecast Sheet
Excel’s Forecast Sheet feature not only creates forecasted values that account for seasonality, but it also can provide confidence boundaries around the forecasted values and create forecasted values for multiple future periods. Using the data shown below begin by clicking in the data you wish to use as the foundation for your forecast sheet. The data requires a time line with a consistent interval and corresponding values. Up to 30% of intervals can be missing and the Forecast Sheet will still be accurate.
Next, click Forecast Sheet on the Data tab of the Ribbon. In the ensuing dialog box (shown below), make any adjustments necessary for the time periods, confidence level, etc. and click Create to build the Forecast Sheet.
Clicking Create results in a new sheet being created to the left of the data sheet with a copy of the data, the results of the forecast, and a chart of the forecast. This is a copy of the data and any changes to the data will not be reflected in the forecast. To make any changes to your selections in the dialogue above you will have to edit the formulas in the forecast sheet or run the Forecast Sheet command again.
The FORECAST.ETS Function
The Forecast Sheet results above use the FORECAST.ETS as shown in the screen shot above. This function is an improvement over the FORECAST function which provided only linear forecasting capabilities. With FORECAST.ETS, you can now generate forecasts with exponential smoothing. The primary benefit to using the FORECAST.ETS function is that its exponential smoothing capacity allows you to create forecasts that account for seasonality in your data. To illustrate, consider the data below (with multiple rows hidden for presentation purposes). In it, a formula that uses the FORECAST.ETS function is computing forecasted sales for January 2021. Notably, because this formula employs the FORECAST.ETS function instead of the FORECAST function, it accounts for seasonality in the data.
| forecast_sheet_and_forecast.ets.xlsx |
RSS Feed