SAP Business Intelligence Integrated Planning (SAP BI-IP) provides the standard allocation formulas which are used for basic allocation requirement. For more complex business cases, standard formulas are not able to fulfill the requirement and in this case FOX which comes with SAP BI-IP can fulfill the complex development requirements.
This blog aims to show the development of FOX formula for sample of specific business case shown below:
Info Provider (for planning purposes):
- Actual (ZACTUAL)
- Plan (ZPLAN)
- Discount Ratio (ZDISCRAT)
- Multiprovider (ZMPPLAN)
Key Figures:
- Sales (ZSALES)
- Trade Promotion (ZTRADPR)
Allocation Rule:
- Trade Promotion (TP) --> top-down planning approach; Company agrees on a yearly TP plan value for each Sales Office.
- Sales --> Actual data of the company is available on all bases.
- Discount --> Actual discount ratios are available on all bases and entered to system manually.
Allocation: Trade Promotion should be allocated to all level of data shown above using Invoiced Sales actual as reference data.
Invoiced Sales = Sales (Actual) - Discount (Actual)
At that point, it is hard and sometimes inefficient to use the standard distribution formula in BI IP. Therefore, the allocation formula is going to be developed as in the below FOX formula.
*****************************************************************
*****************************************************************
*Data Declaration
DATA CY TYPE 0CALYEAR.
DATA CM TYPE 0CALMONTH.
DATA SALES_OFF TYPE ZSALES_OFF.
DATA BRAND TYPE ZBRAND.
DATA MATERIAL TYPE 0MATERIAL.
DATA IN_TOT TYPE F.
DATA TP_TOT TYPE F.
FOREACH SALES_OFF, CY.
*** Yearly TP value***
TP_TOT = {ZTRADPR, #, CM, SALES_OFF, #, #, ZPLAN}.
***TOTAL Invoiced Sales Calculation***
FOREACH CM, BRAND, MATERIAL.
IN_TOT = IN_TOT + {ZSALES, CY, CM, SALES_OFF, BRAND, MATERIAL, ZACTUAL} – ({ZSALES, CY, CM, SALES_OFF, BRAND, MATERIAL, ZACTUAL}*{ZDISCOUNT, CY, CM, SALES_OFF, BRAND, MATERIAL, ZDISCRAT}).
ENDFOR.
***Allocation***
FOREACH CY, CM, BRAND, MATERIAL.
{ZTRADPR, CY, CM, SALES_OFF, BRAND, MATERIAL, ZPLAN} = TP_TOT * ({ZSALES, CY, CM, SALES_OFF, BRAND, MATERIAL, ZACTUAL} – ({ZSALES, CY, CM, SALES_OFF, BRAND, MATERIAL, ZACTUAL}*{ZDISCOUNT, CY, CM, SALES_OFF, BRAND, MATERIAL, ZDISCRAT})) / IN_TOT.
ENDFOR.
***resetting allocation for different sales office and year combination***
IN_TOT = 0.
ENDFOR.
*****************************************************************
*****************************************************************
This FOX Formula above is the sample of how to apply the complex allocation rules. In some cases, the business process is going to be more complicated then the case above. However, by applying the other various useful functionalities of Fox formula like read variable value (instead of defining in for loop), master data attribute read, concatenate, .etc the logic is going to be same in every business process.
Assumption:
- FOX formula in IP is implemented after creating the basic necessary IP processes like aggregation level, filters, etc. as well as BI developments like planning cubes .etc. It is assumed that everything is ready for FOX formula implementation.
Meric Celik SAP BI Consultant in Accenture Istanbul Office.
