Summary

Currency Translation vs. Currency Valuation.
Problem: Foreign Subsidiary balances were valued using different methods than NetSuite.
Solution Part 1: Manually fix the rates in the consolidated translation rate tables.
Solution Part 2: Use reversing entries in next period at same rates (does not work if you need monthly balances), import balances at new rates, rather than delta amounts.
Alternate Solution: Use entries to an “adjustment only book” to account for differences.

Update:

See related post NetSuite Currency Translation II for a flexible approach to adjust consolidated currency amounts and base currency amounts.

Introduction.

Consolidated (multi-subsidiary) reporting can be confusing, especially when it comes to currency translation. NetSuite has the capacity to perform currency translation work automatically, but it takes some effort to understand the method it uses and how to properly set it up.

This article will discuss migrating historical foreign subsidiary balances to NetSuite. Often, foreign subsidiary balances had previously been translated using a different method than what NetSuite uses. Therefore, the consolidated reports generated from NetSuite would not naturally yield the same results as reported in their prior system. But first, let’s understand NetSuite’s general approach to multi-currency.

Background.

It’s important to distinguish between currency conversion, currency revaluation, and currency translation.

1. Currency “conversion” occurs when a transaction is entered in a different currency than the Subsidiary’s base currency. NetSuite will convert the amount into the base currency at the rate contained in the Exchange Rate field. The default exchange rates are driven by tables that hold daily exchange rates, and these tables can be set manually or updated automatically. Importantly, the rates on individual transactions can be changed manually without impacting the underlying tables.
   
   
2. Currency “revaluation” refers to revising the base currency impact of an existing foreign currency transaction. This is done in two ways:
   • When a foreign currency A/R or A/P invoice/bill is paid at a different rate than the original transaction. This results in a “Realized Gain or Loss”.
   • At month end, when the current rate has changed, such that the true base currency value of the open A/R or A/P balance is different than it was when it first was created. This results in an “Unrealized Gain or Loss”.
     
3. Currency “translation“ refers to the way that foreign subsidiary account balances are reported on a parent entity’s financial statement. In contrast with the first 2 concepts, this is not transactional. As its name suggests, it simply translates the foreign subsidiary account balances when viewed in the parent entity’s currency. The tables in NetSuite that contain the translation rates are not the same ones used in conversion. This is explained more fully below.

Currency Translation.

NetSuite provides tables that contain the translation rates for each accounting period. This is not a daily rate, rather these are rates for each period. Typically, Assets, Liabilities, Income, Expenses and Equity are translated differently. NetSuite provides 3 different “rate types”, and there is a field on the “Account” record where a rate “type” is selected.

The 3 rate types are:

Current: This is a spot rate as of the last day of the reporting period. This type is usually used for Assets and Liability accounts.

Average: This is a weighted average rate for the period reported. The “weights” are all transactions hitting GL accounts which use the same “rate type”. This type is usually used with Income and Expense accounts.

Historical: This is the actual rates of all transactions having this rate type. (Essentially this is the same method as the “Average” type, as it is the equivalent of a weighted average rate based on transaction amounts.) This type usually is used for Equity accounts.

Currency Exchange Rate Tables.

There are two sets of exchange rates stored in NetSuite. One set is used for both foreign currency conversion and revaluation (see the definition of these terms above).

The second set of exchange rates are used for translation. These rates cannot be set on a transaction basis, and they are rates for each accounting period, not daily. They can be set manually, but the rate that is set will apply to all accounts that use the same “rate type”.

Delta amounts vs. Balances.

When NetSuite revalues accounts that are set to a “rate type” of “current”, such as most balance sheet accounts, it revalues the entire balance of each account as of the report date, based on the current (“spot”) rate on the report date. When NetSuite revalues accounts set to a rate type of “average” or “historical” (such as P&L accounts or equity accounts), it revalues only the delta amounts for each accounting period.

Currency Translation Sample.

Type	Date	Rate	Ringgit Amount	USD Amount
Revenue	1/1/2019	0.24184	100,000	24,184
Revenue	1/29/2019	0.24307	100,000	24,307
Revenue	02/15/2019	0.24468	200,000	48,936
Revenue	02/25/2019	0.2457	100,000	24,570
			500,000	121,997

Calculated Weighted Avg:

Jan = (.24184 * 100K + .24307 * 100K) / 200K = .242455

Feb = (.24468 * 200K + .2457 * 100K) / 300K = .24502

In the above example, if the company was running a consolidated Income Statement for February 2019, NetSuite would not translate the entire 500K balance in the revenue account. Rather, the Jan amount (200K) would still be translated at the Jan weighted average, and the Feb amount (300K) would be translated at the Feb weighted average rate.

Loading Historical Data.

This presents a challenge when loading historical trial balances which were produced before adopting NetSuite. A company’s practice may have been to translate the YTD balance each month at a non-weighted average for the YTD (i.e. the total of all daily rates ÷ number of days YTD). Thus, even if we manually set the Jan and Feb translation rates in NetSuite, there is no way to consistently yield the result desired, since NetSuite will only look to translate the Feb amounts in Feb, not the Jan amount, and the YTD non-weighted average will not necessarily equal the combined manually-entered Jan and Feb rates when each month is translated separately.

It would be nice if we could simply assign a translation rate on specific transactions, the same way that this can be done for the currency exchange rates when entering a foreign currency transaction. If we could, we would simply import each month’s balance, reverse in the following month (at the same rate), and import the following month’s transaction at a different rate to match what had previously been reported. However, as mentioned, NetSuite’s foreign currency translation is not a true transaction, rather it is a function of reporting – and therefore we cannot assign a different translation rate for different transactions.

Solution 1.

There is a relatively easy solution to the problem, if a company only needs to produce quarterly or annual reports for the historical (pre-NetSuite) activity:

1. Manually set the rates on the last periods in the quarters or years that need to be reported on.
2. Then set the rates to be the same for the first period subsequent to each quarter or year.
3. Enter each quarter’s or year’s balances and set the Journal Entry reversal dates to occur in the following period.
4. When entering the subsequent quarter’s or year’s balance, be sure to load the entire cumulative balance, rather than just the delta amounts.

Solution 2.

If a company needs to produce monthly reports on historical balances prior to NetSuite, the above solution will not work, as each period will need its own correct currency translation rate. Instead, the problem can be solved either using an Adjustment-only book, or entries to an Elimination Subsidiary.

1. Calculate the differences between what NetSuite shows as the USD-Translated account balances each month, and what had previously been reported.
2. Book JE’s either to the Adjustment Book or Elimination Subsidiary with these amounts. The offsetting debit or credit should be booked to the Cumulative Translation Adjustment account (although the account balance normally does not contain transactions, it is possible to post Journals to this account if desired).

Either way, the process is somewhat manual. Companies that are adopting NetSuite OneWorld might need to consider changing how they account for translations so that they can get the full value out of NetSuite capacities.

Master your consolidated reporting.

NetSuite has powerful tools to aid with consolidation, but it can take some time to understand the approach and assess how best to configure it to suit your business needs. If you’d like to learn more about NetSuite’s foreign currency tools and capacities, we’d be happy to talk to you.