If data in your instance rolls up to multiple levels with different currencies, use currency-tagged splits for initial balances instead of historical exchange rates. This will save you time and provide more accurate data.
How Currency-Tagged Splits Work
With currency-tagged splits, you consider historical values only when you enter the initial balance. Thereafter, conversions are automatically handled for each currency. For example, an instance has 3 levels that track Retained Earnings with three different currencies:
- Headquarters uses British pounds (GBP).
- Sales and Marketing uses U.S. dollars (USD).
- Canadian Sales, a division of Sales and Marketing, uses Canadian dollars (CAD).
Canadian Sales rolls up to Sales and Marketing, which rolls up to the British Headquarters.
When you enter initial balances through currency-tagged splits, you enter the amount of each currency, based on the historical rate. The values calculate for you moving forward and convert automatically on the Retained Earnings sheet:
- The Canadian Sales level displays CAD values based on the CAD initial balance.
- The Sales and Marketing level displays the calculated values in USD, including converted CAD values from its sub-level.
- The Headquarters level displays GBP values based on the initial value entered for GBP, including converted CAD and USD values from its multi-currency sub-levels.
If you already have initial balances set up, the values you enter in the initial balance must match the amounts previously computed using historical rates to maintain the accuracy of your historical data.
To enable currency-tagged splits, you need:
- Multiple currencies
- Accounts that use initial balances
- Weighted-average translation account
- Standard sheet with the Currency (system) dimension
- Create the Weighted-Average Translation account.
- Create a standard sheet for initial balances only:
- Add the Weighted Average Translation Account.
- Add the Currency (system) Dimension.
- Open the sheet and add a currency-tagged split for each currency your instance uses.
- For each split, add the initial balance.
If you already have initial balances set up a different way, enter the exact amount for each currency based on the historical rates you used previously.
Create a Weighted-Average Translation Account
You must create weighted-average translation (WAT) accounts for currency-tagged splits if the accounts require an initial balance. A common example of an account to use is Retained Earnings.
Available for: General Ledger accounts or custom accounts with a currency data type.
With weighted-average translation, you can translate values using a weighted average method. This option is useful for translating balance sheet accounts, such as the Retained Earnings account.
To enable a weighted average translation on a specific GL or custom account:
- Go to Modeling > Model Management > General Ledger Accounts or Custom Accounts.
- Select the account to enable or create a new account (it must not be a rollup account).
- In the Account Details screen, find the Type section and click the Delta radio button for both Planned by and Actuals by.
- Find the Data Type section and in the Master Formula box, reference the account to be accumulated in the designated weighted average account. The reference account will pull data from an actuals or a plan-by-delta version.
Example: Reference Net Income on a Retained Earnings account.
- Below Master Formula, click the Weighted Average Translation checkbox.
- Select the average monthly rate from the Exchange Rate drop-down. This must match the exchange rate type of the account entered in the Master Formula field.
Create a Standard Sheet
Create a sheet for the initial balances of the WAT account. As a best practice, create a standard sheet that only includes accounts that require initial balances using currency-tagged splits.
- Go to Modeling > Level-Assigned Sheets and create a standard sheet.
- Add the WAT account or accounts you created.
- From the Available Dimensions box, click Currency (system) and click the double arrow so that the Currency (system) dimension appears in the Selected Dimension field. Click Next.
- Click Complete Sheet when done.
Add Currency-Tagged Split
Finally, add the currency-tagged splits to the new sheet:
- Go to Sheets and select the sheet you created.
- For each currency:
- In the initial balance cell of the WAT account, click Add Splits.
- Enter the currency type in the Split Name field.
- In the Currency column, select the applicable currency from the dropdown list that corresponds with the value that is to be entered on the currency-tagged split.
- Enter the initial balance.
- Select various levels and notice that the sheet displays each level's default currency in the cells.