Audience: Administrators who manage the model.
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.
Watch the video (2m 13s):
Designed with retained earnings, net income, and period-to-date net income accounts in mind, Weighted-Average Translation aligns the different ways these accounts convert currencies and automates the flow between these accounts.
Without the Weighted-Average Translation setting, Adaptive Planning accumulates balances in period-to-date accounts and then converts the currencies. With the Weighted-Average Translation setting, Adaptive Planning converts the currency on the delta before accumulating.
Reset Balance and Balance Transfer automate data flow. Reset Balance makes the account stop accumulating and reset to zero at a specified time. Balance Transfer moves the balance to another account upon reset.
See Create Weighted-Average Translation Accounts. See Consolidation Currency Tools for summaries and links to other multiple currency tools.
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).
How Weighted-Average Translation Works
1 The net income converts periodic values on the monthly average rate as expected.
2 The beginning retained earnings balance uses historical rates. You can also use Currency Tagged Splits to input these values in multiple currencies.
3 The master formula pulls each periodic value of the net income account. The Weighted-Average Translation setting converts the delta with the periodic average exchange rate. Notice the values match the net income values on the top 1. These values would not match without Weighted-Average Translation. The conversion would take place on the accumulated balance at the end-of-month rate or average rate for February only. The conversion would not take into account the rate for January.
4 The YTD net income/loss in the balance sheet accumulates in every currency.
Accounts must have certain settings to use the Weighted-Average Translation setting. See Create Weighted-Average Translation Accounts.
Reset Balance and Balance Transfer at Reset
Reset Balance and Balance Transfer at Reset are optional settings to use with Weighted-Average Translation.
With Reset Balance the accumulating balance resets to zero and starts accumulating again at specified time. For example, YTD net income accumulates data from the net income account throughout the year. Then, it resets to zero and begins accumulating the net income data again in January of the next year.
The time periods available depend on your calendar setup. Common options include Month, Quarter, and Year.
Transfer Balance at Reset
With Balance Transfer at Reset the accumulating balance of the account transfers to another account at the time of reset. For example, the ending balance of the YTD net income account transfers to the retained earnings account at the beginning of the year.
The accounts available to receive a transfer have specific settings: see Create Weighted-Average Accounts.
Example of a Weighted-Average Translation Account with Reset Balance and Balance Transfer
1 YTD accumulates the value of net income throughout the year.
2 At the beginning of the year, the balance of YTD resets to zero and starts accumulating again.
3 The ending balance (at year end) effectively transfers to the beginning retained earnings account.
Exchange Rates with and without Weighted-Average Translation
|Account Settings||What Happens|
Cumulative account by Balance with end of period or average period rates.
Converts ending balance with that period's EOM or average period exchange rate.
Cumulative account by Delta with end of period or average period rates.
|Accumulates the deltas over time and then converts that total with the ending period's EOM or average period exchange rate.|
|Cumulative Account with Weighted-Average Translation and average period rates.||Converts each delta with the average exchange rate of each period, and then accumulates the converted deltas for every period in the future.|
|Cumulative Account with Weighted-Average Translation and end of period rates.||Converts each delta with the EOM rate of each period, and then accumulates the converted deltas for every period in the future.|