Business Scenarios of Advanced Formulas Actions

Aggregating Dimension Members into Different Groups

Background

You can use advance formula to aggregate value by a certain property. For example, if you want to aggregate six account members (Acc01 to Acc06) into two different categories (SUM01, SUM02).

Account	ID	Acc01	Acc02	Acc03	Acc04	Acc05	Acc06
	Sister	SUM01	SUM01	SUM01	SUM02	SUM02	SUM02

Write this script: Data([d/ACCOUNT] =[d/ACCOUNT].[p/Sister]) = ResultLookup()

Then the ResultLookup() returns the record set below, according to the scope. The "Sister" field does not really exist; it’s only for reference.


Account	Account Sister*	Plant	Audit	Product	Time	SignedData
Acc01	SUM01	#	None	16GB	201801	100
Acc02	SUM01	#	None	16GB	201801	200
Acc03	SUM01	PLT01	Manual	16GB	201802	2500
Acc04	SUM02	PLT01	Manual	64GB	201802	7000
Acc05	SUM02	PLT01	Manual	64GB	201802	4000
Acc06	SUM02	PLT01	Manual	64GB	201802	5000

Finally, the [d/ACCOUNT] field will be updated by the [p/Sister] attribute, due to the Data definition, and returns the result like this:


Account	Plant	Audit	Product	Time	SignedData
SUM01	#	None	16GB	201801	300
SUM01	PLT01	Manual	16GB	201802	2500
SUM02	PLT01	Manual	64GB	201802	16000

Accumulated Depreciation Planning

Background

At the beginning of year 2018, Company A plans to purchase a vehicle in March for 480 dollars. The life of the vehicle is estimated to be 5 years, and the net residual value will be 0.

Corresponding master data members for the Account and Flow dimension are maintained as below:

Account Dimension
ID	Description	Account Type	Hierarchy
12103000	motor vehicles	AST	12100000
12107000	accumulated depreciation - motor vehicles	LEQ	12100000
90007000	depreciation month	NFIN	90000000

Flow Dimension
ID	Description	Hierarchy
F_CLO	closing flow
F_DEC	decrease flow	F_TOT
F_INC	increase flow	F_TOT
F_OPE	opening flow	F_TOT
F_TOT	total flow

The value of the vehicle was input in the F_INC row:


Account	Flow	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Aug 18	Sep 18	Oct 18	Nov 18	Dec 18
12103000 (motor vehicle)	# (Unassigned)
	F_TOT	$480	$480	$480	$480	$480	$480	$480	$480	$480	$480
	F_DEC
	F_INC	$480
	F_OPE		$480	$480	$480	$480	$480	$480	$480	$480	$480

Duration of the depreciation was input into the depreciation month row:


ACCOUNT	FLOW	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Aug 18	Sep 18	Oct 18	Nov 18	Dec 18
90007000 (depreciation month)	# (Unassigned)	60.00

Write the script below in an advanced formulas step to perform accumulated depreciation in 2018:

Sample Code

//Set the scope of date dimension from 201803 to 201812
MEMBERSET [d/Date] = "201803" to "201812"
//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]= "201803") / RESULTLOOKUP([d/ACCOUNT]="90007000", [d/FLOW]= "#", [d/Date]= "201803")

