PRIOR_SERIES_PERIOD_LOOKUP
Last updated
Last updated
PRIOR_SERIES_PERIOD_LOOKUP function returns prior series period values. It can also go to forward series period values.
PRIOR_SERIES_PERIOD_LOOKUP(seriesOffset, periodIndex, initialValue) -> [number]
This function can only be used after referring a node for which this calculation needs to be done as shown below in the example.
Seriesoffset and periodindex can also go back to many prior series/periods.
Consider a scenario where you want to create cashflow statement for 3 years and refer the closing balance of prior period as opening balance of current period. In the below table net cash in / out for three periods are given. Calculate the opening cash balance and closing cash balance for all periods in 2020,2021, 2022.
To achieve this, the following formula should be written in the [Opening cash Balance] node:
[Closing Cash Balance]
.
PERIOD_LOOKUP
(-1,
[Closing Cash Balance]
.
PRIOR_SERIES_PERIOD_LOOKUP
(1,12,183439))
Closing Cash Balance node name is referred in the formula before using PERIOD_LOOKUP & PRIOR_SERIES_PERIOD_LOOKUP so that the calculation is performed for this specific node values. In the PRIOR_SERIES_PERIOD_LOOKUP initial value is given as 183439 which will be considered as first period value for the first series. In the SeriesOffset, the value is given as 1 which will go back to previous series and get the 12th period value from the node Closing Cash Balance. PERIOD_LOOKUP will get the previous period value.
The initial value mentioned in the formula (183439) is taken as the January 2020 opening balance. Closing balance of every month is carried forward as the opening balance for the next month. Closing cash balance of 2020 December will be carried forward as opening balance for January 2021. This happens for all the series.
seriesOffset
Number
The number of series it has to go back.
periodindex
Number
The number of periods it has to go back.
initial Value
Number
The value of first period.
Opening Cash Balance 2020
183439
Net Cash in/out 2020
10279
10413
14912
9905
15466
7953
14312
12984
12691
12581
10357
31164
Net Cash in/out 2021
12335
2056
2056
2056
2056
2056
2056
2056
2056
2056
2056
2056
Net Cash in/out 2022
12828
2138
2138
2138
2138
2138
2138
2138
2138
2138
2138
2138