Pidilite_Internship1111111111111111.pptx

Summer Internship at pidilite

1. ENHANCING PROCUREMENT ANALYTICS EFFICIENCY WITH POWER BI Summer Internship, April’2023 – June’2023 Sourita Banerjee Symbiosis Institute of Operations Management, Nashik Project Guide - Abhishek Kumar P I D I L I T E I N D U S T R I E S L I M I T E D , A N D H E R I E A S T , M U M B A I

2. PROJECT DETAILS INVENTORY NON MOVING CREDIT PERIOD Table of Content P i d i l i t e I n d u s t r i e s L i m i t e d , A n d h e r i E a s t , M u m b a i

3. P i d i l i t e I n d u s t r i e s L i m i te d , A n d h e r i E a s t , M u m b a i Project Parameters Department: Sector Scope: Purchase RM (Raw Materials) Classification parameter: Division: Fevicol, IP1, IP2, CC, CPMNT, CPASF, Araldite, Cipy Purchase groups Account Groups (Supplier market): ZDOM, ZIMP

4. INVENTORY ZMM424, MB51 Source Reports: NON MOVING ZMM424,DRM CREDIT PERIOD MB51,MKVZ

5. Inventory ZMM424 : January, February, and March - Consolidated File. The following fields were taken from the same. MB51: Purchase data The following were calculated: Consolidated Stock = Current Stock+ QC Stock+ GIT Average YPM = Average of Last Three months’ YPM Opening Value= Opening+ Stock*MAP Closing Value= Closing Stock*MAP Max & Min Norm Value = Max & Min Stock *MAP (MAP: Moving Average Price)

6. Inventory 8. Excess Quantity = Closing Stock – Max Norm Stock 9. Low Quantity = Min Norm Stock – Closing Stock 10. Excess & Low Value = Excess Quantity & Low Quantity * MAP Consumption Stock= Opening Stock+ Purchase Stock- Closing Stock Consumption Value= Consumption Stock*MAP

7. Dashboard Visualization of Inventory:

8. Pivot Tables & Graphs 1 . Report : DOC _ L 3 M A C _ Y P M : Note: All values are in Crore (INR)

9. 1 . Report : DOC _ L 3 M A C _ Y P M : Note: All values are in Crore (INR)

10. Deduction: T h e a b o v e g r a p h d e p i c t s t h e M o n t h - o n - M o n t h t r e n d o f o p e n i ng , c l o s i ng , a n d purchase values against consumption values. In March, the Consumption is greater than usual, showing the graph spikes greater than the purchase value, indicating inventory planning for future consecutive months. The above graph also depicts the Month-on-Month trend of opening, closing, L3MAC, Average YPM, Purchase, and Consumption values against two parameters: DOC-YPM DOC-L3MAC Insights:

11. 2 . Report – I n v e n t o r y W a t e r f a ll : I nput: Date (i) - 1st of Jan, Feb, March. Date (ii)- Last Day of each month. Note: Calendar Option for Date Selection is to be added. Note: All values are in Crore (INR)

12. 2 . Report – I n v e n t o r y W a t e r f a ll : Note: All values are in Crore (INR)

13. Insights: Note: Month-wise, Slicer is to be updated in Power BI; the Consumption value is kept negative to show the reduction effect in the graph.   Deduction: The above waterfall diagram depicts the opening, closing, and purchase trends against the consumption value of two selected dates of a period of time. The months Jan, Feb, and March has been taken here. Here the dates have been selected as per the inputs stated before.

14. 3 . Report: N o r m V a l u e _ I n v e n t o r y : Note: All values are in Crore (INR)

15. 3 . Report : N o r m V a l u e _ I n v e n t o r y : Note: All values are in Crore (INR)

16. Insights: Deduction: The above graph depicts the Month-on-Month trend of Consumption Value and closing value against the standard Max Norm Value and Min Norm Value for the respective months. In March, the Consumption is greater than usual, showing the graph spikes greater , almost reaching the purchase value, indicating inventory planning for consecutive future months.

17. 4. Report Value_Divisions: Note: All values are in Crore (INR)

18. 4. Report : Value_Divisions: Value in Crore (INR), % of Total depicted

19. Insights: Note: Month-wise Slicer to be added. The current Display is only for the month of March. Data of March: Taken from March 2023 DRM. Deduction: The above graph depicts the Closing stock in Crore (INR) against each Division in the form of a Pie Chart.

20. 5. Report _Excess & Low Inventory: Note: All values are in Crore (INR)

21. 5. Report _Excess & Low Inventory: Note: All values are in Crore (INR)

22. Insights: Deduction: The graph shows the Month-on-Month trend of Excess and Low Inventory Value in Crore INR. This can also be shown D iv i s i o n, Plant, Purchase Group-Wise by giving in the required Division , Plant, Purchase Group F ilt e r . Excess Inventory Quantity = Closing Stock – Max Norm Stock Low Inventory Quantity = Min Norm Stock – Closing Stock Excess & Low Inventory Value = Excess Quantity & Low Quantity * MAP .

