VBA to optimize outflows

Hi all,

I am trying to calculate a liquidity based on expected inflows and working capital threshold.

In the attached workbook, Row 24 is being calculated via formula before a threshold check. I want a macro that checks if a value in any cell in the range (C27: L27) exceeds working capital threshold which is 50M.

If it does, like in H27, the macro should calculate the exceeding amount (95,420,906 - 50,000,000 = 45,420,906) and start deducting it from the most recent periods in Row24 until the value in the cell, where the threshold breached, reaches 50M. However, the value in Row 24 cannot be less than 0. There could be multiple cells where the threshold exceeds so the macro should continue to check until all the values in the range (C27: L27) are equal to or less than 50M

I have manually calculated the Row24 in the 'After' sheet of the attached workbook.

These numbers are dynamic and the threshold can exceed in any of the cells in the range (C27: L27).

Thanks in advance!