NetSuite Currency Translation II

Learn a Technique to Consolidate Foreign Currency Subsidiary Legacy Balances

Currency Translation Rates revisited

In an earlier article, I presented the various types of currency concerns and the difference between Currency Revaluation and Currency Translation in NetSuite. This article will address a complex problem (and solution!), but this is not going to make sense to users without familiarity of the basics. If you are not yet comfortable with these topics, first check out SuiteAnswers article 7733 or SuiteAnswers article 7734 – unless you have trouble falling asleep. To me, this topic is exciting; but to non-accountants, well, it may be right up there with paperclip collections.

Migrating Consolidated Financial Statements

Situation Background

An international company using NetSuite OneWorld needed to onboard a group of foreign currency subsidiaries into NetSuite from other systems. These subsidiaries had been purchased and operating under the new corporate ownership for some time, and therefore there had been several periods’ worth of consolidated financial statements already issued. We needed to bring in the beginning trial balances, but also needed to make sure that the beginning balances agreed with the published financial history at the corporate consolidated level.

NetSuite Consolidation

The financial activity (income statement) and position (balance sheet) of foreign currency subsidiaries needs to be expressed in the currency of the parent entity to produce meaningful consolidated statements. NetSuite’s consolidation engine uses 3 translation rate types to accomplish this conversion: “Current”, “Historical”, and “Average” (if this is unfamiliar, please see NetSuite Support article 7734 and my earlier article first). These “types” are defined at the account level. The specific, actual rates used for each “type” are defined in tables that are unique to each Subsidiary/Parent pair, for each accounting period. In other words, if you want to know what translation rate will be used to convert a particular GL account balance, you would need to see what “Rate type” is set on the Account record, then navigate to Lists > Accounting > Consolidated Exchange Rates and then select a Period, and a “From” and “To” Subsidiary. You will then see 3 rates, one for each type mentioned above.

The Challenge

Back to our story: We were presented with trial balances in both original base currencies and in USD amounts (the corporate parent entity currency). The Asset and Liability amounts had been converted using a uniform rate, as could be expected. Typically (most) Balance Sheet accounts are translated using a “current” or “spot” rate, applied to the balance as of the period end. However, Equity and Income Statements accounts balances expressed in the foreign base currencies were not uniformly translated to USD with one rate. Rather, each account’s balance was expressed in USD using a different exchange rate. (This was not surprising, given the philosophy behind the translation types and account types, but that’s beyond the scope of this article.) This presented a difficulty – NetSuite only allows ONE rate to be used for all Income Statements accounts, and likewise one rate for all Equity accounts, yet we essentially needed a different rate for each individual account to get both the base currency amounts and USD consolidated amounts correct. See illustrated example below for an Australian Dollar subsidiary of a USD parent entity. Note that the subsidiary’s base currency is Australian Dollars:

NetSuite’s Currency Translation Engine

Can’t you just “plug” the amounts?

Clients are often confused when I explain problems with Consolidated Currency amounts, since other Accounting platforms provide the ability to simply adjust the amounts expressed in the parent’s base currency. The reason this cannot be done in NetSuite can be illustrated by the below diagram:

Note that although we have full control over the transactions that hit the Subsidiary’s GL, they must be expressed initially as AUD (in the diagram’s example), and they then go through consolidation to hit the Parent entity’s reporting. We cannot be selective about the translation rates for individual accounts. The most we can do is set the rates for the group of accounts that share the same consolidated rate type.

The Solution: Control over Currency Translation

Two important considerations

The solution to this problem required some unconventional thinking: Instead of bringing in the AUD base currency amounts and trying to work with the rates to get the consolidated amounts (which is actually impossible mathematically, since the accounts used multiple rates), we needed to work backwards from the USD-translated amounts (since we have the least control over these) and then work out how to bring in the AUD base currency amounts to yield the USD balances yet keep those AUD balances correct. One more important discovery about NetSuite is that we can set consolidation rates to near zero, thus avoiding any translation impact (more about that below). Here are the steps to solve:

1. Start with the amounts needed in the parent entity’s currency (in our case, USD amounts). These amounts will be our anchor, since we have the least direct control over these amounts.
2. Next, calculate the overall rates for Historical and Average types. Essentially, this will be a weighted average across all accounts that use each of the two types. We need to do this so that the Trial Balance JE will balance out (beyond the scope of this article, but you’ll need to trust me for now – or try yourself!).
3. Calculate backwards using the derived rates to get to the base currency amounts that will yield the desired parent entity’s currency amounts. We know these are “wrong” in the base currency, but we also know that the consolidated amounts will be correct, based on 1. and 2. Bring in these amounts in the period needed.
4. Set the average and historical consolidation rates in a prior period to near zero (NetSuite won’t let us do zero, but it allows up to 12 decimal places).
5. Calculate the delta between the base currency amounts in step 3. and the actual correct base currency amounts per account. Bring in a second JE in the prior period with these delta amounts.

Viola! The parent entity balances will still be correct, since steps 4 and 5 guarantee zero translation impact, and the base currency amounts will be correct as well, due to the combination of the entries from the 2 periods. See below illustration:

Conclusion

It was extremely satisfying to be able to find a solution for a problem that I had encountered several times in varying forms. I’ve learned a lot about creative problem solving from Marty Zigman and from the talented team at Prolecto. If you are struggling with getting NetSuite to align with your business or accounting needs, please reach out!