Write Business Scenarios for Planning with Advanced Formulas Actions

In this chapter we want to show you how to leverage advanced formulas to realize some common business scenarios including:

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
Calculating Equipment Depreciation

Company A plans to purchase a sedan in February 2021 for 20,000 dollars. The useful life of the vehicle is estimated to be 12 months, and the net residual value will be 0.

The initial vehicle cost without depreciation data
The initial vehicle cost without depreciation data

With this information, you can calculate the depreciation expense and residual book value for your equipment over its useful life.

Sample Code
//Set the scope of date dimension to include the useful life of all your equipment.
MEMBERSET [d/Date] = "202101" TO "202212"

INTEGER @Useful_Life
INTEGER @Iteration

//Repeat this calculation for each piece of equipment (in this case, just the sedan).
FOREACH [d/Equipment]
  @Iteration = 0
  @Useful_Life = ATTRIBUTE([d/Equipment].[p/Useful_Life])

//Repeat this calculation for each date period in scope, in this case from 202101 to 202212.
FOREACH [d/Date]

//After calculating depreciation for the entire useful life, the remaining calculations for this piece of equipment will be skipped.
      IF @Useful_Life <= @Iteration THEN
	  BREAK
	ENDIF

     //Calculate opening balance, depreciation expense, and residual balance for the first period.
	IF RESULTLOOKUP([d/ ACCOUNT] = "COST") > 0 THEN
	  DATA([d/ACCOUNT] = "OPEN_BAL") = RESULTLOOKUP([d/ACCOUNT] = "COST")
	  DATA([d/ACCOUNT] = "Depreciation_Exp") = RESULTLOOKUP([d/ACCOUNT] = "OPEN_BAL") / ATTRIBUTE([d/Equipment].[p/Useful_Life])
	  DATA([d/ACCOUNT] = "RESIDUAL_VALUE") = RESULTLOOKUP([d/ACCOUNT] = "OPEN_BAL") - RESULTLOOKUP([d/ACCOUNT] = "Depreciation_Exp")
	  @Iteration = @Iteration + 1
	ENDIF

     //Calculate the opening balance, depreciation expense, and residual balance for a subsequent period.
	IF RESULTLOOKUP([d/ACCOUNT] = "COST") = null THEN
	  DATA([d/ACCOUNT] = "OPEN_BAL") = RESULTLOOKUP([d/Date] = PREVIOUS(1), [d/ACCOUNT] = "OPEN_BAL")
	  DATA([d/ACCOUNT] = "Depreciation_Exp") = RESULTLOOKUP([d/ACCOUNT] = "OPEN_BAL") / ATTRIBUTE([d/Equipment].[p/Useful_Life])
	  DATA([d/ACCOUNT] = "RESIDUAL_VALUE") = RESULTLOOKUP([d/Date] = PREVIOUS(1), [d/ACCOUNT] = "RESIDUAL_VALUE") - RESULTLOOKUP([d/ACCOUNT] = "Depreciation_Exp")
	  IF RESULTLOOKUP([d/ACCOUNT] = "OPEN_BAL") > 0 THEN
	      @Iteration = @Iteration + 1
	  ENDIF
	ENDIF
  ENDFOR
ENDFOR
The results of the depreciation script
The results of the depreciation script
Note

This script has better performance when it includes fewer pieces of equipment and fewer periods of useful life, since there will be fewer repetitions of the FOREACH functions. For details, see Optimize Advanced Formulas for Better Performance.

Calculating Internal Rate of Return (IRR)

Background

To choose and invest in the most promising project among four candidates, CFO Mary of Company A plans to make the decision by ranking these projects based on the results of IRR calculation. She sends this request to an IT specialist who sets up a data model accordingly and creates a data action with an advanced formula step for her.

The internal rate of return (IRR) is the discount rate that makes the net present value (NPV) of a project zero. You can use advanced formula to calculate IRR based on Newton’s method:

Data Model

Besides the standard Version dimension, the IT specialist customizes the Time, Account, and Project dimensions according to the business needs.

He defines a Time dimension spanning from 2021 to 2032, with its granularity level set to year.

For the members of Account dimension, he defines IRR (Internal Rate of Return), NPV (Net Present Value), NPV_D (Differential Formula of NPV), and CF (Cash Flow).

For the members of Project dimension, he defines four different projects, namely PRJ001 (Project A), PRJ002 (Project B), PRJ003 (Project C), and PRJ004 (Project D).

He also enters data of projected initial investment and cash flows for four projects in the 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.

Sample Code
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.

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.