79111412

Date: 2024-10-21 19:04:21
Score: 0.5
Natty:
Report link

I apologize that an answer comes 5 years later. I ran into the same issue and wanted to be able to validate the differences from Excel and R. It took me a couple hours but I was able to figure it out and thought I would leave the answer here in case others needed it. There are two things to consider.

  1. Excel uses a ETS AAA method which means your model should be train.hw <- ETS(train, model ="AAA") # Here we declare the model. fcttrain.hw <- forecast(train.hw, h=2) # Here we forecast the model and specify how far we forecast out.
  2. The other issue is that Excel automatically detects and determines what smoothing parameters to use. Smoothing parameters are the alpha, beta and gamma values. To find the values being used in Excel you need to perform the function forecast.ets.stat. This will help you to find the alpha beta and gamma values that Excel is selecting. Take those values and apply them to your code in R. train.hw <- ETS(train, model ="AAA", alpha = .1 beta = .1 gamma = .1) This is just an example your values will be different. The alpha and the beta are the important values the gamma is less significant. You can also enter NULL to let the ETS function auto select the parameters.

If you follow incorporate the two points in R then you will end up with an identical or close to identical result to Excel.

Now for the difficult talk. The way Excel calculates the smoothing parameters is different from the ETS function in R. The ETS version in R is more sensitive to the last values in your time series model. So if the latest values go up or down significantly then the smoothing parameters automatically work less to correct for that change. Whereas in Excel there is a bias to smooth that difference more. Both Excel and in the ETS function in R the smoothing parameters are automatically detected and applied. The difference is how both are coded to deal with the smoothing of data.

I hope the answer helps.

Reasons:
  • Long answer (-1):
  • No code block (0.5):
  • Low reputation (1):
Posted by: Jacob Besser