1050 1 Part No. Supplier Category Annual Unit Usage Unit Cost$ Annual$ Usage Cumulative $ Usage % Usage Class 2 1 Reid Inc. Electrical 10 $2.00 3 2 Regier Inc. Chemical 15 $80.00 4 3 Murphy Inc.. Mechanical 50 $4.00 5 4 Murphy Inc.. Hardware 90 $1.00 6 5 Reid Inc. Hardware 110 $10.00 7 6 Denomme In Hardware 120 $25.00 8 7 Howard Inc. Electrical 125 $2.00 9 8 Howard Inc. Electrical 150 $2.00 10 9 Camp Inc. Chemical 200 $2.00 11 10 Denomme In Chemical 250 $1.00 12 11 Denomme In Mechanical 450 $2.00 13 12 Camp Inc. Mechanical 550 $40.00 14 13 Reid Inc. Electrical 550 $2.00 15 14 Camp Inc. Chemical 700 $1.00 16 15 Regier Inc. Mechanical 900 $70.00 17 16 Regier Inc. Hardware $40.00 18 17 Murphy Inc.. Hardware 1200 $2.00 19 18 Reid Inc. Electrical 1200 $3.00 20 19 Murphy Inc.. Chemical 1400 $2.00 21 20 Camp Inc. Mechanical 1400 $1.00 22 23 Step 1: Calculate the dollar amount of the annual usage for part by multiplying annual unit usage by unit cost 24 Step 2: Sort the items according to category 25 Step 3: Subtotal for each category by going to the Data tab and clicking on the subtotal icon and have it subtotal at each change in category and subtotal the annual usage 26 Step 4: Click on 2 so that it only shows the subtotals. 27 Step 5: Sort the Annual Usage subtotals from largest to smallest 28 Step 5: Calculate the cumulative annual dollar usage for each category 29 Step 6: Calculate the percentage usage 30 Step 7: Classify the part as A, B, or C where A is about 20% of the categories that account for 80% of dollar usage, B is about 30% of the categories that account for 15% of dollar usage, C is about 50% of the categories that account for 5% of dollar usage 31 32 33 34 ABC Analysis by Part Number Part Number ABC Analysis by Supplier ABC Analysis by Category