When you go to the internet and google forecasting in excel, you get a whole bunch of discussion of how you can press a couple of buttons and get some forecasts. A lot of the explantions also tell you how you can use the FORECAST.ETS function and retrieve statistics such as alpha and beta and the root mean squared stastic. These explanations also tell you how you can get confidence intervals from the FORECAST.ETS.CONFINT function. One website called real statistics does walk you through how to make your own forecast (and I used this). I originally worked on this whole forecasting business because of a question from a person in one of my classes who wanted to know how the confidence intervals are calculated.

But here is the problem, the ETS.FORECAST is a black box. None of the websites tells you in detail how to replicate the forecast and the confidence intervals in detail. This is a big problem because the forecast is made from exponential smoothing which is a very simple process. When I have tried to make my own model in the worksheet file below I have not been able to replicate the forecast parameters in excel. Maybe this is because I am just stupid (probably). The forecasts for different GDP per capita growth look good, but I think there is something very odd with the excel reported parameters from the forecasts. First, the forecasts often have a clear trend but the trend parameter — the beta — reported in the ETS.FORECAST.STATISTICS is .001. I really may be missing something here so I must be careful about my statements. Maybe they have mismodelling the beta parameter, or the but I don’t think so. The really bad thing about this is that the beta parameter is by far the most important factor in making a forecast. Without the beta parameter the exponential smoothing forecast is a flat line.

The file below begins by demonstrating how the forecast works like all of the other websites (this is really no big deal). In seconds, you can make a time series forecast now in excel. You just select a series of data adjacent to a series of dates, and you get a series of forecasts along with a confidence interval. You can export the forecast to a separate sheet. The only question is what are the techniques that excel uses to do this. irst, just get some data. You can get the file for the economic variables and make forecasts for things like GDP per capita, population, life expectancy etc. The only thing you do is to select the date and then go to the DATA ribbon and then click on the forecast tab as shown below. This uses the FORECAST.ETS function.

Once you do this you can select the option to list the statistics. This is computed with the FORECAST.ETS.SEASONALITY function. An example of this output is shown below.

This is all quite boring. The real question is how are the forecasts made. To illustrate how the forecast is made using exponential smoothing, begin with a simple case without a trend. This forecast will be the same for each year of the forecast. The forecast is a weighted average of the current value and the prior forecast. The alpha is multiplied by the current value and the prior forecast is multiplied by 1-alpha as illustrated below. The illustration demonstrates that the forecast remains constant. The absolute error and the mean square error can be used to find the best value of alpha with a data table or with a macro.

It is more interesting to include a trend in the analysis. To do this you can include a beta factor. This factor is applied to the trend rather than the forecast. The trend is computed as the difference between the prior value without a trend and the current value without a trend. These are computed in a similar as the simple exponential smoothing. For me, the key is to have the correct number of columns. One column is for the exponential smooth without the trend. The next computes the trend. Then, finally the trend forecast can be computed.

The single smooth is the last forecast value without the trend plus the current vallue multiplied by the alpha as shown below.

It is more interesting to understand the trend in the analysis. To do this you can include a beta factor. This factor is applied to the trend rather than the forecast. The trend is computed as the difference between the prior value without a trend and the current value without a trend. These are computed in a similar as the simple exponential smoothing.

I demonstrate how the forecast is made for GDP per capita in many different countries in the video below. By pressing the spinner button you can see the forecast results and how the trends occur and how the variance of the forecast is affected by the variability in the historic prices and the size of the alpha and beta parameter. This is in the forescast page of the file below. In the next sheet of the file I show I have replicated the excel forecast and the alpha and beta parameters are completely different. I use either a goal seek or a solver technique to match the forecast. This replication results in very different parameters from excel and shows how the excel generated parameters do not make sense.

After showing the problems I demonstrate how exponential smoothing works. I first demonstrate the simple smoothing method and how to compute the mean absolute error, the mean square error and the root mean square error. Then I show you you can use a data table to find the alpha statistic the minimises the error. After working through the simple smooting I move to smooting with a trend. I think this is the most important. In this case you can make a two way data table or use the solver add in to compute the alpha and the beta. I demonstrate the effects of different alpha and beta parameters.

Finally, I have tried to replicate the forecast error. The forecast error uses some measure of standard deviation and multiplies the standard deviation by 1.96 to get a 95% confidence (2.5% of being below the forecast and 2.5% of being above the forecast). I have not been able to precisely replicate the forecast error and I cannot find any explanation as to how this is done. But you can look at the formulas that I have collected and see how the forecast error increases with higher value of alpha and beta.