Computing proportional factor in a user defined horizon

[Former Member]

We use proportional factors in our planning to dis aggregate any key figure data in the desired proportions. Here in this blog , I would be showing how we can compute Fixed proportional factors a set of planning objects using the subtotal feature. With this blog I will try to explain as how we can achieve the same using IBP functionalities like Attribute Based subtotals (new feature in SAP1711) , a local member and a macro.

With 1711 release of IBP, there has been added a new functionality of attribute based subtotals which gives the lanner one more eye of having a deeper analysis based on an attribute or a group of attributes. It provides us the total of all the KF values for all the attributes for which the planner chose to display the totals.

SAP help : https://help.sap.com/viewer/b7a719e8ae9c4c91a57698ffcaaf659d/1711/en-US/941d9f598d694944ab11acc3bf3f...

Let’s take a simple example to understand this feature , If a product PROD1 is being sold to 3 different customers CUST1 , CUST2 and CUST3.

And if planner choses to display the totals of Actuals Qty for the attribute Customer ID the result will be as shown below.

The subtotal is shown for all the customers in the respective time buckets



Pre-requisite : We need 1711 excel Add in to use this feature.

Configuration of Sub totals :

1. We need to set a global parameter MAX_SUB_TOTALS to a value which equals the maximum number of attributes for which you want to display subtotals.

In  IBP system , go to Model Configuration App-> Manage Global Parameter -> and define a new           parameter as below:

2. In the planning View , go to Edit Planning View and on the planning level Screen for the selected attributes , Select Total Before / Total After for the attribute for which you want to display the subtotals.

 

Total Before : Total is displayed in (Total) row before all the attributes (Like in our example)

Total After : Totals is displayed in (Total) row after all the Attributes.

Uses of subtotals : The planners can you this feature to perform volume analysis , proportional factor calculation or any other decision making based on their business needs.

 

1. In our example we display the Total of all the KFs by adding one more period of type Total and Label it Total.

So our planning View looks like



 

2. Now we create a stored KF Proportional Factor at same level as the Sales Fcst Revenue and keep it as “All Editable”. We will use this KF to store the value of proportional factor computed at a later stage.Our Planning View will look now as:

 

3. Now we add a local member in this planning view which calculates the proportion factor for each Product Customer Combination based on the logic:

(Total of one individual Cust/Prod in the planning horizon Feb18-Jan19)/

(Total of all Cust/Prod in the planning horizon Feb 18 – Jan 19 )

For eg : For  Customer CA03 and IBP-300 its 20.67 M/41.34 M.

The Local member formula is as shown below:



Please note that we are putting a condition to populate the value in the same row as we have the key figure “Proportional Factor”

So upon adding the local member we get the planning view as:



Please note that I am displaying the Proportional factor Value in %.

4. Now we add a button , using Developer addin,

 

Name it Proportional Factor

And upon adding you will be asked to write the macro , I have used the below code:



As per the above code , Upon press of the button the values of the Proportional factor computed in step 3 will be filled in all the fields of the Key figure “Proportional Factor” as below



Now you can save the data in the database , click Save data.



In this way you can make use of local member and macro to write back to database. Later you can use the values of the KF Proportional factor as a basis for the dis aggregation in the KF settings of other key figures which you want to dis aggregate.

 

Hope you like the approach.

Please share your feedback.

 

Regards,

Anurag