How To: ABC (Selective Inventory Control) analysis

Posted by William on 04/01/2010 under How To | Be the First to Comment

The ABC-analysis (read more) is an often used method in logistics to divide the product collection in three different degrees, sorted on products with the highest revenue. This analysis gives valuable insights when removing (or adding) products from the collection. Products with low revenue could be considered for removal, cleaning up space in the warehouse for other products.

Besides that, an organization can take actions that differ by group. For example, the analysis can help creating an efficient setting for the products in the warehouse. Think about small as possible loading and unloading distances for products with high revenue for example. This saves time and money.

Groups

• The First Group (A) is often a small amount of products generating the largest part of the revenue. This is also called the 80/20 (Pareto) rule, where 20% of the products generates 80% of the total revenue. This rule is not applicable for every company, 70/40 or 80/30 (doesn’t have to be a 100 total) are also possible. For organizations, these products are the most important. Actions should be taken to avoid selling no (higher safety stock).

• The second group (B) contains the mediocre products. Often 30% of the products are generating 15% of the total revenue.

• The last group (C) is the group of products that generates marginal revenue. Often 50% of the products are generating 5% of the total revenue.

This how-to will describe how to realize ABC analysis in Qlikview. Big advantage is the dynamic analysis, up to date with every reload.

Building an ABC analysis dashboard

• This example uses drinks as products.

Step 1: Create a combo chart
Right mouse click, create chart and choose a combo chart

Add the product (the field drink in this example) as a dimension

Add the expression sum(Price) and choose full accumulation

Add the expressions:

•    sum(total Price) * 0.8         (80% revenue)
•    sum(total Price)  * 0.95     (15% revenue)
•    sum(total Price)         (5% revenue)

Choose for the display options, line for all three expressions. Call this line Group(A, B or C): (80, 15 or 5) % of total Revenue.

In the next step, sort your dimension by Y-value, Descending

In the style screen, choose the orientation on the right side and click finish.

The chart will look like this:

Step 2: Create a straight chart
Right mouse click, create chart and choose a straight chart

Add the product (the field drink in this example) as a dimension:

Sort Revenue cumulative Numeric Value Descending

Create the following expressions:
•    Revenue:                 sum(Price)
•    % Revenue:            sum(Price)    check Relative
•    Revenue cumulative:        sum(Price)    check Full Accumulation
•    % Revenue cumulative:    sum(Price)    check Relative and Full Accumulation

In the tab Visual Cues, select Revenue cumulative and fill in the expression as in the example and choose the colour.

Repeat this step for % Revenue cumulative

The straight table looks like this. It’s easy to see which drinks belong in which group.

Step 3: Create a Block Chart

Right mouse click, create chart and choose block chart

Add the product (the field drink in this example) as a dimension:

Add the expression sum(Price) and finish the chart.

Step 4: build the dashboard
The picture below gives a good insight in the different products and their share in total revenue.

ABC at Customer Level
In addition to the analysis at product level, we could also make this analysis combined with customers and an A-, B- and C Group. Customers at level C often take a disproportional share of your time while they provide little revenue.

A cross-check between products and customers will provide even more detail. It often appears that c-customers are buying c-products. It is these kinds of analysis that make Qlikview solutions more concrete in terms of ROI. Decent actions can be taken and results of these actions can be measured.