Managing stock re-order levels

[jimbell]

We have a fast growing business & are looking for a more accurate way to manage stock levels effectively
We are running purchase re-order lists monthly that shows which part numbers are selling enabling us to set re-order levels
Lead time on parts generally is 8 weeks, so we have to guess a long way ahead
The problem area is when we have 1 off non repeat large orders against a part number.
If this part also has a regular low level usage requirement, the 1 off order wipes out the low level re-order request.
Example: we stock a widget. Re-order level 5 & re-order quantity 2.
If we receive an order for 20 off, once placed on order, there is no instruction to order the 5/2 off
When it is only one line this is easy to manage. We have 1000+ lines, so we lose control
Are there any kind of add ons, or other suggestions, on how to manage this please
Any suggestions are gratefully received

[brucedenney]

The problem is that you have one off non repeat orders.

There is no way to know that this is a non repeat order from the data set other than to wait and see if it repeats.

From a purely mathematical point for view, you could calculate the standard deviation in the data set, this will give you a good idea of how much variance there is within the data set.

The problem is if you have just landed a new client who is going to order a large number of these items regularly there is no way to distinguish this from the data than when you have a one off large order.

As such you have to use more than just maths to manage the stock. You need to go back to customer who have bought large quantities and see if they want to do this regularly. If you just follow the maths, when they next reorder, you will not have enough stock so you will loose the order and the statistics will not change so the level will never increase.

The simple answer to your question is that there is not enough information in the data set to accurately predict the stock requirements when this sort of exception happens. What you could do is to look for the exceptional data in the set, (using a standard deviation) and then review those items manually, ring up the customers do the follow up, get the extra information you need to make a decision.

The reality is that you have a limit (working capital/credit/warehouse space) on the amount of stock you can hold. You need to avoid stock shortages and you need to avoid overstocking.

Over stocking can be dealt with by running sales, get that stock gone, get the capital back and reinvest it in other lines.
Under stocking is lost business, if you use sales orders and cancel the lost part of it, this may help you find the lost business.
Prediction can be done based on historical trends, but you need to understand the peaks and troughs in sales. You might have a customer who normally purchases JIT and suddenly buy a bulk load and stop buying JIT, you reduce stock levels and then suddenly you get a massive order.

One of the best things you can do is to graph the data so you can picture it, see the peaks, see the sudden drops when you loose a customer or a customer changes buying patterns.

My suggestion is to use Excel to do this, you can link excel to your live data (subject to the amount of data) and then analyse is using pivot tables and pivot charts.

The bottom line is that there is no way that you can automatically predict stock requirements in a growing highly volatile environment, You need to use tools to identify the exceptional items in the data and then you need to talk to the customers and find out what these mean.

[jimbell]

Thanks for the reply. I guess I already knew the answer before asking.
But I needed to have it confirmed that there was not some "magic" solution
I will look at graphical representation as you suggest
Thanks again!