Inventory Analytics & Breakage Trends – Beverage Supply Chain

Project Title: Inventory Insights & Breakage Trends in Beverage Supply Chain Course Project – SCH-MGMT 663: Supply Chain Analytics, UMass Amherst

Overview: This project focuses on inventory optimization and breakage trend analysis for Alcohol Beverage Services (ABS) using real supply chain data. The objective was to explore patterns in breakage, identify overstock risks, evaluate pricing and discount trends, and assess the feasibility of merging inventory and incident datasets for deeper business insights.

Objectives: • Analyze inventory and breakage patterns to identify cost drivers and operational gaps • Explore seasonal breakage trends, high-discount items, and high-priced SKUs • Identify non-beverage product expansion opportunities • Evaluate SKU overlap between datasets using Product Code matching • Deliver actionable insights through Excel-driven dashboards and visualizations

Key Findings: • Coors Light, Modelo, Stella Artois led in breakage counts — potential packaging or handling issues • Breakage volumes peaked during COVID-19 (2020–2022), dropped in 2023 • Vodka topped inventory by volume; Patron Silver was highest in price • Largest discount: Woodford Reserve at $183 off retail • Non-beverage items (e.g., t-shirts) offer revenue diversification potential • Datasets were successfully merged via Product Code, enabling unified reporting

Tools & Techniques: • Microsoft Excel: Data cleaning, transformation, pivot charts, dashboards • Power Query: Merge & join logic using Product Code • Slicers & Conditional Formatting: Enhanced interactivity • Custom KPIs: Breakage frequency, discount percentages, SKU classification

Files Included: Project_3_Report.pdf: Final written report Project_3_Anand_Gupta.xlsx: Final Excel workbook with merged datasets & dashboard ABS_Store_Inventory_and_Sale_Items.xlsx: Inventory and pricing dataset Alcohol_Beverage_Services_Breakage_Inventory.xlsx: Breakage data across years

Real-World Use Cases: Breakage cost forecasting and reduction, Inventory turnover optimization, ERP integration for SKU-level analysis, Dashboards for executive supply chain reporting, Product assortment strategy & SKU rationalization

Author: Anand Gupta MS in Business Analytics – University of Massachusetts Amherst