🔒
International Engineering Publisher
Serving Researchers Since 2012

Inventory Optimisation, Kit Availability and Business Analytics Through Power Bi and Sap: A Case Study of Ecozen Solutions Pvt. Ltd., Pune

DOI : 10.17577/IJERTCONV14IS120035
Download Full-Text PDF Cite this Publication

Text Only Version

Inventory Optimisation, Kit Availability and Business Analytics Through Power Bi and Sap: A Case Study of Ecozen Solutions Pvt. Ltd., Pune

Siddhi Doke

Student, MBA- Operations and Supply Chain Management,

School of Business, Indira University,

Pune, India 41103 siddhidoke@indirauniversity.edu.in

Dr. Sanjay Asati

Assistant Professor MBA, School of Business

Indira University Pune, India 411033

sanjay.asati@indiraisbs.edu.in

Dr. Manisha Maddel

Associate Professor & Guide, School of Business,

Indira University,

Pune, India-411033 manisha.maddel@indiraisbs.edu.in ORCID: 0009-0008-1720-2959

Abstract – Effective inventory management is essential for manufacturing organisations where material availability, accurate product structures, and timely planning influence operational performance. In organisations handling configurable products and multiple material combinations, fragmented inventory information, variations in Bills of Materials (BOMs), and manual processes can complicate production and dispatch planning. This paper examines inventory optimisation, kit availability, and business analytics at Ecozen Solutions Pvt. Ltd., Pune, through an internship-based descriptive and exploratory case study.

The study draws on four operational projects: automatic child-material stock update for TATA/PMSGY kit Goods Receipt Note (GRN) processing, Balance of System (BOS) kit standardisation for SGY solar projects, analysis of old open Purchase Requisitions (PRs), and development of a PMSGY Kit Availability and Planning Dashboard. SAP was used for material and BOM-related activities, while SQL, Python, Microsoft Excel, and Power BI supported data preparation, analysis, and visualisation. The study examines how accurate master data, component-level stock analysis, Pareto-based prioritisation, and dashboard-based reporting can support inventory and planning decisions.

The findings indicate that reliable BOM structures are essential for kit-level planning, that component-level availability provides more useful information than total stock alone, and that frequently used BOS combinations can be prioritised for standardisation. The analysis of more than 400 old open PRs also highlighted an opportunity to review obsolete requirements and evaluate suitable bulk- processing methods. The paper proposes a structured framework combining SAP data, analytical tools, validation procedures, and planning dashboards. Since the study is based on internship observations and available operational information, its findings are specific to the case and do not establish statistically generalisable or quantified performance improvements.

