Summary

Summary = By Erik huuskonen --- # [ DISCLAIMER ] Sales and Profit cases could not be validated by Aspelin as the data analysis provided a wrongful interpretation of company's recorded profitability, mainly due to a lack of relevant data in Oscar System *(for instance, other costs pertaining to net profits calculations)* and a miscommunication/misunderstanding on the data's meanings *(How sales don't happen on the "invoice date", but on another "sales date")*. So pretty much all of the sales and profits analysis are actually just **"incoming invoices"**. However, predictions on orders and purchases management were validated against Oscar system data of June-July periods and seemed to be of interest to Aspelin. Finally, the suppliers and customers overview were also providing a good picture of the company's network portfolio. --- # Overview The case analysis tracks below include data extracted from Aspelin's internal database **OSCAR system** . The data in the system in categorized as such: - Sales Management - 3 datasets - Purchase Management - Stock Control - Customers & Suppliers - 2 datasets - Customer 992, supplier 519 - Production Management Data - Service Control The cases we're divided pretty much in 7 main parts, but several sub-cases. These are the most insightful sub-cases. - **Analysis on customers and suppliers** *(4 datasets to use from Customers & Suppliers and Sales- & Purchase management)* - Sub-Cases: - Top customers - Top customer preferences - Top suppliers - Top supplier preferences - **Analysis on sales** *(4 datasets to use from Sales & Management)* - Sub-Cases: - Sales pattern - Sales growth - **Analysis on products** *(1 datasets to use from Stock Control, combined with Job registery)* - Sub-Cases: - Top products - Top worst products - Top product groups - Top worst product groups - **Analysis on purchases** *(2 datasets to use from Purchase Management)* - Sub-Cases: - Top materials bought - Top worst materials bought - Top material groups - Top worst material groups - Purchase pattern - **Analysis on profits** *(4 datasets to use from Sales- and Purchase Management)* - Sub-Cases: - Gross profit - Gross profit margin - **Sales & purchasing forecasting** *(4 datasets to use from Sales- and Purchase Management)* - Sub-Cases: - Sales forecasting - Purchase forecasting - **Misc analysis** *(2 datasets to use from Sales Management & Quality deviation maintenance)* - Sales person - Defects - **Lessons Learned so far** ---- # Customers and supplier analysis Descriptive analysis Cases: - Top customers - Top customer preferences - Top suppliers - Top supplier preferences ---- ## Top 5 customers (out of 992) - Fazer Leipomot Oy Ostoreskon - Boström E. Oy Ab - Hoviruoka Oy - Kyröntarhat Laskut - Honkatarhat Oy ---- ## Customer preferences (or most popular options) ``` Terms of delivery Vapaasti varastossamme & Vapaasti varastossamme (Hollola) Delivery method KAUKOKIITO Payment terms 14 pv netto ``` ---- ## Top 5 suppliers (out of 533) - Miller Graphics Pietarsaari - Sataplast Oy - Pentti Laiho Ky Ins.tsto - Sun Chemical Oy - Innobit Oy ---- ## Supplier preferences (or most popular options) ``` Terms of delivery SO (Sopimuksen Mukaan) = As Agreed Delivery method SO (Sopimuksen Mukaan) = As Agreed Payment terms 30 days net ``` ---- ## Conclusion on customers and suppliers We'll see later how ,for example, the top products are Fazer products, mainly due to Fazer being the #1 biggest customer. These are good "priority setters", things Aspelin should shift a focus on as they are the most common but also to show what are getting left out. ---- # Sales analysis Descriptive & diagnostic analysis Cases: - Sales pattern - Sales growth ---- ## Sales pattern Dark violet shows amount of invoices, pink shows value of these sales. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_37034e546ba9989a10d55bc0171c177f.png) End of 2021 looks bad because there are just preorders. A slight down-trend. Peaks seems to happen in the general summer time. I also did a statistical check using statsmodels.tsa.seasonal.seasonal_decompose library, and there was no seasonality. ---- ## Sales growth These graphs will show the percentage of growth by: - Year-over-Year - Quarter-over-Quarter Note: I cut off 2016-2017 and end of 2021 because they overall are quite anomalous (Like in 2016, growth is 600 %) ---- YoY growth ![](https://gitlab.dclabra.fi/wiki/uploads/upload_5d4781310a24e511e700004607a8a2cc.png) In 2021 business growth has slowed down significantly. ---- QoQ growth ![](https://gitlab.dclabra.fi/wiki/uploads/upload_eebd26074a0b9c93fa873705ec180b76.png) Q1 growth is big due to Q4 being really slow, but it probably also has something to do with taxes, which influences not only how customers behave, but also how Aspelin markets and sells their products. ---- ## Conlusion of sales Sales for 2021 are down overall, but not as massively in actual revenue as you'd think by looking at just couple of sources. The value of these simple graphs is to show that even if there are less customers, it doesn't mean less profits. ---- # Product analysis Descriptive analysis Cases: - Top products - Top worst products - Top product groups - Top worst product groups ---- ## Top 5 products Sorted by most amount sold: ``` 2016807 Reissumies 4 kpl pyör Frequency: 78 Freq Percentile: 0.54% Price: 1292363.84 € Price percentile: 0.17% Amount: 32364180 Amount percentile: 5.38% 2017013 Reissumies Tumma 4 kp Frequency: 68 Freq Percentile: 0.47% Price: 5446911.04 € Price percentile: 0.70% Amount: 16627616 Amount percentile: 2.76% Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 0.19% Price: 46907086.00 € Price percentile: 6.02% Amount: 11176250 Amount percentile: 1.86% 2017014 Reissumies Kaura 4 kpl Frequency: 42 Freq Percentile: 0.29% Price: 3641962.50 € Price percentile: 0.47% Amount: 7458350 Amount percentile: 1.24% 2016007 Reissumies 4 kpl Frequency: 36 Freq Percentile: 0.25% Price: 3413107.50 € Price percentile: 0.44% Amount: 7391140 Amount percentile: 1.23% ``` Sorted by most revenue: ``` 2016009 Reissumies 12 kpl Frequency: 48 Freq Percentile: 0.33% Price: 77563247.20 € Price percentile: 9.95% Amount: 6845780 Amount percentile: 1.14% Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 0.19% Price: 46907086.00 € Price percentile: 6.02% Amount: 11176250 Amount percentile: 1.86% Reissumies Tumma 4 kpl 2006687 Frequency: 18 Freq Percentile: 0.12% Price: 42409081.10 € Price percentile: 5.44% Amount: 3649750 Amount percentile: 0.61% Paahtoleipä monivilja pp Priim Frequency: 11 Freq Percentile: 0.08% Price: 21259035.60 € Price percentile: 2.73% Amount: 3827350 Amount percentile: 0.64% Reissumies 4 kpl 2000043 Frequency: 35 Freq Percentile: 0.24% Price: 17812600.00 € Price percentile: 2.29% Amount: 6389000 Amount percentile: 1.06% ``` By times appeared in an invoice: ``` Porkkana 1 kg Frequency: 92 Freq Percentile: 0.63% Price: 8975426.20 € Price percentile: 1.15% Amount: 5501900 Amount percentile: 0.91% Lihapiirakka 10kpl pss Frequency: 81 Freq Percentile: 0.56% Price: 2786682.70 € Price percentile: 0.36% Amount: 4809719 Amount percentile: 0.80% Jääsalaatti Frequency: 80 Freq Percentile: 0.55% Price: 6406272.68 € Price percentile: 0.82% Amount: 3742019 Amount percentile: 0.62% 2016807 Reissumies 4 kpl pyör Frequency: 78 Freq Percentile: 0.54% Price: 1292363.84 € Price percentile: 0.17% Amount: 32364180 Amount percentile: 5.38% Sok Persilja Frequency: 76 Freq Percentile: 0.52% Price: 5376206.80 € Price percentile: 0.69% Amount: 3796780 Amount percentile: 0.63% ``` ---- ## Top products by year Sorted by revenue ``` 2016 Lihapiirakka 4kpl flow Frequency: 5 Freq Percentile: 25.00% Price: 1257162.30 € Price percentile: 75.17% Amount: 341302 Amount percentile: 41.56% Pirkka lihapiirakka 4kpl flow Frequency: 2 Freq Percentile: 10.00% Price: 415198.80 € Price percentile: 24.83% Amount: 39770 Amount percentile: 4.84% Burger Frequency: 2 Freq Percentile: 10.00% Price: 0.00 € Price percentile: 0.00% Amount: 0 Amount percentile: 0.00% Jääsalaatti Fresh Frequency: 1 Freq Percentile: 5.00% Price: 0.00 € Price percentile: 0.00% Amount: 474 Amount percentile: 0.06% Ldpe pussi teippi Frequency: 1 Freq Percentile: 5.00% Price: 0.00 € Price percentile: 0.00% Amount: 0 Amount percentile: 0.00% ``` ``` 2017 Reissumies 4 kpl 2015084 Promo Frequency: 27 Freq Percentile: 1.72% Price: 46907086.00 € Price percentile: 12.17% Amount: 11176250 Amount percentile: 13.56% Paahtoleipä monivilja pp Priim Frequency: 3 Freq Percentile: 0.19% Price: 21246324.00 € Price percentile: 5.51% Amount: 3041400 Amount percentile: 3.69% Reissumies Tumma 4 kpl 2015085 Frequency: 15 Freq Percentile: 0.95% Price: 12293528.50 € Price percentile: 3.19% Amount: 4392450 Amount percentile: 5.33% Jääsalaatti Kaarlo Kani Frequency: 2 Freq Percentile: 0.13% Price: 10850112.00 € Price percentile: 2.81% Amount: 724500 Amount percentile: 0.88% SOK Korianteri 310 Frequency: 2 Freq Percentile: 0.13% Price: 9058802.03 € Price percentile: 2.35% Amount: 1006249 Amount percentile: 1.22% ``` ``` 2018 2016009 Reissumies 12 kpl Frequency: 14 Freq Percentile: 0.41% Price: 77563247.20 € Price percentile: 25.69% Amount: 1323050 Amount percentile: 0.97% Reissumies Tumma 4 kpl 2006687 Frequency: 12 Freq Percentile: 0.35% Price: 42409081.10 € Price percentile: 14.05% Amount: 2986250 Amount percentile: 2.20% Reissumies 4 kpl 2000043 Frequency: 26 Freq Percentile: 0.76% Price: 12940900.00 € Price percentile: 4.29% Amount: 3998000 Amount percentile: 2.94% Patrik Jälkiuuniruispala 6 kpl Frequency: 10 Freq Percentile: 0.29% Price: 7252812.36 € Price percentile: 2.40% Amount: 640500 Amount percentile: 0.47% Riisipiirakka 9kpl flow Frequency: 8 Freq Percentile: 0.23% Price: 5361112.10 € Price percentile: 1.78% Amount: 504028 Amount percentile: 0.37% ``` ``` 2019 2017013 Reissumies Tumma 4 kp Frequency: 10 Freq Percentile: 0.28% Price: 5446911.04 € Price percentile: 7.04% Amount: 6145546 Amount percentile: 3.97% Lehtsalat uusi y-pussi Frequency: 4 Freq Percentile: 0.11% Price: 5348148.00 € Price percentile: 6.92% Amount: 1011150 Amount percentile: 0.65% Suippopaprika Frequency: 2 Freq Percentile: 0.06% Price: 3711548.00 € Price percentile: 4.80% Amount: 373000 Amount percentile: 0.24% 2017014 Reissumies Kaura 4 kpl Frequency: 10 Freq Percentile: 0.28% Price: 3641962.50 € Price percentile: 4.71% Amount: 3089450 Amount percentile: 2.00% Lapu salati uusi y-pussi Frequency: 6 Freq Percentile: 0.17% Price: 3181158.16 € Price percentile: 4.11% Amount: 1387750 Amount percentile: 0.90% ``` ``` 2020 Pirkka Luomu Lehtisalaatti 007 Frequency: 5 Freq Percentile: 0.13% Price: 3801735.00 € Price percentile: 29.34% Amount: 671950 Amount percentile: 0.40% Lihapiirakka 4kpl flow Frequency: 8 Freq Percentile: 0.21% Price: 3061729.00 € Price percentile: 23.63% Amount: 1120659 Amount percentile: 0.66% Lapu salati y-pussi Frequency: 4 Freq Percentile: 0.11% Price: 2535097.50 € Price percentile: 19.56% Amount: 1228350 Amount percentile: 0.72% Persilja 1P Frequency: 8 Freq Percentile: 0.21% Price: 2189700.00 € Price percentile: 16.90% Amount: 639650 Amount percentile: 0.38% Vihreäkeiju Sitruunamelissa Frequency: 4 Freq Percentile: 0.11% Price: 349272.00 € Price percentile: 2.70% Amount: 61400 Amount percentile: 0.04% ``` ``` 2021 100% Kaurarieskanen pyöreäpohj Frequency: 4 Freq Percentile: 0.19% Price: 0.00 € Price percentile: nan% Amount: 247250 Amount percentile: 0.43% 2000035 Pitkojen Pitko 600g Frequency: 2 Freq Percentile: 0.09% Price: 0.00 € Price percentile: nan% Amount: 76100 Amount percentile: 0.13% 2017013 Reissumies 25µ Tumma Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 41250 Amount percentile: 0.07% 2017013 Reissumies 25 µ Tumma Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 8600 Amount percentile: 0.01% 10005373 Huhtamäki Paper Cup 1 Frequency: 1 Freq Percentile: 0.05% Price: 0.00 € Price percentile: nan% Amount: 533 Amount percentile: 0.00% ``` ---- ## Top 5 least performing products By total amount sold: ``` Saariston Porkkana 1 kg -225 532.9149 Pe-pussi 440x900 -71 450.0000 Pe-pussi UR adap. PRO 230 x 23 -23 478.2609 Sekaleipä pitkä 0.0000 Setsuurisämpylä 0.0000 ``` By revenue: ``` Saariston Porkkana 1 kg -1 849 370 € Pe-pussi UR adap. PRO 230 x 23 -208 487 € 0 0.00 € Persilja antifog 0.00 € Persilja opp/pp 0.00 € ``` By times appeared in an invoice (Actually 1045 items, here's a few:) ``` Menu 5991 245 33x33 50 kp 445 1 10 kg vihreä 1 Jyväsydänsämpylä 6kpl 360g 1 PP-pussi 200*280 1 Karhujää 10 kg murska 1 ``` ---- ## 3 most common products sold together ``` ((' 2017013 Reissumies Tumma 4 kp', ' 2017013 Reissumies Tumma 4 kp', ' 2017013 Reissumies Tumma 4 kp'), 930), ((' Lidl lihapiirakka 8 kpl', ' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss'), 890), ((' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss', ' Lihapiirakka 10kpl pss'), 785), ((' Sok Tilli', ' Sok Tilli', ' Sok Tilli'), 702), ((' Iso Pehtoorin pussi painamaton', ' Painettu Yleispussi 235*450 mm', ' Painettu Yleispussi 235*450 mm'), 690), ``` ---- ## Item group sales Most sold ``` Vikettipussit painettu 139 867 799 Muotosauma painettu 54 078 994 Radat painettu (kq) 45 435 489 Vikettipussit painamaton 14 560 528 Radat painamaton (kq) 12 137 473 ``` Most sold by value ``` Vikettipussit painettu 32 847 991 € Erikoispussit painettu 10 332 000 € Muotosauma painettu 10 035 348 € feipit 3 870 000 € Vikettipussit painamaton 2 677 500 € ``` Most frequent ``` Vikettipussit painettu 3049 Muotosauma painettu 1371 Radat painettu (kq) 991 Vikettipussit painamaton 362 Radat painamaton (kq) 292 ``` ---- Least sold ``` älä käytä 0 OPP valkoinen 5 700 PP 25 638 rahti 29 600 Barrier 49 500 ``` I can't be sure of the "least sold" as many items don't have a "sell value". Least frequent ``` OPP valkoinen 1 (kq) Alihankintatyöt tai osa-alihankinta mm. Kokemäki (kq) 1 älä käytä 1 rahti 2 Laminaattiradat (kq) 3 ``` ---- ## Conclusions of product basics - Most sold - 2016807 Reissumies 4 kpl pyör 32364180 - Overall Fazer products - Group: Vikettipussit painettu - Least sold - Saariston Porkkana 1 kg -225532.9149 - (I don't actually know what negative amount means here...) - Group: "OPP valkoinen" & "kq) Alihankintatyöt tai osa-alihankinta mm. Kokemäki (kq)" - Most lucrative - 2016009 Reissumies 12 kpl 77 563 247.20 € - Overall Fazer products - Group: Vikettipussit painettu 32 847 991 € - Least lucrative - Saariston Porkkana 1 kg -1 849 370 € - Group: Again, not sure of this one - Most frequent - Porkkana 1 kg 92 - Group: Vikettipussit painettu 3049 - Least frequent - 1045 individual items only bought once - Group: OPP valkoinen ---- Again, we can see how the top products are Fazer products. The product groups make sense, packaging is the main product of Aspelin afterall. ---- # Purchase analysis Descriptive & diagnostic analysis Cases: - Top materials bought - Top worst materials bought - Top material groups - Top worst material groups - Purchase pattern A "purchase" in this context means "products/materials Aspelin has bought" to create their own products. ## Purchase pattern Dark orange shows amount of purchases, light orange shows value of these purchases. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_f0c7e4d0ffc2439fb8dd00e669caf8fc.png) Peaks follow peaks, dips follow dips. However sometimes there are more individually expensive purchases. --- ## Top 5 materials bought By total amount purchased: ``` Niputin 4 764 000 Price: 136 250.40 € 2013121 OPP pussi 2 900 000 Price: 45 795.00 € AUAPM1 LDPE 25 1 664 000 Price: 20 800.00 € 0,02 x 650 x 680 + 215 sv 1 600 000 Price: 97 917.13 € 2013120 OPP pussi 1 000 000 Price: 18 515.00 € ``` By price: ``` Rincel MXM AF 1140*0,025 597950.00 € Amount: 269500 LDPE 100*460+150SW 449350.00 € Amount: 215200 BOPP25 TSAAF 1140 401390.00 € Amount: 200000 Telkomix 80/20 ohennusaine 302150.00 € Amount: 215000 Etax BA7/EA20 276850.00 € Amount: 220000 ``` By times appeared in an invoice: ``` Yellow nc base Y0131 114 Duraline yellow 4-värisarjan k 96 Multi white 482-93448 83 Blue nc base B015 75 Duraline magenta 4-värisarjan 75 ``` ---- ## Top least frequent/special case products bought Items that were bought only once: ``` Tiivistesarja nostosylinteri N 1 MicrosoftSQLServerStandardEdit 1 Kuvalaattateippi 52815 460mmx0 1 Tuotanto ja toimituskulut 1 Lisätyöt 1 Toimituskulut 8.6. 1 57650C 1 Monitori BENQ BENQ 17" BL702A 1 aloituskulu 1 Toimituskulut 1 450/900 19my polyolefiini 1 Kokonaismigraatiotesti 1 Stanssi 1 STANSSI 1 ``` ---- Items that have 0 cost: ``` BOPP AF 30*1140 0.000000 € Amount: 25 FXPF 780*0,038 0.000000 € Amount: 50 PE-letku 85mm testierä2 0.000000 € Amount: 10 PLCBAF 680*0,025 0.000000 € Amount: 21 PP 40*760 F113 + 2% PolyBi 0.000000 € Amount: 500 PP25*0630 Protec-119 + Poly-Bi 0.000000 € Amount: 50 430833 COEXPE 1160x0,035 0.000000 € Amount: 50 LDPE30*0820 Ecoplast 0.000000 € Amount: 200 Solmupussi 7 kg 513199 200kpl/ 0.000000 € Amount: 300 LDPE30*700 Ecoplast 0.000000 € Amount: 220 432605 COEXPE 840x0,029 0.000000 € Amount: 100 LDPE30*880 Ecoplast 0.000000 € Amount: 170 Rot raster 62GU447766 hidas wa 0.000000 € Amount: 60 LDPE35*1000 0.000000 € Amount: 254 432606 COEXPE 1100x0,033 0.000000 € Amount: 100 450/900 19my polyolefiini 0.000000 € Amount: 1 44365 BRXH-C 450/900mm 15my 0.000000 € Amount: 1335 432607 COEXPE 1160x0,035 0.000000 € Amount: 50 432782 CoexPE 1160*0,050 0.000000 € Amount: 50 434449 LDPE 520*0,030 0.000000 € Amount: 10 FXCAF 1140*0,025 0.000000 € Amount: 20 Woodly 1140*0,040 0.000000 € Amount: 100 433018 CoexPe 70*740 0.000000 € Amount: 50 BS 800*0,025 0.000000 € Amount: 51 CPP KB 25*1000 0.000000 € Amount: 25 Cata 25 A09 1140 mm 0.000000 € Amount: 440 Woodly 320*0,030 0.000000 € Amount: 12 BS 780*0,050 0.000000 € Amount: 49 kaupintaRK-35*1000 0.000000 € Amount: 2765 kaupintaRXE-25*0640 0.000000 € Amount: 12311 kaupinta RK-35*1140 0.000000 € Amount: 4125 Woodly40*0900 0.000000 € Amount: 90 Woodly 800*0,030 0.000000 € Amount: 100 BS 760*0,040 0.000000 € Amount: 50 koePP25*0900 af 0.000000 € Amount: 41 Woodly 320*0,030 perforoitu 0.000000 € Amount: 12 kaupintaRXP+32*0730 0.000000 € Amount: 1169 PE-letku 85mm testierä1 0.000000 € Amount: 10 ``` ---- By times appeared in an invoice (Actually 650 items, here's a few:) ``` Leivosrasia 3 valk 1 Pakastemansikka 400 g 1 M1921238 Banderolli 1 LDPE30*700 Ecoplast 1 FXC 900*0,050 1 ``` ---- ## Top material groups Most bought ``` Värit 1268550 LOPE 877667 PP 325788 COEX 310242 (kpl) Alihankintatyöt tai osa-alihankinta mm. Kokemäki (kpl) 293492 ``` Most sold by value ``` LOPE 451711.15 € PP 174836.52 € Vikettipussit painettu 136057.59 € Vikettipussit painamaton 125822.39 € Värit 108802.76 € ``` Most frequent ``` LOPE 170 PP 68 Vikettipussit painettu 58 Värit 56 Vikettipussit painamaton 48 ``` ---- ## Top least bought material groups Least bought ``` rahti 10 muut mm Laattamyynti 100 PE 108 Polyolefiini 350 Sellofani 600 ``` Least bought by value ``` rahti 138.30 € muut mm Laattamyynti 470.00 € PE 625.32 € Polyolefiini 1954.90 € BIBE 2380.24 € ``` Least frequent ``` Sellofani 1 PE 1 rahti 1 tekniset tiedot 1 muut mm Laattamyynti 1 ``` ---- ## Conclusions of material aquisition - Most sold - Niputin 4 764 000 - Group: Värit 1268550 - Least sold - Misc. items like office supplies or one off "deliveries" - Group: rahti - Most expensive - Rincel MXM AF 1140*0,025 597950.00 € Amount: 269500 - Group: LOPE 451711.15 € - Least expensive - Many items are aquired free, but the most bought - kaupintaRXE-25*0640 0 € Amount: 12311 - Group: rahti 138.30 € - Most frequent - Yellow nc base Y0131 114 - Group: LOPE 170 - Least frequent - 650 items - Group: Sellofani, PE, rahti, tekniset tiedot, muut mm Laattamyynti ---- Most bought materials are colours and material for printing. Also "niputin" which I guess is a zip tie that's used to tie bags together and whatnot. ---- # Profit analysis Descriptive analysis Cases: - Gross profit - Gross profit margin ---- Let's first reminds ourselves what sales and purchases look like: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_713d0a197a62a815dc4c65fe673c3e67.png) Sales are on a down-trend both in invoice amount and revenue, purchases similarly, but seem to be bouncing back upwards in 2021. Let's add the gross profit line in the graph: ---- ![](https://gitlab.dclabra.fi/wiki/uploads/upload_66eb024f7c01bb594705b46bf237aeae.png) It's getting kinda confusing, Let's isolate the "value" of sales and purchases and make them bars. Then add the gross profits and gross profit margin percentage. ---- Profits are in yellow, gross profit marging percentage in black, percentage on the right. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_b55e470b690ee160d94c61eea669b6ab.png) Gross profits seem to follow sale margins: Peaks follow peaks, dips, follow dips. For purchases, profits typically go down when sales are down and purchases go up, naturally. We can say that profits are not in an obvious down-trend, but they are lower in 2021 than in 2020. Whenever sales are down and purchases are up, the profit takes a dip and vice versa. ---- Same, but quarterly: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_cbc4b770cdfdeb5ef7a41e2b47777d11.png) A slight down-trend in profits, a huge jump in 2020-Q4 gross profit margin due to sales and purchases being so incredibly low. Otherwise, it seems to hover around the 40% mark. The net- or gross profits affect the profits margin the most. The simplest way to adjust profit margins is to adjust the sale price of the products. Profit margin also goes down if company is trying to increase market share. Then of course, outside factors affect margins too. These require market analysis thought, something that was outside the scope of this project and we couldn't find data about the overall packaging industry margins. Apprently, **40%** is a **low margin**, **50%** would be **OK** and **60%** is **really good**. This is a generalization, it could be different in the packaging market. ---- Let's check if this behaviour is typical for Q4's. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_3c3b74c80f816137c196ee69085c092c.png) Yes it is! Also, Q1 has always been slower than Q2 and Q3 too. ---- Here are sales yearly averages. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_9e2334c98ce03a43cd9556a66f48d916.png) Overall, on average, everyting is on slighty lower than on the previous year. Purchases are abnormally high in 2021 so far, but it probably means the rest of the year sees less purcheses, so it should even out. ---- ## Compound Annual Growth Rate (CAGR) This seems to be the only metric that can be used to compare how well does Aspelin hold up in comparison to the packaging industry as a whole. These industries follow growth by actual revenue and this metric called Compound Annual Growth Rate (CAGR), which is the rate of return that would be required for an investment to grow from its beginning balance to its ending balance, assuming the profits were reinvested at the end of each year of the investment’s life span. Different sources use different timescales, but it seems that packaging industry's CAGR is about +4% between 2020-2025. ---- ![](https://gitlab.dclabra.fi/wiki/uploads/upload_b0f047ab7746a86f96d2d31aadf6bc1b.png) For Aspelin, between 2018-2020, the CAGR is 6.77%, which is above the average estimations. This does fit the fact that Aspelin says they've grown massively in quick succession and seems to be competetive by the industry standards. ---- # Fazer So as Fazer is the #1 customer, making up almost 10% of all the orders ever placed, it would make sense to check how much does Fazer actually affect the sales of Aspelin. ---- The upcoming graphs show in red the difference between sales and sales without Fazer. ![](https://gitlab.dclabra.fi/wiki/uploads/upload_a6d531f7273e9dd9fe07d16e999884f2.png) This shows how without Fazer, Aspelin would lose 10% of all the sales coming in. ---- ![](https://gitlab.dclabra.fi/wiki/uploads/upload_a9fa9da9fdf857d20fa21ed87c13d84a.png) With revenue, without Fazer, Aspelin would lose 9.3% of all revenue. ---- I would do a similar graph for profits, but that requires to look up what materials Aspelin buys which they make products that Fazer buys. And that is kinda unprecisise considering the column flipping I'd have to do. ---- When is Fazer buying from Aspelin? This graph shows Fazer's buying patterns compared to overall buying patterns (growth in this case) ![](https://gitlab.dclabra.fi/wiki/uploads/upload_28842f571b7038bd510557c3ad13b98c.png) And this should hammer home how integrated Fazer is to Aspelin's overall sales behaviour. ---- Difference Fazer makes: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_390c6acb8075e6fb1803a4e01a8504b9.png) Fazer is big, but they aren't that "worth it", as they don't bring that much profits and a ton is spent on materials for their products. ---- ## Conclusion on Fazer This might show over-reliance on Fazer to make ends meet. Let's say Fazer would change to a different company for their packaging needs: Overall, Aspelin would lose 10% of their sales and revenue. That's about 50 000 € a month on average. But as Aspelin said, Fazer is "worth it" for many other reasons, like brand, stability and other benefits their relationship with Fazer brings. ---- # Sales & purchase forecasting Predictive analysis Cases: - Sales forecasting - Purchase forecasting I used SARIMA-method to do these forecasts as it's made for this type of thing. Overall, only thing I could forecast was amount of orders. The model didn't handle revenue or anything else that well and more complex prediction (like item recommendations) would require a neural network based solution. ---- ## Sales forecasting ![](https://gitlab.dclabra.fi/wiki/uploads/upload_f1986b9bd2ee8c28f1a65f9831f7d893.png) 1 year prediction on how many orders would be coming through at the end of the month: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_5022a64c11b35a427e9d8925f49b0365.png) The Mean Squared Error = 3238.26 The Root Mean Squared Error = 56.91 ---- A predicted down-trend, following the slight down-trend that was visible to the eye with the sales graph. But the grey area is the "Min-max" possibility area. So it could very well we anywhere in that area. ---- ## Purchase forecasting ![](https://gitlab.dclabra.fi/wiki/uploads/upload_bf02e9f9df61dcc80832082dbe43dc11.png) 1 year predictions on how many orders would be coming through at the end of the month: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_ad92005fc30d8b0effd3b2f57320a8c2.png) Here the prediction seems to "stabilize" in this very shallow dip. So this method predicts the company is going to bumb up their material purchases in 2022. The Mean Squared Error = 155.41 The Root Mean Squared Error = 12.47 ---- # Misc analysis Descriptive analysis Cases: - Sales person - Defects ---- ## Sales person Only analysis that could be done to this was to figure out who was the "best" sales person. But I figured there's only like 2 people responsible for logging in all the data and a bunch of people probably don't even work at Aspelin anymore so I only looked at 2021 onward. ---- ``` Eija Sjöblom 377 Katja Riutto 306 Roosa Mielonen 105 Kirsi-Marja Kastemäki 90 Jouko Majanen 16 Varasto 6 Harri Helén 3 Mari Anttila 1 ``` ---- All 2021 handled revenue: ``` Eija Sjöblom 2 875 191.31€ Katja Riutto 2 070 777.20€ Roosa Mielonen 599 223.58€ Kirsi-Marja Kastemäki 215 245.63€ Jouko Majanen 10 007.22€ Harri Helén 7 571.56€ Mari Anttila 1 937.58€ Varasto 869.32€ ``` Sjöblom and Riutto probably are the main people logging things, everyone else does it because nobody else will, I guess. ---- ## Defects There isn't much to talk about defects, as the data was just logs of individual cases. But here's the categories of defects: ``` Valmistusvirhe 34 Huomautus 13 Materiaalivirhe 4 Aikataulu 3 Pakkaus 2 Muu syy 2 ``` Most defects happen on the manufacturing line, for example, bag being glued together or prints having bad colors. ---- # Feedback from Aspelin After the first presentation, they noted that profits and sales were too low, for example, 2021 is their best year, but the analysis I did showed everything was going down. This was due to me mainly using incorrect date columns. So what I did was actually "incoming invoices", not actual sales by day, as the actual invoice comes in another day and the actual order can leave like 6 months later. So even if those were *"incorrect"*, they did find rest of the insights interesting: They know their situation with Fazer and have discussed their ideology towards them and it seems it's worth to keep them them as a #1 customer even for the brand alone, for example. The products and such brought wonder, how and when Oscar gets updated data. The predictions showed them that doing simple prediction is indeed simple. --- Aspelin said, they're taking the time to analyze everything and to give further feedback about how Oscar system works when they are ready. --- # Lessons learned **Things that went well with the project:** - Data listing - This helped to get a cohesive understanding of the data we had and brainstorm ideas - Actual brainstorming and ideas - Kawtar's work and documentation helped with the inspiration immensely - Even if the data was limited, there are insights that should bring value for the company **Problems faced with the project:** - No idea of the goals, requirements nor standards - Sandboxing is fun and all, but it's ultimately goalless, thus very unoptimized and slow. - There should be some sort of a Data Listing/walkthrough provided by the company - There are still things that are confusing about the data (columns or column values) - Some things were "hidden" within the system that took a while to find. - However, many questions were answered when I snooped around the Oscar system - Getting the data - I copy&pasted from the Oscar system as it didn't allow saving files. - Combining columns from different datatables - There are tools in the Oscar system to get few more columns to a datatable to create more context, however some logical things are missing (Orders table doesn't have both price & items) - Some of these Oscar tools couldn't be used as the data couldn't be copy&pasted a.k.a the only way to save it. - One should make a plan on how to structure documentation # Personal lessons learned ## The Good stuff - A really good understanding of Pandas and matplotlib - Will be massive help in pre-processing and manipulating data. - A much better understanding how to manage and structure a project, especially one has no concrete goal. - Is going to help in future projects, so I'm not wasting time doing wrong things. - Better understanding of businesses, business-lingo and what's valuable to a business. - Is going to help with putting myself "in the shoes of the manager" ## Things that could be improven - More frequent communication between client and team (This was mainly due to people being on summer leave) - This was the main cause for the mistakes in the analysis - Reading more about business management and overall how businesses operate - Some say that a good data-analyst is a good business-analyst... - Asserting a concrete goal, even an unofficial/temporary one - More about evolving personal project management. - Not breaking git - I dunno why, but I somehow seem to manage to break my repository in almost every project... # Time tracking As June was a month of *"getting things up and running"*, I had nothing to do. I did read some general info about business analytics and such to get some background, but other than that, I could only do is wait until I had access to the Oscar system and the data. **And how I imagined the project to go was:** - June: Get to know the data, do basic analysis - July: Do statistics - August: Present results, make presentations, make deeper conclusion on results ***How it actually went:*** - June: Few meetings, read some trivia - July: Get to know data - August: Present results, make presentations, make deeper conclusion on results So with that *setback*, here's the **Clockify** time tracking: ![](https://gitlab.dclabra.fi/wiki/uploads/upload_04043a6bb21e7d8f299fef11161b1845.png)