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 Accounts Weighted Average set up
First, make sure that a “Historical” exchange rate type is added.
“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
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
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
If you have questions or need assistance implementing Adaptive Insight, please feel to contact us!