This article is relevant if you are responsible for consolidated financial reporting in NetSuite and find yourself puzzled by how exchange rates are calculated — or if your auditors are asking questions about consolidated exchange rates and cumulative translation adjustments and you are struggling to answer.

Background

When organizations operate across borders in a multi-entity fashion, NetSuite’s OneWorld architecture provides powerful tools to consolidate financials across subsidiaries. But with that power comes complexity. Time and again, our professional team at Prolecto Resources gets called in to demystify how NetSuite derives its consolidated exchange rates — especially the elusive average and historical rates that show up in financial reports but don’t always reconcile easily. Related to this topic is the cumulative translation adjustment, or CTA.

Clients often ask: “Where do these consolidated exchange rates come from, and how are they derived?”.  “Why don’t the opening and closing consolidated balances tie from one period to the next?”.  “Why is the P&L showing numbers that do not match our expectations?”  These questions typically surface during audits or when Corporate Controllers try to validate intercompany eliminations and currency translation impacts. NetSuite conveniently provides the consolidated exchange rates, but understanding how they’re calculated — and how to explain them to stakeholders — requires a deeper dive.

In this article, I spent quality time with Prolecto’s Accounting Practice Leader, Meir Bulman, who has cracked the code on what is happening behind the scenes.   His leadership has solved quite challenging multi-level, multi-currency (and crypto), challenges with cutovers, third-party trial balance integrations for NetSuite consolidated reporting, and demanding auditor inquiries.  The nature of this article will be about two videos of an interview I conducted with Meir as he walked me through the mechanics.

Understanding NetSuite’s Consolidated Exchange Rates

NetSuite consolidates subsidiaries transacting in different functional currencies by translating their ledgers into the parent’s reporting currency. This translation is purely for reporting purposes; outside of elimination entries, no actual transactions are posted to the general ledger when viewing information via reports at the consolidated entity level. Instead, NetSuite uses a set of internal rate tables that determine how balances are converted using three rate types: current, average, and historical.

Click on the image to see the concept overview in full-screen.

What Confuses Most Controllers and Auditors

There are three fundamental rates used in the consolidated reporting routine.

1. Current rates are straightforward: they are based on the exchange rate in the transactional currency rate tables as of the last day of the period.
2. Average rates are less intuitive: NetSuite computes a weighted average based on daily transaction volumes by account, pulling daily rates from its standard currency exchange table.
3. Historical rates are used primarily for equity and fixed assets: these follow the same weighted logic but are limited to transactions using accounts tagged with the historical rate type.

The confusion arises because NetSuite doesn’t post these conversions as ledger entries; instead, it applies them at report time based on rules hidden deep in system logic. As a result:

• Reported balances may not tie out period-over-period. Prolecto can explain this phenomenon with a crafty report that Meir built to help auditors get comfortable.
• Drilldowns don’t show transactions for CTA (Cumulative Translation Adjustment) accounts. This is because it is a report-only balance — however, NetSuite provides a tool to help people understand the nature of the values.
• Average rate calculations aren’t easily auditable: Prolecto gives its clients a SuiteQL (SQL) report that explains this clearly.
• Historical transactional contexts (e.g., significant transactions on a specific day) can unintuitively skew rates due to the nature of the weighted average algorithm and confuse folks’ common sense.

A SuiteQL-Powered Solution for Transparency

To solve this, Meir built a SuiteQL-based report using our license-free Query Renderer tool, available to all Prolecto clients.  These reports recreate NetSuite’s rate calculation process step-by-step, exposing the exact math NetSuite uses and allowing controllers and auditors to trace the origins of the rates. Ideally, Oracle NetSuite would provide this as a pre-built report — in the meantime, we solved it.

See It in Action: Video Demonstration

To understand all of this, I have broken this article into two parts.

1. Part I: The Basics of Consolidated Exchange Rates
2. Part II: Solving Cumulative Translations Adjustments (CTA) Challenges and Complex NetSuite Cutover Financial Reporting and Ledger Fidelity

Here is a direct reference to the Part I video.   Below is the video:

The Part II video is here for convenience and below.

Consolidated Exchange Rate Implementation Details

Here is a summary of the ways Meir tackled the fundamentals for explaining NetSuite’s hidden logic.

1. Calculate Average Rates Using Daily Transaction Volumes:  we query daily GL activity, regardless of transaction currency, and retrieve the relevant exchange rate for each day.  By converting all activity to absolute values (ignoring debit/credit orientation), we replicate NetSuite’s weighting logic.  A sum-product formula then generates the precise average rate.
2. Tie Directly to NetSuite’s Consolidated Rate Table: our report outputs a calculated rate that matches the value found in NetSuite’s Consolidated Exchange Rate table. This rate is easily explainable and reconcilable — even in the face of audit scrutiny.
3. Support for Historical Rates: the same algorithm applies, scoped to accounts tagged as “historical” rate type.  This allows clarity for equity movements, capital infusions, and long-term investments.

These fundamentals need to be well-understood before tackling more advanced concepts (for a subsequent article).

Work with High-Performance NetSuite Leadership

NetSuite’s exchange rate logic is powerful but opaque. Left unexplained, it can undermine a Controller’s confidence and an auditor’s trust.  At Prolecto Resources, we decode this logic with transparent, auditor-satisfying reports powered by SuiteQL — at no additional license cost to our clients. Our approach is grounded in accounting principles, system knowledge, and common-sense modeling, which are the essential elements required to unlock NetSuite’s potential.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to demystify NetSuite’s currency consolidation for your team and auditors, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube