Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
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.



















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(iG(i-1T(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
Labels in this area