meric.celik

Previous post Next post
Currently Being Moderated
Meric Celik

SAP BI-IP : Allocation Using FOX Formula

Posted by Meric Celik in meric.celik on Mar 23, 2009 10:20:55 PM
Allocation is a very fundamental procedure of business planning for a company. Especially in Top-Down planning approach, the planned values are required to be allocated to lower levels which are determined by company for specific businesses purposes.

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:

 

 Table

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.

Comments

Actions

Filter Blog

By date: