ROLLFORWARD_ADVANCED

Description

The ROLLFORWARD_ADVANCED allows to project a node into the future with a list of provided drivers, both relative and absolute. The function has optional parameters: index levels, which are incremented/shifted on each loop of the projection; the setting of start and end values for the projection; window settings that allow a selection of previous values to be used for a loop calculation, its aggregation level, and a shift type.

This function combines the functionality of both the ROLLFORWARD and ROLLFORWARD_MUL functionality. Differentiating between the mode of how drivers are applied can be done via the "mode" option in the formula.

Technical Signature

ROLLFORWARD_ADVANCED(input[, [drivers] [, time [, start, end [, windowSize [, windowFunction [, windowType [, mode [, [agingLevels] [, agingOffset] [, linkedLevelsAggregation]]]]]]]]]])

Parameters

Mandatory parameters

• input: The ‘input’ with values that are to be projected.

Optional parameters

• drivers: A list of nodes that will be applied as drivers. Relative drivers are determined by using the percentage unit on the node. By default, this list is empty.

  • (multiple) drivers can but do not need to be placed in squared brackets

    • Exception: When using a custom parameter order and multiple drivers, this list of drivers needs to be placed in squared brackets

• time: The level that is used to project the ‘input’. The function will calculate the projected values in a loop over the ‘time’ calculating each value based on the value of the previous loop. If no ‘time’ is provided, the value defaults to the lowest available time level of the ‘input’.

• start: The level value from ‘time’ that the projection will begin at. Any value in the ‘input’ that comes before the ‘start’ level value will be left unchanged. If no value is provided then the ‘time’ value from the ‘input’ incremented by one will be used.

• end: The level value from the ‘time’ that the projection will end at. Any value in the ‘input’ that comes after the ‘end’ level value will be left unchanged. If no explicit time level is provided, then the lowest level value of the ‘time’ value will be used.

• windowSize: When calculating a value for a loop iteration, the ‘windowSize’ defines the number of previous values that are used for the calculation. The calculation itself is an aggregation of all values in the window using the provided ‘windowFunction’. For example, a 'windowSize' of 1 would mean that the current value will be calculated only using the last previous value, while a size of three would use the previous 3 values. Note that a window value will not be calculated until it is full. The default window size is 1.

• windowFunction: The aggregation function to apply to the values within the window to calculate a value for each ‘time’ iteration. The available values are:

  • FIRST: The first value in the window. This is the default value.

  • LAST: The last value in the window.

  • AVG: The average of the values in the window.

  • SUM: The sum of all values in the window

  • PROD: The product of all values in the window

• windowType: Determines if the values in the window will be updated while looping. The available values are:

  • ROLLING: If the window is full the oldest value in the window will be replaced with the most recently calculated value. This is the default value.

  • FIXED: Once the window is full no new values will be added to the window

• mode: Determines how 'drivers' are applied. The available values are:

  • ADDITIVE: Each driver is applied additive in the provided order and there are no cross effects between them. This is the default value.

  • MULTIPLICATIVE: Drivers are applied in the provided order one after another. This means that every driver is applied on the base including all previous drivers, therefore potentially triggering cross effects between drivers.

• agingLevels: A list of levels from the ‘input’ that will be incremented/shifted with every loop iteration along the’time' value. For example, this can be used to increment a contract age every year when iterating over the years as ‘time’ value. By default, this list is empty.

• agingOffset: With which offset every ‘agingLevel’ is incremented/shifted with every loop iteration. This follows the same format as the SHIFT function, where -1 increments the 'agingLevel' by one value. The default value is -1.

• linkedLevelsAggregation: The aggregation function that applies to both the ‘input’ and the ‘drivers' if they have rows which contain levels and their linked to levels, but do not follow the links that were specified in the dimension management (broken links). This aggregation will only be applied if the incorrect links relate to a level that is within the same dimension as the ‘time’ value or an 'agingLevel’.
  The available aggregation types can be found here. The default value is LAST.

Limitations

• Every absolute driver needs to have the same levels as the ‘input’

• Every relative driver needs to either have the same levels or a subset of the levels of those on the ‘input’

• The following restriction apply to the ‘time’ value:

  • It must be a level of the ‘input’

  • It is required if there is no time level on the ‘input’

  • It must be the most granular level from its dimension on the ‘input’

• The following restrictions apply to the ‘agingLevel’:

  • Each ‘agingLevel’ must be on the ‘input’

  • No ‘agingLevel’ can be from the same dimension as the ‘time’ value

  • No ‘agingLevel’ can not be from the same dimension as another ‘agingLevel’

  • Each ‘agingLevel’ must be the most granular level from its dimension on the ‘input’

• The ‘windowSize’ should be 1 or larger

• The ‘agingOffset’ must be -1 or less

• The ‘start' and ‘end’ must be values from the 'time’ value

• The position of the ‘start’ within the ‘time’ value dimension must come before the ‘end’. This includes taking into consideration higher level in the dimension than the ‘time’ value.

• Important: This function always makes the links from the input and ‘agingLevel’ dimension be the same as in the dimension management.

• Do not use this function if you want the node to have different level links as the dimensions.

Custom Parameter Order

The ROLLFORWARD_ADVANCED can additionally be specified with a custom parameter order.

