Business Scenario
In most Business Scenarios, we will have requirements to do calculations such as Working Capital, Days Sales Outstanding etc. for the users to analyze their needs. These KPI’s may sometimes be complex calculations which involves math, requiring many Base Level as well as Parent Level members too. For this purpose, Script Logic may be used but it impacts the performance adversely and using Dimension Formulae may literally be impossible if the calculation involves many members say around 50. The work-around for this scenario would be to use the functionality of Account Transformation combined with simple Dimension Formula to calculate these KPIs.
Introduction
Consider the scenario where we require calculating Total Working Capital. Working Capital is calculated as Current Assets minus Current Liabilities. Let’s assume that Current Assets and Current Liabilities are not being maintained as dimension members and each of Current Assets and Current Liabilities by themselves involves adding different Base level and Parent Level accounts each. For this purpose, we can achieve the calculation by avoiding complex Script Logic. The performance too would be really good following this approach.
Pre-Requisite
- Knowledge of Account Transformation Business Rule.
How To Procedure:
STEP1:
Create 3 Members in Account Dimension namely:
WCASSET – Working Capital Assets and is defined as AST Account Type.
WCLIAB – Working Capital Liabilities and is defined as a LEQ Account Type.
WORKINGCAPITAL – Total Working Capital and is defined as AST Account Type.
Since WCLIAB is already defined as LEQ Account, Working Capital here would be
WORKINGCAPITAL = WCASSET + WCLIAB
STEP2:
Prepare the list of accounts by asking the end users comprises the WCASSET and WCLIAB. In this case WCASSET is a combination of 9 Accounts and case WCLIAB is a combination of 8 Accounts which involve many Parent Level Accounts too which in turn contain many Base Level Accounts. These are then maintained in the Account Transformation Business Rule Table.
A separate Transformation group will be maintained for these (in this case the Transformation Group is WORKINGCAPITAL) so that this calculation does not affect any cash flow calculations which are normally done using Account Transformation Business Rules.
The Source data source, Destination account, Destination Data Source etc. will depend upon the Business needs. If any of the dimensions are not present in the Application, they can be ignored, the functionality is still valid.
Here all the accounts which comprise of WCASSET are first copied into Destination Account WCASSET and similarly all the accounts which comprise of WCLIAB are copied into Destination Account WCLIAB. The Reverse Sign is checked for WCLIAB calculations to record the liabilities directly with negative signage.
STEP3:
The standard Account Transformation Script Logic File CALCACCOUNT.LGF must be used to create a new Logic File. In this case the new Logic File is named as CALCWORKINGCAPITAL.LGF
The code must be tweaked to mention the Transformation Group mentioned when we maintained the Business Rules for WCASSET and WCLIAB. In this scenario, it is WORKINGCAPITAL. This ensures that this process is independent of any standard Account Transformation Business Rules that are being enforced for the purpose of Cash Flow etc.
STEP4:
A Data Manager Package must be created in BPC Excel to enable the user running this calculation whenever necessary. This can again be created from the Standard process Chain provided for Account Transformation Business Rule /CPMB/RUNCALCACCOUNT. The only tweak that has to be done using the Advanced Script Logic Editor is to the Logic File name since we created a logic file CALCWORKINGCAPITAL.LGF as a new Script Logic.
The package is now ready for the users to run it whenever necessary. The result after running this package would be that the WCASSET and WCLIAB would be calculated. WORKINGCAPITAL is then calculated using the Dimension Formula maintained in the Master Data as mentioned in STEP 1.
WORKINGCAPITAL is calculated on the fly as just the sum of these two. This improves the performance of reports by a great extent as we are using just a simple addition as against a complex dimension formula if we had not used the functionality of Account Transformation Business Rule.
This technique can be generalized to calculate many regular KPI requirements like Days Sales Outstanding, Net Revenue etc. depending on the business requirements.
