One of our customers required a 3rd Normal Formal Holts Winters Multiplicative predictive model, running on planning. The predictive planning add-in doesn’t support this method – but it’s actually one of the most interesting predictive algorithms since it supports seasonality – a must have for retailers.

Solution

Our solution was to implement the essbase @TREND function.

It’s a generic calculation function that uses input parameters to define how the calc runs:

@TREND(@remove(@xrange(&histStart->"Jan",&histEnd->"Dec"),&histStart:&histEnd->BegBalance),,,@remove(@xrange("Best Case"->&histStart->"Jan","Best Case"->&histEnd->"Dec"),"Best Case"->&histStart:&histEnd->"BegBalance"),,@remove(@XRANGE(&predStart->"Jan",&predEnd->"Dec"),&predStart:&predEnd->"BegBalance"),TES, 12, &C1, &C2, &C3);

That’s quite a mouthful – please explain!

The trend function relies on 3 key sets of data:

1. Historic Data to use to model the future
2. A method, and tuning parameters for it
3. Seasonality – the system needs to know how often periods are repeated.
4. Forecast – where to put the forecast.

This is passed in to the trend function as follows:

@TREND(history,,,,,forecast,TES, 12, C1, C2, C3);

In this case, the history & forecast are an [@XRANGE] function to create the list of history we need. Note that in a period dimension of BegBalance, Jan,Feb….Nov,Dec, xrange(FY14->Jan,FY15->Dec) WILL include FY15 BegBalance, hence the remove you see in the functions above so that we only use Jan->Dec for prediction, and we only write back out to Jan->Dec.

TES is the trend method to use – Triple Exponential Smoothing (Holt-Winters method) 12 is the periodicity of the data – it in the history and forecast lists, we expect there to be a 12 month cycle. (If predicting by quarters this parameter would be 4, by years=1) Parameters C1,C2 & C3 are the inputs to this method. See INSERT XLS LINK HERE for a worked example – but principally, C1=Level, C2=Gradient, C3=Repetition/Waviness (there is a bit more to it than that).

The Holts winters method requires that you have at least 2x seasons of data to run a prediction (ie 2 years of data) It is however more accurate the more historical data you have. Also be aware the trend forecasts reduce accuracy the further forward you look!