Skip to main content

I am trying to rebuild our Balance Sheet reporting from NetSuite into Pigment using a Saved Search. However, because our Balance Sheet is consolidated between entities that use different currencies, the Saved Search transaction amounts do not match the reported amounts in NetSuite by period. This seems to be a difference in currency exchange rate methodologies between the way the Saved Search pulls FX (by transaction) and the way the Balance Sheet report pulls FX (by reporting period). 

Has anyone been able to solve this and pull in historical Balance Sheets month over month to match the reporting?

Hi Chris,

What you will want to do is to make sure in your Balance Sheet saved search that the Consolidated Exchange Rate under the results tab is set to None and that there is a column for currency so you can determine by transaction which currency it is. Then you will want to create a Consolidated Exchange Rate saved search. Make sure to filter on the to currency to be what your consolidated reporting currency is. For instance, if you report everything in USD as your final currency, then filter the to currency on USD. 

Bring both of these saved searches into Pigment as separate transaction lists. For the exchange rates, you will have 3 3 columns that will be mapped in your transaction list. You will create a dimension that is FX Rate Types with the items being AVG, EOM and HIST. EOM will be the column labeled as current in your saved search, AVG will be the column labeled as average and HIST will be the column labeled as historical. Pull this all into a metric so that you have the Currency, FX Rates Types and Month dimensions. If you are using the Pigment templates, the metric to put the data into is the FX_01_Input_FX Rates metric in the HUB. Map the currency dimension using the From currency column.

The rest will funnel through to the remaining metrics in the template. The final output metric is Push_DH_FX_FX Rates, which maps your entities to reporting currency. Make sure that your entities are mapped to currencies in the Push_DH_FX_Entity Currencies metric, as this is used to map entity to currency.

You will then want to put your Balance Sheet data together into a metric to calculate ending balances that have entity and reporting currency as dimensions. You will map this metric to the Reporting Currency “Local”.  Then to get the group (reporting consolidated currency), you will take your Balance Sheet metric and multiply it by your DH_FX_FX Rates metric and select FX Rate Types as “EOM”. When converting Balance Sheet, it takes the FX Rate as of end of month and converts the transaction using this rate. This would then give you your balance sheet balances as of local currency and converted group (reporting consolidated currency).

Hope this helps!

 

Amelia

Reply


Ask your question here!

Terms & ConditionsCookie settings