Statistics for everyone: Excel and Statistics(14), Exponential smoothing

Excel and Statistics, everybody should know. : Contents

Exponential smoothing

Call ‘Exponential smoothing’.

You will see a menu that is almost similar to the ‘moving average’ that we learned earlier. In the same way, Specify ‘Iutput Range’ and ‘Output Range’.

The resulting chart looks almost the same, but the calculations are very different.

Predicted value = Actual value * 0.7 + Previous predicted value * 0.3

In the ‘moving average method’, the forecast value is not affected by the previous predicted value, but only the actual value, but in the exponential smoothing method, the predicted value is affected by the previous predicted value, and the first actual value is affected until the last, but gradually the impact becomes ‘exponentially smaller’ as time passes.

That’s why it’s called ‘exponential smoothing’.

In this way, the actual value will have an effect of 0.7, and the previous actual value will have an effect by an exponential factor of 0.3, gradually having less impact. If you don’t understand well, you don’t have to.

These ‘moving averages’ and ‘exponential averages’ have somewhat similar purposes, but the menu composition has not been taken into account at all, and I don’t understand why the other menus actually did this. Perhaps it was not upgraded because there was no user demand after the program was configured. There are a lot of pretty useful features, but users will reflect it if they use it well and ask for it consistently.

Plus Alpha

Open ‘Prediction.xlsxŒ’, hover over your data, and click ‘Forecast sheet‘. Excel selects the surrounding data together. If there are blanks in the data, there may be an error in the selection, so you can select the range in advance and click on the ‘Forecast sheet’. There are a few optionsŽ, but for now, leave them as they are, and click Create. You see the graph in preview.

Another new sheet is created. Columns A and B remain the same, and three additional columns are created to form a graph.

GOING DOWNWARDS, FOR FORECASTING, the FORECAST.ETS function is used.

For confidence intervals of forecasts, as you can see, FORECAST.ETS.CONFINT function is used.

If you look at the FORECAST.ETS function, it says that the ‘exponential smoothing method’ was used. In other words, it will be a more applied method of the ‘exponential smoothing method’ that we learned earlier.

‘Forecast End’ makes intuitive sense, the rest of the options require little modification, and all of them are the arguments of the FORECAST.ETS function. If you have prior information about ‘seasonality’, it seems that you can have the option of ‘setting manually‘’.

If you enable ‘Include Forecast Statistics’’, the results will show you a few additional information, but I don’t think we need to know more.

These are results of the FORECAST.ETS.STAT function.

If you open this file in excel 2010, this function is named as _xlfn since it doesn’t exist in 2010. Although, the chart created is readable.

Excel and Statistics, everybody should know. : Contents

=================================================