I'm trying to develop a tool that will automatically generate a daily production schedule. The last feature I'm trying to add is a function to prioritize the production plan by Mfg Line Number.

I4 = Cases On-Hand
N4 = Avg CS Sold/Wk
Q4 = Max CS Sold/Wk
AG4 = On Order (Demand)
AK4 = Cases or Amt We should Have On-Hand
AL4 = Cases or Amt We should make
BA4 = Mfg. Line Number

I'm currently taking I4 (On-Hand) subtracting AG4 (On-Order) and then dividing the result by the Q4 (Max CS Sold/Wk) to generate a %. Based on that percent I have the following formula to rank the results by Line.


I know it's a lot to ask but I could use a fresh set of eyes here. Is there a better way? How would you do it?

Any help would be greatly appreciated.