Learn SAC Advanced Formula Actions for Common Business Scenarios In No Time

[uladzislau_pralat]

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: