Complex formula (to me) & don't know where to begin

[karmes]

Hello -

I would say I am slightly above basic skills in excel and now I need to see if I can do something a little more complicated (for me) but I don;t even know what the functions for it may be called so I have no idea what to even search for in the forums...

I am creating an inventory spreadsheet that has (qty on hand) - (qty required) = +/- (inv req) If that (inv req) is greater than my (min order), I want it to calculate the number I have to order by whatever multiplier it takes to get to the min order or multiples of the minimum order. So:

I have 300 on hand, I have a requirement for 900, which leaves me (600). My minimum order is 400 so I would need to order 800 and ideally I could create something that would do that calculation for me automatically. Is that possible in excel? where do I even start to find the functions and such that would help me do this?

Thanks for any advice that can be offered! I am completely perplexed at the moment.

Kristi

[Melvinrobb]

If B3=On Hand, C3=Required, and D3=Min Order, the following formula should work:

Please Login or Register  to view this content.

[BB1972]

Kristi, welcome to the forum.

Your logic and train of thought are sound; and yes, this is something Excel is very good at doing. You should upload a small sample set of data you're working with - even just with what's above as data - to get the ball rolling. While we're happy to help here, some members are disinclined to type out your data-set as a prerequisite to helping you.


EDIT: Or you could just use Melvinrobb's formula

[judgeh59]

Being an Inventory Analyst I have a few questions
Do you have a ROP (Re-order Point) - this is not Necessarily the same as minimum order quantity (MOQ)

[karmes]

Melvinrobb -thank you so much!!! that worked perfectly and it was much simpler than I thought it would be. I had all these and/if/greater than/less than mumbo jumbo swirling in my head and was quite relieved it wasn't that complicated. Thanks for the quick reply too!!!!

[karmes]

judgeh59 - Hmmmm, I think I am using minimum order as the reorder point based on my current requirements and next months project requirements. I don't see how to upload a sheet with just a small amount of data to show you what I am trying to do. This is coming out of trying to do a cash flow projection. we are a new small small business.

Qty on hand April Req May Req June req
cost/unit min order in units min cost 748 350 500
$0.75 400 $400.75 400
$9.14 75 $84.14 75

[Melvinrobb]

No problem. I figured you would be pleasantly surprised.
Next post, try and give a more relevant title post (even if you are unsure of the correct formula), such as "calculate inventory re-order amount based on multiple of minimum". It will help others to find the post when searching for it, and more importantly for you, it will help others who may be able to help you out recognize that and open up the post.
Also, Make sure to change this thread to "solved" (under thread tools).

[judgeh59]

to attach a file, click on Go Advanced and then scroll down to attachment management....So MOQ (Minimum Order Quantity) can and sometimes is the same as ROP (Re-Order Point) depending, usually on lead time and usage (Demand)....

[karmes]

projected inv purchase.xlsxprojected inv purchase.xlsx

I think that may have uploaded it.

[karmes]

No problem. I figured you would be pleasantly surprised.
Next post, try and give a more relevant title post (even if you are unsure of the correct formula), such as "calculate inventory re-order amount based on multiple of minimum". It will help others to find the post when searching for it, and more importantly for you, it will help others who may be able to help you out recognize that and open up the post.
Also, Make sure to change this thread to "solved" (under thread tools).

Thanks for the pointers and suggestion! I just realized what i uploaded didn't have the formula in it but it did do exactly what I *think* i need it to unless
judgeh59 thinks there is a flaw in my logic

[judgeh59]

so here is weird part....so in your worksheet your OHI (OnHandInv) is 400 and your demand for April is 748. That leaves you with 348, under your MOQ(minimum order quantity), so your order 400, now depending on when those come in (leadtime) you now have 748 on hand. So, in May, you can use the OHI of 400, you have to use the OHI of 748 and the demand for May is 350 and would not put you below the MOQ, IF you used the OHI of 400 and then you would order another 400 and have to much inventory and probably increase your DOI (days of inventory)...does this make sense?....

[karmes]

so here is weird part....so in your worksheet your OHI (OnHandInv) is 400 and your demand for April is 748. That leaves you with 348, under your MOQ(minimum order quantity), so your order 400, now depending on when those come in (leadtime) you now have 748 on hand. So, in May, you can use the OHI of 400, you have to use the OHI of 748 and the demand for May is 350 and would not put you below the MOQ, IF you used the OHI of 400 and then you would order another 400 and have to much inventory and probably increase your DOI (days of inventory)...does this make sense?....

Oh boy... I was looking at it this way - OHI 400 - 748 = -348 then i order 400 which leaves me with 52 to carry to next month, that won't leave me enough to do next month so I need to actually order 800.

[karmes]

judgeh59 - i just filled in more of my actual spreadsheet and now I see what you are saying. This formula doesn't take into account if my future demand is less than what I have on hand. If I had 300 OHI but only had requirements going forward for 200, it would say I needed to order because my min was 400 when in fact I don't. I don't want anymore inventory than I absolutely need to have. Now I am thinking the formula/logic is more complicated again.

[judgeh59]

okay....my bad....that's what I get for 2 things at once....sorry about that....you are correct that your OHI will drop to 52 at the end April and you'll have to order but the next calculation has to come from the new OHI of 52 not the OHI of 400 so you have to keep a running OHI to do the calculation....so it is very possible that MelvinRobbs formula will work, just be a where of the running OHI

[judgeh59]

welcome to the crazy world of inventory control....or lack there of.....it might be that laying out out your data in a different way may work....think of it as a check book....debit and credits....

[karmes]

okay....my bad....that's what I get for 2 things at once....sorry about that....you are correct that your OHI will drop to 52 at the end April and you'll have to order but the next calculation has to come from the new OHI of 52 not the OHI of 400 so you have to keep a running OHI to do the calculation....so it is very possible that MelvinRobbs formula will work, just be a where of the running OHI

Ahhh, ok I see what I did wrong because I wasn't using the current OHI. So now when my requirements plus inventory don't exceed 400 it shows as -400. If it is negative I wouldn't need to order then. How would you edit the formula to say if it is negative make 0?

[karmes]

welcome to the crazy world of inventory control....or lack there of.....it might be that laying out out your data in a different way may work....think of it as a check book....debit and credits....

I hope I learn to like it

[judgeh59]

Please Login or Register  to view this content.

so, if the ROUNDUP function is greater than 0 it will return the ROUNDUP function data, if is below 0 it will return a 0

[karmes]

Wow, I would have never figured that one out...whew. Thanks so much everyone for your help!!!!

[judgeh59]

my pleasure....and yes, it is a very fun job....for me anyway.....