Forum Discussion
Forecast.ETS Formula: Returning Negative Growth on a cumulative quantity
Hello,
I'm using the forecast.ets function against a cumulative quantity to help remove some of the volatility in the forward looking forecast. The cumulative quantity, of course, is never less then the prior month's value. In back-testing this formula, it works quite well until Jan 2022, when it returns a value less than the prior month, despite negative growth never happening once in the past.
Hoping someone is much better at statistics than I am and can explain why it would return negative growth when it is based on a cumulative historical quantity.
The formula is the standard Forecast formula:
=ROUND(FORECAST.ETS(AC1,$B2:AB2,$B1:AB1),0)
Thanks in advance,
Dexter
Since you are forcasting growth, I would suggest you to forecast relative value witch should be more stable than forecasting ponctual datapoint. You may use the formula below:
=ROUND(EXP(FORECAST.ETS(AC1,LN($B2:AB2),$B1:AB1)),0)
Residual between actual to forecasted data will be improved and be reduced by a factor of 3 with your dataset.
I would still expect discrepancy arrising if the underlying growth rate increase or decrease as condition might change over time.
I hope this help you
Regards
JayBee10 Thank you for responding. I do like this alternate approach. However it is still projecting negative growth in several periods despite having not a single month with actual negative growth. That's really what I'm trying to understand. There are periods of growth slower than prior periods but month x values are always greater than the preceding period 100% of the time.
In both lines plotted on the chart below, I am using a 3 month lag (using actual data & it's timeline up to the month being forecasted - 3 months). Both the formula you provided and the standard formula I provided above project periods with negative growth when using the cumulative total as the historical actual basis (note only 1 of these two is represented in the chart below - the formula you provided).
When I use the # Customers added/month as the basis instead (non cumulative qty), there is not a single month where a negative quantity is projected. Using that forecast and calculating from it the projected cumulative growth rate is what is represented by the blue line.
