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.
Last updated