# Inventory Planning - Help needed on replenishment quantity FORMULA

1. ## Inventory Planning - Help needed on replenishment quantity FORMULA

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.

2. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

MY INVENTORY LEVEL AT THE END OF THE MONTH SHOULD NOT EXCEED \$200,000.00 WHICH IS THE CAPPING LIMIT.
does this mean for an individual part number/sku or sum total?

3. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

I think i have the solution that you want, but with out the capping limit portion, as I don't know if it is sum or per part, if it is per part it is easier to do, however if it is for sum total of all parts, then it will be more difficult because each part is of a different price but there is no indication of how important each part is relative to the others.

my answer is in column AH, AC through AG are my calculation steps, just for reference, you can delete them without affecting AH. it helps to make these columns to help you visualize the longer formula. I hope it works the way you want.

Inventory Planning.xlsx

4. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

capping limit is for sum total.

5. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

Thanks Scott will look at the solution provided.

6. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

Hi Scott! Got a chance to look through the solution provided by you and its pretty close. Did some random checks and came across some examples where I feel numbers projected for requirement are not correct.

Part: C9452N
ROP: 91
Upper SL: 234
OH: 3
OO: 0
BO: 80
MOQ: 20
Pro. Qty.: 140

Part: C9453N
ROP: 174
Upper SL: 447
OH: 125
OO: 50
BO: 280
MOQ: 1
Pro. Qty.: 92

Based on your formula quantity proposed are 140 and 92 units. With the numbers I'll be able to clear backlog of C9452N and will end up placing new orders again as after clearing BO, my stock level will be below ROP. For C9453N am not even will be able to clear the entire backlog.

My need here is to get a number which will help me stock up to the upper limit.

For example, based on the above numbers proposed quantity for C9452N should be 300 units. Which will help me clear the BO of 80 units and leave me with on hand inventory of 220 units.

7. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

=IF(\$X132+\$Y132-\$AA132<\$V132,IF(AND(\$Z132>0,IF(\$W132-\$X132+\$Y132-\$AA132<=0,0,\$W132-\$X132+\$Y132-\$AA132)<\$W132),IFERROR(INT(IF(\$W132-\$X132+\$Y132-\$AA132<=0,0,\$W132-\$X132+\$Y132+\$AA132)/\$Z132)*\$Z132,0),IF(\$W132-\$X132+\$Y132-\$AA132<=0,0,\$W132-\$X132+\$Y132-\$AA132)),0)

copy and paste this into cell AH132 for C9452N, it will give you the correct answer but it does it by adding the backlog when calculating the number of MOQs to order ie \$W132-\$X132+\$Y132+\$AA132)/\$Z132 part of the formula, i don't know if that is what you were looking for. it gives me 652 for sku C9453N (the other red highlighted row)

try it out on others that were correct during your spot check to see if affects those as well

8. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

I think it should be correct given that C9451N gives me 1503, which would be 1503-581 backlog+17 on hand=939 upperstock limit

here it is with the update to the formula, maybe spot check this one instead.
Inventory Planning2.xlsx

9. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

Thanks!
Will check the formula out.
If you refer the initial attachment, under column AE there are formulas which solves my MOQ issue but I wasn't able to include the same with the logic which am looking for, as it retains an error in cases where OH+OO-BO is less than ROP but MOQ is 0.

10. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

C9390N has an OO of -100, but the formula holds 751-366 BL+4 OH-(-100) OO=489 UL

11. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

so the sum of On hand + sum of order (the formula we've been working on) should be less than or equal to \$200,000, right now it is \$203,517.37.

how do you propose you would like to handle which parts are ordered less than the order (the formula we've been working on)?

I would suggest adjusting the Upper Stocking Limit on the parts that you don't use as frequently, tightening up the JIT, or lowering the frequency of purchases for parts with the least lead time, that way if you cut the Sum of Upper Stocking Limit to \$200,000 you will never be over.

12. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

Scott! The formula is working fine ...
There is another favor to ask of you... There are certain SKU's where UL is equal to MOQ... in those cases is it possible to have required quantity if OH+OO-BO is less than ROP??? because in such cases propose quantity will only appear when OH is 0.

13. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

That's what am intending to do.... for some of the SKU's UL is actually the MOQ as I wanted to maintain some level of inventory. If I make changes over there my \$value of UL will fall below 200,000.00 mark.

14. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

sure, that's pretty simple, i did an example on line 47, I changed the OH to 0 as a test, I highlighted it Purple. the original quantity in the cell was 9

Line 38 the answer is still 0 because there are 10 on order which means that you don't need to order more

Inventory Planning3.xlsx

15. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

Scott! formula is throwing up results as expected. Thanks for the help

16. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

no problem, it was a fun problem to solve, can you hit the star to boost my reputation?

17. ## Re: Inventory Planning - Help needed on replenishment quantity FORMULA

next phase is to have this workbook completely automated, which is in the pipeline.....there are few more interesting projects coming up. will share the intent and logic as and where I start working on them.

thanks again