Hi, I need to do following calculations on a dataframe. It should be done for each period and calculated value will be used for next period's calculation. Adding sample data and formula from excel here. Thanks in advance for your help.
Need to calculate NewCashFlow and NewAllocation for period 1 onwards.
Formula for NewCashFlow for period 1 is =(G5*F2)+E5+E2 and NewAllocation is =H5/SUM(H$5:H$7)
From Period 2 onwards NewCashFlow is =(G8*I5)+E8+H5 and NewAllocation is =H8/SUM(H$8:H$10)
Sample data is:
| Period | HorizonDate | GroupCode | AssignmentCode | CashFlowAmt | AllocationPercent | UnassignedCashFlowAmt | NewCashFlow | NewAllocation |
| 0 | 3/31/2023 | Group1 | Allocation 1 | 38316684.90 | 0.47 | 0.00 | | |
| 0 | 3/31/2023 | Group1 | Allocation 2 | 2589294.65 | 0.03 | 0.00 | | |
| 0 | 3/31/2023 | Group1 | Allocation 3 | 41209290.55 | 0.50 | 0.00 | | |
| 1 | 4/30/2023 | Group1 | Allocation 1 | -3087.23 | 0.00 | 5846.86 | 38316325.94 | 0.466620701 |
| 1 | 4/30/2023 | Group1 | Allocation 2 | -208.62 | 0.00 | 5846.86 | 2589270.40 | 0.031532438 |
| 1 | 4/30/2023 | Group1 | Allocation 3 | -3320.29 | 0.00 | 5846.86 | 41208904.49 | 0.501846861 |
| 2 | 5/31/2023 | Group1 | Allocation 1 | -3104.73 | 0.00 | -11987.00 | 38307627.82 | 0.466620701 |
| 2 | 5/31/2023 | Group1 | Allocation 2 | -209.81 | 0.00 | -11987.00 | 2588682.61 | 0.031532438 |
| 2 | 5/31/2023 | Group1 | Allocation 3 | -3339.11 | 0.00 | -11987.00 | 41199549.74 | 0.501846861 |
| 3 | 6/30/2023 | Group1 | Allocation 1 | -3115.46 | 0.00 | -2954.63 | 38303133.67 | 0.466620701 |
| 3 | 6/30/2023 | Group1 | Allocation 2 | -210.53 | 0.00 | -2954.63 | 2588378.91 | 0.031532438 |
| 3 | 6/30/2023 | Group1 | Allocation 3 | -3350.66 | 0.00 | -2954.63 | 41194716.31 | 0.501846861 |
