Consider the following use case:
A customer would like to be able to see which intercompany accounts are related to each other in its intercompany reporting. This will allow the company to better understand the flow of balances as they move up the corporate hierarchy. While there is intercompany matching in the workflows, it’s not always easy to get such a holistic look at an organization.
Assumptions and Methodologies
- Cube Views were created to show all intercompany accounts and how the balances eliminate between entities.
- Intercompany accounts are suffixed with ‘_IC’.
- Account rows can show the plug account used for each account to show how they tie together.
- There is dynamic-reporting functionality to catch any new intercompany accounts and pairings.
- UD8 is used for reporting purposes.
- Create a UD8 member to retrieve a given account’s assigned plug account.
- Incorporate this UD8 member into the reporting.
A little background on intercompany
Intercompany transactions are transactions between separate entities in the system. As a best practice, these accounts are suffixed with ‘_IC’ to make them easier to identify. Each intercompany account needs another associated intercompany account (or accounts) for the contra activity; in other words, a yin for every yang.
For example, an account named 11000_IC – Intercompany Accounts Receivable holds an expected asset coming from another entity within the corporate entity’s hierarchy, so there should be a matching liability (e.g., 21000_IC – Intercompany Accounts Payable) sitting at another entity that goes with it. These two accounts also should share the same plug-account designation. As a best practice, we generally associate the plug account with the liability side of the equation. In this example, the plug would be labelled 21000_IC and be suffixed with ‘_Plug’ or ‘_Suspense’ as desired). In addition, the intercompany transformation rule for each entity should tag the intercompany partner for the transaction(s). Intercompany transaction balances eliminate at the first mutual parent.
How it works
Company B in the hierarchy above has a debt payable to Company C, and both roll up into Company A. In this scenario, Company B would have the account 21000_IC transaction tagged as an intercompany partner of Company C. Company C would then book the 11000_IC transaction to intercompany partner Company B. As they roll up into Company A, those two transactions would offset and eliminate to Company A’s balance sheet. If it were Company D and Company E, those balances would eliminate at Company B, whereas Company D and Company H wouldn’t eliminate until Company A, as that would be its first mutual parent.
While there are some built-in reports around an intercompany, they don’t always show the whole story. For our purposes, we have created cube views to show how the transactions move as they go up the hierarchy.
In the example shown below, the intercompany accounts and Origin dimension are broken out. For Entity P_4200_GBP at account 12400, it eliminates its child E_4301_GBP with the help of a Journal Entry and 21200 eliminates E_4201_GBP at P_4200_GBP.The suspense account is highlighted in the row title. This is not native to OneStream. Instead, user-defined dimension eight is used to retrieve that information. This can help a company understand which of its intercompany accounts are interrelated.
To get the Plug Account (in this example, the company labels these with ‘_Suspense’) into the row description, we must first create our UD8 member, which we’ve named ‘SuspenseAccount’; the formula for this account checks to make sure the View dimension is set to ‘Annotation’ and the account being evaluated is an intercompany account – if IC Account is set to True. If it is, the formula (shown in the bottom-left corner) returns the ‘Plug Account’ from the account settings.
Now we can utilize this member in our cube view.
The reporting needs to be dynamic to account for any new intercompany accounts that could be added in the future. In the row parameter, ‘Account’ is used as the ‘Primary Dimension Type’; in the ‘Member Filter,’ a ‘where’ clause is used as a filter only for intercompany accounts. The first part of the filter looks like this:
A#Balance_Sheet.base.where(IsIC = True). Please note the spaces before and after the equal sign are necessary for the where clause to work.
To get the Suspense information to show in the account row line, the ‘Name’ function is used in the member filter. In this case, ‘|MFAccountDesc|’ is pulling the member filter account and pulling its description. For this user, the description includes the account number. If both are needed, you use ‘|MFAccount| – |MFAccountDesc|’ instead. To this, an ‘xfcell’ function is added using the account, a view member of ‘Annotation,’ and the UD8 member to pull the UD8 result:
A#Balance_Sheet.base.where(IsIC = True):Name(|MFAccountDesc| Suspense: xfcell(A#|MFAccount|:V#Annotation:U8#SuspenseAcct))
The resultant cube-view rows now include the Suspense account names. In the example below, interrelated accounts are boxed in red and blue.
Want to learn more information? Find more information on how to leverage OneStream.