Technical Articles
Learn SAC Advanced Formula Actions for Common Business Scenarios In No Time
If you want to learn advanced formula actions for common business scenario then this blog is for you. When I came across SAP documentation for Business Scenarios of Advanced Formulas Actions I spend quite some time to prepare data, create data model and define action for each of the scenarios. In this blog I want to share my work with you to save you trouble doing routine work and focus on actual understanding business requirements and advanced formula actions.
Here is a list of scenarios for which I provide implementation information:
- Aggregating dimension members into different groups
- Accumulated depreciation planning
- Forecasting HR turnover rates based on historical data
- Intercompany elimination on A/R and A/P
Aggregating Dimension Members into Different Groups
Business scenario is described in SAP documentation. Below you find information how setup it in your system.
Model:
Data and Account dimensions:
Account dimension, Data dimension and transaction data can be downloaded from GitHub. Advanced formula action is defined as follow:
DATA([d/Account]=[d/Account].[p/SUBACC])=RESULTLOOKUP()
IF [d/Account].[p/SUBACC] != "" THEN
DELETE([d/Account]=[d/Account])
ENDIF
Note: I introduced DELETE statement to prevent duplicating data
This is how data looks before running advanced formula action:
and after:
Accumulated depreciation planning
Business scenario is described in SAP documentation. Below you find information how setup it in your system.
Model:
Flow and Account dimensions:
Flow dimension, Account dimension and Transactional Data can be downloaded from GitHub. Advanced formula action is defined as follow:
//Set the scope of date dimension from 202003 to 202012
MEMBERSET [d/Date] = "202003" to "202012"
//Get monthly depreciation value, which equals to acquisition cost / duration of a depreciation
DATA ([d/Account]= "12107000", [d/Flow]= "F_INC") =
RESULTLOOKUP([d/Account]="12103000", [d/Flow]= "F_INC", [d/Date]= "202003") / RESULTLOOKUP([d/Account]="90007000", [d/Flow]= "#", [d/Date]= "202003")
////Loop with the defined date dimension scope, in this case it is from 201803 to 201812
//FOREACH [d/Date]
// //Calculate the ending balance
// DATA ([d/Account]= "12107000", [d/Flow]= "F_CLO") =
// RESULTLOOKUP ([d/Account]= "12107000", [d/Flow]= "F_OPE") + RESULTLOOKUP ([d/Account]= "12107000", [d/Flow]= "F_INC")+ RESULTLOOKUP ([d/Account]="12107000", [d/Flow]="F_DEC")
// //Carry forward previous closing value to current month's opening value
// DATA ([d/Account]= "12107000", [d/Flow]= "F_OPE", [d/Date]= NEXT()) = RESULTLOOKUP([d/Account]="12107000", [d/Flow]= "F_CLO" )
//ENDFOR
DATA() = CARRYFORWARD([d/Flow] ,"F_OPE" ,"F_CLO" ,"F_OPE"+ "F_INC"+"F_DEC" )
Note: I improved the script using more performance efficient statement CARRYFORWARD instead of FOREACH. You can play with both. End result is the same in both cases.
This is how data looks before running advanced formula action:
and after:
Forecasting HR turnover rates based on historical data
Business scenario is described in SAP documentation. Below you find information how setup it in your system.
Model:
Account dimension:
Account dimension and Transactional Data can be downloaded from GitHub. Advanced formula action is defined as follow.
Step 1:
MEMBERSET [d/Account] = "CLOSING"
MEMBERSET [d/Date] = "202101" to "202112"
DATA([d/Account] = "CLOSING") = RESULTLOOKUP ( [d/Account]="CLOSING", [d/Date] ="202012")
Step 2:
MEMBERSET [d/Account]=("TURNOVERS","CLOSING")
FOREACH [d/Date]
//Before performing any calculations, copy the closing amount of the prior period to the opening of current period.
DATA ([d/Account]= "OPENING") = RESULTLOOKUP ([d/Account]= "CLOSING", [d/Date]= PREVIOUS())
//Forecast turnover number of current period based on the historical turnover rate of the same period last year. current year’s turnover rate = current year’s opening amount * (previous year’s turnovers / previous year’s opening amount)
DATA ([d/Account]= "TURNOVERS") = ROUND(RESULTLOOKUP ([d/Account]= "OPENING") * RESULTLOOKUP ([d/Account]= "TURNOVERS", [d/Date]= PREVIOUS (12)) / RESULTLOOKUP ([d/Account]= "OPENING", [d/Date]= PREVIOUS (12)),0)
//Update current period’s closing amount by subtracting turnovers from the opening amount
DATA ([d/Account]= "CLOSING") = RESULTLOOKUP ([d/Account]= "OPENING") - RESULTLOOKUP ([d/Account]= "TURNOVERS")
ENDFOR
This is how data looks before running advanced formula action:
and after:
Intercompany elimination on A/R and A/P
Business scenario is described in SAP documentation. Below you find information how setup it in your system.
Model:
Account, Audit, Interco_Entity and Entity dimensions:
Account dimension, Audit dimension, Interco_Entity dimension, Entity dimension and transaction data can be downloaded from GitHub. Advanced formula action is defined as follow:
CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = ON
MEMBERSET [d/Date] = "202001" TO "202012"
MEMBERSET [d/Audit] = "10"
IF [d/Account] = ("BSA_IC_AR" , "BSA_IC_AP") Then
DATA ([d/Entity] = ELIMMEMBER ([d/Entity].[h/HIERARCHY], [d/Entity] , [d/Interco_Entity], [d/Entity].[p/ELIMINATION] = "Y" ), [d/Audit] = "30" ) = RESULTLOOKUP () * -1
DATA ([d/Entity] = ELIMMEMBER ([d/Entity].[h/HIERARCHY], [d/Entity] , [d/Interco_Entity], [d/Entity].[p/ELIMINATION] = "Y" ), [d/Account] = [d/Account].[p/ELIMACC], [d/Audit] = "30" ) = RESULTLOOKUP ()
ENDIF
This is how data looks before running advanced formula action:
and after:
Thank you very much for your blog. It is very helpful
I am searching for sample data for the planning module. Are thre any ressources?
Hi Thorsten,
I would recommend to check following resources that come with realistic sample data:
Regards, Uladzislau
Seems like you are taking the SAP's documentation to the next level.
Great and easy-to-understand examples.
Thank you, Uladzislau!
Thanks so much for your blog.
I have been looking for sample data to practice the advances formula in data action. This provides me with a way better understanding of calculation scope and logic.
Thank you, Uladzislau.