Spend Analysis $$ Item List Appendix A

Item Description Annual Usage Qty$/$unit Unit Price

Syringes, disposable, $10$cc $700$ cs$/400$ $ $140\mathrm{.}00/$cs

Needle, single sample, $21$G $2,200$ cs$/1000259\mathrm{.}20/$cs

Needle, multi$-$sample, $21$G $3,600$ Cs$/1000327\mathrm{.}40/$CS

Saline Solution, NaCl $6\mathrm{.}500$ cs$/1243\mathrm{.}00/$cs

Intra$-$ocular Lens, various diopters $1,270$ each $230\mathrm{.}00$ ea

Gauze Sponges, $4$x$4,$sterile $920$ cs$/400084\mathrm{.}00/$cs

Gauze Sponges, $4$x$4,$ bulk $247$ cs$/500052\mathrm{.}40/$cs

Petri Dish, blank, $100$mm $629$ cs$/50076\mathrm{.}25/$cs

Total Hip Implant $307$ each $907\mathrm{.}00$ ea

Total Knee Implant $547$ each $1,170\mathrm{.}00$ ea

Surgeon$$s Gown, disposable, large $1,640$ cs$/40287\mathrm{.}00/$cs

Surgeon$$s Gown, disposable, small $2,128$ cs$/40228\mathrm{.}45/$cs

Test Reagent Kit, HCl $140$ bx$/10265\mathrm{.}50/$bx

Sensitivity Disc, multi $948$ pk$/10032\mathrm{.}70/$pk

Dish, TSA Blood Plate, $100$mm $360$ ctn$/10076\mathrm{.}50/$ctn

Suture, Chromic Gut, $4-$O $67$ bx$/12188\mathrm{.}00/$bx

Suture, Silk, $30,$ taper needle $138$ bx$/36201\mathrm{.}68/$bx

Staple, surgical, Endo$-$GIA, reload $472$ bx$/40718\mathrm{.}80/$bx

Collector, Specimen, urine, $$Mexican Hat$1,652$ cs$/1000227\mathrm{.}60/$cs

Catheter, male, silicone, short$-$term $944$ bx$/10166\mathrm{.}30/$bx

SECTION $1$

$1.$ Use Excel or a similar database to create a spreadsheet for the attached information describing $20$ healthcare products. The spreadsheet should have column headings similar to the data sheet, expanded to include columns like those on the attached Spend Analysis Template. As you proceed through the sections of this assignment, calculate the described steps for each product before proceeding to the next section.

$2.$ Extend the Annual Usage and the Unit Cost for each item to determine the Total Annual Spend for each item. $($Annual Usage x Unit Price $=$ Total Annual Spend per item$).$

$3.$ When all line items have been calculated, use the spreadsheet feature to Sum the Total Cumulative Annual Spend for all items.

SECTION $2$

$1.$ Rearrange the order of items, listing the largest individual total as line item $1,$ then listing each consecutive item total in descending order. That is$,$ the second largest total spend becomes line item $2,$ the third largest is next, and so on$.$ All twenty products should be arranged until the lowest total spend is listed as line item $20.$ With the product listing rearranged in descending order, it is now possible to determine the A$-$B$-$C classification of the products.

SECTION $3$

$1.$ In your worksheet, place a Sub$-$Total marker after the required number of A items. $($Remember$,$ A Class items represent $10\%$ of line items, so that should be $2$ items from a list of $20.)$ Do the same for the B group, and the remaining items will represent the C items.

$2.$ Calculate a sub$-$total for the A Class items, then for the B Class items, and finally for the C Class Group.

$3.$ Compare your results to the expected range $$ A Class should represent $$about$70\%$ of the total cumulative spend; B Class sub$-$total should be $$about$$ a further $20\%$ of the total cumulative spend; the C Class group will represent $$about$$ as little as $10\%$ of the overall total.