Pre-processing of historical data before forecasting in SAP IBP by using substitution method
As we all know that , we need to have historical data i.e. sales qty or consumption qty to get accurate forecast in supply chain planning. if we have any gap or any missing data in historical data, it will impact on accuracy of forecasting values.
Since i want to cover only substitution part in this blog, I have consider Historical periods only. (if you want to forecast after preprocessing, you can maintain forecast period values also)
We can perform substitution by using three methods i.e Mean, Median & Given value.
Here I have taken Mean as method for substitution so that system will consider “Actuals Qty” as input value and it get the mean value. After processing system post the values in “Actual Qty adj”.
Mean: system will consider the actual qty from last 52 weeks and get the mean value as substitution value and update in missing key figure value in relevant time periods.
Now go to Excel add on —–Open planning view — Application jobs—-Statistical forecasting.
Select forecast model to be used “Substitution_ForecastMode, enter target UOM and click on next icon.
After job run, system calculated the mean value of existing actual qty values and updated the key figure values in “actual qty Adj” as we configured in forecast model.
Now will see how system work Median option in forecast profile. For this, we need to select “Median” as Method used for substitution in forecast model as below
Now run the job as we did in previous step.
Now go to Excel add on —-Open planning view — Application jobs—-Statistical forecasting.
After job run, system calculated the median value of existing actual qty values and updated the key figure values in “actual qty Adj” as we configured in forecast model.
When we take “Mean” as method of substitution, the actual qty adj for FG900-1010-CUST001 are 178, 178, 178 but when we use “Median” as method of substitution, key figure values are updated as 172, 172, 172 for last three weeks.
Now will see how system work “Give Value” option as method of substitution in forecast profile. Fore this, we need to select “Given value” as method of substitution in forecast profile as below and we need to enter the value so that system will substitute blank values with “Given value” as per forecast profile.
Here i mentioned 200 as given value so that system should replace blank value with 200 after job run. now will run the job by using following steps.
Now go to Excel add on —-Open planning view —Application jobs—-Statistical forecasting.
Since i had given 200 as given value in forecast profile, system updated actual qty adj as 200 wherever key figure value missing.
This is how SAP IBP helps to planner to get substitution values for key figure (Actuals Qty).
Thanks & Regards
Sr. Consultant PP/QM/IBP