Question about Statistical Forecast

[Former Member]

Hi,

We are trying to configure Statistical Forecast and the challenge is the planning level:

Our input KF is called Sales History and has a base planning level as:
PE/PRD/SOG/SOF/SGP/DC/ST   (Period/Product/S.Org/S.Off/S.Gp/Dist.Channel/Sold-To level)

however we want our forecast to be run at a more aggregate level as:
PE/PRD/SOG/SOF    (Period/Product/S.Org/S.Off),

i.e. without 3 levels SGP/DC/ST (S.Gp/Dist.Channel/Sold-To level)

My questions are:

1)  How can we forecast at an aggregate level without using too many helper KFs?
2)  Given that input KF and output KF for statistical forecast have to be at the same the aggregated planning level, how can we disaggregate forecast result back to the detail level?

Thank you.

Yee Ann

[former_member183849 · Accepted solution]

Hi Yee Yuen,

Here is an idea that could take you in the right direction

1. Define a new forecast output key figure at the required aggregate level, i.e. at planning level 'PE/PRD/SOG/SOF’.  Eg. FCST_AGG
2. Define an additional forecast moving average output key figure at aggregate level Eg. MOVINGAVG_AGG
3. Define a forecast moving average output key figure at the desegregated level 'PE/PRD/SOG/SOF/SGP/DC/ST’. Eg. MOVINGAVG_DISAGG
4. Define a calculated key figure @ 'PE/PRD/SOG/SOF/SGP/DC/ST’. This will take the forecast value to disaggregate level .
   1. You could define the formula for this as FCST_AGG@PE/PRD/SOG/SOF * (MOVINGAVG_DISAGG@PE/PRD/SOG/SOF/SGP/DC/ST / MOVINGAVG_AGG @ PE/PRD/SOG/SOF).

Essentially we are using moving average at aggregate and disaggregate level to calculate a disaggregation ratio and and applying that ratio to generate  forecast @ disaggregate level. Hope this helps.

Ram

[Former Member · Accepted solution]

Hi Ram,

Thank you very much for your respond, my question is partially answered.  I would also like to know if there's way not to create too many extra helper KFs and planning levels.  From our Sales History @ PE/PRD/SOG/SOF/SGP/DC/ST  to FCST_AGG @ 'PE/PRD/SOG/SOF,
we are aggregating 3 levels, i.e. SGP/DC/ST

To do aggregation at the level that we need, it seems like I have to do create extra planning levels, helper KFs and calculation definition level by level, i.e:

Sales History @ PE/PRD/SOG/SOF/SGP/ST (no DC) = sum (Sales History @ PE/PRD/SOG/SOF/SGP/DC/ST)

HKF1 @ PE/PRD/SOG/SOF/SGP/ST (no DC) = Sales History @ PE/PRD/SOG/SOF/SGP/ST (no DC)

HKF2 @ PE/PRD/SOG/SOF/SGP (no DC, ST) = sum (HKF1 @ PE/PRD/SOG/SOF/SGP/ST) (no DC)

FCST_AGG @ PE/PRD/SOG/SOF (no DC, ST, SGP) = sum (HKF2 @ PE/PRD/SOG/SOF/SGP) (no DC, ST)

Then at the end, use FCST_AGG @ PE/PRD/SOG/SOF as input in the Stat FC

Is there a smarter way to do the aggregation of the data from Sales History @ PE/PRD/SOG/SOF/SGP/DC/ST  to FCST_AGG @ 'PE/PRD/SOG/SOF?

Thank you.

Yee Ann

[former_member183849 · Accepted solution]

hi Yee Ann,

You can directly define a new key figure at FCST_AGG @ PE/PRD/SOG/SOF and an aggegation over original keyfigure @ 'PE/PRD/SOG/SOF/SGP/DC/ST'. You do not need intermediate keyfigures/planninglevels

As a side note, you will need the planning level 'PE/PRD/SOG/SOF' in any case because that is where the output key figure is. I do not think you have to create too many planning levels.

Hope this helps.

Kind regards, Ram

[Former Member · Accepted solution]

Hello Ram,

I am facing a similar situation:

I want to calculate a statistical forecast on a planning level (PER/PROD/CHAN - CHAN = Sales Channel of customer) higher than the base planning level of the input/output key figure (PER/PROD/CUST). From your answer above I read that it is not possible to insert the planning level for the forecast just in the forecasting profile or directly when running a forecast.

You wrote that you can define 3 different output key figures (FCST_AGG, MOVINGAVG_AGG and MOVINGAVG_AGG) and then calculate FCST_DISAGG=FCST_AGG*(MOVINGAVG_DISAGG/MOVINGAVG_AGG). However, I don't get your idea: Do you calculate three different forecasts for the three output key figures? If yes, what are the respective input key figures and which forecasting methods/profiles do you use? Does this mean that you have to run three different forecasts instead of one everytime you want to forecast?

Please let me know if you could help me with that.

Cheers, Jonathan

[former_member183849 · Accepted solution]

hi Jonathan,

If you carefully review Yee Ann's question, she wants to be able to forecast at an aggregate and then disaggregate result back to detail level.

It seems that is not the situation you are having. Therefore you do not have to define additional planning levels as long as the forecast output key figure is at same level or more aggregate level in relation to input key figure.

Hope this helps.

Ram

[Former Member · Accepted solution]

Hi Ram,

thank you for your fast reply. I might not have expressed myself clearly enough. Our situation is as follows:

We have an input key figure (ACTUALSQTY) at level PERPRODCUST and an output KF (STATISTICALFCSTQTY) at the same level. The statistical forecast itself however should be calculated at the higher level PERPRODCHAN (CHAN=distribution channel of the customer), and the results should be stored at STATISTICALFCSTQTY@PERPRODCUST, on the lower level. We need this STATISTICALFCSTQTY@PERPRODCUST as input for the manual sales forecast; the sales forecast KF has the base planning level PERPRODCUST, therefore I need to have the input at this level as well.

As far as I know, it is possible in APO DP to explicitly name a level for the statistical forecast itself, even when the level of the input/output key figures is lower.

I was hoping that there is a similar functionality in S&OP on HANA, i.e. I define a forecasting profile with input and output KF at PERPRODCUST and insert the forecast level PERPRODCHAN either in the forecasting profile or in the Excel AddIn when running a forecast.

As I assume from your answer above, I was thinking too simple.

If I define ACTUALSQTY@PERPRODCHAN and STATISTICALFCSTQTY@PERPRODCHAN I can do the forecast at this aggregated level, that is fine. However, I need another KF like DISAGG_STATISTICALFCST@PERPRODCUST as input for SALESFCSTQTY@PERPRODCUST, i.e. for the manual sales forecast on level PERPRODCUST. I think that is actually the same situation that Yee Yuen described in

"2)  Given that input KF and output KF for statistical forecast have to be at the same the aggregated planning level, how can we disaggregate forecast result back to the detail level?"

And I didn't get your idea of how to derive this DISAGG_STATISTICALFCST@PERPRODCUST from STATISTICALFCSTQTY@PERPRODCHAN.

It would be great if you could help me again!

Cheers, Jonathan

[former_member183849 · Accepted solution]

hi Jonathan,

Yes you must create 3 different forecast profiles and execute those before you can get the forecast number you require. I know this is not ideal for your situation. Good news is input keyfigure for all of those is going to be same : ACTUALSQTY @  PERPRODCUST.

>>>>>

You wrote that you can define 3 different output key figures (FCST_AGG, MOVINGAVG_AGG and MOVINGAVG_AGG) and then calculate FCST_DISAGG=FCST_AGG*(MOVINGAVG_DISAGG/MOVINGAVG_AGG). However, I don't get your idea: Do you calculate three different forecasts for the three output key figures?

>>

<RAM>Yes</RAM>

>>>If yes, what are the respective input key figures and which forecasting methods/profiles do you use? Does this mean that you have to run three different forecasts instead of one every time you want to forecast?

>>>

<RAM>
You are right, this is a bit round about. Like I said you need three forecast profiles all of them using same input key figure ACTUALSQTY @ PERPRODCUST:
1) Forecast method, <Any you desire>, output keyfigure: FCST_AGG, plan level 
PERPRODCHAN
2) Forecast method, Moving average, output keyfigure: MOVINGAVG_AGG,
plan level
PERPRODCHAN
3) Forecast method, Moving average, output keyfigure: MOVINGAVG_DISAGG,
plan level
PERPRODCCUST

</RAM>

Your final forecast key figure can be defined as a calculated a key figure,

FCST_DISAGG@ PERPRODCUST = FCST_AGG@PERPRODCHAN *(MOVINGAVG_DISAGG@PERPRODCUST/MOVINGAVG_AGG@PERPRODCHAN )

I hope this answers your question. Essentially you are using moving average ratio as driver for disaggregation from PERPRODCHAN to PERPRODCUST level.

Kind regards, Ram

[Former Member · Accepted solution]

Hi Ram,

Thanks very much for the Input. The whole Idea of running the forecast at aggregated level is mostly driven by the fact that  the input data at the detailed level may not be suitable to run a statistic (both on qualitative and/or quantitative aspects). So my question is the following: How reliable is the ratio used for the disaggregation at the end since it involves the results of a forecast executed at the detailed level? Or maybe the moving average method has some features helping to minimize the statistical errors? if then maybe the method could have been used directly to run the statistics @ Perprodcust?

I am afraid that at the end the reduction of the statistical error may not be proportional to the efforts spent.

Kind Regards

Kenneth

[Former Member · Accepted solution]

Hi Ram,

thank you for the detailed answer. We now have to consider, if using moving average for calculating a ratio to disaggregate the forecast is really helpful for our situation.

Cheers,

Jonathan

[former_member183849 · Accepted solution]

hi Kenneth,

Moving average is a possible an option to disaggregate down to fine granular levels. In general this is a business question: how do you want to distribute values from aggregate level downwards?

Do you want to hard code the disaggregation ratio? Do you want to calculate a factor instead as a calculated key figure? For example you could count how many customers does each customer channel have and use that as a ratio (if you do not care for individual past contributions as in moving average ratio).

Kind regards, Ram

[former_member183849 · Accepted solution]

hi Jonathan,

Good luck. In such case, please see the other points in my answer to Kenneth as well. With that you could mark the questions as answered or not. That way other users can use this as a reference.

Best, Ram

[Krishna1]

Guys,

We are doing some thing similar...

1) SF input can be cal or stored. (Data is coming at Country level)

2) SF output is always stored..( Data is needed at Regional level)

3) Use copy or calculate KF ( Sum up country level data to regional level of step1) as  input to SF...

Hope this helpful...Call me if you have more questions +1 917 386 4187...

Thanks,

Krishna

[former_member183849]

hi Krishna,

Please review my answer to Jonathan. You do not need additional planning levels or key figures, simply a configure a suitable forecast profile specifying input and output key figures (which are already compatible in your case).

Best, Ram