Materials ABC Coding - by dollars and other category

ABC coding enables inventory planning and control parameters to be applied to all the parts base on part cost per unit. These classified codes are based on Pareto's "80/20" Rule (sometimes called ABC Analysis). The underlying principle of Pareto's "80/20" Rule is that 80% of the effect is provided by 20% of the cause as shown in Figure 6.1. "A" parts typically constitute the top 80% of the total inventory value. However, percentages vary for the different companies, as do the number of categories depends on the type of business, costs and usage structure of the inventory. What I mean is that a lower value but a high volume consumption part may be an A-item Class, and another part which has a very high unit cost but with low demand may be categorized as C-item. Usage value is usually calculated based on annualized part value.

In Figure 6.2, Pareto Analysis is used to classify inventory groups. Part items are ranked in descending order of usage value, and plotted on a cumulative frequency curve. This analysis reveals find that 10% of items account for 70% of usage value, the next 20% has 15% of value. The final 70% have 15% of value. Typically for Class A with 10% of items contributing to 70% of the usage value, planners should monitor at least once every week because month-end review would be too late to react due to higher risk of stockout and causing a dip in customer service level. Tight control for such Class-A items can have a great impact over the cost of inventory. A reorder cycle system would be desirable to control Class-B stock items (20% of items contributing to 15% of usage value) as well as for Class A stock items. A two-bin or annual demand system could manage the final 70% of Class-C stock items which makes up only 15% of usage value. Pareto analysis points the way to where control efforts are best directed. However, planners must exercise good judgment especially on critical inventory items (in cases where a production equipment was down for a day without the part due to stockout could mean production output loss and loss of sales of hundreds of thousands dollars, or any security matters that Pareto analysis in itself does not reveal.

The Pareto Analysis requires items to be listed with their unit costs and average rate of usage (either monthly, quarterly or  annualize the monthly usage rate). We then:

   ▪  calculate use by value
   ▪  sort the list into items by value descending
   ▪  calculate % that each item contributes to total value
   ▪  derive a cumulative % list
   ▪  evaluate the cumulative list and identify appropriate breakpoints

Figure 6.1 Pareto Analysis of "80/20" Rule	Figure 6.2 Pareto Analysis of parts usage value

Table 6.1 explains the ABC Codes (based on Classification Code A1,A2,A3,B1,B2...G2,G3) for all the part items and their total ending inventory on-hand dollars. The table in Green displays a rather complex cost classification structure of the part items than you would normally see in a traditional ABC costs classification. In fact providing more classified cost Codes enables a better cost control on inventory - either from the aspects of demand forecasting, order quantity techniques, inventory cost control measures, excess and obsolescence control, and strategic customer service level performance.

In Table 6.1, sort the list by "Total Cost" in column D, in descending order. To compute the cumulative percentages in column E, type the following formula in Cell E2 and copy it all the way down to Cell E22.

=SUM($D$2:D2)/$D$23

A Pareto Curve in Figure 6.3 is then plotted base on Table 6.1. The analysis shows that 80% of the inventory ending on-hand values comprised of items with part code A1, A2, B3, B1, B2, C1, A3, E2, E3 and E1, in increasing order of  cumulative percentages.  The Pareto Curve obtained in Figure 6.3 can be used for any problem analysis that you want to evaluate on your inventory, for example, on quarterly inventory reduction measures, usage events frequency analysis, and contractual service level review. In this case, I used the plotted Pareto Curve to evaluate on the Inventory Turnovers. Inventory Turns is the number of times you sell your average investment in inventory each year. To calculate your Inventory Turns, you would need to know your beginning stock on-hand balance, purchase receipts amount for the period, and the ending stock on-hand balance. The formula are:

Below is another Inventory Turns formula that you can use based on Net Sales for the period instead of using COGS. However, the previous formula is preferred because in this formula sales are recorded at Market Value, while inventories are recorded at Cost. This formula can not be used to effectively gauge the healthiness of your inventories, but at best, is to measure the industry averages. An interpretation would be that a low ratio means low turnover which implies poor sales, whereas a high ratio implies strong sales.

In the followings example, I calculated the Inventory Turns for each Part Code. The target overall inventory turns was 5.0 and the actual inventory turns was 4.7. Looking at the 80% of the total inventory value, the Pareto curve reveals that only stock items of part code Class-C1 and Class-A3 are underachieving (C1 parts turns 4.0 times whereas A3 parts only achieved 1.5 turns). Knowing this, planners should carefully review the inventory level for Class-A3 parts which possibly have forecast and safety stock quantity setting too high than the actual demand. As Class-A3 parts have unit cost in $8,000.01 to $10,000 range, reduce probably one or a few safety stock quantity would improves its turns many times higher.

You can Download the example worksheet here with Tables 6.1, Table 6.2 and the plotted charts.

Figure 6.3 Pareto analysis of inventory values base on ABC Classification Code	Figure 6.4 Inventory Turns of all the Material Classes

Figure 6.5 shows a typical Pareto Analysis on the causes of delivery misses from a poorly performed monthly Customer Service Level. The column on "Lines of Orders Missed" are sorted in descending order before Pareto Cumulative Percents are calculated ( the last column). The Pareto Curve in Figure 6.5 shows the magnitude of the causes of the delivery "Misses", which means deliveries that were done more than 24 hours after the dates of request are considered as customer service level "Misses". Again, this kind of Pareto Curve would gives planners sufficient depth of understanding to take corrective measures to improve the service level for the future periods.
[Download worksheet]

	Figure 6.5
	Customer Service Level (Non-Hits with delivery after 24 hours)
Causes of Miss:	Lines of orders requested	Lines of orders Hit	Service Level Percent	Lines of orders  Missed	Pareto Cumulative Missed %
Ordered more than forecasted usage	113	72	63.7%	41	32.5%
Stockout due to insufficient safety stock	79	46	58.2%	33	58.7%
1st-time purchase in the last 12 months	128	97	75.8%	31	83.3%
Stockout due to purchase orders released late	83	71	85.5%	12	92.9%
Suppliers problems	34	25	73.5%	9	100.0%

It is also easy for someone to misuse ABC Codes. ABC codes can be used to establish maximum days supply for ordering logic by setting supply days lead time, says from 30 to 15 days. This seems a simple way to force an increase in inventory turns. However, such action does not consider the increase in freight costs and handling costs associated with twice as many supply deliveries. Suppliers may also not really ready and capable of shipping every 15 days. The action also subject the part to risk of stockout due to miscalculated replenishment lead time and also later to increases safety stock levels.

Next Page >> Different Types of Material Classifications