I am trying to create a formula to help my scheduler and materials manager order enough material to meet demands that are greater than what we can produce in our current lead time. I want to start as far out as my customers orders are planned and roll overflow orders backwards until all quantities are below the specified max production. Here is what I am working with as well as a formula that would do the trick if it were actually legit....
Max Production: $A$1
Monthly Order Quantities: A2:S2
If any given month's production is greater than $A$1 then I need the overage rolled into the previous month and so an and so forth.
Logic:
S2= if (S2<=$A$1,S2, if (S2>$A$1, R2=R2+(S2-$A$1) and S2=$A$1))
R2= if (R2<=$A$1,R2, if (R2>$A$1, Q2=Q2+(R2-$A$1) and R2=$A$1))
Q2= if (Q2<=$A$1,Q2, if (Q2>$A$1, P2=P2+(Q2-$A$1) and Q2=$A$1))
Bookmarks