Talking Tech: Foreign Currency Tips/Tricks for Reporting Using NetSuite Saved Search

Let’s Talk Tech.

Saved searches in NetSuite are generally very powerful and support multi-currency environments. Still, pulling financial data in a saved search in the transaction currency can be tricky. Knowing how to setup the search, what fields and formulas to pull into the Results columns, and applicable use cases are crucial to maximizing NetSuite’s foreign currency reporting capabilities.

Use Case: Ability to create a Saved Search that displays all invoices under a specific customer, where the business can report the transactions in the Locale Currency.

Example: The complexity usually arises when transacting in a currency that is not the subsidiary’s base currency, so in this example, let’s look at a subsidiary hierarchy with a parent in USD and a child subsidiary in the UK, with base currency GBP.

• Abaci, Inc. – “USD Base Currency”
  • Abaci, Ltd. – “GBP Base Currency”
  • Elimination Subsidiary – ABACI

Now let’s say a customer in the GBP subsidiary also transacts in EUR, and we want to report on EUR transactions in that transaction currency. We have an invoice in EUR, on the UK books below:

Above, we’re looking at the invoice line in EUR and the Exchange Rate info back to GBP, the subsidiary base currency, on the Accounting sub-tab of the invoice.

First, we want to acquaint ourselves with a normal Amount search. With default settings, the search will show the Amount in USD, because that’s the top level of consolidation in the subsidiary structure. First it will convert EUR to GBP, based on the transaction exchange rate, then it will convert GBP to USD based on the consolidated rate. Here is a saved search for that invoice that just uses the Amount fields:

The $5420.79 in the search result comes €3388 EUR on the invoice * the transaction exchange rate (.67653277) to get the amount in GBP, £2,292.09. £2,292.09 is what will show if you go to the GL impact page of the invoice, since transactions always post in the subsidiary base currency. It’s then converting to USD via the Consolidation Exchange rate under Lists > Accounting > Consolidated Exchange Rates to get the USD total of $5420.79. (Consolidated Exchange Rates are normally calculated each month at month end)

Saved Search Foreign Currency Preference

This conversion preference lives on the Results page of the search:

When selecting “None”, the transaction won’t consolidate, it just converts to the subsidiary base currency, GBP when running the search:

That’s helpful, but what if you need the reporting in the transaction currency, in this case EUR, in your search results?

You’re probably familiar with the Amount (Foreign Currency) value on search results. This shows the amount in the transaction currency:

Reporting on Subtotal, Taxes Amount, & Total

When you’re looking for the subtotal, or if there’s no tax, that might be all you need to do. If you want to report on Subtotal, Tax Amount, and Total Amount in the transaction currency, it a little trickier. Notice in the screenshot above, the Amount (Foreign Currency) field is just the subtotal, not the total.

While there is no Amount (Tax – Foreign Currency) or Amount (Line Total – Foreign Currency) field available unfortunately. By adding a simple formula field this will get you the info needed:

Fields to Add:

1. Tax Amount in the transaction currency: ({netamountnotax}+{taxamount})/NULLIF({exchangerate},0)-{fxamount}
2. Subtotal + Tax Amount in the transaction currency: ({netamountnotax}+{taxamount})/NULLIF({exchangerate},0)

Here it is in the Results tab:

Results

Our search results then precisely match the invoice values in the transaction currency, EUR:

Another example this might come up is if using a Summary Saved Search to set a field value such as displaying the total applied to credits or payments in a field on the invoice record. If the transaction currency doesn’t match the subsidiary base currency, you will want to be careful that the results are summarizing in the correct currency.

NetSuite saved searches can provide powerful, robust insights, but certain processes can be challenging without prior search experience. Hopefully these tips and tricks help pull the exact information that you need for your multi-currency reporting.