//Loop with the defined date dimension scope, in this case it is from 201803 to 201812
FOREACH [d/Date]
  //Carry forward previous closing value to current month's opening value
  DATA ([d/ACCOUNT ]= "12107000", [d/FLOW]= "F_OPE") = RESULTLOOKUP([d/ACCOUNT="12107000", [d/FLOW]= "F_CLO", [d/Date]= PREVIOUS())
  //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/ACOUNT]="12107000", [d/FLOW]="F_DEC")
ENDFOR

Results

Finally, the Accumulated Depreciation will return the calculated result below:


Account	Flow	Mar 18	Apr 18	May 18	Jun 18	Jul 18	Aug 18	Sep 18	Oct 18	Nov 18	Dec 18
12107000 (accumulated depreciation - motor vehicles)	# (Unassigned)
	F_CLO	$8	$16	$24	$32	$40	$48	$56	$64	$72	$80
	F_DEC
	F_INC	$8	$8	$8	$8	$8	$8	$8	$8	$8	$8
	F_OPE		$8	$16	$24	$32	$40	$48	$56	$64	$72

Forecasting HR Turnover Rates Based on Historical Data

Background

The HR manager of Company A wants to plan the turnover rate target in 2019 in a headcount planning scenario. She discusses this with her group members and decides the best way to do headcount planning in 2019 is to refer to the 2018 historical turnover rates and apply them to corresponding 2019 plan data.

She sends this request to the IT specialist and the IT specialist sets up the data model accordingly and create a data action for her.

Data Model

As usual, the IT specialist adds the Account, Time and P_DATASRC dimensions to the model. For Account dimension, he maintains a new dimension member HEADCOUNT. For P_DATASRC dimension, he maintains two different dimension members Load and Stored Logic Calculation to trace the source of data changes.

After that, the IT specialist creates a special dimension MOVEMENT to separate different types of headcount changes (such as hiring and turnover number) and different balance types (such as opening balance and closing balance). Members and attributes of the MOVEMENT dimension are maintained as below:

Data Actions

Then he creates a data action in the application composed of three data action steps:

In the first copy data action, he wants to copy data from Actual version to Plan version in 2018 Dec. Then he writes two advanced formulas actions to first populate data for the whole year of 2019 for movement type Closing with 2018 December’s closing flow, then calculate the turnovers in 2019 by multiplying previous year’s termination rates by the current opening balance. During the process, each month’s closing balance is carried forward to the next month’s opening balance.

Step 1: Copy from version Actual to Plan

In the first copy action step, he filters ACTUAL for the Category dimension so that data can be read from actual and then copied to the current plan version. The target plan version is not defined here in this step. It will be chosen by the end user when triggering the data action in a story.

In the copy rules, he changes the audit dimension P_DATASRC from Load to Stored Logic Calculation so that data manually entered by the end users can be separated from data generated during the execution of the data action.

Step 2: Initiate 2019 headcount data

In the second advanced formulas step, he writes the script below:

Sample Code

CONFIG.GENERATE_UNBOOKED_DATA = OFF

MEMBERSET [d/ACCOUNT] = "HEADCOUNT"
MEMBERSET [d/MOVEMENT] = ("CLOSING", "#")
MEMBERSET [d/Time] = "201901" to "201912"
MEMBERSET [d/P_DATASRC] = "AT_SAC_CALC"

DATA ([d/MOVEMENT]= "CLOSING") = RESULTLOOKUP ([d/MOVEMENT]= "CLOSING", [d/Time]= "201812")

The advanced formulas populates the headcount numbers for the upcoming 2019 plan periods and uses the closing headcount number of 2018 Dec. as the initial values.

Step 3: Recalculate and forecast 2019 turnovers

In the last advanced formulas step, he writes the script below:

Sample Code

CONFIG.GENERATE_UNBOOKED_DATA = OFF

MEMBERSET [d/ACCOUNT] = "HEADCOUNT"
MEMBERSET [d/MOVEMENT] = ("CLOSING", "TURNOVERS")
MEMBERSET [d/Time] = "201901" to "201912"
MEMBERSET [d/P_DATASRC] = "AT_SAC_CALC"

//FOREACH is necessary here for the calculated result in one period to be used in the calculation of the next period
FOREACH [d/Time]
  //Before performing any calculations, copy the closing amount of the prior period to the opening of current period.
  DATA  ([d/MOVEMENT]= "OPENING") = RESULTLOOKUP ([d/MOVEMENT]= "CLOSING", [d/Time]= 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/MOVEMENT]= "TURNOVERS") = RESULTLOOKUP ([d/MOVEMENT]= "OPENING") * RESULTLOOKUP ([d/MOVEMENT]= "TURNOVERS", [d/Time]= PREVIOUS (12)) / RESULTLOOKUP ([d/MOVEMENT]= "OPENING", [d/Time]= PREVIOUS (12))
//Update current period’s closing amount by subtracting turnovers from the opening amount
  DATA  ([d/MOVEMENT]= "CLOSING") = RESULTLOOKUP ([d/MOVEMENT]= "OPENING") - RESULTLOOKUP ([d/MOVEMENT]= "TURNOVERS") 
