Consider the following use case:

A customer would like to be able to automate its Cumulative Translation Adjustment (CTA) account within the Equity section of its Balance Sheet account hierarchy. The problem is, the definition of the calculation takes the difference of Total Assets, Total Liabilities, and Total Equity, and essentially plugs the result to make the Balance Sheet Equation balance. Since the CTA account sits within the Equity rollup, however, it creates a circular reference.

Assumptions:

  • The CTA calculation account must remain a part of the reporting equity hierarchy.
  • The CTA calculation results still must be reviewed for reasonability.
  • The balance sheet should then always balance.

Methodologies:

  • Design an alternative hierarchy to create an ‘Equity Net of CTA’ rollup.
  • Adjust the CTA calculation account to be the difference between Total Assets, Total Liabilities, and Equity Net of CTA.

Background on cumulative translation adjustments

Translation adjustments occur when there is a difference between exchange rates at the time of a transaction (historical exchange rate) and the rate at the time of reporting (current exchange rate). For example, an asset is acquired and booked at a value of $30,000. Between the time of the booking and the next reporting period, the exchange rate changes and the current valuation becomes $33,000. To keep the balance sheet in balance, the $3,000 increase on the asset side must be offset by an equal increase in owners’ equity on the other side of the balance sheet.

Automating CTA

Step 1 – Create an Alternate Hierarchy

As a best practice, we’ve already created a separate hierarchy under our account dimension named ‘Alt_Hierarchies – Alternate Hierarchies for Reporting or Calculation Purposes.’ Within this new branch, we’re going to create a new parent member named ‘Equity_Net_of_CTA – Equity Net of CTA for CTA Calculation’. Because we know we can use most of the equity section, we can copy the top members en masse. The account we will need to remove later is in the ‘Acc_OCI’ rollup shown below. We can select all the highlighted members shown, right-click on them, and choose ‘Copy Selected Members’.

Automating CTA Calculations - Step 1

Next, select the ‘Equity_Net_of_CTA’ parent we created earlier, right-click again, and choose ‘Paste Relationships (Add)’ – ‘As First Child’.

Automating CTA Calculations - Paste Relationship

NOTE: Be careful to select the correct option. Adding relationships creates shared members. Selecting ‘Paste Relationships (Move)’ would physically relocate the selected accounts out of their current hierarchy.

Now we can navigate through the hierarchy to select the ‘CTA_Calc’ member, right-click it, and select ‘Remove Relationships’.

That account is no longer in the new alternative rollup, which allows us to use this new hierarchy in the member formula calculation for the ‘CTA_Calc’.

Because we need ‘CTA_Calc’ to be a consolidating member in its hierarchy, we cannot make it a Dynamic Calc Formula Type. In OneStream, dynamic calculations do not roll up in account hierarchies, and should be used only for statistical or reporting accounts that do not affect reporting hierarchies. Review your hierarchy structure and any other calculated accounts to determine which FormulaPass should be used. For our purposes, we are using FormulaPass12.

Note: Since this is a calculated member and we don’t want anyone to override the calculated data, we are changing the ‘Allow Input’ option to False.

For the formula, we will call the ‘Total Assets’, ‘Total Liabilities’, and ‘Equity Net of CTA for CTA Calculation’ members and do a simple calculation formula.

‘plugs the difference between Assets vs. Liabilities and Equity

Dim targetStr As String = “A#CTA_Calc:F#EndBal:I#None:O#Import:U1#CTA:U2#None:U8#None”

Dim assetsStr As String = “A#Assets:F#EndBal:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”

Dim liabStr As String = “A#Liabilities:F#EndBal:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”

Dim equityStr As String = “A#Equity_Net_of_CTA:F#EndBal:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”
‘this equity hierarchy does not include the CTA calc member

api.data.calculate(targetStr & ” = “ & assetsStr & ” – ( “ & liabStr & ” + “ & equityStr & “)”)

For our reporting, we now can pull the regular hierarchy rollup and see the results, forcing the balance sheet to balance properly. Drilling into the top member, we can see the ‘CTA_Calc’ contribution to review. In addition, if we add the ‘Formula for Calculation Drill Down’ (below), we can further drill-down to see the calculation components.

Automating CTA Calculations-Formula for Calculation Drill Down

Dim result As New DrillDownFormulaResult()

result.Explanation = “Formula Definition: Total Assets – (Total Liabilities + Total Equity Net of CTA)”

result.SourceDataCells.Add(“A#Assets:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”)

result.SourceDataCells.Add(“A#Liabilities:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”)

result.SourceDataCells.Add(“A#Equity_Net_of_CTA:I#Top:O#Top:U1#Top_Data_Sources:U2#Top_Departments:U8#None”)

Return result

As an added measure, checking the results of the calculation for reasonability should be added to the confirmation or certification process for the workflow and/or added to Close Manager or Task Manager for verification and sign-off.

Want to learn more information? Find more information on how to leverage OneStream.

By Michel Sabourin

Talk to one of our CPM experts today.