(2 OM) 14. Formulas

Question 1

Users need to consider the following questions when deciding how to create a specific formula:

1. Should the formula change between ___ or be static?
2. Should the formula be ___ specific or applied to all levels?
3. What formula function do you need to most ___ build the formula?
4. Are there ___ ___ that you need to include to target a specific time, level, dimension, or attribute?
5. Is there a specific account you need to reference in the formula? Or should you build it using ___ ___ syntax, which does not require an account reference?
6. In what ___ should you create the formula, meaning what account type or sheet?

Answer 1

1. Versions
2. Level
3. Efficiently
4. Term modifiers
5. Dot notion
6. Location

Question 2

The Formula Assistant Tool consists of several parts including…

1. Operators
2. Functions
3. Levels
4. Accounts
5. Account Term Modifiers
6. Formula
7. Import Option

Answer 2

1. Operators
2. Functions
3. Accounts
4. Account Term Modifiers
5. Formula

Question 3

Which of the below is not considered an operator in the Formula Assistant Tool?

1. +
2. -
3. And
4. =
5. div ()
6. %
7. [ ]
8. >

Answer 3

7. [ ]

Question 4

The Formula Assistant Tool can be accessed in a variety of locations, including ___ and ___.

Answer 4

Sheets, Accounts.
(Standard, Cube, Modelled sheets.
GL, Custom, Metric,Cube, Modelled Accounts).

Question 5

Formulas cannot be version specific. True or false?

Answer 5

False (They can be version specific and it depends on the placement of the formula and account).

Question 6

Which of the below is not an example of formula syntax?

1. ACCT.
2. ASSUM.
3. Div()
4. Round()
5. [time=this-1]
6. +
   7.this.year.positionof(this.month)

Answer 6

6. +

(1 & 2 are Account syntax, 3 & 4 are Function syntax, 5 is Term Modifier syntax and 7 is Dot Notion syntax).

Question 7

ROW. syntax appears in ___ sheets and refers to an individual ___ of data, meaning each calculation is independent of each other.

Answer 7

Modelled, row

Question 8

You would use ROW. syntax when you create calculated modelled accounts that refer to ___ columns, lookup tables and calculated modelled accounts built in that sheet.

Answer 8

Existing

Question 9

What are the 4 Formula Function types?

1. Logical
2. Mathematical
3. Date
4. Boolean
5. String

Answer 9

1. Logical
2. Mathematical
3. Date
4. String

Question 10

Use mathematical functions to evaluate basic formulas with ___ outcome.

Answer 10

One

Question 11

The mathematical function “Div (N, D)” divides the ___ by the ___.

Answer 11

Numerator, denominator

Question 12

The mathematical function “Divf (N, D)” returns the same result as Div (N, D) but is the faster form in that if the ___ is 0, the numerator is not evaluated.

Answer 12

Denominator

Question 13

Use logical functions to evaluate based on the outcome of different scenarios. Examples include…

1. Or
2. If
3. Iff
4. Isblank
5. Error
6. Switch

Answer 13

2. If
3. Iff
4. Isblank
5. Error
6. Switch

Question 14

The logical function “If (EXPR, T, F)” returns the value of ___ if boolean expression ___ is true, otherwise it returns the value of ___.

Answer 14

T, EXPR, F

Question 15

The logical function “Iff (EXPR, T, F)” returns the same result as If (EXPR, T, F) but is the faster form in that if the condition is ___, the third argument F is not evaluated. Similarly, if the condition is ___, the second argument T is not evaluated.

Answer 15

True, false

Question 16

Use Date Functions in flexible ___ models or in combination with ___ sheet Date Element columns.

Answer 16

Time, modelled

Question 17

Examples of Date Functions include…

1. ToDate
2. Hour
3. Day
4. Month
5. Version.PositionOf
6. TimeFraction
   .

Answer 17

1. ToDate
2. Day
3. Month
4. Version.PositionOf
5. TimeFraction

Question 18

Use String Functions to evaluate outcomes based on ___-___ data.

Answer 18

Non-numerical

Question 19

Examples of String Functions include…

1. Text
2. Concat
3. Length
4. Search
5. Substring
6. ToNumber

Answer 19

2. Concat
3. Length
4. Search
5. Substring
6. ToNumber

Question 20

Term Modifiers allow a user to further target a formula beyond accounts. The available Term Modifiers are ___, ___, and all existing ___ and ___.

Answer 20

Time, Level, Dimensions, Attributes

Question 21

Examples of Time Term Modifiers include…

1. This+n
2. This.year
3. This-n
4. 2025
5. This.Month+Next.Month
6. This-12:this-1

Answer 21

1. This+n
2. This.year
3. This-n
4. 2025
5. This-12:this-1

Question 22

Level modifiers allow you to create formulas with accounts that reference data from a different ___ than the one the formula is created on.