That offers the possibility to specify the parameters by their names without following the standard order described above.

This enables a deviation from the default for any parameter within the function without naming all prior parameters.

A combination of parameters by the standard order followed by a custom parameter order is possible as well.

How to:

• Parameters in a custom order requires the parameter to be named, before entering the parameter value (e.g. ROLLFORWARD_ADVANCED(windowSize = 3, OriginalNode = ‘Revenue’)

  • Parameter names need to be identical with the specified parameter name used in the “Parameter” section (upper/lower case letters do not matter)

  • The parameter name should not be put into quotes or other punctuation marks

    • Node names still need to be placed in single quotes, level names and level values in double quotes and math constants without any quotes

    • When providing a list of multiple drivers, the list needs to be placed in squared brackets

      • ROLLFORWARD_ADVANCED('inputNode', drivers = ['driver1', ‘driver2’])

      • ROLLFORWARD_ADVANCED('inputNode', drivers = 'driver1', ‘driver2’)

  • The parameter name and the parameter value are always separated with an equal sign and can be preceded or followed by white spaces or lines breaks

Limitations:

• When using a custom parameter order, all following parameters also need to be named

• A parameter cannot be specified by their name twice

• A parameter specified by the default order and later in the operation by its name will overwrite the specified value of the default order.

Example node operation:

ROLLFORWARD_ADVANCED(‘ExpandOneOnYearNode’, mode = “MULTIPLICATIVE”, windowFunction = “AVG”, windowSize = 2)

• All parameters which where not explicitly specified as named parameters will use the default value

• Only ‘mode’, ‘windowFunction’ and ‘windowiSze’ will use the specified values instead of their defaults

An ‘input’ is mandatory to be provided.

95

90

100

95

90

90

90

90

90

90

100

100

100

100

100

100

50

100

200

95

90

90 + 50 = 140

140

140

140

140 + 100 = 240

100

100

100

100

100 + 200 = 300

300

0.02

0.1

0.2

95

90

90 + (90 * 0.02) = 91,8

91,8

91,8

91,8 + (91,8 * 0.1) = 100,98

100,98 + (100,98 * 0.2) = 121,18

100

100 + (100 * 0.02) = 102

102

102

102 + (102 * 0.1) = 112,2

112,2 + (112,2 * 0.2) = 134,64

95

90

90

90

90

100

100

100

100

95

90

95

90

95

90

95

100

100

100

Flavour

2018

2018-Q1

2018-Q2

2018-Q3

2018-Q4

Lemon

15

30

30

15

Cookies

20

30

30

20

Flavour

2018

2019

2018

2018-Q1

2018-Q2

2018-Q3

2018-Q4

2019-Q1

2019-Q2

2019-Q3

2019-Q4

2020-Q1

2020-Q2

2020-Q3

2020-Q4

Lemon

15

30

30

15

15

30

30

15

15

30

30

15

Cookies

20

30

30

20

20

30

30

20

20

30

30

20

95

90

95 + 90
= 185

90 + 185
= 275

185 + 275
= 460

275 + 460
= 735

460 + 735
= 1195

100

0+100
= 100

100 + 100
= 200

100 + 200
= 300

200 + 300
= 500

300 + 500
= 800

95

90

90

90

90

90

90

100

100

100

100

100

100

95

90

50

100

Contributions

0.02

0.1

0.2

95

90

(90 + 50) * (1 + 0.02)
= 142,8

142,8

142,8

(142,8 + 0) * (1 + 0.1)
= 157,08

(157,08 + 100) * (1 + 0.2)
= 308,5

95

90

70

100

90

150

50

100

600

200

-0.02

-0.1

-0.2

95

90

70

50 * (1 + (-0.02))
= 49

100

90

70 * (1 + (-0.02))
= 68.6

49

100 * (1 + (-0.1))
= 90

150

90 * (1 + (-0.02))
= 88.2

68.6 + 600
= 668.6

49 * (1 + (-0.1))
= 44.1

(90 + 200) * (1 + (-0.2))
= 232

95

90

70

100

90

150

50

100

600

200

-0.02

-0.1

-0.2

95

90

70

50 * (1 + -0.02)
= 49

100

90

100 * (1 + -0.1)
= 90

150

70 * (1 + -0.02)
= 68.6

49 + 600
= 649

200 * (1 + -0.2)
= 160

Seasonal Effects

time

This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years.

start

The 'start' parameter defines the start of the projection period.

end

The 'end' parameter defines the end of the projection period.

windowSize

The ‘windowSize’ parameter defines the number of previous values that should be used for the calculation.

For the seasonal effects, that could be the previous months that should be taken into account to represent the seasonality.

Loop Calculations

time

This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years.

start

The 'start' parameter defines the start of the projection period.

end

The 'end' parameter defines the end of the projection period.

Aging

time

This parameter defines the level that will be used for the calculation of the loop for the projection. This could be fore example months, quarters or years.

start

The 'start' parameter defines the start of the projection period.

end

The 'end' parameter defines the end of the projection period.

agingLevel

The ‘agingLevel' parameter defines which level(s) are incrementally shifted along the 'time’.

For the aging use case, that could for example be the level ContractAge.

agingOffset

The “agingOffset” parameter defines with which offset a level is shifted with every loop along the projection.