1
$\begingroup$

I am really stuck trying to figure out why my manual calculation of ETS forecast doesn't match what's automatically produced by the FORECAST.ETS function in Microsoft Excel.

In the specific example I am looking at there's no seasonality or aggregation happening. So in fact it's just the matter of simple exponential smoothing, which in my understanding should be the alpha parameter multiplied by the last available historical value plus 1 minus alpha multiplied by the previous forecast value, like this

$\widehat{y}_{t+1}=\alpha *y_{t}+(1-\alpha )*\widehat{y}_{t}$

Whereupon for all the periods following the period when last available historical value is available, that historical value becomes fixed. So $y_{t}$ is the historical value used for all forecasts beyond the last period with actual value but the $\widehat{y}_{t}$ changes as you go. Here's an example file

However, when I use the same alpha as Excel, my manually-calculated answers are still different from the ones of the Excel function...I have watched a ton of videos on Excel's ETS but none actually show the calculation behind the function, only how to use the function itself.

All help would be much appreciated!

Improve this question
$\endgroup$
5
  • $\begingroup$ Hi: I can't tell you why you differ but, my experience is that, once you run out of actual data, you don't keep going by keeping the previous value fixed and just updating the $\hat{y}_t$. That's not the definition of SES. So, you should only be forecasting out one step after the last data point available. If you get the same numbers up until that point, then you're okay. $\endgroup$
    – mlofton
    Commented Jun 7, 2021 at 14:04
  • 1
    $\begingroup$ Can you add a Minimal Working Example of the behavior that is puzzling you, and what you would expect instead? $\endgroup$
    – Stephan Kolassa
    Commented Jun 7, 2021 at 14:18
  • $\begingroup$ @StephanKolassa, thank you both for the comment. I have added a link to the example file in the body of the post. Am still none the wiser. Hope you can help! Thanks again. $\endgroup$
    – Lii Bernovski-Smith
    Commented Jun 8, 2021 at 7:25
  • $\begingroup$ @mlofton, thank you...sadly, my manual calculation differs from the Excel's own automated one even at the first forecast value. I have added an example file to my post, if you have any ideas, please do let me know! Thanks again. $\endgroup$
    – Lii Bernovski-Smith
    Commented Jun 8, 2021 at 7:29
  • 1
    $\begingroup$ Lii: Bernovski-Smith: Can you try flipping your $\alpha$ and your $(1-\alpha)$ because , in many applications, it is not clear whether the smoothing parameter is defined as $\alpha$ or $(1-\alpha)$. For the initialization, I would just use the first value as $\hat{y_t}$ and then start smoothing at $t=2$. If you still don't get what they have, let me know and I'll try to find some time to play around with it. As Stephen mentioned, initialization could be mysterious so you may never be able to match what they get unless you find out how the initialization is done. $\endgroup$
    – mlofton
    Commented Jun 9, 2021 at 13:37

1 Answer 1

Reset to default
2
$\begingroup$

On the one hand, I am unable to recreate Excel's calculation.

  • One part of the problem is that SES crucially depends on how the level component is initialized. Common ways of doing so are using the first observation (as you do in your calculation), or using the average of all observations, or - especially if we operate in a state space framework - estimating the initial value via maximum likelihood. Apparently, there is no way to get the initial level value from Excel.
  • The other part is that there appears to be no way of getting the in-sample fits or level components.
  • The third aspect of the problem is that forecasts from SES are flat, but the ones given by Excel are not. Thus, there seems to be something more fundamentally broken in Excel's forecast routines. Which in turn, to be honest, reduces my disposition to hunt for what precisely is wrong here.

Note that the third point also applies to your calculation. Specifically, after the historical observations ended, the forecast should be just the last value of the level component for every future time point. What you are doing is that you keep on updating this value repeatedly, for each future time point, using the last historical value each time. This is not how SES is done, and to be honest, I don't see why you would want to do it.

Incidentally, another potential problem with your calculation is that you update the level component $\ell_t$ at time $t$ using the previous value of the level component $\ell_{t-1}$, which is fine, but you also use the previous observation $y_{t-1}$. While this is one possible convention, another frequent one is that SES updates $\ell_t$ using $\ell_{t-1}$ and the current observation $y_t$:

$$ \ell_t = \alpha y_t+(1-\alpha)\ell_{t-1}, $$

where $t=2, \dots, T$ for observations $y_1, \dots, y_T$, and $\ell_1$ is the initial value per above (so, often we will set $\ell_1=y_1$). Forecasts are then $\hat{y}_{T+h}=\ell_T$ for all $h>0$. These two conventions don't usually make a lot of difference, but they can trip you up if you want to recreate a tool's forecasts and are unaware that it uses the opposite convention.

Bottom line: ditch Excel. It has a long history of being crap for statistical calculations (see many papers by McCullough in the 2000s), and while it appears to have cleaned up its act somewhat for "standard" statistics, it looks like it managed to get SES wrong, which is just about the simplest forecasting algorithm you could think of, so getting it wrong in published software takes some dedication.

Instead, use established forecasting software, like the forecast or fable packages for R (all of which are free). These are illustrated in the excellent free online textbooks Forecasting: Principles and Practice (2nd ed.) by Athanasopoulos & Hyndman and Forecasting: Principles and Practice (3rd ed.) by Athanasopoulos & Hyndman, respectively.

Improve this answer
$\endgroup$
5
  • $\begingroup$ Thank you for getting back to me! Sorry to be so daft, am trying to work out the correct SES forecasting formula in your explanation but am struggling a little. Please could you clarify it as an equation if possible? That'd be super-helpful! Thanks again :) $\endgroup$
    – Lii Bernovski-Smith
    Commented Jun 8, 2021 at 9:18
  • 1
    $\begingroup$ I edited my answer, hope it helps. $\endgroup$
    – Stephan Kolassa
    Commented Jun 8, 2021 at 9:34
  • $\begingroup$ Thanks! I now understand what you mean, very clear explanation. However, there seem to be differing opinions. What do you make of this one stating that the previous actual value should be used, not the current? itl.nist.gov/div898/handbook/pmc/section4/pmc431.htm $\endgroup$
    – Lii Bernovski-Smith
    Commented Jun 8, 2021 at 9:41
  • 1
    $\begingroup$ Yes, there are indeed different conventions here (note that the NIST page also notes this), so I should have been a little less strident in my post. I'll edit. It doesn't really make a lot of a practical difference in forecasts, it's more something that can trip you up if you want to exactly recreate calculations some tool did - especially if you are not aware that there are different conventions here. ... $\endgroup$
    – Stephan Kolassa
    Commented Jun 8, 2021 at 9:47
  • $\begingroup$ ... Also, the difference may just be an index shift, depending on whether "$\ell_t$" is "the level component that belongs to time $t$", or "the level component that would be used to forecast from the (next) time period $t$ on". $\endgroup$
    – Stephan Kolassa
    Commented Jun 8, 2021 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.