Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Creating SAP IBP Formulas

Objective

After completing this lesson, you will be able to understand the concept of SAP IBP Formulas

SAP IBP Formulas

SAP IBP formulas (formerly called Local members), are like a key figure that are not read from the back end. These key figures can be created in front end in the SAP IBP, Microsoft Excel Add-in, and calculated using a Microsoft excel formula.

Creating SAP IBP formulas is easy, but it is worth taking stock of what you plan to achieve and being aware of their limitations.

SAP IBP formula based key figure values are not read from the back-end and do not exist in the configuration. Neither the values from these key figures can be extracted, for example, using Cloud Integration Data Services. Also, you cannot use them in the Advanced Analytics application.

SAP IBP formulas can be created in SAP IBP Planning views by an expert user and added to templates. Since no configuration is needed hence, there is no dependency on transports, freeze periods, and so on. It's a good option for a larger SAP Integrated Business Planning for Supply Chain implementation, where the number of key figures Grew over the cycle of implementation. To that point, some of the applications for the SAP IBP formula could be "niche" processes, where planners must be aware of certain input or conditions. The SAP IBP formulas, in that case, can support some custom calculations to help with data Analysis.

Certain requirements like to work with specific rows and columns in SAP IBP Microsoft Excel Planning Views can be easily achieved by SAP IBP formulas and not with configuration.

SAP IBP formulas are flexible and can be attached to either columns or to the row axis or both. The formulas can also be attached to the members, member combinations, and dimensions as needed. You can decide the priority if there are several elements attached to SAP IBP formula.

SAP IBP Formulas Tab

Note

SAP IBP formulas feature can be accessed with SAP IBP Microsoft Excel Add-in version 2411 or higher.

The SAP IBP Formulas tab can be found in the Create Planning View and Edit Planning View dialogs of SAP IBP Microsoft Excel Add-in.

The SAP IBP Formulas tab contains advanced capabilities to create, activate, edit, and delete formulas.

The SAP IBP Formulas tab can be accessed only if you have the template administrator rights or permission type Formulas Operation Type is assigned.

Note that you can also add an SAP IBP Formulas in the SAP IBP Microsoft Excel Add-in planning view. When you activate the Formula Recognition, all Microsoft Office Excel formulas in the planning grid are automatically converted into Local Members. You can then view and modify the local members in formula tab of the Edit Planning View dialog. Technical naming of the local members starts from "LocalMember001" and then increases by 1 ("LocalMember002"), and so on.

SAP IBP formula recognition is an option that can be found in Formulas tab of Create Planning View or Edit Planning View dialog. Under section Options, select the Activate Recognition of Formulas select the check box to activate.

If SAP IBP planning views which has SAP IBP Formulas are modified, ensure to adjust the SAP IBP Formulas as needed and set the status to Active. Validation of SAP IBP Formulas takes place only if the formula status is set to active.

Few of the functions available to use in SAP IBP Formulas definition in SAP IBP Microsoft Excel Add-in are IBPMEMBER, IBPDIMENSIONMEMBERS, IBPHEADER, IBPPOSITION.

Create SAP IBP Formulas

Business Example

Your manager has asked you to create the calculation to update the planning view when the Sales Forecast is being updated interactively. The goal is to display a delta between the Sales Forecast Qty and the Consensus Demand Qty and calculate the delta value in percentage.

ExerciseStart Exercise

Note

When the data values for the exercise include ##, replace ## with the group number that had been provided to you.

Steps

Using the ChartVBA_Embedded template, create a new planning view containing Consensus Demand and Sales Forecast Qty key figures for next 12 months for your products for the Series2 product subfamily.
1. If you have the favorite from the previous exercise open, close it, and in the Planning View section of the SAP IBP ribbon, choose New View → From Template. Then from the drop-down, select the ChartVBA_Embedded template.
2. Choose MonthTime Periods starting from the current period to 12 months in the future, for example, Oct ‘24 to Sep ‘25.
3. Select Attributes and choose Product Sub-Family.
4. Select Key Figures and choose the following key figures:
  - Consensus Demand.
  - Sales Forecast Qty.
  - Choose a Currency as EUR.
  - Choose a Unit of Measure as EA.
5. Select Filter and first choose the My Group Products filter ## that you created previously then choose Add Attribute. Then choose the Series 2Product Sub-Family.
6. Choose OK to view the report.
7. Hide the graph by selecting the Collapse button in cell A3.
Create SAP IBP Formula to calculate difference between Consensus Demand and Sales Forecast Quantity and display it in SAP IBP Excel Add-in.
1. In the Planning view section of SAP IBP ribbon, choose Edit View → Edit Planning view.
2. Select the Formulas tab, then click on Create Formula button. A Pop-up screen Create Formula appears.
3. Enter the Name as Difference and Header as Difference In Qty in respective columns.
4. Now, specify the required to formula to calculate the difference between Consensus Demand and Sales Forecast Qty key figures in the column Formula under Calculation section. The exact formula to be entered is mentioned within punctuation:
  "=IBPMEMBER([KEY_FIGURES].[].[CONSENSUSDEMAND])-IBPMEMBER([KEY_FIGURES].[].[SALESFORECASTQTY])"
5. Select the position details to specify where the calculated values must appear in SAP IBP Excel Add-in. Under the Position section, select option 'Member' in Attached to column and select option 'After' in Insert column.
6. In the column next to Member, click on three dots to bring up the selection screen.
7. Under Dimension, select 'Key Figures' option from drop-down and select 'Sales Fcst Qty' under key figures section and chose OK to exist selection screen.
8. Ensure that the newly created formula is set to active status, by selecting the Active button at the top-left corner of the screen.
9. Choose OK to close the Create Formula screen.
10. Choose OK to return to the Planning view.
Display the Delta value in percentage using the SAP IBP Formula Recognition option in SAP IBP Excel Add-in.
1. In the Planning view section of SAP IBP ribbon, choose Edit View → Edit Planning view.
2. Select Formulas tab. Select the check box 'Activate Recognition of Formulas' under options section.
3. Choose OK to return to the Planning view.
4. Just under the last row of your planning view, after the Difference In Qty key figure, enter the description Difference % and hit, Enter.
5. In the first data column, enter the following formula by referring to the cells of the key figures: Difference in Qty/Sales Forecast Qty%.
  Use the value of the Key figures for the formula from the rows.
6. Choose Enter and wait for the formula to be replicated to all the columns in the current row.
7. In the Planning view section of SAP IBP ribbon, choose Edit View → Edit Planning view.
8. Choose Formulas tab and observe that a new local member 'LocalMember001' being generated.
9. Click on pencil icon of Local member to view the formula that has been generated and settings such as position details. Choose OK to exit the edit local members screen.
10. Choose OK to return to the Planning view.
11. To save your planning view as Favorite, choose Favorites and choose Add.
12. Enter a relevant name for your Favorite, and choose Add.

Continue to quiz