Problem 1 ABC Inventory Analysis A university maintenance department has collected the annual usage and unit costs of common supplies shown in the table below. Part Number Annual Demand Unit Cost P05 46 16 P20 560 21 P27 1860 360 P28 1725 410 P31 616 10 P32 1200 82 P33 636 11 P34 540 14 P39 145 20 P40 170 25 P43 2365 320 P44 920 132 P45 322 14 P46 393 11 P50 282 48 P54 885 126 P62 1120 82 P65 452 216 P67 2670 300 P71 600 160 P73 320 50 P76 428 221 P78 1000 102 P82 18 9 P87 1150 92 P93 50 20 P98 31 17 P99 28 8 a) Copy and paste the above data into an Excel worksheet named Problem 1. Complete an ABC inventory analysis table. Use Excel formulas for all calculations. As soon as you finish the column annual part value, make sure that data is sorted in descending order of annual part value. A sample format is included below. b) Create a Pareto chart (in MS-Excel its a scatter chart). For scatter charts, MS-Excel uses the first column selected as the X axis and the second column as the Y axis. Since we want the opposite, choose insert scatter without selecting any data. Then right-click the chart and choose select data. Select the cumulative percentage of item values as the X values and cumulative percentage of inventory value as the Y values. c) Clearly indicate which items are in A, B or C categories (e.g. create a new column in the table). You do not need to use shaded boxes on the chart showing A, B and C items but you should (somehow) clearly indicate which parts belong to which class. d) Create a 20 day cycle counting schedule showing which items should be counted on each of the 20 days. A items should be counted every 2 days (e.g. day 1,3,519). B items should be counted every 5 days and C items should be counted every 10 days. Create a new column that indicates, for each part, what days within the 20 day cycle it will be counted on. Create a schedule which spreads workload evenly over days. For example, dont count every C item on days 1 and 11. Instead, count the first C item on days 1,11 count the second C item on days 2,12 and the third C item on days 3, 13 etc). Note that all A items should be counted on exactly 10 days out of 20; B items should be counted on exactly 4 days out of 20 and C items should be counted on exactly 2 days out of 20.