Forecast.ETS Formula: Returning Negative Growth on a cumulative quantity

[DexterG_III]

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

[#2]

@DexterG_III 

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

[#3]

@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.