How important do you think it is to control your warehouse? Many companies around the world underestimate the power of effective warehouse management.
In this post, I will demonstrate how to apply statistics to your warehouse, so you can view it as a valuable asset that can bring numerous benefits.
It is not the purpose of this post to demonstrate them, but I will always be grateful to share my thoughts with you in case you are curious.
BACKGROUND
From now on we assume we have a warehouse where we store thousands of materials. Each of them has its own ID, which means we are able to check its data flow (in this case, the consumption).
If we get to this point where we can manage this simple data request, then your company is able to apply this statistical system to control and optimize stock levels.
* It does not matter which your warehouse management system is (as long as you can trace your consumption movements for each reference)
The main goal is to do a continuous regulation of materials to make sure you only have what you really need, therefore:
- It will reduce overstocks, increasing overall benefits for your company.
- It will increase understocks, reducing the stock breakage rysk.
- It will notice obsolete materials (no movement).
- It will notice consumption anomalies (when some reference goes out of tendency).
VARIABLES
Two main variables compose this system:
Minimum stock
Defined as the consumption rate during the delivery period of the material
Security stock
Defined as the standard deviation of the consumption during the period of study
Translating it into equations, it would look like:
Where we still have two unknown parameters: the delivery time and the security constant.
- Delivery time: depending on how powerful your warehouse management system is, you can either have a static or dynamic tag. My recommendation to start is applying a static one, by simply asking the supplier the average delivery time.
- Constant: this parameter comes from a statistical assumption done by Carl Friedrich Gauss in the XVIII century. Back then, he demonstrated and published the famous “Normal Distribution” theory, that explains a huge part of current natural behaviours. For the purpose of this post, we will apply it to calculate our security stock. In a practical way, we will associate a different confidence interval depending on the criticity of the material:
Normal Distribution
We will now see it with an example so you clearly see what we are talking about.
EXAMPLE
Let´s bring all these formulas to life with an example:
- MATERIAL: Pneumatic cylinder
- RISK: Critical spare part (type A)
- DELIVERY TIME: 25 days
| Consumption | |
| January | 25 |
| February | 32 |
| March | 10 |
| April | 16 |
| May | 7 |
| June | 30 |
| July | 10 |
| August | 30 |
| September | 25 |
| October | 26 |
| November | 27 |
| December | 22 |
Assuming all the data given above, we could easily calculate the optimal stock level for this specific material:
The constant has been defined according to the Risk Type. This parameter depends exclusively on your business goals. As a company you will define a confidence interval for each type of material, according to your needs and requirements.
Just as a guide for you to have, this is a typical user-case:
- High risk (type A): 90%
- Medium risk (type B): 80%
- Low risk (type C): 60%
In conclussion, we should set the optimal stock level in our warehouse to: 18 + 11 = 29 units.
* This is the minimum level required to ensure the availability of this material in my warehouse when needed, with a 90% confidence level. It also maximizes benefits by avoiding overstock.
How do I apply this in a massive way?
when it comes to real life, we normally talk about systems up to thousands of references, which make it impossible for us to run this experiment manually.
Do not worry! There are several solutions for this particular issue. Just for you to have a quick inspiration, I will now show you a basic excel system prepared to manage the experiment in a massive way.
excel management system
This excel file is divided into two main sections:
- Data base: where I automatically upload all the data for every reference available in the warehouse.
- Data visualization: analysis section to easily see the conclusions brought by the system.
Data base
As you can see, we have a table serving as a data base to store the data from the different materials.
In this specific case we chose to develop a visual basic program, but we could have easily done the same by using power Query.
As always, it is not the purpose of this post to go into coding details, but I would be grateful to share it in case you are interested in.
Data visualization
Properly visualizing the conclusions generated by the system is just as important as the system itself, as I always emphasize:
"A picture is worth a thousand words."
Big J
I hope you find this post interesting. I can´t wait for you to start applying it in your job!
We wish to thank you again for the stunning ideas you offered Janet
when preparing a post-graduate research as well as, most importantly, for providing the many ideas in a blog post.
Provided that we had known of your web-site a year ago, we may have been saved the unwanted measures we were having to take.
Thank you very much. adults toys
I am so grateful you found inspiration in this post, as well as Janet’s! I wish you the best applying this ideas to your own goals. I am sure you will not regret it!