Hi All,
I use excel to monitor my inventory levels on a daily basis and punch in the requirements in the system. Based on nature and requirement of the business I have maintained certain constants to calculate safety stock, reorder level and required quantity.
Am not satisfied with the results of required quantity. I have tried but not able to combine the logics to get desired results.
Here's what am looking for:
If, Onhand+Onorder-Backlog is less than Reorder level, then Upper stocking limit - (onhand+onorder-backlog) gives me the quantity which I need to order for ramping up inventory level.
My problem arises where SKU's have MOQ's, as my replenishment quantity should be in multiples of MOQ (over here I have used floor formula to resolve MOQ issue)
This is what I need: IF, OH+OO-BO<ROP, STOCKING LIMIT - (OH+OO-BO) GIVES ME THE REQUIREMENT, IF THERE IS MOQ THEN THE PROPOSED REQUIREMENT SHOULD BE IN MULTIPLE OF MOQ, AND IF THERE IS NO MOQ THEN REQUIREMENT SHOULD BE THE ACTUAL NUMBERS.
THE REQUIREMNT SHOULD NOT EXCEED UPPER STOCKING LIMIT FOR THE SKU.
MY INVENTORY LEVEL AT THE END OF THE MONTH SHOULD NOT EXCEED $200,000.00 WHICH IS THE CAPPING LIMIT.
APPRETIATE YOUR HELP AS THIS IS CRITICAL FOR MY PROJECT AM WORKING ON.
Bookmarks