23. 6 . Pivot_Current Month Receipts_Consumption : Filters Applied: Materials, Plant

24. Non- Moving The logic of Non-moving: NM 1: Closing stock of last month>0, and Last 6 -month’s average consumption =0 NM2 or NM>180: DOC as on last month'> 180. Consolidated value and Quantity are Non-moving Quantity and Stock.

25. Non- Moving Data Source: DOC (Days of cover: Current stock/ (L3MAC or YPM forecast) Current stock Qty: ZMM384 or ZMM424 = Consolidated Calculated Stock 3.Current Stock Value: (ZMM384 or ZMM424) * MAP (Moving average price) 4.MAP = ZMM384 or ZMM484 5.Consumption data from ZMM384 or ZMM424 of last 6 months

26. Dashboard Visualization of Non-Moving:

27. Pivot Tables & Graphs 1 . Report: N M I n v e n t o r y W a t e r f a ll : Input: Month 1: March 2022; Month 2: March 2023 Note: All values are in Crore (INR)

28. 1 . Report : N M I n v e n t o r y W a t e r f a ll : Note: All values are in Crore (INR)

29. Input: Month 1: Jan 2022; Month 2: March 2023 Note: All values are in Crore (INR) Report : NM Inventory Waterfall:

30. Report : NM Inventory Waterfall: Note: All values are in Crore (INR)

31. 2 . Report : D i v i s i o n W i s e _ N M V a l u e : Note: All values are in Crore (INR)

32. 2 . Report : D i v i s i o n W i s e _ N M V a l u e : Note: All values are in Crore (INR)

33. Insights: Deduction: The above graph shows the NM Value in Crore (INR) on a Quarter- On-Quarter basis for all the different Divisions. The Quarter Periods include: March 2022 June 2022 September 2022 December 2022 March 2023

34. 2 . Report : D i v i s i o n W i s e _ N M V a l u e _ M a r c h : Note: All values are in Crore (INR)

35. 3 . Report : D i v i s i o n W i s e _ N M V a l u e _ M a r c h : Note: All values are in Crore (INR)

36. 4 . Report : Q u a r t e r - o n - Q u a r t e r _ N M a n d N M > 1 8 : Note: All values are in Crore (INR)

37. 4 . Report: Q u a r t e r - o n - Q u a r t e r _ N M a n d N M > 1 8 : Note: All values are in Crore (INR) Deduction: The above graph shows the Value in Crore (INR) on a Quarter-on-Quarter basis by differentiating on the basis of NM types: NM, NM>180

38. Credit Period Data source: MB51 and MKVZ Fields with source: MB51: Vendor Vendor description Total Purchase (Amount in L ocal C urrency) MKVZ: Account group (ZDOM, ZIMP) Payment term Payment term Description Calculated fields: Weighted average payment terms = Sum (Amount in local currency*Payment term)/Sum (Amount in local currency)

39. Dashboard Visualization of Credit Period:

40. Pivot Tables & Graphs 1. Report : Account-Group-Wise Payment Term for: Import (ZIMP account group) Domestic (ZDOM account group) Grand Total RM (ZDOM and ZIMP) Input: Period 1: December End 2022; Period 2: Jan - March End 2023

41. 1. Report: Account-Group-Wise Deduction: The above graph shows the progress report of the credit period and the comparison between the Average Number of days between ZDOM and ZIMP and the Total Credit Days between the two periods, as stated in the input.

42. 2. Report : Change in Payment Terms: Input: Period 1: December End 2022; Period 2: Jan - March End 2023

43. Insights: Deduction: The above list shows the Vendors with changes in payment terms between two selected periods, as stated in the inputs. Outputs: Corresponding Vendor Code, Vendor Description, Payments terms of the two periods, and Amount in Local Currency in Crore (INR) received between the two periods can be deduced from it.

44. 3. Report: Venders_Credit Terms The list depicts the list of Vendors with MSME mentioned who are not reaching the Maximum Credit Terms. Maximum Credit Terms are assumed to be 45 days for MSME Vendors and 90 days for Non-MSME Vendors for the sake of calculation. Note: We need to maintain a Z-Table in SAP to record whether a vendor is in MSME , along with its Payment Terms.

45. 3. Report: Venders_Credit Terms

46. SOB Violation Impact Data Source: SOB Violation Report Base List of SOB Violation Cases

47. Note: Cases visible where impact is Non-Zero; Filters Applied: Material, Plant, Purchase Group, Impact. 3. Report: SOB Violation Cases

48. Thank You. Sourita Banerjee Summer Internship, April’2023 – June’2023 Symbiosis Institute of Operations Management, Nashik