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.
See Weighted-Average Translation Walk-through, a detailed walk-through that improves data flow and automation using currency-tagged splits and weighted-average translation.
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. See Create Weighted-Average Translation Accounts.
- 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 accounts for currency-tagged splits if the accounts require an initial balance. A common example of an account to use is Retained Earnings. See Create Weighted-Average Translation Accounts for step-by-step instructions and Concept: Weighted-Average Translation for general information.
Create a Standard Sheet for Initial Balances
Create a sheet for the initial balances of the Weighted-Average Translation 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 Weighted-Average Translation 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.
Open the Sheet and Add Currency-Tagged Split
Add the currency-tagged splits to the new sheet in each leaf level (level without sub-levels).
- Go to Sheets and select the sheet you created.
- Select a leaf level. List the levels the selected level rolls up to and list the currency for each rollup.
- For each currency:
- In the initial balance cell of the weighted-average translation account, click Add Splits.
- Enter the currency type in the Split Name field.
- In the Currency column, select the currency from the drop-down 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.