Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Simplify Cumulative Formula

https://drive.google.com/file/d/1K08wErvFMTg_SoqWxZWO4x0WECyD99Q8/view?usp=sharing

 

In the attached file, Final to Collect (4) is arrived after calculating 1,2,3 steps.
Is there anyway to simplify this calculation,
The basic logic is as below:

From the To Collect need to subtract Unapplied Receipts

Eg A :
In this case Unapplied Receipts < 2021 January To Collect (15 < 25 )
For 2021 January , Final To Collect =To Collect -Unapplied Receipts = 25-15 = 10
For 2021 February, Final To Collect= To Collect=3
For 2021 March, Final To Collect= To Collect=40

Eg B :
Suppose Unapplied Receipts was 30
In this case Unapplied Receipts > 2021 January To Collect (30 > 25 )
For 2021 January , Final To Collect =To Collect -Unapplied Receipts = 25-30= Since 30 is not fully subtractable from 25, 5 will be subtracted from 2021 February
therefore, for 2021 January ,Final To Collect = 0

For 2021 February, Final To Collect= To Collect -Unapplied Receipts=3 -5=
But since 5 is not fully subtractable from 3, 2 will be subtracted in March
therefore, for 2021 February, Final To Collect = 0

For 2021 March, Final To Collect= To Collect -Unapplied Receipts=40-2
Final To Collect = 40-2 = 38

For 2021 April, Final To Collect = To Collect …and so on…

My question is, to perform this logic, current I am performing steps 1,2, 3,4 as shown in the picture.
Is there any easier way of doing this?