Simple exponential smoothing with FORECAST.ETS in Excel: what are the calculations behind it? 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!
 A: 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.