ENDFOR

After the IT specialist completes these 3 steps, the data action is successfully created. Then HR manager triggers the data action in the story and gets the following forecast data (take the first quarter of 2019 as an example):

From the table she sees that values in 2018 December in both Actual and Plan are now the same. This is the result of the copy data action. Also, the opening balance of different locations in each month equals to the previous month’s closing balance in 2019. This is a result of the carry forward calculation designed in step 3. She confirms the data to be correct and sends the data to other HR colleagues for further adjustments.

Intercompany Elimination on A/R and A/P

Background

In the preparation of this year’s consolidated accounts, CFO Mary of Company A wants to eliminate all intercompany A/R and A/P transactions between subsidiary companies in the group and show the elimination amount in the report.

She sends this request to the IT specialist and the IT specialist set up the data model accordingly and create a data action for her.

Data Model

Besides the standard Time and Version dimension, the IT specialist customizes the Account, Entity, Interco_Entity and Audit dimension according to the business needs.

For the Account dimension, he defines two account groups for A/R and A/P. Each group contains a parent member and two sub account members respectively created for external transactions with third-party companies and intercompany transactions inside the group. He also introduces an attribute ELIMACC to indicate which account should be used to post intercompany elimination results and show intercompany elimination differences.

For the Audit dimension, to separate the data loaded by manual entry before the elimination and data generated after elimination, he maintains dimension members and attributes as below:

For the Entity dimension, he introduces a new elimination member for each of the parent entities and use an additional attribute ELIMINATION to mark these elimination members as “Y” so that corresponding intercompany elimination value will be posted to these members. The Interco_ENTITY dimension contains all the base-level members of the ENTITY dimension.

Data Actions

Then he creates a data action and add the advanced formulas below to it:

Sample Code

CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = ON

MEMBERSET [d/Time] = "201801" TO "201812"
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/Eliminiation] = "Y" ), [d/Audit] = "30" ) = RESULTLOOKUP () * -1
  DATA ([d/Entity] = ELIMMEMBER ([d/Entity].[h/Hierarchy], [d/Entity] , [d/Interco_Entity], [d/Entity].[p/Eliminiation] = "Y" ), [d/Account] = [d/Account].[p/ELIMACC], [d/Audit] = "30" ) = RESULTLOOKUP ()

In the advanced formulas, first the “flipping sign according to account type” configuration is turned on. Different account types, namely AST and LEQ in this case will return positive and negative value respectively. Then the IF condition checks whether the current account is either intercompany A/R or intercompany A/P, both are the accounts used for intercompany elimination. If true, the formula will execute the following two statements:

In the first statement the negative value is posted to the same account in IF condition but to a different AUDIT ID (the one used for intercompany elimination) and a specific Entity dimension member returned by the ELIMMEMBER function. It’s a member below the first common parent of [d/ENTITY] and [d/Interco_ENTITY] having the specified attribute value "Y". As a result, the intercompany business is eliminated when reporting on the Audit member TOTAL.
The second statement posts the same value to the elimination member but to a different account defined by the attribute ELIMACC which shows the intercompany difference. Due to the same attribute value BS_ICDIFF, all A/R and A/P eliminated value will be aggregated and should balance to zero if all intercompany entries are reconciled.

After the IT specialist completes the data action, CFO Mary opens her story and the original data displayed as below:

She executes the data action offered by the IT specialist. In the story, she tests the effect of the data action for intercompany elimination: In the table accounts payable is 300€ between USA and Germany and 200€ between Germany and France. Values on the account receivables side is vice versa. In total there’s 1500€ of accounts payable and 1200€ of account receivable on group level before elimination. After elimination, there’s only 1000€ AP and 700€ AR on group level. 500€ AP/AR intercompany transactions have been eliminated. Since we do not have any intercompany differences at the group level, the account Elimination AP/AR balances to 0€. The number is correct.

Then she further drills down into the entity dimension to see the individual elimination results. It turns out that the AP/AR intercompany transactions between France and Germany is eliminated on the parent Europe, whereas AP/AR intercompany transactions between Germany and USA is eliminated on the level Global, the common parent of Germany and USA.