I need help on Q4 on this screenshot attached. Q4-Insert a formula in cell "J5" that will automatically generate the total cost per item group noted in cell "I5" (green cell)
XUS. Supply ChainExcelAssessment_Blank - Excel ? FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW A1 Supply Chain Exercise (45 minutes) J Item Group Total Cost per B C D E G H 1 Supply Chain Exercise (45 minutes) 2 3 Instructions: 4 1) Add a filter to the table below 5 2) Calculate "Total Cost/Item" for all items in column G (yellow column) 63) Make cell "15" a drop down list where you can select from the various "Item Groups" (orange cell) 74) Insert a formula in cell "35" that will automatically generate the total cost per item group noted in cell "15" (green cell) 8 5) Use a "vlookup" formula and the "Item Roll Up" tab (red tab) to populate the "item Group Roll Ups" in column D (purple column) 6) In column H, define the ABC class for each item. All items in Item Group Medicines" or "Medical equip greater than $5k" should be class A. Items not in 9 either of those categories that have a total cost / item of great than $5,000 should be class B. All Other Items should be class C 7) Create a new tab for the Executive Leadership Team (ELT) with a Pivot Table summarizing Total Costs by "item Group Roll Up". This pivot table should also 10 allow ELT to expand / drill in to see total cost by "item Group" and then by "Item". Please include any other data / analysis you think ELT may want to see. 11 12 Item Numb Item Name Item Group Item Group Roll Up Unit Cost Quantity (# of Unit Total Cost/Item ABC Clas 13 10001 Mask, Oxygen, Nonrebreather, Pediatric Medical supplies Medical $ 1.33 148 196.93 #VALUE! 1410002 Methyldopa 250mg, Film coated tablet Medicines Medical $ 0.03 707 20.86 15 10003 Salbutamol, 100mcg/dose, 200 dose inhaler Medicines Medical $ 1.50 396 594.00 16 10004 Magnesium trisilicate 250mg + Aluminium hydrc Medicines Medical $ 0.01 401 3.81 17 10005 Suture, Polyglycolic acid, 0, 3/8 circle, 35mm nee Medical supplies Medical $ 0.71 717 507.85 18 10006 Printer paper roll, for Pima CD4 analyzer Medical supplies Medical $ 4.10 685 2808.50 19 10007 Wax, Dental, Modeling, Pink, 500g (24 sheets) Medical supplies Medical $ 0.46 63 28.87 20 10008 Arm Sling, Medium Medical supplies Medical $ 2.84 338 959.92 21 10009 Tape, Identification, for surgical instruments, Bli Medical supplies Medical $ 10.00 648 6480.00 22 10010 Sleeve, for complete curing light Medical supplies Medical $ 0.12 802 99.05 23 10011 Standard nail, Surgical implant, for SIGN system, Medical supplies Medical $ 100.00 626 62600.00 24 10012 Tape, Identification, for surgical instruments, or Medical supplies Medical $ 9720.00 Instructions Executive Leadership Team Supply Chain Exercise 1 Item Roll Up Supply Chain Exercise 2 QOH QO0 Consumption READY COUNT: 91 #VALUE! 10.00 +
