Skip to main content


Adaptive Insights LLC.

Weighted-Average Translation Walkthrough

Walks you through how to improve data flow between your income statement and balance sheet. Enable the weighted-average translation setting in Retained Earnings, Net Income, and YTD Net Income accounts. Then seed the initial balance for Retained Earnings using currency-tagged splits. This setup automates data flow between these accounts.

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.

See Create Weighted-Average Translation Accounts and Weighted-Average Translation Account Overview for more information. 

The Basic Steps

Use custom or general ledger accounts to create this example. For this walk-through, we use general ledger accounts. This walk-through illustrates a common general ledger and calendar setup, but yours may be different. 

  1. Create a new child account of Retained Earnings, called Retained EarningsBeginning
  2. Create a new YTD Income (Loss) account as a child of Retained Earnings.
  3. Check and confirm the account settings of the Net Income account. 
  4. Set up an initial balance sheet for the Retained Earnings, Beginning account.
  5. Use currency-tagged splits to seed the initial balance of Retained Earnings, Beginning
  6. Add the balance sheet accounts to a Balance Sheet sheet and watch the data flow!

This walk-through replaces stored data in the Retained Earnings account with calculated values. Automatic transfers accumulate thereafter. Export the data if you want to save existing data in this account. Or, contact us to help you transition.

Change Retained Earnings into a Rollup Account

Most instances include a Retained Earnings account in the general ledger list. To make Retained Earnings a rollup account, create a child account. 

This step moves the data that is in Retained Earnings to the new child account. When you add a master formula to the child account, you delete the data.

Edit Retained Earnings 

Prepare the Retained Earnings account to be a rollup account.

  1. From the nav menu, click Modeling. Then click General Ledger Accounts
  2. In the account list, click the Retained Earnings account (generally found in Liabilities and Equities > Equity).
  3. In the settings:
    • Change the Name to: Retained Earnings, Ending
    • Change the Exchange Rate to Avg: Monthly Average.
  4. Click Save.

Create  Retained Earnings, Beginning Account

Set up a new account to receive the weighted-average translation transfer. 

  1. In the account list, highlight Retained Earnings, Ending.
  2. From the toolbar, click Create New Account create new account.
  3. For the settings:
    • Code: REBeg for the code.
    • Name: Retained Earnings, Beginning.
    • Type: Cumulative by default because it's the child of cumulative account.
    • Planned by and Actuals by: Click Delta.
      Allows the account to receive the weighted-average translation transfer from YTD Income (Loss). The setting has no other affect on the account because you won't enter data in this account.
      Setting for Beginning Retained Earnings
  4. For Data Type settings:
    • Master Formula: Enter the number 0 (zero).
      The zero in the formula makes this account read-only in sheets. The account accumulates whatever you enter in the initial balances and any incoming transfers.
    • Weighted-Average Translation: Click the checkbox.
      Enable so the account can receive the ending balance of the YTD Income (Loss) account.
    • Exchange Rate: Choose Avg: Monthly Average
      This account won't calculate currency translations because of the currency-tagged splits you add to its initial balance sheet. Match this setting to the YTD Income (Loss) exchange rate type as a best practice.

      Data Type Settings for Beginning Retained Earnings
  5. Click Save Save


Your general ledger account list looks like when you're done with this step:


Create a New YTD Income (Loss) Account

Most instances include a YTD Net Income, but you can't edit this account in the ways required for this walk-through. Create a new YTD Income (Loss) account with the appropriate settings.

  1. In the account list, click the Retained Earnings, Ending account.
  2. From the toolbar, click Create New Account create new account.
  3. In the Account Details:
    • Code: YTDNI.
    • Name:  YTD Income (Loss).
    • Rolls up to: Check that it's Retained Earnings, Ending
    • Type: Cumulative by default because it's the child of a cumulative account.
    • Planned by and Actuals by: Choose Delta.
      This setting applies the exchange rate to the  the change from period to period, rather than the accumulated or the periodic total.

      YTD Account Details Settings
    • Master formula: Enter ACCT.Net_Income, the code for the Net Income account.
      The account pulls in the periodic value of the net income account and accumulates the values.
    • Weighted Average Translation: Click the checkbox.
      The account converts the delta by the selected exchange rate before accumulating it. This keeps the exchange rate affect aligned with the periodic account's data.
    • Reset Balance: Click the checkbox and select Year.
      The account accumulates until the end of December. Then, it resets the balance to zero and starts accumulating again.
    • Transfer Balance on Reset: Click the checkbox and select Retained Earnings, Beginning .
      The account transfers the balance at the end of the year to the Beginning Retained Earnings account at the start of the next year.
    • Exchange Rate: Choose Avg: Monthly Average
      Match the exchange rate used in the Net Income account to keep the affects of the exchange rates aligned.  

