I am exploring a BAQ that calculates what “target” inventory levels should be. I am no expert in this area but I am looking to gain knowledge. I’ve started by calculating the average daily usage and multiplying by lead time. I then add the minimum and safety qty to this number. This represents my “high limit”. The “low limit” would then be minimum + safety qty. Within these limits represent “proper” inventory level. Are there other considerations you can think of? I am sure I have oversimplified this.
Higher ups speak in a language of inventory turns and cash flow and working capital. That’s all financial based and completely disconnected to the parts they represent. So I thought taking a bottom-up approach would give me the ability to target parts with poor planning parameters or unusual buying patterns. Long story short, find the appropriate inventory level and then grade us against it. It really would give feedback to the circuit where planners/buyers are empowered to update the planning settings.
I think depending on how you are coming up with your MOH and SS, you might be planning too much inventory by adding that to average daily usage. What are your plans for these?
For our repetitive inventory calculations in Epicor we use Minimum On Hand for average usage over lead time. We use safety stock for “unexpected” usage. We calculate SS using Demand Variability (usage standard deviation calc) and sometimes Supply Variability (lead time standard deviation calc).
We recalculate these on a quarterly basis. This is the basis of our statistical SS calcs: https://web.mit.edu/2.810/www/files/readings/King_SafetyStock.pdf
We have found that this methodology works pretty well for our inventory that has regular usage. It falls down for large project orders or usage that is very sporadic.
Also, Epicor’s IPO program looks quite nifty in my opinion. I attended a session on it at insights. Have you seen that and the methodologies it uses? It could even provide additional ideas for your plans.
The min/max should be a factor of your average job quantity. If you use use 4 of a part on each job but the math comes out to 7 you want to stock 8.
I have a BAQ that we use to do this. You put in a start and end date and it will give you average daily usage during that period and use lead time to find min quantity. It doesn’t take into account demand variability which I should arguably change.
It also calculates current minimum cost vs inventory cost if quantities were adjusted. It works well and dropped our inventory >30%.
I can try and strip out all of the UD fields and share it but I think I’m a couple of versions ahead of you so it won’t import.
The BAQ I have is specific to our company so I don’t think it would do you any good. The idea was the same as yours though as far as it’s a tool that lets the buyers/inventory maintain part parameters.
Try to align your high limit with your company’s goal for inventory turns. If they want 6/year then your high limit would be ~2 months of usage.
We have some purchased parts that are long lead that would bring us far away from our inventory turns goal so we utilize contract POs for those.
The simple calc of (Avg Daily Use * lead time) doesn’t account for variability of demand or lead time, so you have to be careful about the time frame that you calculate the avg use from. The paper @Nancy_Hoyt linked is awesome and explains the effect this has. If you can utilize the calcs in that paper then no ‘higher up’ will be able to argue with you no matter how smart they think they are.
Getting the min/max numbers right is one of the hardest things to do, full stop. Doesn’t mean you don’t try. AI companies are now also selling solutions for this exact problem.
Unless you can predict the future, all you really have to go on is historical data, which is not enough to know how much you need next month. You can use it to analyze consumption history, come up with a per day usage, and combine that with other factors like lead time, etc, to get closer to figuring out how much you should have on hand at any given time.
Per day consumption gets trickier to calculate when you consider something has to be on the market to be sold/consumed. Is this a brand new product that has been on the market for 2 weeks? Then analyzing a 60 day sales history is unfair for that part. When your business model involves only offering products for sale when they are in stock, like eCommerce, for example, days when the product is out of stock shouldn’t be included, etc.
When higher ups and others talk about the money side, it usually boils down to service level. When I have a part that I pull from stock, what % of the time is it there when I need it? It is a tight rope walk with always having your product available when you need it on one side, and having too much money tied up in inventory holding costs on the other. What service level % is right for you and your company is an important decision.
Great feedback! I appreciate it all. A couple things that hit for me and/or clarifications based on some assumptions/questions in your feedback:
That’s a great way to quickly look and validate that my numbers look “reasonable”. I appreciate this.
I should mention that I am looking at our next 6 months of projected usage and then figuring out the avg daily usage based on that (not historical usage).
100% - I don’t expect anything to be perfect. But is there room for improvement? Absolutely.
Have you looked at the Min / Max / Safety Mass Update program? This might do most of the hard work for you. You can have it perform the calculations and give you the proposed values without updating the actuals.
Yes - and I do think that’s a possible way to manage this so that it’s super easy/transparent of what our expected range is. I also was able to find the BO/Method used to calculate those values and overwrite the calculations to suit how I want them to be calculated. So it’s very plausible to use this system and through a BPM it can be customized to suit any business’s inclination as far as how to set them.
Hidden indeed I was unfamiliar with it, in the help it has “the As of Date field, select the cutoff date for usage history used to perform quantity calculations” this is the end date to be considered correct? is there a way to dictate the start date? our early years of transactions are inaccurate so I would like to only use the last 3 years
Yes, the ‘As Of Date’ is the date it works back from. The number of days to look back from this date is set using the ‘History Window’ at either part class or part level.
From the Epicor Application Help:
