Statistical forecasting is a demand planning process to predict the numbers of anticipated sales in a future time horizon based on past data. In forecasting there are different methodologies to arrive at forecast numbers. One of them is regression analysis of the past data points of a variable based on what forecast numbers are calculated. There are two types of variables. One is an independent variable and another is a dependent variable.
In regression analysis the linear relationship is established between dependent and independent variables to consider the impact of the independent variable on the forecast. If there is one independent variable used in forecasting method, then that method is called a simple linear regression. If there are multiple independent variables, then that method is called multiple linear regression. SAP Advanced Planning and Optimization (SAP APO) has two different forecasting models, one for simple linear regression and another for multiple linear regression. SAP Integrated Business Planning provides a built-in forecasting model for multiple linear regression only, but there is no forecast model based on the linear regression method.
We show the use of a multiple linear regression model with one variable to work as a simple linear regression model in SAP Integrated Business Planning.
Configure the Data Model
Log in to the SAP Integrated Business Planning cloud and open the Model Configuration group of apps. To complete this step, click the triangle to the right of Communication Manager in the menu bar and select Model Configuration from the drop-down list of options (Figure 1).
Select Model Configuration in the main SAP Integrated Business Planning apps screen
This action displays the screen shown in Figure 2.
Model Configuration apps
In Figure 2, click the Configuration tile to open the Configuration app shown in Figure 3. Model Configuration apps help you create a planning model in SAP Integrated Business Planning. A planning model describes what are the planning levels, input—output information, how data is stored, and calculation.
Configuration showing links for planning model entities
In the Configuration app click the Attributes link and create attributes for customer, location, and product dimensions (Figure 3). For the sake of simplicity, in our example, model one attribute of each dimension—Z9CUST, Z9LOC, and Z9PROD.
Click the Create icon (Figure 4).
Create an attribute
This action opens the Add Attribute section shown in Figure 5.
Populate fields in the Add Attribute section
In Figure 5 add information in the Attribute ID, Description, Data Type, and Length (in terms of digits) fields as shown in Figure 6 and then click the Add button (circled).
Create additional attributes
Similarly add attributes for Z9LOC and Z9PROD. Enter Z9 in the search field. This action lists the three attributes that are added (Figure 6). Note the suffix “*” after each Attribute ID. This suffix signifies these attributes are not yet saved.
Click the save icon (circled) to save the attributes. Note that suffix “*” disappears after the attributes are saved (Figure 7).
Attributes signify the planning levels in the planning model
After you create the attributes, click the Master Data Types link in Figure 7. A master data type is a logical group of the attributes. Click the create icon circled in Figure 8.
Click the create icon to create a master data type
This action displays the Create Master Data Type window in which you enter the name of the master data type in the ID field (Figure 9). Click the OK button after you enter the name.
Enter the name of the master data type
Enter Product in the Name* and Description fields and select all product-related attributes (in our example, for simplicity, only one attribute related to product is created, but there can be multiple attributes for a product) as shown in Figure 10.
Save the master data type
Click the save icon (circled in Figure 10) to save the master data type. You can then see the the master data type in Figure 11.
The Master Data screen for Z9PRODUCT
Similarly, create other master data types for Z9LOCATION and Z9CUSTOMER as shown in Figures 12 and 13.
The Master Data screen for Z9LOCATION
The Master Data screen for Z9CUSTOMER
Create a Time Profile
After you create Master Data Types, click the Time Profiles button in Figure 13. This action displays the screen in Figure 14 in which you click the create icon to create a Time Profile.
Create a Time Profile
This action displays the Create Time Profile window (Figure 15) in which you enter a value for the Time Profile in the ID field and then click the OK button. The Time Profile created for our example is 996.
Enter a value for the Time profile
In the next screen (Figure 16) the time frame between the Start Date and End Date of the Time Profile indicates the period within which planning is done.
Add periodocity levels in the Time Profile
Click the add new level icon (the plus sign circled in Figure 16) to add period levels in the time bucket profile (Figure 17).
Add period levels in the time bucket profile
In the Name column enter MONTHLY, QUARTERLY, and YEARLY for Levels 1, 2, and 3, respectively. Choose a period type for Base level 1 and 2 (for Base Level 0 it is not mandatory to enter a period type ) and then click the save icon (Figure 17).
Create a Planning Area
After you create a Time Profile, click the Planning Area and Details link (Figure 18). Create and activate Planning Area ZLRPA with Time Profile 996 and a Storage Time Profile Level as MONTHLY.
Planning Area and Details – Planning Horizons
In Figure 18 click the planning horizons icon (indicated by pointer 1). This action opens the pop-up screen shown in Figure 18. Maintain From and To columns for every periodicitiy as shown by pointer 2. Negative values denote periods in the past with the current day as reference. Click the OK button to save the planning horizons for the planning area.
In Figure 19 click the planning operator icon as shown by pointer 1. This action opens the Assign Planning Operators screen. Select the STATFORECASTING (statistical forecast SAP standard operator) indicator as indicated by pointer 2 in Figure 19. Make sure that you select this indicator before activating the planning area. This step enables statistical forecasting in the SAP Integrated Business Planning, add-on for Microsoft Excel. Click the OK button.
Planning Area and Details – Operator selection for forecasting
(The dark area in Figure 19 is the parent screen from which child screen shows up. Once the child screen closes, then the parent screen will look bright.)
To activate planning area ZLRPA, click the activate icon (the green arrow pointing toward the right). To track the status of this planning area, click the full log icon next to the green arrow icon (Figure 20).
Planning area activation and details
Create a Planning Level
After you create a planning area, click the Planning Levels link in Figure 21 to create the planning PERPRODLOCCUST (period product location customer) with root attributes as shown in Figures 21 and 22. This is the planning level at which the statistical forecast is planned by the forecast run.
Create a planning level
Planning level showing time profile, customer location, and product master data
In Figure 21 click the create icon as shown by pointer 1. This action opens the Create Planning Level pop-up screen. Enter the planning level name as PERPRODLOCCUST as shown by pointer 2 and click the OK button as shown by pointer 3.
In Figure 22, check the indicators in the Root column for every Master Data type. In this case MONTHLY is selected as the root for the time profile, Z9CUST (customer) is selected as the root attribute for customer Master Data, Z9LOC (location) is selected as the root attribute for location Master Data, and Z9PROD (product) is selected as the root attribute for product Master Data. Click the save icon.
Create Key Figures
Key figures are the quantitative input and output for the planning model. Examples of key figures are Sales Forecast, Marketing Forecast, and Consensus Demand Plan.
After you create a planning level, create four stored key figures. Click the Key Figures link in Figure 22.
This action opens the screen in Figure 23.
Creation of the key figure
Click the create icon shown in Figure 23. In the pop-up screen select the Key Figure radio button, provide a Key Figure ID as ZC9ORDHIST, and select the Base Planning Level as PERPRODLOCCUST. Click the OK button. This action displays the screen in Figure 24.
Details needed for the creation of the key figure
Enter data in the following fields in Figure 24:
1. Name and Description: In the Name and Description fields, enter a short description and a long description, respectively. In my example, enter ORDER HISTORY in both fields. These names will be seen in the Excel add-in Planning views.
2. Stored, Calculcated, or Alert Key Figure check boxes: Input key figures are stored, and key figures based on a formula are calculated. In addition the Alert Key Figure check box can be selected, and Alert key figures can only have the values 0 or 1, meaning that the alert itself is either ON or OFF. The Snapshot Key Figure check box is by default grayed out. However, if the key figure was created from the Manage Snapshot window, then the Snapshot Key Figure check box in the Key Figure window is enabled. For our example, the key figures are stored, so select the Stored check box.
3. Edit Allowed: This field determines editable properties of the key figure. You can click this field and select the following options from the drop-down list:
a. Not Editable – Mostly for key figures that are to be blocked for modification such as order history
b. Editable by System Algorithm – Only the system planning algorithm can edit the key figure (for example, sales forecast)
c. Editable in the Current or Future – The key figure can be editable by a user or the system in current or future time buckets (for example, Adjustment for a forecast)
d. Editable in the Past – The key figure that is editable in the past (for example, adjustment in order history)
e. All Editable – The key figure is open to change in past, current, and future buckets.
In our example, the order history is kept Not Editable.
4. Aggregation Mode: In this field you can determine the rule for aggregation of the values from lower planning levels to higher planning levels. Aggregation is done by summing numbers at the lower planning level as this is quantitative or numeric data. But if it would have been a ratio, the aggregation would have been Average. Disaggregation mode is available only for key figures for which any of the Edit Allowed options is selected except for Not Editable. In the Disaggregation field, you can select the following options:
a. Proportional if aggregated value is not zero; otherwise, equal distribution. This option is typically used for quantity and revenue key figures.
b. Copy Value – Copy value at higher level
c. Equal Distribution
d. Proportional if the aggregated value is not zero; otherwise, copy value. This option is typically used for price and cost key figures, for example, CONSENSUSDEMANDPRICE and COSTPERUNIT.
Click the input key figure icon as shown by pointer 5. This action opens the Input Key Figures pop-up screen. This screen shows all the key figures in the planning area as input key figures, out of which the relevant ones are to be chosen. Select the Stored Value check box and click the OK button as shown in Figure 25.
Input key figures
This action opens the screen in Figure 26. Click the save icon to save the changes for the key figure.
Key Figure ZC9ORDHIST
You can repeat the steps described above (Figures 22-26) to create other key figures as per the properties listed in Table 1.
Stored, calculated ?
To store sales history of product
Stored and calculated
Proportional if aggregated value is not zero; otherwise, equal distribution
To store statistical forecast results
Proportional if aggregated value is not zero; otherwise, equal distribution
Maintain the value of an independent variable that is used for linear regression
Create a key figure to capture the forecast error
Key figures details
Figure 27 shows the details for key figure ZC9STATFORECAST.
Key figure ZC9STATFORECAST
Figure 28 shows the details for key figure ZC9INDVAR.
Key Figure ZC9INDVAR
Figure 29 shows the details for key figure FORECASTMAPE.
Key Figure FORECASTMAPE
Configure a Forecast Model
To configure a data model, search for the applicable group Demand Planner. To complete this step, click the triangle to the right of Communication Manager and select Demand Planner from the drop-down list of options (Figure 30).
Select Demand Planner from the list of options in the main SAP Integrated Business Planning apps screen
This action displays the screen in Figure 31 in which you click the Manage Forecast Models tile.
The Manage Forecast Model app
In the Manage Forecast Models app, click the Create button at the bottom of the screen (Figure 32).
Click the Create button in the Manage Forecast Models screen
This action opens a small window that shows a list of planning areas available (Figure 33). To select the planning area ZLRPA that was activated, search for the planning area by entering the planning area ID (ZLRPA) in the Search field. In the pop-up screen that appears, double-click the highlighted Planning area ID to select it.
Select a planning area
Define General Parameters
After you select the planning area, the system automatically opens the screen shown in Figure 34. Populate the Model Name and Description fields. In the Time Settings section, enter general parameters such as Periodicity, Historical Periods, and Forecast Periods (Figure 34). (You don’t need to click the Save button in this step.)
Assign the general parameters
After assigning the general parameters, click the PREPROCESSING STEPS link (Figure 35). In the preprocessing steps, you can define algorithms for outlier corrections. However, for our purposes, no algorithm assignment is necessary. Outlier corrections are generally needed when there is an unusual sales history due to non-repetitive events. For example, if there is a hurricane in a specific sales region, then sales of certain products may be unusually high and it may not happen every year. So to forecast for the future such an outlier is removed from the history so that it does not influence the forecast for the same period in the future. This is beyond the scope of the article as there is nothing new about this functionality in SAP Integrated Business Planning.
Click the FORECASTING STEPS link in Figure 35 to assign the overall parameters and forecast algorithms. In the screen that appears (Figure 36), enter parameters for the following fields:
- Main Input for Forecasting Steps: ORDER HISTORY
- Target Key Figure for Forecast: STATISTICAL FORECAST
For the purpose of this article leave the following parameters blank:
Consider Product Lifecycle Information
Target Key Figure for Ex-Post Forecast
After you assign the overall parameters, click the + icon in Figure 36. This action opens a pop-up window in which you select the Multiple Linear Regression algorithm from the available list (Figure 37).
Add a forecast algorithm
After you select the Multiple Linear Regression algorithm, the screen in Figure 38 appears. Specify the key figure in the Independent Variable field value that is used for linear regression. For the purpose of generalization, in our example we named the key figure INDEPENDENT VARIABLE. Leave the Variable Selection field with the default value of None.
Assign Independent Variable Key Figure
In postprocessing steps you can configure the forecast error measuring methods. To complete this step, click the POSTPROCESSING STEPS link (Figure 39). Assign the key figure FORECAST MAPE to the Mean Absolute Percentage Error (MAPE) method. To complete this step, select FORECAST MAPE in the Target Key Figure for MAPE field and select the check box for Mean Absolute Percentage Error (MAPE). After you click the Save button, you receive a message that forecast models has been successfully updated.
Assign a forecast error key figure and save the screen
To execute the statistical forecast using the Multiple Linear Regression model in SAP Integrated Business Planning, log in to SAP Integrated Business Planning, add-in for Microsoft Excel to work with planning views (Figure 40).
Log in to the SAP Integrated Business Planning, add-in for Microsoft Excel
After logging in to the SAP Integrated Business Planning, add-in for Microsoft Excel, click the New View button to load the planning view parameters (Figure 41). You can use the From Template if the template has been created or you can load Without Template On Current Sheet. For our example, choose the From Template option with template Multiple Linear Regression Statistical Forecasting that has been custom created rather than a standard template from SAP.
Select the planning view template
After selecting the template, select the Time, Planning Level, Key Figures, and Filter for executing the Multiple Linear Regression Statistical forecast as shown in Figures 42 to 45.
In Figure 42 select the Time Period as MONTHLY to view the data in MONTHLY buckets. Select the From and To periods to define the horizon. Select Yes for Rolling. Rolling signifies the rolling horizon concept where current date is considered as planning start date so every day the start date changes or keeps rolling.
Select a Time Period in the Time Settings section
Do not click the OK button yet. Click the Planning Level tab in Figure 43. Search and select the attributes as shown in Figure 43. Do not click the OK button yet. Click the Key Figures tab.
Select planning levels
In Figure 44 search and select the Key Figures and do not click the OK button yet. Click the Filter tab.
Select the key figures
It is possible to add filters based on planning levels.Click Filter and then select the drop-down symbol in the field below Attribute as shown in Figure 45. This action shows all the attributes checked in Figure 43.
Addition of attribute in the filter
Select one attribute by clicking it. Add another attribute if needed by clicking the Add Attribute button (Figure 46) and select other attributes.
Click the Add Attribute button to input more filters
In Figure 47 select the drop-down symbol below Operator Values. The options under Operator Values can be = or ≠.
Select operator either = or ≠
As shown in Figure 48 it is possible to enter values manually.
Select values for the attributes
These values are attribute values, so in Figure 48 Product = IBP100, Location = IBPLOC1, and customer = IBPCUST1 is the filter. Now click the OK button to go to an Excel view. The Excel view shows data only for these values, not for other products, customers, or locations.
After selecting all the above (Time, Planning Level, Key Figures, and Filter), click the OK button. The planning view template displays the attributes and key figures selected on the planning horizon (Figure 49).
Planning View with data
Note that the key figure ORDER HISTORY has been uploaded with historical data using the Data Integration app. This app is available in SAP Integrated Business Planning with which data can be uploaded to key figures and Master Data types.
In Figure 49, the key figure INDEPENDENT VARIABLE has been uploaded with series data starting from 1 in the first bucket (Jan 2016) of the historical horizon until 24, which is the last bucket of the forecast horizon (Dec 2017).
The key point is that for Linear Regression, this independent variable should be populated with a linearly increasing series. This is how SAP APO uses linear series internally, which is not visible to the user in the Linear Regression Strategy 94 nor configurable in the application front end. Strategy 94 is pre-configured out of the box unique number to do linear regression in SAP APO.
Now click the Statistical Forecasting button on the top of the screen and click the Run button to run the forecast (Figure 50).
Run the statistical forecast
Clicking the Run button opens the window shown in Figure 51 in which you define the aggregation level and model to be executed. In the example in Figure 51, the PRODUCT, LOCATION, and CUSTOMER check boxes are checked to selecte these attributes for executing the Multiple Linear Regression model.
Forecast model and aggregation level selection
After selecting the forecast model and aggregation level, you select the filter product for which the model should be executed and click the Run button (Figure 52). In a current scenario only product IBP100 is selected as the filter on an Ad Hoc basis. You can have the forecast run for one of the products on a need basis as this is ad hoc run. Generally, there are filters defined as per the requirements of business that can be used to execute the run.
Select the filter product for the forecast model
After you click the Run button, the batch job is scheduled and a message appears in a pop-up window as shown in Figure 53.
A message about batch job scheduling appears in the pop-up window
The status of the batch job can be tracked as per Figures 54 and 55. Click the Statistical Forecasting dropdown and select Status as shown in Figure 54.
The batch job status menu
This shows the status of all the statistical forecasting runs taken (Figure 55).
The batch job status details
Once the batch job is complete, you can see the status of the last run executed as Completed. Close the log window by clicking the Close button. This action displays the screen in Figure 56. Click the Refresh Button in Figure 56.
Click the planning view Refresh button
After refresh you can see in Figure 57 that the statistical forecast using the Multiple Linear Regression algorithm for linear regression has been generated as expected.
Linear regression statistical forecast using a Multiple Linear Regression forecast with Linear Regression (Strategy 94) in SAP APO
Considering the same set of order history data, the forecast is generated using linear regression (Strategy 94) in SAP APO (Figure 58). These forecast numbers are exactly the same as in SAP Integrated Business Planning, add-in for Microsoft Excel.
The purpose of the forecast data in SAP APO is to show that the forecast in Key Figure Stat Fcst: Rev in Figure 58 from SAP APO is the same forecast as in Key Figure STATISTICAL FORECAST in Figure 57 of SAP Integrated Business Planning. The entire article from Figure 1 to Figure 57 is about using SAP Integrated Business Planning + Excel Add-In to mimic what is shown in Figure 58 of the SAP APO screen.
Forecast using Linear Regression in SAP APO