Sam has a kitchen store in the heart of Helsinki. The store sells all kind of kitchen equipment. Sam has been really busy during last years and the assortment of the store has grown a lot. Sam has not really planned the growth and he is uncertain which products are actually generating the sales so that he could focus on right items. Sam has noticed that sometimes popular products run out of shelves and some products seem to just collect dust on them.
Perform an ABC (sales value) - XYZ (sales units) analysis on Sam's data (ABC XYZ analysis.xlsx).
Answer to following questions:
- How big % of products generate 50% of sales?
- Additional question: How big share of the margin do these products have?
- Based on the data, what decisions would you make if you were Sam?
- What other calculations would you make?
- Is the information provided sufficient? If not, what information would you need in order to make decisions?
Product name Total sold pieces 2016 Total orders 2016 Total received order lines 2016 Total purchase cost 2016 Total sales eur 2016 602 1 1582.544 2848.28 2952 66 43098.71 79713.156 1731 7 8443.3 13806.26 104 1 0 2077.92 347.88 544 2 14 9530.9976 23397.606 O L DO V O UT A W N H 385 2 84 39418.08 79364.974 12048 70 453 35534.93995 94796.89 1165 22 140 8491.2694 14575.52 18 1 0 84.05856 204.24 279 1 2 1990.229 1983.08 11 5844 4 43 20372.224 41707.424 12 3459 27 301 5325.47827 10350.119 13 1345 70 26076.1905 64033.058 14 31 2 114.57 392.708 NAPA 15 7 0 187.512 425.49 16 211 3 6847.452 12373.824 17 3000 27 41 16126.935 37575.99 18 138 16 6655.47 15048.591 19 14 0 37.08 67.362 20 281 3 2927.9582 8017.557 HWN PPP 21 404 29 14566.391 32859.568 22 4914 36 34621.19902 186657.554 23 50 0 134.99136 364.56 24 11065 10 115 24600.62995 58910.874 25 950 19 127 1468.52799 2874.96 26 1 1 23.225 57.3 27 2325 6 61 15667.94 36867.131 28 91 1 3 916.28404 2116.028 29 99 2 522.29 1307.68 30 4816 18 400 96672.39731 202405.233 31 2212 95 31754.0231 72264.135 A UT 32 668 7 5079.999 9173.32 33 1278 4 55 7436.3638 16220.848\f34 49 1272.89 2664.48 35 313 49 10442.16 24545.198 36 88 608.751 1035.25 37 2 30.248 58.36 38 2020 56 7414.28212 15327.998 39 347 8 2804.6016 7137.658 40 1396 17 10127.5855 23838.156 41 60 HAHOHWHY NHWHPNP 2 238.52 623 42 1 249.838 520.95 43 1304 101 51171.05022 118776.611 44 75 4 823.37 1702.918 45 1876 272 90241.00206 192187.298 46 41 4 5153.491 11132.556 47 384 8 1026.474 1941.563 48 241 849.78 1771.68 49 895 214 93226.91247 195957.843 50 343 HUNNY 46 17720.72355 39418.992 51 1603 15 18833.5222 35222.746 52 188 3 1935.00272 4215.932 53 3515 21 270 36951.7722 53619.082 54 2089 46 118 26096.18014 51026.492 55 148 1 3 883.08703 2210.77 56 152 3 746.724 1720.32 W H 57 1007 39 3182.191 7525.01 58 3140 105 220 68100.4975 78290.556 59 2 2 1849.09385 4214.673 60 1617 19 1860.98595 3680.535 W H N N 61 86 5 10575.95 18749.705 62 981 74 4030.21862 10289.561 63 8463 10 461 79039.3744 187634.929 64 2055 11 29 5663.23245 10504.875 65 35 19 13028.536 24167.996 V W H 66 496 4 2324.352 3890.25 67 794 45 5344.572 8716.96868 1439 43 4662.5894 7703.84 69 1632 121 161312.2252 300598.527 70 267 1 298.8 960 71 229 22 7868.5012 24257.534 72 2978 5 3564.992 7462.56 73 8125 8 4913.87904 9140.4 74 901 46 226 10655.782 20610.445 75 19 0 1211.045 4514.18 76 740 28 5821.604 10593.715 77 3 3 2138.2785 4143.898 78 257 20 3673.90955 3067.452 79 87 2 3 349.43824 696.7 80 1694 301 77221.33504 165965.024 81 906 19 36 3174.25392 5594.8 82 5895 15 538 153949.1655 358599.778 83 1161 2 155 57538.53456 139054.9 84 40 0 60 150 85 53 1 289.92 530.76 86 334 16 18 2705.2994 4919.132 87 2990 30 558 102947.2105 235411.324 88 1819 59 5577.43168 9968.516 89 38 1 504.78 934.058 90 40 2 619.895 1557.05 91 1917 70 238 7140.92077 11927.423 92 228 4 3108.9631 6950.414 W H 93 1797 37 29337.303 70423.72 94 538 29 124 10081.30496 17108.698 95 134 998.27 2121.74 W H 96 3823 49 14219.56079 28471.501 97 26248 214 1029 134366.0873 358763.615 98 141 1 197.97 592.5 99 198 10 9057.37 17221.381 100 23 0 127.6704 0.346 101 29 2 1196.64 2405.865102 2187 w 52 12893.98586 27331.527 103 0 1295.124 1736.7 104 824 11 465 78529.283 195356.424 105 142 5 2782.7 6018.368 106 385 23 35 6932.96796 12693.616 107 221 1 3 1833.579 4014.45 108 1304 32 131 15537.60916 31010.932 109 600 22 129 1379.0862 2660.787 110 1044 3 25 29589.37564 62175.607 111 4 2 323.562 712.3 112 30 UI NH 3 122.638 226.74 113 219 22 563.551 1164.79 114 4868 20 797 355218.1099 796130.54 115 6499 54 430 23081.93426 37603.663 116 10 2 327.465 458.614 W H 117 1548 39 3947.114 9803.67 118 554 13 38 4543.96 11743.59 119 22707 257 1960 130785.0606 264419.633 120 4686 25 12567.99672 22525.89 121 68 6 5000.7786 9840.827 122 1160 1 3937.332 7520.346 123 8 0 45.45 107.15 124 97 518.537 973.426 125 84 2 222.18544 421.594 126 14 0 55.896 112.2 127 1227 42 15403.0348 41961.037 128 2 0 84.02007 43.831 129 264 24 2030.22378 4275.781 130 1962 16 133 7000.38234 13793.116 131 1537 6 53 7208.67504 12897.474 132 16518 104 1721 239260.539 551552.638 133 201 1 6 2741.393 7487.186 134 6582 43 654 9377.55364 16319.856 135 577 4 52 1586.469 4751.273217 79 1 715.7946 1925.36 218 578 AW - 67 19013.309 48314.531 219 976 76 1799.718 5002.16 220 1527 19 1457.19338 2808.39 221 260 20 4961.6062 11681.925 222 8931 33 579 13371.06095 23821.167 223 223 854.975 1466.022 224 10292 42 406 25792. 11819 53192.941 225 470 12 34 10425.996 22229.04 226 12933 219 1920 131271.4296 19782.046 227 2511 37 16535.28 31842.546 3322 CO 228 38 6991.21388 14833.07 229 77 6 6037.584 14395.586 230 31 24 4677.69682 11017.284 231 155 9 1412.729 3363.59 232 21 0 446.556 971.252 233 388 29 97 6410.58658 10812.608 234 5 5 1754.996 4373.739 235 133 3 1245 2290.8 DO - WA 236 14 1659.815 3986.6 237 448 11 8654.3357 16652.065 238 780 2 19 6805.72587 15562.993 239 1405 156 51960.5793 113978.706 240 576 7 29 1 1046.89524 19419.332 241 1523 28 20927.9432 44290.678 242 1016 - NA 6 2750.6541 6937.47 243 11 482.6475 1008.53 244 24759 209 2502 112370.8609 216538.817 245 1062 46 241 9367.91552 23251.962 246 2984 40 241 4002.85634 6889.496 247 1203 5 5440.1565 12403.788 248 61 2 94.116 186.32 249 44 2 133.942 307.658 250 34 7 1908.2078 3707.68 251 386 17 34 706.8525 1274.195 252 1003 4 4826.532 8569.32 - N 253 1078 19 4303.4982 10126.422 254 1291 6 369 80366.468 178525.24 255 3679 38 284 11666.00734 20229.774 256 690 6 3183.307 8948.904 - N 257 64 10152.794 N 5455.97726 258 11 275.2 510.4259 1112 19 81 1966.873 4111.564 260 1444 150 92713.10996 202127.52 261 259 27 7492.66925 16469.156 262 33 2 1884.795 4201.4 263 458 12 22 875.04 1562.76 264 123 5 495.91 726.8 265 173 16 768.6974 1260.912 266 2 0 34.236 0.036 267 218 11 10830.74398 45766.824 268 119.74 199.5 269 2192 68 16562.6023 45028.35 270 146 1 468.27326 1190.2 271 2596 5815.614 15045.19 272 171 4334.25872 9063.008 273 4281 112 299 33983.20741 74684.267 274 36 3 1792.149 4238.481 275 2369 12 49 2980.56378 5534.184 276 149 4 895.77 2136.174 277 19 0 30.44 1 10 278 169 19 4524.75 9576.6 279 671 1 16 4011.8655 10419.504 280 764 2 6 1565.26402 3383.254 281 1645 46 252 35284.54113 60918.615 282 6964 156 629 85900.30989 145500.244 283 46 31 34 20931.40908 42711.67 284 4153 44 142 8273.3823 16038.804 285 12 1 233.9 514.036 286 374 18 67 1311.773 2482.91 287 16 927.2 2155.74 288 1914 34 184 28801.927 58826.63 289 376 23 1020.87549 2088.625 290 5736 120 258 112483.6778 214070.739 291 147 264 527.5 292 53041 116 1569 105297.5044 195875.067 293 30 0 79.56 234 294 43 2 6 612.168 0.649 295 56 2 2211.7 4144.982 296 1093 4 81 96595.28049 215200.857 297 223 5 2998.68 6199.526 298 1127 19 136 29695.71415 68939.764 299 486 CO 50 3341.85902 7670.215 300 347.188 577.05
