How to do Combination of ABC and VED analysis in excel sheet

How to do Combination of ABC and VED Analysis in Excel

Under combination of ABC and VED analysis medicine inventory are classified into

CATEGORY 1    :          These items are both expensive and critically vital for patient’s life. They need to be monitored and controlled continuously. These items might be in sub-categories namely, AV, AE, AD, BV and CV.

CATEGORY 2    :           The items in this group are not as expensive as the first group or not as critically vital for patient’s life. These need to be controlled periodically. These items might be in sub-categories namely, BE, CE, BD.

CATEGORY 3    :           These items in this group are the least expensive and vital. They do not need to be controlled periodically. These items might be in sub-category namely, CD.

Step 1 :           Draw the Matrix see fig 1

 Step 2 :           Combine the ABC analysis and VED analysis column cell see fig. 2

Step 3 :           Now Filter the AV, AE, AD, BV and CV, select and count all the drugs, this is the CATEGORY 1.

This is critically vital and expensive and need daily monitoring.

Step 4 :           Now filter the BE, CE, BD, select and count the drugs, this is the CATEGORY 2.

This category is not as expensive or critically vital as category 1 and need periodic monitoring and control.

Step 5 :           Now filter the CD, select and count the drugs, this is the CATEGORY 3.

This category is least expensive and vital and need periodic monitoring and control.

STEP 6    :    Final sheet of Inventory management by ABC and VED analysis.

Final Matrix sheet of inventory management by ABC and VED analysis: see Fig 7.

also refer post : 

1.    ABC Analysis   

2.    VED analysis

Share: