Inventory Management 101 – ABC Inventory Analysis

28 June 2020

Inventory Management 101 – ABC Inventory Analysis

ABC analysis is an approach for classifying inventory items based on the items' consumption values.

Consumption value is the total value of an item consumed over a specified time period. The period could be weeks, months or years.

The Steps to conduct an ABC Analysis are as follows:

1.    Determine periodical usage or sales for each item.

2.    Determine the percentage of the total usage or sales by item.

3.    Rank the items from highest to lowest percentage.

We will illustrate the determine of ABC via periodical usage.

1.    A classification items are very important and sometimes business critical. These typically sold in large volumes.

2.    B classification items are important, but less important than ‘A’ items and more important than ‘C’ items. These are typically mid-range volume

3.    C classification items are lower demand than ‘B’ items

Table 1

Table 1 illustrated the formula

1.    Total Monthly Run Rate for all the items (=SUM(D2:D10)

2.    ABC% (Total Usage) , is the item usage divide by Total Monthly Run Rate (=D2/$D$11

3.    ABC Classification, to classify the ABC base on ABC%(Total Usage). If Item ABC%(Total Usage) > 20%, will be A item, ). If Item ABC%(Total Usage) > 10%, will be B item, ). If Item ABC%(Total Usage) < 5%, will be C item.

=IFS(E2>20%, "A", E2>10%,"B",E2<5%,"C")

About the Author

Grace is a freelancer for Supply Chain Management specialized in helping companies reducing operation costs and increase profits through optimized Supply Chain, Sourcing and Procurement Operation.

 Click to view profile

Inventory Management 101 – Min Max Inventory Calculation

The Min/Max inventory ordering method is the basic method of inventory control.  There are two lines

a.    Min – Minimum amount of inventory is the value represents a stock level that triggers a reorder process

b.    Max- Maximum value represents a the target stock level, will not trigger to ordering activities.

Table 1

Table 1 illustrate the formula for calculation and analysis. This example includes one week of safety stock

·       Reorder quantity. The Reorder Quantity

·       Reorder decision. Either to order or not to order

·       Min value. The level which will trigger the reordering process

·       Max value. The reorder quantity.

·       Safety Stock. The level of safety stock

1.    Daily Run rate: Average daily run rate base on either weekly working days or calendar days

2.    Safety stock: Daily run rate *lead time (=F2*G2)

3.    Min: (Daily Run Rate  X Lead time )+Safety Stock ((F2*G2)+H2)

4.    Max: 2 cycles of Safety Stock (Daily Run Rate X Lead time)*2. (=H2*2)

5.    Reorder Quantity: Reorder quantity with Safety Stock (=H2+I2)

6.    Reorder Analysis: If Reorder quantity is more than Balance Quantity plus Safety Stock, the decision is to order. As the inventory on hand will not be sufficient to cover the daily run rate for the duration of lead time.

=IF(K2>(E2+H2),"order","enough")

About the Author

Grace is a freelancer for Supply Chain Management specialized in helping companies reducing operation costs and increase profits through optimized Supply Chain, Sourcing and Procurement Operation.

 Click to view profile

Inventory Management 101 – Reorder Point Calculation

Two common questions always asked from Supply Chain practioner is

“What is the quantity I should order?”

“When should I place the order?”

The formula for Reorder Point is

(Average Daily Usage X Lead time in Days)+Safety Stock.

Table 1

Table 1 illustrate the formula for calculation and analysis.

·       Reorder quantity

·       Reorder decision, either to order or enough inventory to meet the daily usage

1.    Daily Run rate: Average daily run rate base on either weekly working days or calendar days

2.    Safety stock: Daily run rate *lead time

(F2*H2)

3.    Reorder Quantity : (Average Daily Usage X Lead time in Days)+Safety Stock

(F2*H2)+G2

4.    Reorder Analysis : If Reorder quantity is more than Balance Quantity plus Safety Stock, the decision is to order. As the inventory on hand will not be sufficient to cover the daily run rate for the duration of lead time.

(=IF(I2>(E2+G2),"order","enough")

About the Author

Grace is a freelancer for Supply Chain Management specialized in helping companies reducing operation costs and increase profits through optimized Supply Chain, Sourcing and Procurement Operation.

 Click to view profile

Inventory Management 101 - Aging Perishable Inventory

Inventory Management 101 - Aging Perishable Inventory

Aged inventory is basically the products which are either slow-moving and in very low demand, or barely sell at all.  To add to the complication of age inventory management is Perishable inventory.

A perishable good is any product in which will be expiry date or due to environmental conditions through time, such as meat and meat by-products, fish and seafood, dairy products, fruit and vegetables, flowers, pharmaceutical products, and chemicals.

For the inventory with expiry windows after inventory has been received for sales and process, the expiry windows could be computed and monitored.

Table 1: 

Table 1:

The example: A simple computation to monitor the inventory’s shelf life.

Aging Days is the count days from creation date (inventory received) to current date

·       Aging Days formula in excel

o   I3=DAYS(TODAY(),E3)

·       Expired (180 days after Creation Date) – this is the shelf life of inventory is 180 days after the creation date.

o   =IF(I3<$K$1,"OK","Expired")

·       Item A_4, Item A_5, Item A_6 are expired base on the 180 days of shelf life standard.

About the Author

Grace is a freelancer for Supply Chain Management specialized in helping companies reducing operation costs and increase profits through optimized Supply Chain, Sourcing and Procurement Operation.

 Click to view profile