Keywords – Inventory optimisation, SAP, Bill of Materials, kit availability, supply chain analytics, Power BI, BOS standardisation, Pareto analysis, production planning.

  1. INTRODUCTION

    Inventory management plays an important role in manufacturing and supply chain operations because organisations must maintain sufficient materials to meet production and project requirements without creating unnecessary inventory complexity. Poor material visibility, inaccurate master data, and inadequate coordination between planning and stores can lead to shortages, delays, and inefficient use of available resources.

    Ecozen Solutions Pvt. Ltd. is a climate-smart technology company working across motor controls, energy storage, and data analytics, with solutions including solar pump controllers and solar cold-storage systems [1]. Its product and project requirements create a need for reliable material planning, accurate BOM structures, and effective coordination across procurement, stores, production planning, and other functions. During the internship, several operational activities were studied to understand how SAP and data analytics could support these requirements. The work included parent-child material structures for kit GRN processing, BOS combination analysis, review of old open PRs, and development of a kit availability dashboard.

    These activities were connected by a common objective: improving the quality and usefulness of material-related information for operational decisions.

    The paper addresses three questions:

    How can accurate SAP material and BOM structures support kit-level inventory visibility?

    How can data analysis and Pareto prioritisation support BOS kit standardisation and inventory planning?

    How can a Power BI dashboard improve visibility into kit requirements, shortages, and remaining stock?

    The contribution of this paper is a practical framework that links ERP-based material data with analytical tools and planning outputs. It documents the project approach, identifies operational improvement opportunities, and proposes measures for further validation.

  2. LITERATURE REVIEW AND CONCEPTUAL BACKGROUND

    1. Inventory Management and Material

      AvailabilityInventory management involves maintaining and monitoring stock to meet operational demand while controlling excess inventory and associated costs. Material availability depends not only on the total quantity held but also on the specific components required, their location, and their planned consumption. For configurable products, a sufficient total stock quantity does not necessarily mean that a complete kit can be assembled. Every required component must be available in the appropriate quantity. This makes component-level analysis important for kit planning and shortage identification.

    2. Enterprise Resource Planning and SAP

      Enterprise Resource Planning (ERP) systems integrate information relating to materials, procurement, inventory, and production. SAP supports material master management, BOM maintenance, and material requirements planning. SAP documentation describes Material Requirements Planning as a process for ensuring material availability and planning procurement proposals against requirements [2].

      Accurate master data is important because planning calculations depend on the material definitions, units of measurement, BOM quantities, and other relevant parameters maintained in the system. Incorrect or incomplete information can affect downstream planning and reporting.

    3. Bill of Materials and Kit Management

      A Bill of Materials specifies the components and quantities required to produce or assemble a parent material. In a kit-based environment, the BOM provides the link between the finished kit or parent material and its individual components.

      Reliable BOM management supports component consumption calculations, stock verification, kit preparation, and material planning. Changes to BOM quantities or component structures should therefore be validated before they are used for operational decisions.

    4. Standardisation and Pareto Analysis

      Standardisation reduces unnecessary variation by identifying common configurations and developing consistent structures for repeated requirements. In the BOS project, configurations varied according to factors such as cable requirements, earthing, conduit, structure type, and solar panel configuration.

      Pareto analysis can help prioritise frequently occurring combinations by arranging them in descending order of frequency and calculating their cumulative contribution.The method supports the identification of combinations that may be suitable for standardisation. However, actual coverage percentages must be calculated from the validated project dataset rather than assumed in advance.

    5. Business Intelligence and Power BI

      Business intelligence tools transform operational data into visual reports that support monitoring and decision-making. Power BI can present inventory information across different warehouses, products, and dimensions. Microsoft's inventory visibility documentation illustrates how dashboards can be used

      to identify understocked and overstocked items and monitor on- hand quantities and supply-demand relationships [3].

      Research on supply chain performance dashboards also discusses the value of consolidating data from different systems to improve visibility and decision support [4]. However, a dashboard is only as reliable as its underlying data and calculation logic.

    6. SQL and Python for Operational Analysis

      SQL supports structured data extraction, filtering, aggregation, and grouping. These capabilities are useful when analysing material combinations, transaction records, and inventory information. Python can support data cleaning, data transformation, and repetitive calculations before the information is presented in a dashboard.

      Using these tools together can establish a repeatable analytical process, provided that the data sources, calculation rules, and validation checks are clearly defined.

    7. Research Gap

    The concepts discussed above are well established, but their practical application requires adaptation to the organisation's products, data structure, and operating processes. The present study addresses this practical requirement by examining four connected activities within one organisational setting: kit GRN- related BOM management, BOS standardisation, open PR review, and kit availability analytics.

    The study does not attempt to develop a new inventory theory. Instead, it provides an applied case study showing how ERP data, analytical methods, and dashboard reporting can be combined to support operational planning.

  3. RESEARCH DESIGN AND METHODOLOGY

    1. Objectives

      The primary objective is to examine how SAP-based material management and business analytics can support inventory optimisation and kit availability planning at Ecozen Solutions Pvt. Ltd.The specific objectives are:

      To examine parent-child BOM structures and the proposed automatic child-material stock update during kit GRN.

      To analyse BOS combinations and identify frequently occurring configurations for standardisation.

      To review old open PRs and evaluate opportunities for reducing manual processing.

      To develop a dashboard-based approach for analysing kit requirements, component shortages, and remaining stock.

      To recommend improvements in data quality, inventory visibility, and planning processes.

    2. Design

      The study follows a descriptive and exploratory case-study design. It is based on operational activities performed during the internship, review of available organisational information, analytical work, and process discussions with relevant teams. The case-study approach is appropriate because the research examines how specific operational activities are managed within a real organisational environment. It does not involve experimental manipulation or claim statistical generalisation to other companies.

    3. Data Sources and Sampling

      The study used the following categories of information:

      TABLE I. Data Sources and purpose

      Data source

      Purpose

      SAP material and BOM information

      Understanding parent-child structures and component

      requirements

      Inventory and warehouse data

      Analysing available quantities and material distribution

      BOS installation data

      Identifying combinations and performing frequency analysis

      Open PR information

      Reviewing old open requisitions and bulk-processing opportunities

      Excel worksheets

      Preparing, formatting, and validating data

      SQL and Python outputs

      Supporting data processing and analytical calculations

      Power BI dashboards

      Presenting inventory and kit- planning information

      Process discussions

      Understanding operational requirements and validation needs

      Only information available and permitted for use during the internship was considered. The study does not assume that every dataset was complete or that all proposed changes were implemented in the live production environment.

    4. Analytical Procedure

      The general procedure followed these stages: (i) understanding the operational requirement and existing process;

      (ii) collecting the relevant material, BOM, inventory, or transaction data; (iii) cleaning and validating the available information; (iv) applying the appropriate analytical method; (v) developing or refining the relevant output, calculation, or dashboard; (vi) reviewing the results with the applicable business or technical stakeholders; and (vii) identifying improvement opportunities and recommending further validation.

    5. Qualitative Scoring

      The study used BOM validation to review parent-child relationships and component quantities, frequency analysis to count the occurrence of different BOS combinations, Pareto analysis to rank combinations by frequency and calculate cumulative contribution, component-level shortage analysis to compare required material quantities with available stock, kit feasibility analysis to determine the maximum possible kit quantity based on the limiting component, and dashboard analysis to present warehouse-wise stock, requirements, shortages, and remaining quantities.

      1. Component Shortage Calculation

        Shortage (i) = Maximum of (0, Required Quantity Available Quantity)

      2. Theoretical Maximum Kit Capacity

        Maximum Kit Capacity = Minimum of (Available Quantity of Each Component ÷ Quantity Required per Kit), rounded down to the nearest whole number.

        Where:

        • Shortage (i) = Shortage of component i

        • a(i) = Available quantity of component i

        • b(i) = Quantity of component i required per kit

        • K(max) = Maximum number of complete kits that can be assembled

    These calculations help determine material shortages and the maximum number of complete kits that can be produced using available inventory. The component with the lowest production capacity determines the overall kit production limit.

    This calculation assumes that the stock quantities are available for allocation, the BOM is valid, and the relevant materials can be used together. Actual planning must also consider stock reservations, warehouse restrictions, quality status, and other applicable business rules.

  4. EXISTING PROCUREMENT PROCESS

    1. Kit GRN and Child Material Stock Update

      The first project focused on the proposed automatic stock update of child materials during TATA/PMSGY kit GRN processing. The work involved understanding the requirement, preparing parent and child material masters, maintaining BOM structures in SAP Quality Server, updating component prices, and preparing a consolidated BOM and pricing sheet for testing.

      The process required coordination with the SAP and Accounts teams to verify the material structure and the expected invntory and accounting impact. The purpose was to establish a reliable relationship between the parent kit and its components.

      The key process dependency was the accuracy of the material master and BOM information. Any missing component, incorrect quantity, or inconsistent material data could affect the expected result. Accordingly, BOM validation and controlled testing were important parts of the work.

    2. BOS Kit Standardisation for SGY Solar Projects

      The second project examined the variations in BOS requirements across SGY solar installations. The configurations depended on several technical parameters, including cable types and lengths, earthing, conduit, lightning arrestors, structure types, and panel configurations.

      The work involved mapping the combinations found in the available installation data and analysing their frequency using

      SQL. Power BI was used to present the analytical results and support the review of recurring combinations.

      The intended outcome was to identify frequently used configurations that could be considered for standardisation, while retaining the flexibility needed for project-specific requirements.

    3. Old Open Purchase Requisitions

      The third project involved reviewing more than 400 old open Purchase Requisitions associated with Plant 2001 (RMWH), covering requisitions dated up to 31 March 2026.

      The work included understanding the existing manual deletion process, identifying difficulties in processing a large list, and evaluating SAP MASS as a possible bulk-processing approach. Access and implementation requirements were discussed with the SAP team.

      The analysis highlighted the importance of reviewing PR status and line-item details before bulk processing. Requisitions with mismatched line-item numbers or other exceptions may require separate handling. Bulk deletion should only be performed after appropriate approval and validation.

    4. PMSGY Kit Availability and Planning

    The fourth project focused on developing a Power BI dashboard to support warehouse-wise inventory analysis and kit availability planning.

    The analytical process linked available inventory with the component quantities specified in each kit's BOM. It was designed to identify whether stock was sufficient for a planned kit quantity, determine component-level shortages, estimate the number of kits that could be prepared, and calculate the remaining stock after planned consumption.

    Python supported data preparation and calculations, while Power BI provided the visual reporting layer. The dashboard approach was intended to make material constraints easier to identify before planning or dispatch decisions were made.

  5. DATA ANALYSIS AND RESULTS

    1. Parent-Child BOM and Stock Update Analysis

      The kit GRN project demonstrated the operational importance of maintaining a correct parent-child relationship in SAP. The parent material represents the kit, while the child materials represent its individual components.

      The project work included preparation of the material structure, BOM maintenance, component pricing updates, and coordination for testing. These activities established the information required to validate the proposed stock-update process.

      The principal finding is that automation depends on correct master data and a validated process. Automating an incorrect BOM structure could reproduce errors more quickly rather than eliminate them. The process should therefore be tested using

      representative scenarios and reconciled against expected inventory and accounting results.

    2. BOS Combination and Pareto Analysis

      The BOS project used installation data to examine recurring combinations of components. SQL supported the identification and grouping of combinations, while Pareto analysis provided a method for ranking them by frequency.

      For each combination (j), the frequency contribution can be expressed as:

      Percentage Contribution (Pj) = (Frequency of Combination j ÷ Total Frequency of All Combinations) × 100

      Where:

      • Pj = Percentage contribution of combination j

      • fj = Number of occurrences of combination j

      • Total Frequency = Sum of occurrences of all combinations

        This formula calculates the percentage contribution of each combination to the total occurrences. It helps identify the most frequently occurring combinations and understand their relative importance in the overall analysis.

        The cumulative percentage is calculated by adding the percentage contributions in descending order of frequency. This helps determine which combinations account for the largest share of recorded installations.

        The analysis provides a basis for deciding which combinations deserve priority in standardisation. However, the exact coverage percentage and number of combinations required to reach a particular coverage target should be reported only after validating the final dataset.

    3. Old Open PR Analysis

      The PR project began with a list containing more than 400 old open requisitions. The review focused on the existing manual closure approach and the feasibility of bulk processing through SAP MASS.

      The analysis indicated that large-scale PR review can involve significant checking when requisitions differ in status or line- item structure. A suitable bulk-processing method could reduce repetitive effort, but only if the selected records are verified and the process is authorised.The work therefore identified a potential process-improvement opportunity rather than claiming that all listed PRs were deleted. The final number successfully closed and the associated time savings should be established from the SAP team's closure report, where available.

    4. . Kit Availability and Shortage Analysis

      The PMSGY dashboard applied BOM-based calculations to compare kit requirements with available stock.

      1. Required Quantity Calculation

        Required Quantity (i) = Planned Number of Kits × BOM Quantity per Kit (i)

      2. Remaining Stock Calculation

        Remaining Stock (i) = Available Stock (i) Planned Consumption (i)

        Where:

        • Required Quantity (i) = Total quantity of component i needed for production.

        • Planned Kits = Number of kits planned for production.

        • BOM Quantity per Kit (i) = Quantity of component i required to produce one kit.

        • Available Stock (i) = Current inventory of component i.

        • Planned Consumption (i) = Total quantity of component i expected to be consumed during production.

        • Remaining Stock (i) = Inventory balance after planned production.

      These calculations help determine material requirements and estimate remaining inventory after planned production. They support accurate material planning, inventory control, and timely procurement decisions to prevent shortages and ensure uninterrupted production.

      A negative result in the second calculation indicates a potential shortage if planned consumption exceeds available stock. For reporting purposes, shortages can be displayed separately as positive quantities, while remaining stock can be shown only after checking whether the proposed plan is feasible.

      The analysis also established that the kit quantity is constrained by the component with the lowest available-to-required ratio. A warehouse ma hold a large total inventory but still be unable to complete the planned number of kits if one critical component is unavailable.

      Figure 1 Combination-Wise Inventory Shortage Dashboard

      Figure 2 BOS Kit Priority Allocation Dashboard

      Figure 3 Structure Kit Priority Allocation Dashboard

    5. Consolidated Results

    Table II. Summary of Project Findings

    Project

    Main analytical focus

    Finding or contribution

    Kit GRN and child-material stock update

    Parent-child BOM structure and stock-update validation

    Highlighted the need for accurate BOMs, pricing,

    and cross- functional testing

    BOS kit

    standardisation

    Combination frequency and Pareto analysis

    Provided a

    method for prioritising recurring BOS

    configurations

    Old open PR analysis

    Review of more than 400 old open PRs

    Identified an

    opportunity to assess suitable

    bulk-processing methods

    PMSGY kit availability dashboard

    BOM

    consumption, stock availability, and shortages

    Established a structured approach to kit feasibility and

    component-level stock visibility

    The results are primarily process and analytical findings. Numerical improvements in processing time, inventory reduction, kit fulfilment, or cost savings cannot be concluded without validated before-and-after measurements.

    Table III. Kit Availability Calculation Framework

    Measure

    Calculation

    Interpretation

    Required

    component quantity

    Planned kits × BOM quantity per kit

    Total component requirement

    Component shortage

    Maximum of zero and (required

    quantity available quantity)

    Quantity of

    material not available

    Theoretical kit capacity

    Minimum across components of floor (available quantity

    ÷ BOM quantity per kit)

    Maximum kits possible based on component stock

    Planned consumption

    Feasible planned kits × BOM

    quantity per kit

    Stock expected to be consumed

    Remaining stock

    Available stock

    planned consumption

    Stock left after feasible allocation

    Kit fulfilment percentage

    Feasible kits ÷ required kits × 100

    Percentage of planned kit demand that can be met

  6. DISCUSSION

    The four projects demonstrate that inventory optimisation is not limited to reducing stock quantities. It also requires accurate information about what materials exist, how they are grouped into kits, where they are stored, and how they will be consumed.

    The first project showed the importance of linking the parent kit with its components through a validated BOM. The second demonstrated how historical combination data can support standardisation decisions. The third highlighted the need for systematic review of old procurement requirements. The fourth

    connected BOM requirements with available stock to support kit-level planning.

    A common theme across these projects is data quality. Material codes, BOM quantities, units of measurement, stock quantities, and transaction statuses must be accurate before the analytical output can be relied upon. Poor data quality can lead to incorrect shortage calculations, misleading Pareto results, or inappropriate bulk-processing decisions.

    The study also illustrates the complementary roles of ERP and business intelligence. SAP supports structured material and transaction management, while SQL and Python support analysis and transformation of data. Power BI presents the resulting information in a format that is easier to review. The effectiveness of this arrangement depends on reliable data refreshes, consistent calculation logic, and clear ownership of corrective actions.

    The managerial implication is that organisations should prioritise data validation and process ownership alongside dashboard development. A visual report alone does not solve inventory problems; it must lead to timely investigation, allocation, replenishment, or process correction.

  7. PROPOSED IMPROVEMENT FRAMEWORK

    1. BOM Validation and Controlled Testing

      A standard validation checklist should be used before a new parent-child BOM structure is used in an operational process. The checklist should cover component codes, descriptions, units of measurement, quantities, validity, and pricing where relevant.

      The proposed child-material stock update should be verified in a controlled environment. Expected and actual stock changes should be reconciled, and accounting implications should be reviewed by the appropriate teams before production implementation.

    2. BOS Standardisation

    Frequently occurring BOS combinations should be reviewed for possible standardisation. A combination should not be standardised solely because it occurs frequently; engineering requirements, customer specifications, and project constraints must also be considered.

    A periodic review of the frequency distribution can identify changes in demand patterns and new opportunities for standardisation.

    C Implementation Roadmap

    Table IV. Proposed Implementation Roadmap

    Phase

    Suggested activities

    Expected output

    Phase I: Validation

    Review material master, BOM, inventory, and PR

    data

    Reliable baseline data

    Phase II: Standardisation

    Rank BOS combinations and review high- frequency

    configurations

    Prioritised standardisation candidates

    Phase III: Controlled testing

    Validate kit GRN stock-update

    scenarios and bulk PR procedures

    Documented test and approval results

    Phase IV: Dashboard refinement

    Verify shortage logic, kit

    feasibility, and warehouse views

    Consistent planning dashboard

    Phase V: Monitoring

    Review performance measures and

    exceptions periodically

    Evidence for further improvement

    The roadmap is a proposal. Implementation timelines and resource requirements should be confirmed with the relevant teams.

  8. MANAGERIAL IMPLICATIONS

    preparation, or testing rather than verified full-scale implementation.

    The paper does not include validated before-and-after measurements for inventory reduction, processing time, kit fulfilment improvement, or cost savings. The exact BOS coverage percentages and final PR closure results should be incorporated only after the corresponding datasets and reports have been verified.

    The analysis may also be affected by incomplete records, changes in BOM structures, stock reservations, warehouse- specific restrictions, and differences between planned and actual requirements. Confidential organisational data further limits the level of detail that can be disclosed.

    Future research could examine the impact of the dashboard over several planning cycles, compareplanned kit quantities with actual kit preparation, measure the time required for PR processing before and after a validated change, and assess whether BOS standardisation reduces unnecessary material variation.

    Further work could also evaluate automated SAP-to-Power BI data integration, subject to technical feasibility and company approval. Such research would help establish whether the proposed framework produces measurable operational improvements.

    The study offers several implications for supply chain and production planning.

    First, inventory planning should focus on material-level constraints rather than relying exclusively on total warehouse stock.

    Second, BOM accuracy should be treated as a prerequisite for dependable kit calculations. Third, historical project data can support standardisation, but engineering suitability must remain part of the decision.

    Fourth, procurement housekeeping should be governed by clear review and approval procedures. Finally, dashboards should be integrated into routine planning reviews, with assigned responsibility for resolving the exceptions they identify.

    Together, these practices can establish a more structured process for converting operational data into planning actions. Their actual impact should be evaluated through a defined measurement period rather than assumed from the existence of a dashboard or automation proposal.

  9. LIMITATIONS AND FUTURE RESEARCH

    The study has several limitations. It is based on a bounded internship period and the operational information available during that period. Some activities involved analysis,

  10. CONCLUSION

