ROLLFORWARD_ADVANCED
AVAILABLE SINCE 6.0
As the name indicates, this formula is the ADVANCED version of the regular ROLLFORWARD formula.
The ROLLFORWARD_ADVANCED has essentially the same functionality as the ROLLFOWARD with the advantage of additional optional parameters that allow for a more advanced projection of your data.
These additional optional parameters are described in detail below.
To learn more about the ROLLFORWARD, please look at the formulas' documentation page where the basic use case of the ROLLFORWARD with one input and 1-3 drivers is shown.
Overview
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
Optional parameters
|
Limitations |
|
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:
Limitations:
Example node operation: ROLLFORWARD_ADVANCED(‘ExpandOneOnYearNode’, mode = “MULTIPLICATIVE”, windowFunction = “AVG”, windowSize = 2)
An ‘input’ is mandatory to be provided. |
Examples
Note all examples are with a time horizon of 2017 - 2023
ROLLFORWARD_ADVANCED with no optional parameters
Nodes:
input: Sold Ice Cream
Flavour | 2017 | 2018 |
---|---|---|
Lemon | 95 | 90 |
Cookies | 100 |
ROLLFORWARD_ADVANCED('input')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 | 90 | 90 | 90 | 90 |
Cookies | 100 | 100 | 100 | 100 | 100 | 100 |
ROLLFORWARD_ADVANCED with an absolute driver
Nodes:
AbsoluteDriver: New Locations
Flavour | 2019 | 2022 | 2023 |
---|---|---|---|
Lemon | 50 | 100 | |
Cookies | 200 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'AbsoluteDriver')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 + 50 = 140 | 140 | 140 | 140 | 140 + 100 = 240 |
Cookies | 100 | 100 | 100 | 100 | 100 + 200 = 300 | 300 |
ROLLFORWARD_ADVANCED with a relative driver
Nodes:
RelativeDriver (unit = ‘Percent’): Marketing Activities
2019 | 2022 | 2023 |
---|---|---|
0.02 | 0.1 | 0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'RelativeDriver')
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 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 |
Cookies | 100 | 100 + (100 * 0.02) = 102 | 102 | 102 | 102 + (102 * 0.1) = 112,2 | 112,2 + (112,2 * 0.2) = 134,64 |
ROLLFORWARD_ADVANCED with ‘start’ and ‘end’ configured
All previous examples use the default ‘start’ of 2019 as the input data ends in 2018, and ‘end’ of 2023 as this is the end of the horizon.
This example shows how the range can be changed.
ROLLFORWARD_ADVANCED('input', "Year", "2019", "2021")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 |
---|---|---|---|---|---|
Lemon | 95 | 90 | 90 | 90 | 90 |
Cookies | 100 | 100 | 100 | 100 |
ROLLFORWARD_ADVANCED with ‘windowSize’ configured
All previous examples use the default ‘windowSize’ of 1.
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking seasonality into account.
Outputnode: ROLLFORWARD_ADVANCED('input', "Year", "2019", "2023", 2)
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 95 | 90 | 95 | 90 | 95 |
Cookies | 100 | 100 | 100 |
Example for seasonality over the time level ‘Quarters’
input: Sold Ice Cream in Quarters
Flavour | 2018 | |||
2018-Q1 | 2018-Q2 | 2018-Q3 | 2018-Q4 | |
Lemon | 15 | 30 | 30 | 15 |
Cookies | 20 | 30 | 30 | 20 |
Outputnode: ROLLFORWARD_ADVANCED('input', "Quarter", "2019-Q1", "2020-Q4", 4)
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 |
ROLLFORWARD_ADVANCED with ‘windowFunction’ configured
All previous examples use the default ‘windowFunction’ "FIRST".
This example shows how changing this parameter can affect the calculations.
ROLLFORWARD_ADVANCED('input', "Year", "2019", "2021", 2, "SUM")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 95 + 90 | 90 + 185 | 185 + 275 | 275 + 460 | 460 + 735 |
Cookies | 100 | 0+100 | 100 + 100 | 100 + 200 | 200 + 300 | 300 + 500 |
NOTE
'…' uses default values.
ROLLFORWARD_ADVANCED with ‘windowType’ configured
All previous examples use the default ‘windowType’ "ROLLING".
This example shows how changing this parameter can affect the calculations.
ROLLFORWARD_ADVANCED('input', "Year", "2019", "2021", 1, "FIRST", "FIXED")
Flavour | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
Lemon | 95 | 90 | 90 | 90 | 90 | 90 | 90 |
Cookies | 100 | 100 | 100 | 100 | 100 | 100 |
NOTE
'…' uses default values.
ROLLFORWARD_ADVANCED with ‘mode’ configured
All previous examples use the default ‘mode’ "ADDITIVE".
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking interest rates into account.
input: Account Balance (Base)
2017 | 2018 | |
---|---|---|
Account Balance | 95 | 90 |
AbsoluteDriver: Deposit
2019 | 2022 | 2023 | |
---|---|---|---|
Deposits | 50 | 100 |
RelativeDriver (unit = ‘Percent’): Additional Contributions
2019 | 2022 | 2023 | |
---|---|---|---|
Contributions | 0.02 | 0.1 | 0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input', 'AbsoluteDriver', 'RelativeDriver', "Year", "2019", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE")
2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | |
---|---|---|---|---|---|---|---|
Account Balance | 95 | 90 | (90 + 50) * (1 + 0.02) | 142,8 | 142,8 | (142,8 + 0) * (1 + 0.1) | (157,08 + 100) * (1 + 0.2) |
ROLLFORWARD_ADVANCED with ‘agingLevels’ configured
All previous examples use no ‘agingLevel’ values.
This example shows how changing this parameter can affect the calculations.
This functionality can be used to project data while taking contract aging into account.
Nodes:
input2: Contracts
Contract Age | 2017 | 2018 | 2019 |
---|---|---|---|
1 | 95 | 90 | 70 |
2 | 100 | 90 | |
3 | 150 |
AbsoluteDriver2: Additional Contracts
Contract Age | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|
1 | 50 | |||
2 | 100 | |||
3 | 600 | 200 |
RelativeDriver2: Churn Rate
2020 | 2022 | 2023 |
---|---|---|
-0.02 | -0.1 | -0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input2', 'AbsoluteDriver2', 'RelativeDriver2', "Year", "2020", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Age"])
Contract Age | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
1 | 95 | 90 | 70 | 50 * (1 + (-0.02)) | |||
2 | 100 | 90 | 70 * (1 + (-0.02)) | 49 | 100 * (1 + (-0.1)) | ||
3 | 150 | 90 * (1 + (-0.02)) | 68.6 + 600 | 49 * (1 + (-0.1)) | (90 + 200) * (1 + (-0.2)) |
ROLLFORWARD_ADVANCED with ‘agingOffset’ configured
The previous example use the default ‘agingOffset’ value of -1.
This example shows how changing this parameter can affect the calculations.
input3: Contracts
Contract Types | 2017 | 2018 | 2019 |
---|---|---|---|
A | 95 | 90 | 70 |
B | 100 | 90 | |
C | 150 |
AbsoluteDriver3: Additional Contracts
Contract Types | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|
A | 50 | |||
B | 100 | |||
C | 600 | 200 |
RelativeDriver3: Churn Rate
2020 | 2022 | 2023 |
---|---|---|
-0.02 | -0.1 | -0.2 |
Outputnode: ROLLFORWARD_ADVANCED('input3', 'AbsoluteDriver3', 'RelativeDriver3', "Year", "2020", "2023", 1, "FIRST", "ROLLING", "MULTIPLICATIVE", ["Contract Types"], -2)
Contract Types | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|---|---|---|
A | 95 | 90 | 70 | 50 * (1 + -0.02) | |||
B | 100 | 90 | 100 * (1 + -0.1) | ||||
C | 150 | 70 * (1 + -0.02) | 49 + 600 | 200 * (1 + -0.2) |
Potential Use Cases
Potential Use Case | Which Parameters | How to use it? |
---|---|---|
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. |