Need formulas to adjust order and balance if inventory exceeds threshold

Cols B - C list six months' budgeted weekly sales.

As different Products have different Lead Times, and different Order sizes, formulas in Cols F and G "match" the Lead Time week against the Sale week and "round up" the Quantities where necessary:

=IFERROR(CEILING(INDEX($C$6:$C$30,MATCH(E6+(G$4*7),$B$6:$B$30,0)),F$4),"")

(Finds the quantity in Col C where Col B matches 'Lead time date in Col E + Lead time weeks in G4', and "rounds up" the quantity to the nearest Order Value in E4.)
E.g Sales w/e 21 Jan will be 7,670 items. Order has to be placed two weeks in advance (7 Jan), As the Order quantity is in 50s, the order will be for 7,700, leaving 30 in Inventory.
Cols F - G show "unchanged" Orders and resulting Closing Stocks.

All that works perfectly.

What I need is to incorporate another element in the Col F formula so it reduces the order quantity by however many multiple of the Order Size IF the previous week's inventory exceeds the Order Value, and change the formula in Col G so it deducts the previous 'excess' inventory (as shown in Cols J - K).

Checksums in row 39 show all three sets match in total, but utilising the excess inventory (Col K) saves buying 450 units.

All solutions, suggestions and alternatives welcome as ever.

Ochimus