I am forecasting volumes and cannot explain a strange output we are getting from what seems to be solid equations.
To forecast
- Take 2017 Month over Month % Changes -
(New/Old)/Old - Apply 2017 monthly % changes to unknown months for 2018
For instance, to estimate August 2018 volume, we use:
July 2018*[((August 2017-July 2017)/July 2017)+1]
Then, we decide to look at the yearly % change in volume (i.e. Jan 17 to Jan 18 and so on.)
Once we get to forecasted months (Aug 18 - Dec 18) we are seeing a constant yearly growth rate of 55% even though we used monthly % changes to forecast.
Can anyone explain the underlying math that is happening here?
In photo, blues are actual, yellows are forecasted. I am just showing a few equations, but the forecast equation was used to determine all yellow cells. The % change was used to determine all yearly growths.
The forecasted volume for e.g. Aug18 is $$A18 = \frac{A17}{J17}\cdot J18$$ The yearly growth for Aug18 is $$\frac{A18-A17}{A17}= \frac{\frac{A17}{J17}\cdot J18 - A17}{A17}= \frac{J18-J17}{J17}= 53 \text {%}$$ i.e. the yearly growth for Jul18.
This will be true of all the forecasted months in 2018 as the ratio between the forecasted months in 2018 is the same as the ratio between the same months in 2017.