Passer au contenu principal

logoCorrectSize.png

Adaptive Insights French

Formula Writing Basics (26m 24s)

The user interface might not match what you see in this video.

Type of Formulas

  • Shared formulas 

    • Most common type of formula.

    • Put on the Account, can be different for Levels and Versions.

    • Warning:  Can be overridden by data imports and/or data entry.

  • Master formulas

    • Put on the Account, the same for all Levels and Versions.

    • Automatically read-only on sheets.

  • Metric accounts

    • Put on the Account, the same for all Levels and Versions.

    • Automatically read-only on sheets.

  • Modeled/Cube sheet accounts

    • Put on the Account, the same for all Levels and Versions.

      • Cube calculated accounts support Actual data.

    • Automatically read-only on sheets.

  • Cell formulas (user edits)

    • Avoid if at all possible as they create future maintenance.

 

Linking Accounts

An alternative to using formulas for moving data between sheet types.

  • Standard linked accounts

    • Brings data from Modeled/Cube accounts to GL or Custom accounts.

    • For Modeled accounts, it pulls over all the split details as well.

    • Maintains data dimensionality.

    • More than 40 linked accounts can impact performance.

  • Cube linked accounts

    • The data from the source flows into the cube.

    • Does not require an IsBlank formula, can be used in other formulas as your IsBlank test.

    • Does not count towards the size of the sheet.

    • Can have Actual data.

    • Works well for the same, or fewer dimension scenarios.

  • Both are automatically read-only on sheets.

 

Things to Know, Consider, and Avoid

Things to Know

  • Accounts are “private” by default;  if you need to reference the value from another Level, you need to adjust the Data Privacy setting.

  • Know what type of account you are working with

    • P&L

    • Balance Sheet delta:  calculate the change for the period.

    • Balance Sheet balance:  calculate the balance for the period.

  • Changing a Modeled/Cube/Metric/Master formula affects all Versions, even those that are “locked”.
     

Things to Consider

  • Use Master formula if an account has the same formula for every Level and Version.

    • Common for Custom accounts used in reporting.

  • Keep your Modeled/Cube sheet codes related to the sheet name.

  • Prefix your Custom and Metric account codes with c_ and m_, helps with debugging.

    • Acct.c_Revenue is easier to find than Acct.Revenue

  • Modeled, Cube, and Custom calculated accounts default to account type:  Number. Switch to Currency if your situation may ever require it.
     

Things to Avoid

  • Formulas that do the same math, applied to every Level.

    • Bad:  Div(acct.headcount, acct.headcount[level=Top Level(+)]

    • Good:  Divf(acct.headcount, assum.TopLevelHeadcount)

  • Hard coding numbers into a formula if there is any possibility it would change.

  • Modeled/Cube sheet Assumption accounts.

    • Better to use global Assumption/Custom accounts.

 

Formula Writing 101

  • In most cases you should use the Formula Assistant.

    • Reduces typing errors.

  • Make sure you test your formula as you go.

  • Use spaces and carriage returns in formulas to improve readability.

    • Indenting is also key.

  • Use # to add comments to formulas.  Also handy for debugging as you can comment out parts of a formula.

  • Make it a habit to close any parentheses as you open them.

  • Always use the faster versions of functions:  IFF and DIVF.