YTD Data Settings

  1. Click Save Save


When you're done with this step, your account list looks like this:

Completed tree.png

Check the Settings of the Net Income (Loss)

Your instances includes a Net Income root account for the income statement. Check the settings to make sure it works with your new YTD Income (Loss) account:

  1. Highlight the Net Income account.
  2. In the settings, for exchange rate, select Avg: Monthly Average.
  3. Click Save Save.

Create the Sheet for  Retained Earnings Initial Balances

If you aren't using currency-tagged splits to seed your retained earnings yet, you manually entered or calculated the exchange rates for each currency in each level. Setting up currency-tagged splits doesn't change this data as long as you use the same historic values for each currency when you seed the account. See Currency Tagged Splits for Initial  Balances for an overview of the feature. 

  1. Go to Modeling > Level Assigned Sheets to create a standard sheet. For the name, enter Initial Balance Entry for RE.  
  2. Add the Retained Earnings, Beginning account to the sheet and click Show initial balance.

    Initial Balance Sheet Account
  3. For dimensions, add Currency (system). Currency-tagged splits requires this dimension on a sheet.
  4. Add the levels that can access the sheet and save.

    Dimensions in Initial Balance Sheet


When you complete this step, you have a sheet that looks like this and allows for currency-tagged splits.

New Initial Balance Sheet

Seed the Initial Balances for Retained Earnings, Beginning

For this walk-through, the instance has several levels that track Retained Earnings with three different currencies:

  • Headquarters uses British pounds (GBP).
    • Research and Development uses British pounds (GBP)
    • Sales and Marketing uses U.S. dollars (USD). 
      • Canadian Sales, a division of Sales and Marketing, uses Canadian dollars (CAD).
      • US Sales, a division of Sales and Marketing, uses U.S. dollars (USD). 
      • Marketing, a division of Sales and Marketing, uses U.S. dollars (USD).

Canadian Sales rolls up to Sales and Marketing, which rolls up to British Headquarters. You only enter the initial balances in the leaf levels for each currency. There's four leaf levels (levels without sub-levels) in this example: Research and Development, Canadian Sales, US Sales, and Marketing.

To seed the initial balance:

  1. From the nav menu, click Sheets and open the Initial Balance Entry for RE sheet.
  2. Choose Actuals from the version drop-down. Choose a leaf level from the levels drop-down. In this case, select Canadian Sales.  
  3. Note all the levels Canadian Sales rolls up to. Canadian sales rolls up to two levels: Sales and Marketing and Headquarters. Note the currencies for each of the rollup levels: GBP and USD.
  4. Add splits for each currency noted in step 3 and the currency of the level: (CAD). 
    1. Right-click a cell in the Initial Balance column for Retained Earnings, Beginning. Select Add Split.

      Create Split
    2. Enter the name of the split to match the currency. Start with CAD.
    3. Click the drop-down arrow in the cell of the Currency column and choose the currency that matches the split name. 
      Currency drop-down
  5. Enter the initial balance for each currencies split.

    Seed Initial Balances with currency tagged splits
  1. Save the sheet. 

When you seed the initial balance, the leaf level shows the values in all three currencies through time:

Beginning Retained Earnings Initial Balances

The rollup levels, like Sales and Marketing, show the total in the currency for that level only. Explore the cell to see the contributing levels and their values in all currencies: Rollup level with splits

Resulting Data Flow

Of the four accounts (Retained Earnings, Retained Earnings, Beginning, YTD Income (Loss) and Net Income), the only account you update in sheets is Net Income. In this example, Net Income is a calculated value based on revenue and expense activity. For simplicity, we computed the same amount for Net Income in the income statement for each month: $75,000.

The data feeds into YTD Income (Loss) on the balance sheet from the master formula. The YTD Income (Loss) data converts currencies on the delta. Then accumulates through the year.

Relation between the Net Income and YTD Income (Loss) accounts

Notice in the Balance Sheet that the cells for YTD Income (Loss) are gray. This means you and your team can't edit the data. There's also a blue triangle in the cell, which means a formula calculates the data. Explore a YTD Income (Loss) cell to see that the data is the result of a master formula that pulls in the Net Income and accumulates it.  

Explore YTD Cell

The balance in YTD Income (Loss) resets to zero at the end of the year, and starts accumulating again. 

YTD Reset

The Retained Earnings, Beginning account cells are also gray. This is because the account has a formula and you cannot edit the data. Month to month, the balance doesn't change. At the beginning of the fiscal year, the account receives the balance transfer from the YTD Income (Loss), and accumulates with the previous balance.

Retained Earnings Transfer

  • Was this article helpful?