Question

SHOW STEPS, FORMULAS, AND EXPLANATIONS

a. The following table contains figures on the monthly volume and unit costs for a random sample of the 16 items from a list of 2,000 inventory items at a health care facility. Develop an A-B-C classification for these items.

Item	Unit Cost	Usage	Item	Unit Cost	Usage
K34	$10	200	F99	$20	60
K35	25	600	D45	10	550
K36	36	150	D48	12	90
M10	16	25	D52	15	110
M20	20	80	D57	40	120
Z45	80	200	N08	30	40
F14	20	300	P05	16	500
F95	30	800	P09	10	30

b. Given the monthly usages in the following table, classify the items in A, B, and C categories according to dollar usage.

Item	Usage	Unit Cost
4021	90	$1,400
9402	300	12
4066	30	700
6500	150	20
9280	10	1,020
4050	80	140
6850	2,000	10
3010	400	20
4400	5,000	5

c. Determine the percentage of items in each category and the annual dollar value for each category for part b.

Answer 1

a) ABC analysis

Step 1: Calculate Dollar value of each item by multiplying the Unit cost with Usage.

Step 2: Sort the items in descending order of Dollar value.

Step 3: Compute for each item the % of total dollar value of all items.

Step 4: Calculate running sum of % (Cumulative %)

Step 5: Classifiy the items upto cumulative % of 80 as A category, Next 15-17% as B category and the last 3-5% as C catgory

Formulas:

E2 =C2*D2  

F2 =E2/$E$18

G3 =F3+G2

copy down to row 17

b) Repeat the same step-wise method for this part