Warehouse management - Part 1

I have been giving a lot of thought recently to how we should implement warehouse management. Just using row/bay/bin is too restrictive. 

If we have a warehouse structured like this :

it will not fit well to a rigid system of warehouse management.
 

What I propose a system where we have just one table for areas that can contain stock items. The best name would be "Locations" but that has already been taken, so I will for the moment call them "Containers". All containers can have parent containers. The above warehouse now looks like this:

As you can see the warehouse now can be modeled as a tree diagram. Each container can be set as to whether it can contain items, or just act as a container for child containers.







Each container has details of its physical position in the warehouse, and it's size. Also properties as to what it can contain (refrigeration unit for instance). Whether the contents are liquid etc.


Starting to implement this is easy, as each warehouse can be defined as a container. We just need a new table to hold the container information.

ABC inventory ranking system is completed

As detailed in this blog post we have been working on a system for ranking inventory items by the standard ABC classification system.

I am happy to announce that this new system has just been uploaded to the KwaMoja repositories and those who wish to try it can download our development snapshot from here.

The only change from the original blog post was a suggestion from Francis that we use a consumption value system as our default ranking method rather than the proposed sales value method - thanks Francis.

The next step in warehouse management system is to setup the warehouse storage definitions as outlined in the excellent document prepared by Buz and Mary Beth (sorry I don't know more details of these people) that can be downloaded from here. Then we are into the cycle count code.

This code was offered to Phil Daintree at webERP but appears to have been rejected.

ABC analysis of stock items.

One of the most important methods of keeping accurate stock levels - though by no means the most widely used, especially in Africa - is cyclical stock checking. That is the constant checking of the stock throughout a financial period, rather than leaving stock checks to the period end.

This has two big advantages in the control of an organisations stock. Firstly the stock levels are kept in a much more accurate state as they are checked more frequently, and secondly any variances are far easier to track down if the period between checking the item is much shorter.

A necessary prerequisite of cyclical stock checking is an ABC analysis of the organisations stock. By ABC analysis I mean to rank the items by:

A - The most important items to the organisation

B - Important to the organisation but not critical

C - Slow moving or non important stock items.

At the moment we have no ABC analysis. This is a proposal to rectify that.

ABC classification is a way of grouping your stock items. There are a few different ways to set up an ABC Ranking, such as Velocity (times sold), Quantity sold/Consumed or by Margin. But the most common method is the Annual Sales Volume ranking. This method will allow you to identify the small number of items that usually account for most of your sales value (think 80/20 rule).

My plan is to first implement Annual Sales Volume Ranking method, but to do it in such a way as to make adding other methods easy in the future. To do this I propose to setup a table to hold the methods to be used. This table will have the following structure:

CREATE TABLE `abcmethods` (
     `methodid` TINYINT NOT NULL DEFAULT 0,
     `methodname` VARCHAR(40) NOT NULL DEFAULT '',
     PRIMARY KEY (`methodid`)
);

Initially this will have just one record in, 
methodid=>0 
methodname=>Annual Sales Volume Ranking

The next table will contain the groups that are being used. This will specify the criteria used. Each method can have several groups. I propose the following table:

CREATE TABLE `abcgroups`(
     `groupid` INT(11) NOT NULL DEFAULT 0,
     `groupname` VARCHAR(40) NOT NULL DEFAULT '',
     `methodid` TINYINT NOT NULL DEFAULT 0,
     `apercentage` TINYINT NOT NULL DEFAULT 0,
     `bpercentage` TINYINT NOT NULL DEFAULT 0,
     `cpercentage` TINYINT NOT NULL DEFAULT 0,
     `zerousage` CHAR(1) NOT NULL DEFAULT 'D',
     `months` TINYINT NOT NULL DEFAULT 12,
     PRIMARY KEY (`groupid`),
     CONSTRAINT `abcgroups_ibfk_1` FOREIGN KEY (`methodid`) REFERENCES `abcmethods` (`methodid`) 
);

The zerousage field is intended to hold the category into which items that have no usage at all should be put. This would normally be C or D. The months field is the number of prior months movements that should be analysed.
Finally I propose a separate table to hold the ABC category for each item and group. This would look like:

CREATE TABLE `abcstock` (
     `groupid` INT(11) NOT NULL DEFAULT 0,
     `stockid` VARCHAR(20) NOT NULL DEFAULT '',
     `abccategory` CHAR(1) NOT NULL DEFAULT 'C',
     PRIMARY KEY (`groupid`, `stockid`), 
     CONSTRAINT `abcstock_ibfk_1` FOREIGN KEY (`groupid`) REFERENCES `abcgroups` (`groupid`),
     CONSTRAINT `abcstock_ibfk_2` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`) 
);   

Using these three tables should provide for a very flexible system, easily changed in the future.

The function to actually assign the categories would work something like this:

1.  Calculate the 12 month value usage for all of the stock items.

2.  Rank the items in descending order by value.

3.  The "A" items are the top 80%.

4.  The "B" items make up the next 15%.

5.  The "C" items are the remaining items that have any usage in the period being looked at.

6.  Label zero-usage items as "D".

Comments and constructive (yes Phil I am looking at you!) criticisms would be very much appreciated.