- SAP Managed Tags:
Introduction:
I was facing significant challenges in implementing Trend Model, Seasonal Model and Trend seasonal model, so after much effort and research, I attempted to simplify it to enable others to benefit from it. I aimed to make it easier to implement for those who struggled with solving this problem.
Attaching Excel Where you can easily get your desired result maintaining your set of data in every Model.
For External Initialization Period
https://docs.google.com/spreadsheets/d/1txGRvkkAQFxUskktlqQuVN1v4yoLOSZM/edit#gid=2071855033
For standard Initialization Period.
https://docs.google.com/spreadsheets/d/1t-GRFMElR-cD1JDtqqmf9R30CsO4UVfP/edit#gid=492975581
Material Master
Have Maintained below values
Forecast Model: X Seasonal & Trend Model
Period Indicator: Week, you can maintain any you want.
History Period: Which is basically the previous sales data of 24 Months.
Forecast Period: I have maintained 3 as I required 3 months Forecast Value.
Initial Period: Initial Period is basically the number of data on which you want system to calculate forecast as in my example I have maintained 24 that means I want system to read all 24 values, as we all know that if we do not maintain it system will follow below mentioned table.
In one Scenario I have maintained Initial Period and in 1 scenario did not maintained so system is considering its standard Initial Period Logic which is mentioned below.
Initialization: X = Initialization by System
Model Initializing:
Model initialization is the process of determining the necessary model parameters such as the basic value, the trend value, and the seasonal indices for the selected forecast model. It is necessary when you use a model that forecasts a value for one period based on the forecast value for the period directly before it. Obviously an initial value is required to start the forecast.
The following table shows you which model parameters are necessary for each forecast model.
As a rule, the forecast model is initialized automatically. To do this, the system requires a certain number of historical values. This number depends on the forecast model, as shown in the following table.
Period/Season: Here we need to maintain the periods we have in a season
For example, as I have maintained 4 which means I have a season which contain of 4 months in the above material master screen
Have maintained Consumption values for 24 Weeks. Attaching the screen shot below for Reference Only.
Trend Model with External Initializing Period:
The set of data I have used, it is also mentioned in Attached Excel.
Executing MP30/MP38 Tcode: Below are the Result:
Formula for calculating Trend:
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption/sales value data set.
T(i) = (2 * i - N - 1)/2
Here i = The number of individual data in a sequence like, 24,23,22,21,20,19 and so on till 1
N= Total number of historic data which is 24 in our case.
We need to Get T(i) Value for every set of value which means need to generate 24 T(i) Values.
For Example,
T(i) 1st =(2*24-24-1)/2
= 11.5
T(i) 2nd = (2* 23-24-1)/2
= 10.5 and so on TILL 24TH Value.
After getting all T(i) and solving the formula we have got the Trend Value 0.0327
Formula for Basic Value:
Average of all 24-week data (Historical Data) + Trend which you have calculated with the above formula *(24-1)/2
How 23 is calculated basically we need to minus 1 from total number of historical periods which would be 24-1=23
After solving the formula for basic value we have got the value 253.167
Forecast Value Formula:
For forecast Value we would be adding trend in Basic Value.
Here we will be adding the basic value which is 253.167 into Trend Value which is .0327 and will get the first forecast value and for second forecast value will add the same trend value to first forecast value.
Forecast Value 1 253.1992754
Forecast Value 2 253.2318841
Forecast Value 3 253.2644928
Trend Model with standard Initializing Period:
I have used the same data set for every Forecasting Model as maintained above.
Formulas:
For current Basic value: It would be always your first value (current month/week sales) from your data set. As in my case it is 115.
For Previous Basic Values: It would be always your 2ND value (current month/week sales) from your data set. As in my case it is 220.
Trend Formula for previous period or initialization:
Subtract Last(history) value from 3rd last(history) value and divide by 2 i.e.
H(22)-H (24)/2
Here in our case (250-310)/2 = -30
Here we are not finished yet we have to generate trend value for every set of value by applying below mentioned formula.
example starting from 21st value We have to generate trend for each single line/value going upward, as we have already generated the trend value for last three values through other mentioned trend value, which is -30.
Trend Formula for every line (Set of Value)
T(i) = T(i-1) + B * (G(i) - G(i-1) - T(i-1))
G(t-1) Basic Value from Previous Period
T(t-1) Trend value from previous period
b Beta Value
G(t) Basic Value from Current Period.
Trend for 21st value
When we will input values in above formula we will get a 21st trend value -30.40
here we have to add average of all values 286 and first trend value which is -30 to get previous basic value.
G(T-1) = 256
T(t-1) = -30
B = .10
G(t) = 222
Trend for 20TH Value
G(T-1) = 222
T(t-1) = -30.40
B = .10
G(t) = 210
When we will input values in above formula we will get a 20th trend value -28.56
I Have Attached excel File which has working of all trend line values.
Executing MP30 TCODE for single material, can execute mp38 for multiple material or on Plant Level.
Result After executing MP30 System Created 3 Forecast as mentioned below.
After right click on trend value we will be selecting Additional Parameter to see previous period data. As mentioned below
As you can see system is showing us Current Values and previous values.
Forecast Value Formula:
Forecast value 1 97.17
Forecast value 2 79.35
Forecast value 3 61.52
Here we have to add Last trend value which Is -17.826 in last sales value which is 115 and for second forecast value we will be adding the same trend value in the previous forecast value which is 97.17.
Seasonal Model with External Initializing Period:
I will be using the same historical values as I have used in trend Model.
Here I have maintained Forecast Model as ‘S’ And Period/Season 3 that means I have season whose length would be of 3 weeks.
Executing MP30 TCode: Below are the Results
Basic Value = The base value is equal to the mean value from the historical data.
Here we have period length of 3 weeks so we have to add every 3rd value as I have maintained in excel file.
Season Index for first period: The sum of every third value / total number of periods in your data * Average of all data
Forecast Value: Season index*Basic Value
I have segregated the season with different colors in excel so that you can easily interoperate.
Seasonal Model with standard Initializing Period:
Here I have maintained that I have a season of 3 weeks
MP30 TCODE SCREEN
Basic Value:
131.560
Formula For seasonal Trend:
As we have mentioned 3 in period forecast system will only generate 3 seasonal index using the last three value in the data set, in my case values are
To Calculate Seasonal index, we have to follow below mentioned formula.
Seasonal Index: Last value/average of last three values.
i.e. = 310/286
=1.083916
2nd = 298/286
=1.041958
3rd =250/286
=0.874126
Formula for basic Value: Previous Basic Value + Beta* (current basic value/3rd value of seasonal index- Previous Basic Value)
Forecast Values:
Forecast Value 1 142.6
Forecast value 2 137.08
Forecast value 3 115
Seasonal Trend Model with initializing Period:
Have Maintained X as forecast Model, and assuming I have a length of season which is 3.
Executing MP30 TCode: Below are the Results
Formula For calculating Trend:
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption value.
T(i) = T(i) = (2 * i - N - 1)/2
Here i = The number of individual data in a sequence like, 24,23,22,21,20,19 and so on till 1
N= Total number of historic data which is 24 in our case.
For Trend and seasonal Model T(i) Value will remain same for every 3 values as we have maintained period per season as 3.
System will add every 3rd value from the data set we have maintained.
Seasonal Trend Model with standard initializing Period:
Material Master
Maintained X FOR Trend & Seasonal Model.
After Executing MP30/MP38
Showing Additional Parameter where we can see the previous data also.
Here we will consider last 6 values as we have a season of 3 values as mentioned below and we have to consider +3 as well.
Formula to calculate trend value :
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption value.
T(i) = T(i) = 3/2=1.5
And = T(i)= -3/2=-1.5
For Trend and seasonal Model T(i) Value will remain same for every 3 values as we have maintained period per season as 3.
Trend value =-22.88
When we get our basic trend value through above formula we will calculate the first basic value dependent on first trend value whose formula mentioned below
Average of all values + trend value which we have just calculated above* (6-1)/2
6 because we have a season of three weeks and +3 we have to addas mentioned in above standard chart.
To calculate seasonal index we will use below calculation.
We will calculate 3 seasonal indexes as we have to generate three forecast values
1st value =Basic Trend-(24-19)*basic value
Basic Trend = -22.88
Basic Value 194.44
Value would be = 308.8888889
Now we will divide above calculated value with the actual value of that particular period which would be the 24th value of my table and multiply it by 100
=(310/308.88)
= 1.003597122
Will perform the same activity for every value from 24th till 18th and then add every third value and divide by 2 in result we will get our 3 Seasonal indexes you can refer excel file as well.
For Basic Value :
Where:
G(t-1)= 194.4444444
T(t-1) = -22.88888889
V(t) = 190 which would be the 7th value in descending order.
S(t) = first seasonal index value i.e .963
We will use this formula from 18th value till first value and will get the basic value .
For trend value we will use below mentioned formula
T(t-1) = -22.88
B= 0.1
G(t) = 197 which we have calculated from the above formula from basic value. First, we will calculate basic values then will calculate trend value with the above formula.
G(t-1)=194.44
=-20.33225725 would be our trend value, we will calculate trend value of every single data we have in our data list till the first one starting from 18th value going to 1st value
Formula for Forecast value:
Basic value + trend value * Seasonal Index.
Conclusion
This documents resulting in understanding the process of Forecasting model in a very simple way, which is highly valuable for Manufacturing and related stakeholders.
Your valuable feedback is required.
I was facing significant challenges in implementing Trend Model, Seasonal Model and Trend seasonal model, so after much effort and research, I attempted to simplify it to enable others to benefit from it. I aimed to make it easier to implement for those who struggled with solving this problem.
Attaching Excel Where you can easily get your desired result maintaining your set of data in every Model.
For External Initialization Period
https://docs.google.com/spreadsheets/d/1txGRvkkAQFxUskktlqQuVN1v4yoLOSZM/edit#gid=2071855033
For standard Initialization Period.
https://docs.google.com/spreadsheets/d/1t-GRFMElR-cD1JDtqqmf9R30CsO4UVfP/edit#gid=492975581
Material Master
Have Maintained below values
Forecast Model: X Seasonal & Trend Model
Period Indicator: Week, you can maintain any you want.
History Period: Which is basically the previous sales data of 24 Months.
Forecast Period: I have maintained 3 as I required 3 months Forecast Value.
Initial Period: Initial Period is basically the number of data on which you want system to calculate forecast as in my example I have maintained 24 that means I want system to read all 24 values, as we all know that if we do not maintain it system will follow below mentioned table.
In one Scenario I have maintained Initial Period and in 1 scenario did not maintained so system is considering its standard Initial Period Logic which is mentioned below.
Initialization: X = Initialization by System
Model Initializing:
Model initialization is the process of determining the necessary model parameters such as the basic value, the trend value, and the seasonal indices for the selected forecast model. It is necessary when you use a model that forecasts a value for one period based on the forecast value for the period directly before it. Obviously an initial value is required to start the forecast.
The following table shows you which model parameters are necessary for each forecast model.
| Model | Model parameters |
| Trend model | Basic value, trend value |
| Seasonal model | Basic value, seasonal indices |
| Seasonal trend model | Basic value, trend value, seasonal indices |
As a rule, the forecast model is initialized automatically. To do this, the system requires a certain number of historical values. This number depends on the forecast model, as shown in the following table.
| Model | No. of historical values |
| Trend model | 3 |
| Seasonal model | 1 season |
| Seasonal trend model | 1 season + 3 |
Period/Season: Here we need to maintain the periods we have in a season
For example, as I have maintained 4 which means I have a season which contain of 4 months in the above material master screen
Have maintained Consumption values for 24 Weeks. Attaching the screen shot below for Reference Only.
Trend Model with External Initializing Period:
The set of data I have used, it is also mentioned in Attached Excel.
| 115 |
| 220 |
| 320 |
| 116 |
| 120 |
| 430 |
| 221 |
| 209 |
| 290 |
| 670 |
| 310 |
| 600 |
| 132 |
| 144 |
| 154 |
| 168 |
| 148 |
| 190 |
| 220 |
| 210 |
| 222 |
| 250 |
| 298 |
| 310 |
Executing MP30/MP38 Tcode: Below are the Result:
Formula for calculating Trend:
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption/sales value data set.
T(i) = (2 * i - N - 1)/2
Here i = The number of individual data in a sequence like, 24,23,22,21,20,19 and so on till 1
N= Total number of historic data which is 24 in our case.
We need to Get T(i) Value for every set of value which means need to generate 24 T(i) Values.
For Example,
T(i) 1st =(2*24-24-1)/2
= 11.5
T(i) 2nd = (2* 23-24-1)/2
= 10.5 and so on TILL 24TH Value.
After getting all T(i) and solving the formula we have got the Trend Value 0.0327
Formula for Basic Value:
Average of all 24-week data (Historical Data) + Trend which you have calculated with the above formula *(24-1)/2
How 23 is calculated basically we need to minus 1 from total number of historical periods which would be 24-1=23
After solving the formula for basic value we have got the value 253.167
Forecast Value Formula:
For forecast Value we would be adding trend in Basic Value.
Here we will be adding the basic value which is 253.167 into Trend Value which is .0327 and will get the first forecast value and for second forecast value will add the same trend value to first forecast value.
Forecast Value 1 253.1992754
Forecast Value 2 253.2318841
Forecast Value 3 253.2644928
Trend Model with standard Initializing Period:
I have used the same data set for every Forecasting Model as maintained above.
Formulas:
For current Basic value: It would be always your first value (current month/week sales) from your data set. As in my case it is 115.
For Previous Basic Values: It would be always your 2ND value (current month/week sales) from your data set. As in my case it is 220.
Trend Formula for previous period or initialization:
Subtract Last(history) value from 3rd last(history) value and divide by 2 i.e.
H(22)-H (24)/2
Here in our case (250-310)/2 = -30
Here we are not finished yet we have to generate trend value for every set of value by applying below mentioned formula.
example starting from 21st value We have to generate trend for each single line/value going upward, as we have already generated the trend value for last three values through other mentioned trend value, which is -30.
Trend Formula for every line (Set of Value)
T(i) = T(i-1) + B * (G(i) - G(i-1) - T(i-1))
G(t-1) Basic Value from Previous Period
T(t-1) Trend value from previous period
b Beta Value
G(t) Basic Value from Current Period.
Trend for 21st value
When we will input values in above formula we will get a 21st trend value -30.40
here we have to add average of all values 286 and first trend value which is -30 to get previous basic value.
G(T-1) = 256
T(t-1) = -30
B = .10
G(t) = 222
Trend for 20TH Value
G(T-1) = 222
T(t-1) = -30.40
B = .10
G(t) = 210
When we will input values in above formula we will get a 20th trend value -28.56
I Have Attached excel File which has working of all trend line values.
Executing MP30 TCODE for single material, can execute mp38 for multiple material or on Plant Level.
Result After executing MP30 System Created 3 Forecast as mentioned below.
After right click on trend value we will be selecting Additional Parameter to see previous period data. As mentioned below
As you can see system is showing us Current Values and previous values.
Forecast Value Formula:
Forecast value 1 97.17
Forecast value 2 79.35
Forecast value 3 61.52
Here we have to add Last trend value which Is -17.826 in last sales value which is 115 and for second forecast value we will be adding the same trend value in the previous forecast value which is 97.17.
Seasonal Model with External Initializing Period:
I will be using the same historical values as I have used in trend Model.
Here I have maintained Forecast Model as ‘S’ And Period/Season 3 that means I have season whose length would be of 3 weeks.
Executing MP30 TCode: Below are the Results
Basic Value = The base value is equal to the mean value from the historical data.
Here we have period length of 3 weeks so we have to add every 3rd value as I have maintained in excel file.
Season Index for first period: The sum of every third value / total number of periods in your data * Average of all data
Forecast Value: Season index*Basic Value
I have segregated the season with different colors in excel so that you can easily interoperate.
Seasonal Model with standard Initializing Period:
Here I have maintained that I have a season of 3 weeks
MP30 TCODE SCREEN
Basic Value:
131.560
Formula For seasonal Trend:
As we have mentioned 3 in period forecast system will only generate 3 seasonal index using the last three value in the data set, in my case values are
| 250 |
| 298 |
| 310 |
To Calculate Seasonal index, we have to follow below mentioned formula.
Seasonal Index: Last value/average of last three values.
i.e. = 310/286
=1.083916
2nd = 298/286
=1.041958
3rd =250/286
=0.874126
Formula for basic Value: Previous Basic Value + Beta* (current basic value/3rd value of seasonal index- Previous Basic Value)
Forecast Values:
Forecast Value 1 142.6
Forecast value 2 137.08
Forecast value 3 115
Seasonal Trend Model with initializing Period:
Have Maintained X as forecast Model, and assuming I have a length of season which is 3.
Executing MP30 TCode: Below are the Results
Formula For calculating Trend:
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption value.
T(i) = T(i) = (2 * i - N - 1)/2
Here i = The number of individual data in a sequence like, 24,23,22,21,20,19 and so on till 1
N= Total number of historic data which is 24 in our case.
For Trend and seasonal Model T(i) Value will remain same for every 3 values as we have maintained period per season as 3.
System will add every 3rd value from the data set we have maintained.
Seasonal Trend Model with standard initializing Period:
Material Master
Maintained X FOR Trend & Seasonal Model.
After Executing MP30/MP38
Showing Additional Parameter where we can see the previous data also.
Here we will consider last 6 values as we have a season of 3 values as mentioned below and we have to consider +3 as well.
| Model | No. of historical values |
| Seasonal trend model | 1 season + 3 |
Formula to calculate trend value :
Sum (V(i) * T(i))
T = ---------------------
Sum (T(i) * T(i))
Where V(i) = is your Data from your consumption value.
T(i) = T(i) = 3/2=1.5
And = T(i)= -3/2=-1.5
For Trend and seasonal Model T(i) Value will remain same for every 3 values as we have maintained period per season as 3.
Trend value =-22.88
When we get our basic trend value through above formula we will calculate the first basic value dependent on first trend value whose formula mentioned below
Average of all values + trend value which we have just calculated above* (6-1)/2
6 because we have a season of three weeks and +3 we have to addas mentioned in above standard chart.
To calculate seasonal index we will use below calculation.
We will calculate 3 seasonal indexes as we have to generate three forecast values
1st value =Basic Trend-(24-19)*basic value
Basic Trend = -22.88
Basic Value 194.44
Value would be = 308.8888889
Now we will divide above calculated value with the actual value of that particular period which would be the 24th value of my table and multiply it by 100
=(310/308.88)
= 1.003597122
Will perform the same activity for every value from 24th till 18th and then add every third value and divide by 2 in result we will get our 3 Seasonal indexes you can refer excel file as well.
For Basic Value :
Where:
G(t-1)= 194.4444444
T(t-1) = -22.88888889
V(t) = 190 which would be the 7th value in descending order.
S(t) = first seasonal index value i.e .963
We will use this formula from 18th value till first value and will get the basic value .
For trend value we will use below mentioned formula
T(t-1) = -22.88
B= 0.1
G(t) = 197 which we have calculated from the above formula from basic value. First, we will calculate basic values then will calculate trend value with the above formula.
G(t-1)=194.44
=-20.33225725 would be our trend value, we will calculate trend value of every single data we have in our data list till the first one starting from 18th value going to 1st value
Formula for Forecast value:
Basic value + trend value * Seasonal Index.
Conclusion
This documents resulting in understanding the process of Forecasting model in a very simple way, which is highly valuable for Manufacturing and related stakeholders.
Your valuable feedback is required.
2 Comments
| User | Count |
|---|---|
| 8 | |
| 4 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 |