Answer 22

Level

Question 23

Does the following Level Term Modifiers follow correct syntax - true or false?

1. [Level=<Level-Name>(+)]</Level-Name>
2. [Level=<Level-Name>(-)]</Level-Name>

Answer 23

1. True (the syntax is including all children from the specified level).
2. True (the syntax is excluding all children from the specified level).

Question 24

For formulas to work across organization levels, the data privacy for an account must be public. True or false?

Answer 24

True

Question 25

Dimension and attribute modifiers allow you to target specific values entered at a dimension or attribute value intersection. You can then use these values within formulas with accounts to only target specific values, which allows for more ___ planning and reporting.

Answer 25

Granular

Question 26

You use dot notation syntax in "if" or "iff" functions with an operator <, >, =, <=, >= comparing the statement to a specific value. True or false?

Answer 26

True

Question 27

Examples of dot notion operands include... 1. This.Level 2. This.Account. 3. This.Dimension_Name 4. This.Attribute_Name 5. This.Sheet 6. This.Version 7. This.TimePer 8. This.ModelledAccount

Answer 27

1. This.Level 2. This.Account. 3. This.Dimension_Name 4. This.Attribute_Name 6. This.Version 7. This.TimePer

Question 28

Shared formulas can apply to ___ Levels for a ___ Version (rather than all Versions).

Answer 28

All, specific

Question 29

Pros of shared formulas are they are ___ and you can ___ and export them. Cons of shared formulas is they cannot carry ___.

Answer 29

Flexible, import, dimensionality.

Question 30

Dimensional details can only carry from one account to another by setting the receiving account "Type" to "Link". True or false?

Answer 30

True

Question 31

When importing Shared Formulas from an Excel workbook, you can delete shared formulas by leaving the "Formulas" column blank. True or false?

Answer 31

True

Question 32

Audit trail is inherited when creating a new level or cloning a Level. True or false?

Answer 32

False

Question 33

You can only clone the lowest child levels. A rollup level is not available for cloning. True or false?

Answer 33

True

Question 34

Default Formulas are formulas that can be created on any ___ or ___ account.

Answer 34

GL, custom

Question 35

Default Formulas can be applied to all Versions and Levels using the "Replace" option. Otherwise click "Preserve" which will which will retain all formulas and data in all versions but allow application to the selected version(s). True or false?

Answer 35

True

Question 36

A pro of default formulas is once applied, they do not have to be maintained as they automatically populate across all ___ and ___. You can also use formula ___ which allows you to adjust the formula as a result of a change in methodology, without impacting all other versions.

Answer 36

Version, Levels, override

Question 37

Cons of default formulas is that they ___ existing data and ___ account access.

Answer 37

Delete, restricts

Question 38

In Default Formulas, the override formula setting allows you to ___ a global formula for specific unlocked versions.

Answer 38

Adjust

Question 39

The override formula setting is available for which of the following types of accounts?... 1. GL or Custom accounts that have a default formula applied. 2. Metric accounts. 3. Calculated modeled accounts. 4. Cube calculated accounts or cube metric accounts. 5. System Accounts.

Answer 39

1. GL or Custom accounts that have a default formula applied. 2. Metric accounts. 3. Calculated modeled accounts. 4. Cube calculated accounts or cube metric accounts.

Question 40

What is considered best practice for Formula Syntax? 1. Use Formula Assistant. 2. Consider the order of logic. 3. Use indents, lines and comments (#). 4. Keep account codes consistent, simple and short. 5. Break complex formulas apart. 6. Daisychain formulas.

Answer 40

1. Use Formula Assistant. 2. Consider the order of logic. 3. Use indents, lines and comments (#). 4. Keep account codes consistent, simple and short. 5. Break complex formulas apart.

Question 41

What is considered best practice for Formula References? 1. Use hardcoded values. 2. Use time modifiers which are dynamic, rather than dates. 3. Divf and Iff evaluate faster than Div and If.

Answer 41

2. Use time modifiers which are dynamic, rather than dates. 3. Divf and Iff evaluate faster than Div and If.

Question 42

Once formulas are built, ___ and ___ them and ___ any formula errors.

Answer 42

Validate, test, clear.

Question 43

The most common formula errors are caused by... 1. Not using dot notion syntax. 2. Circular references. 3. Incorrect syntax 4. Data privacy (Account is not set to "Value of account is public at all levels")

Answer 43

2. Circular references. 3. Incorrect syntax 4. Data privacy (Account is not set to "Value of account is public at all levels")

Question 44

Shared formulas ___ be overridden by data imports or data entry.

Answer 44

Can

Question 45

You can create Default Formulas apply to ___ Levels and Versions. This will set an account to read-only. You can create Shared Formulas to apply to ___ Levels and ___ Version(s).

Answer 45

All, all, specific.