Forum Discussion
Forecast.ETS Formula: Returning Negative Growth on a cumulative quantity
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
- DexterG_IIISep 26, 2022Iron Contributor
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.