I have two tables (top i.e. Total level and bottom i.e. by Product and Tenure level). I need to breakdown the total level to product level by a waterfall logic.
The logic is that I need to follow the ordering so that the first row of adjustment will be -3400 (row1). And every subsequent row will need to deduct from the total amount.
-3500-(-3400)
-3500-row2-row1
.and so on…
However, the adjustments cannot be overly adjusted (validate columns cant be negative).
As shown, row2 column H is -29612. This is an over-adjustment. Hence, for row2, i shouldnt adjust and would need to take -1988 instead.
The next row would be the same until there’s no negative amount in the validate column anymore (see final output - columns M & N).
Finally i would need to perform this for > 10 customers and the product varies for each customer.