This case study examined how SAP-based material management and business analytics can support inventory optimisation and kit availability planning at Ecozen Solutions Pvt. Ltd., Pune.

The four projects addressed connected operational needs: parent-child BOM management for kit GRN processing, BOS combination standardisation, review of old open Purchase Requisitions, and development of a PMSGY kit availability dashboard.

The study highlighted the importance of accurate master data, validated BOM structures, component-level inventory analysis, systematic review of old procurement requirements, and clear presentation of planning information. SQL, Python, Excel, and Power BI provided complementary capabilities for preparing, analysing, and presenting operational data.

The central conclusion is that inventory optimisation requires a combination of reliable ERP information, appropriate analytical methods, defined business rules, and consistent follow-up. The proposed framework provides a basis for further operational validation and continuous improvement. Quantified benefits should be established through verified baseline and post- implementation measurements.

REFERENCES

  1. Ecozen Solutions Pvt. Ltd., About Ecozen Solutions, Official Company Website. Available: https://www.ecozensolutions.com/about-us

  2. SAP, Materials Requirement Planning, SAP Help Portal. Available: https://help.sap.com/docs/SAP_S4HANA_ON- PREMISE/9905622a5c1f49ba84e9076fc83a9c2c/20e6e95754d1bc12e10 000000a4450e5.html

  3. Microsoft, Inventory Visibility Power BI Dashboard, Microsoft Learn. Available: https://learn.microsoft.com/en-us/dynamics365/supply- chain/inventory/inventory-visibility-dashboard

  4. D. H. Nabil, M. H. Rahman, A. H. Chowdhury, and B. C. Menezes, Managing supply chain performance using a real time Microsoft Power BI dashboard by action design research (ADR) method, Cogent Engineering, 2023. Available: https://doi.org/10.1080/23311916.2023.2257924

  5. SAP, BOM Categories, SAP Help Portal. Available: https://help.sap.com/docs/SAP_S4HANA_ON- PREMISE/18ee18df146f46e9a7738186eebceaa7/a309b953495bb44ce10 000000a174cb4.html

  6. S. Chopra, Supply Chain Management: Strategy, Planning, and Operation. Pearson.

  7. R. K. Yin, Case Study Research and Applications: Design and Methods. SAGE Publications.