Read Forecast With Exponential Smoothing Results Excel

This article discusses the forecast method in excel which is function of the information tab and allows you to quickly make forecasts from historic data. I piece of work through the exponential smoothing technique used to make the forecasts and in particular the parameters used in the excel tool and include a spreadsheet where I tried to reverse engineer the method. Using forecasts from the parameters implied by the excel forecast I try to back out the implied parameters.

Ane of my students asked me how the excel forecast works. At the time I had no idea that in that location was even a forecast in excel.  Then I saw the excel forecast which looks pretty cool. When you go to the internet and google forecasting in excel, you get a whole bunch of give-and-take of how you tin can press a couple of buttons and get some forecasts. A lot of the explanations also tell you how you lot can use the FORECAST.ETS function and retrieve statistics such as alpha and beta and the root mean squared statistic. These explanations too tell you how you tin get confidence intervals from the FORECAST.ETS.CONFINT function. One website called real statistics does walk yous through how to make your own forecast (and I used this). I originally worked on this whole forecasting business organization because of a question from a person in i of my classes who wanted to know how the confidence intervals are calculated.

But hither is the problem, the ETS.FORECAST is a black box. None of the websites tells yous in detail how to replicate the forecast and the confidence intervals in detail. This is a large problem considering the forecast is made from exponential smoothing which is a very simple process. When I take tried to make my ain model in the worksheet file below I have not been able to replicate the forecast parameters in excel.

Excel File that Works Through Excel Forecast and Exponential Smoothing in Detailed Manner with Parmeter Examination

Maybe this is considering I am just stupid (probably). The forecasts for different GDP per capita growth look good, but I think at that place is something very odd with the excel reported parameters from the forecasts. Commencement, 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 exist careful about my statements. Perhaps they are mis-modelling the beta parameter, but I don't think so. The really bad affair virtually 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 that you lot can download by clicking the in a higher place button begins by demonstrating how the excel forecast tool works like all of the other websites (this is really no big deal). In seconds, you can make a time serial forecast now in excel. Y'all just select a series of information adjacent to a series of dates, and you get a serial of forecasts forth with a confidence interval. Y'all can export the forecast to a separate sheet. The only question is what are the techniques that excel uses to do this. Get-go, just go some data. You tin get the file for the economic variables and make forecasts for things similar Gdp per capita, population, life expectancy etc. The only thing you lot practice is to select the date and so go to the DATA ribbon so click on the forecast tab as shown below. This uses the FORECAST.ETS office.

Once you practice this you can select the choice to list the statistics. This is computed with the FORECAST.ETS.SEASONALITY office. 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 instance without a tendency. This forecast will exist the same for each year of the forecast. The forecast is a weighted average of the electric current data value and the prior forecast. The current information is given a weight of alpha and the prior forecast is given a weight of (one-alpha). This means the alpha is multiplied by the current value and the prior forecast is multiplied past one-alpha as illustrated below. The illustration demonstrates that the forecast remains constant. The accented error and the mean foursquare error tin can exist used to find the best value of blastoff with a data table or with a macro.  The column Q has the blastoff multiplied past the current data value.  The mistake is the difference between the predicted value and the bodily data.

It is more interesting to include a trend in the assay then to merely weight the prior forecast and the current value. To do this tendency analysis you tin include a beta factor. This beta or trend cistron is applied to the tendency rather than the forecast. The tendency is computed equally the difference between the prior value without a trend and the current value without a tendency. These are computed in a similar every bit the unproblematic exponential smoothing. For me, the central is to take the correct number of columns. One column is for the exponential shine without the trend. The next computes the trend. And so, finally the trend forecast tin be computed.

The unmarried polish is the last forecast value without the trend plus the current value multiplied by the alpha as shown below.

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

I demonstrate how the forecast is made for Gdp per capita in many dissimilar countries in the video beneath. By pressing the spinner button you lot tin can see the forecast results and how the trends occur and how the variance of the forecast is afflicted past the variability in the celebrated prices and the size of the alpha and beta parameter. This is in the forescast page of the file beneath. In the adjacent sheet of the file I evidence I accept 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 brand sense.

Subsequently showing the problems I demonstrate how exponential smoothing works. I first demonstrate the elementary smoothing method and how to compute the mean absolute error, the hateful square error and the root hateful foursquare mistake. Then I show you you can use a data table to detect the alpha statistic the minimises the error. After working through the uncomplicated smoothing I move to smoothing with a tendency. I think this is the nearly important. In this case y'all can brand a two way data table or use the solver add in to compute the alpha and the beta. I demonstrate the furnishings of different alpha and beta parameters.

Finally, I have tried to replicate the forecast mistake. The forecast mistake uses some measure of standard deviation and multiplies the standard divergence by one.96 to go a 95% confidence (2.5% of being below the forecast and ii.5% of being above the forecast). I take non been able to precisely replicate the forecast error and I cannot notice any explanation every bit to how this is done. But you tin look at the formulas that I accept collected and run across how the forecast error increases with higher value of blastoff and beta.

Read Forecast With Exponential Smoothing Results Excel

Source: https://edbodmer.com/forecasting-and-exponential-smoothing/

Share this post