PRIOR_SERIES_PERIOD_LOOKUP

PRIOR_SERIES_PERIOD_LOOKUP function returns prior series period values. It can also go to forward series period values.

Syntax

PRIOR_SERIES_PERIOD_LOOKUP(seriesOffset, periodIndex, initialValue) -> [number]

Arguments

Arguments	Datatype	Description
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.

Remarks

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.

Example

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.

Period	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
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

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.

Result

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.