Skip to main content
Documentation
Documentation
Adaptive Planning
Last Updated: 2023-06-23
Example: Set Up Data Flow with Weighted-Average Translation

Example: Set Up Data Flow with Weighted-Average Translation

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 for
    Retained Earnings
    , called
    Retained Earnings, Beginning
    .
  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 default 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
    .
  3. For the settings:
    • Code
      :
      REBeg
       for the code.
    • Name
      :
      Retained Earnings, Beginning
      .
    • Type
      :
      Cumulative
      by default because it's the child of
      a
      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.
  4. For
    Data Type
     settings:
    • Default 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 check box. 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.
Result
Your general ledger account list looks like this when you're done with this step:
NewRollupandChildTree.png

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
    .
  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 b
      y: Choose
      Delta
      . This setting applies the exchange rate to the the change from period to period, rather than the accumulated or the periodic total.
    • Default 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 Translatio
      n: Click the check box. 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 check box 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 check box 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.
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
    .

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.
  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.
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
  6. 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 default 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 default 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