The WhatIf Company stocks a variety of products. To control inventory more effec

Question

The WhatIf Company stocks a variety of products. To control inventory more effectively, they would like to classify their inventory according to the ABC approach. Following is a list of their stock and the annual usage and unit value for each: ITEM NUMBER ANNUAL USAGE UNIT COST ITEM NUMBER ANNUAL USAGE UNIT CoST t350 $610 20 15 16 10 120 23 45 1900 19 20 21 300 45 3200 500 910 4750 2710 1800 105 12 80 344 67 510 692 95 10 820 23 24 28 440 24 870 244 10 35 15 110 160 12 13 45 2g 30 165 the inventory items according to the ABC approach using dollar value of annual a 10% profit margin, if there is a complete stockout lasting one month of all class A items, how much in sales S will they lose? Round up possible items sold to the nearest whole number i.e. you can't sell 1.5 items. Repeat the exercise for class B and class Citems. 2. How would you guide the company?

Explanation / Answer

1.ABC Classification in Excel

Then sorting in descending order of dollar usage

You can note that to classify we have taken the Pareto rule that 80% of dollar value is contributed by some items that are A

So to classify them as A, B, C we need to sort them according to their dollar usage and the first 20 % of items i.e 6 items from 30 will be A category. Next 30 % i.e 9 items next will be in B category and rest items will be in C category

2.

Loss in one month

Formula

Possible items cant be sold

Formula

For class B and C

For class B and C though the items lost in sell are higher as compared to A but the revenue loss is more in A so company need to focus more on A items as compared to B and C to save it from stockout

Comment if any doubt

Thanks

Item Number Annual Usage Unit cost Annual dollar usage 1 36 350 12600 2 510 30 15300 3 50 23 1150 4 300 45 13500 5 18 1900 34200 6 500 8 4000 7 710 4 2840 8 80 26 2080 9 344 28 9632 10 67 440 29480 11 510 2 1020 12 682 35 23870 13 95 50 4750 14 10 3 30 15 820 1 820 16 60 610 36600 17 120 20 2400 18 270 15 4050 19 45 50 2250 20 19 3200 60800 21 910 3 2730 22 12 4750 57000 23 30 2710 81300 24 24 1800 43200 25 870 105 91350 26 244 30 7320 27 750 15 11250 28 45 110 4950 29 46 160 7360 30 165 25 4125

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.