Inventory Management 101 – ABC Inventory Cycle Count Method

5 July 2020

Inventory Management 101 – ABC Inventory Cycle Count Method

Cycle Count is a periodic inventory audit practice.  Inventory are counted or may be physically checked on a continuous schedule.  The portion and frequency are predefined to ensure counting of each item is at least once in a fiscal year cycle

To implement a cycle count plan, inventory is usually defines and classes to A, B and C item

Item A are usually schedule to be count at least once a month.  The characteristics:

·       These items are subjected to a strict inventory control and is usually stored at a secured area in terms of storage

·       These items are holding high inventory cost or high demand for sales

Item B are usually schedule to be count at least once every two months. The characteristics are

·       These items have less inventory cost than Item A

·       These items have a less strict control than Item A

Item C are usually schedule to be count at every three months or longer. The characteristics are

·       These items are usually lower inventory cost than Item B

·       These items have high usage rate with high Reorder Point value and frequencies

·       These items are usually with bulk purchases mechanism

Table 1 illustrates how to define ABC and Cycle Count Frequencies via an Excel Spreadsheet.

Table 1 illustrated the formula

1.    Total Cost is Total of each inventory item (=D2*E2)

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

3.    ABC Classification, to classify the ABC base on inventory unit cost.  

If Inventory Cost more than $1000, is item A, if inventory Cost more than $100, is Item B, if Inventory Cost less than $100, is Item C

4.    =IFS(D2>=1000, "A", D2>100,"B",H2<100,"C")

5.    Count Period if each item. Item A is every month, Item B is every two months, Item C is very three months.

=IFS(I2="A", "every month", I2="B","every two months",I2="C","every three months"

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 – 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

Inventory Management 101- Aging Inventory

Aging Inventory Management

An Aging Inventory is the inventory that has been identify as slow moving base on Demand Forecast Planning, plus an additional cost to the warehouse or stores for storage of the inventory until it is used or sole.

The role of inventory management is to maintain a desired stock level of specific products or items. The desired level is for fulfillment of demand, while minimized the inventory holding cost

Table 1

 
      Analysis of the Aging Inventory base on Table 1

1.     Aging Days Categories

a.     To Categorize the Aging Days Categories (Column J)

b.    Excel Formula of Nested IF

=IFS(I2<=50,"1-50days", I2<=100,"51-100days", I2<=150,"101-150days", I2<=200,"151-200days", I2>=200,">200days")

2.     Base on the aging days, compute the total amount (Cell H24)

3.     Provision of inventory aging amount base on pareto, 80/20 rules

4.     This example, the inventories of ITEM A_1 to ITEM A_5,  can be provision for further disposition.

5.     Further Inventory disposition plan can be via the following methods

o   Scrap

o   Cross sales

o   Reduce future buy, if possible, base on forecast

    

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 101 - Lead time Calculation

Inventory Management 101 – Lead time Calculation

Lead time is the time between the initiation and completion of a production process.

Total Lead time is the total time that need perform production till completion. These includes Supply chain management, manufacturing ,logistics transportation and clearance.

How to calculate lead time?

Sample of Total lead time for production and export

Total Lead time (for incoming and outgoing inventory or SKUs)

= Supply Chain Management + Manufacturing + Logistics + Transportation

How to calculate total lead time in Excel  

1.    Input your date (in this example is either at Purchase Order Creation date or Sales Order Creation date)

2.    Input the Supply Chain Management and Production Lead time. This should be in days (in this example enter the total days at Supplier Lead time or Kitting and Production Lead time).

3.    Input the Logistics and Transportation Lead time. This should be in days (in this example enter the total days at Logistics).

4.    Total Lead time (Days) is the sum of Supply Chain Management, Production Lead time and Logistics) (e.g. D6=B6+C6)

5.    Create a formula to add the days to the date (e.g. E6=A6+D6)

6.    Estimate Arrival date (ETA) is the either Purchase Order Creation Date or Sales Order Creation Date . Format the result as a date.

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