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).

Figure 1

Select Model Configuration in the main SAP Integrated Business Planning apps screen

This action displays the screen shown in Figure 2.

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.

Figure 3

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).

Figure 4

Create an attribute

This action opens the Add Attribute section shown in Figure 5.

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).

Figure 6

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).

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.

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.

Figure 9

Enter the name of the master data type

Enter Product in the Name* and Description fields and select all product-related attributes as shown in Figure 10 (in our example, for simplicity, only one attribute related to product is created, but there can be multiple attributes for a product—for example, product group and brand).

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.

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.

Figure 12

The Master Data screen for Z9LOCATION

Figure 13

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. 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. 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.

Figure 14

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.

Figure 15

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.

Figure 16

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).

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.

Figure 18

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.

Figure 19

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).

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.

Figure 21

Create a planning level

Figure 22

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.

Figure 23

Creation of the key figure

As shown in Figure 23 click the create icon. 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.

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.< p/>

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:

• Not Editable – Mostly for key figures that are to be blocked for modification such as order history
• Editable by System Algorithm – Only the system planning algorithm can edit the key figure (for example, sales forecast)
• 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)
• Editable in the Past – The key figure that is editable in the past (for example, adjustment in order history)
• 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 had 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 Mode field, you can select the following options:

• Proportional if aggregated value is not zero; otherwise, equal distribution. This option is typically used for quantity and revenue key figures.
• Copy Value – Copy the value at the higher level
• Equal Distribution
• 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.

Figure 25

Input key figures

This action opens the screen in Figure 26.

Figure 26

Key Figure ZC9ORDHIST

Click the save icon to save the changes for the key figure.

You can repeat the steps described above (Figures 22-26) to create other key figures as per the properties listed in Table 1.

Figure 27 shows the details for key figure ZC9STATFORECAST.

Figure 27

Key figure ZC9STATFORECAST

Figure 28 shows the details for key figure ZC9INDVAR.

Figure 28

Key Figure ZC9INDVAR

Figure 29 shows the details for key figure FORECASTMAPE.

Figure 29

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).

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.

Figure 31

The Manage Forecast Model app

In the Manage Forecast Models app, click the Create button at the bottom of the screen (Figure 32).

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).

Figure 33

Select a planning area

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.

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.)

Figure 34

Assign the general parameters

Preprocessing Steps

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.

Figure 35

Preprocessing steps

Forecasting Steps

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.

Figure 36

Overall parameters

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).

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.

Figure 38

Assign Independent Variable Key Figure

Postprocessing Steps

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.

Figure 39

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).

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.

Figure 41

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.

Figure 42

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.

Figure 43

Select planning levels

In Figure 44 search and select the Key Figures and do not click the OK button yet. Click the Filter tab.

Figure 44

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.

Figure 45

Addition of attribute in the filter

This action shows all the attributes checked in Figure 43. Select one attribute by clicking it. Add another attribute if needed by clicking the Add Attribute button (Figure 46) and select other attributes.

Figure 46

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 ?.

Figure 47

Select operator either = or ?

As shown in Figure 48 it is possible to enter values manually. 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.

Figure 48

Select values for the attributes

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).

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).

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.

Figure 51

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.

Figure 52

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.

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.

Figure 54

The batch job status menu

This shows the status of all the statistical forecasting runs taken (Figure 55).

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.

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.

Figure 57

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.

Figure 58

Forecast using Linear Regression in SAP APO

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.