Write Business Scenarios for Planning with Advanced Formulas Actions

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).

Background

Data Model

Data Actions

He then creates a data action and calculates IRR for different projects using FOREACH and FOR statements in the advanced formula step.

MEMBERSET [d/Date] = "2021" TO "2032"
MEMBERSET [d/ACCOUNT] = "CF"
MEMBERSET [d/PROJECT] = ("PRJ001", "PRJ002", "PRJ003", "PRJ004")

INTEGER @iCounter
INTEGER @iStart = 1
INTEGER @iEnd = 10
INTEGER @IRRInitial = 0

VARIABLEMEMBER #N OF [d/ACCOUNT]
VARIABLEMEMBER #PreviousIRR OF [d/ACCOUNT]

// Get a value by using a number parameter converted by an INT() function.

@iEnd = INT(%pEnd%)

DATA([d/ACCOUNT] = #N, [d/Date] = "2021") = @IRRInitial
DATA([d/ACCOUNT] = "IRR", [d/Date] = "2021") = @IRRInitial

// Set the periods of time from 0 to n.

FOREACH [d/Date]
	DATA([d/ACCOUNT] = #N, [d/Date] = NEXT()) = RESULTLOOKUP([d/ACCOUNT] = #N) + 1.0
ENDFOR

FOR @iCounter = @iStart TO @iEnd // Iterate to find IRR.
	// Define NPV.

	DATA([d/ACCOUNT] = "NPV", [d/Date] = "2021") = RESULTLOOKUP([d/ACCOUNT] = "CF") / POWER((1.0 + RESULTLOOKUP([d/ACCOUNT] = "IRR", [d/Date] = "2021")), RESULTLOOKUP([d/ACCOUNT] = #N))

	// Define the differential formula of NPV.

	DATA([d/ACCOUNT] = "NPV_D", [d/Date] = "2021") = -1 * RESULTLOOKUP([d/ACCOUNT] = #N) * RESULTLOOKUP([d/ACCOUNT] = "CF") / POWER((1.0 + RESULTLOOKUP([d/ACCOUNT] = "IRR", [d/Date] = "2021")), RESULTLOOKUP([d/ACCOUNT] = #N) + 1.0)

	// Calculate IRR. 
	DATA([d/ACCOUNT] = "IRR", [d/Date] = "2021") = RESULTLOOKUP([d/ACCOUNT] = "IRR", [d/Date] = "2021") - RESULTLOOKUP([d/ACCOUNT] = "NPV", [d/Date] = "2021") / RESULTLOOKUP([d/ACCOUNT] = "NPV_D", [d/Date] = "2021")

	// In order to avoid unnecessary iterations, compare the IRR values of the previous and current iterations and exit the iteration if the values are the same. 
	IF RESULTLOOKUP([d/ACCOUNT] = #PreviousIRR, [d/Date] = "2021") = RESULTLOOKUP([d/ACCOUNT] = "IRR", [d/Date] = "2021") THEN   
		BREAK
	ENDIF

	// Store the value of the current IRR in #PreviousIRR for the next iteration
	DATA([d/ACCOUNT] = #PreviousIRR, [d/Date] = "2021") = RESULTLOOKUP([d/ACCOUNT] = "IRR", [d/Date] = "2021")
ENDFOR

Finally he gets the following calculated results.

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:

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:

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

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:

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.