Adaptive planning uses End of Month rate to consolidate Balance sheet values. But it can be tricky when some balance sheet accounts are using Historical rate. Although there is a CTA system calculation, most of the time, it just forces the Equity balances on a total level but not on detailed Account Levels.

To get around with this situation, we can utilize the Weighted-Average Translation functionality so the equity accounts can hold their historical value on consolidated levels.

Weighted-Average Translation (WAT) is an account setting for cumulative accounts. A master formula pulls the data from a periodic account into the cumulative account. Weighted-Average Translation then converts the currencies on the delta at the average periodic exchange rate. This provides a weighted-average conversion. Additional settings: Balance Reset and Balance Transfer at Reset provide automated data flows.

When to Use Weighted-Average Translation

Use the Weighted-Average Translation setting when you:

  • Have more than one currency AND
  • Want to accumulate periodic account data in a cumulative account.

The most common use case is to automate the data flow and conversion from a net income account in the income statement to a YTD net income in the retained earnings section of the balance sheet for each fiscal period.

Other use cases:

  • Dividends and other accounts that flow through retained earnings.
  • Fixed asset or investment roll-forward with activity-based accounts (additions, retirements, and more)
  • Equity accounts using Historical rates

This is how Translation works in Adaptive

Using the Native consolidation, each local currency cumulative values are translated using current month’s rate than transaction month’s rate.

With Weighted average translation, each balance is converted using rate on the month the transaction happens and carry over that translated amount to the next month. This method represents more accurate valuation of Equity balances account.

Equity balances account
Equity balances account

Equity Accounts Weighted Average set up

First, make sure that a “Historical” exchange rate type is added.

"Historical" exchange rate type
“Historical” exchange rate type

Then, each equity account that will use weighted average translation should be set up as periodic account to handle activity (this is where actuals data should be imported), we can utilize Custom accounts for this purpose, then apply the “Historical exchange rate” type on this account.

set up as periodic account to handle activity
set up as periodic account to handle activity

On the Equity Account section of your General ledger, each account that needs weighted average translation should be set up as Planned by and Actuals by “Delta” to enable weighted average translation.

set up as Planned by and Actuals
set up as Planned by and Actuals

Under Data Type section of account details. A formula needs to be added on “Master Formula” section to pull the values from Periodic Equity accounts, these accounts are also using Historical Rates.

"Master Formula" section
“Master Formula” section

If you have questions or need assistance implementing Adaptive Insight please feel to contact us!

About Abaci
Abaci is a premier NetSuite & Adaptive Solution Provider with deep knowledge in both business process and technology architecture. Our team is passionate about NetSuite & Adaptive and has an exclusive commitment to providing the highest level of system expertise on its full suite of cloud-based business management applications.