+ Reply to Thread
Results 1 to 17 of 17

Inventory Planning - Help needed on replenishment quantity FORMULA

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #3
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #4
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Inventory Planning - Help needed on replenishment quantity FORMULA

    capping limit is for sum total.

  5. #5
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Inventory Planning - Help needed on replenishment quantity FORMULA

    Thanks Scott will look at the solution provided.

  6. #6
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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.
    Attached Files Attached Files
    Last edited by dev.jajati; 05-23-2013 at 05:07 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #8
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #9
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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. #10
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #11
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #12
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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. #13
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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. #14
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #15
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    Re: Inventory Planning - Help needed on replenishment quantity FORMULA

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

  16. #16
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    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. #17
    Registered User
    Join Date
    05-20-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2013
    Posts
    30

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts