Analyzing Mint Classics Inventory with MySQL Workbench

Introduction

The Challenge at Hand 

Demonstrated Skills

1. Data Analysis: The art of conversing with data through SQL to uncover the stories it holds.

2. Business Understanding: A deep dive into the database structure and business operations of Mint Classics to ensure my analyses were grounded in reality.

3. Decision-Making: The synthesis of data into coherent recommendations, guiding the company towards informed choices.

The Tools of the Trade

• MySQL Workbench: My trusty sidekick for importing databases, running intricate SQL queries, and dissecting data.

• GitHub: The repository where my project artifacts and reports reside, a testament to my analytical journey. [Analyze-Data-in-a-Model-Car-Database-with-MySQL-Workbench]

Task 1 — Importing the Classic Car Model Database

Task 2 — Understanding the Mint Classics Database and How It Works

Task 3 — Investigating Business Challenges and Identifying Relevant Tables

1. Where are items stored and if they were rearranged, could a warehouse be eliminated?

SELECT w.warehouseCode, COUNT(p.productCode) AS product_count
FROM Products p
JOIN Warehouses w ON p.warehouseCode = w.warehouseCode
GROUP BY w.warehouseCode
ORDER BY product_count DESC;

2. How are inventory numbers related to sales figures? Do the inventory counts seem appropriate for each item?

SELECT warehouseCode, productLine, COUNT(productCode) AS product_count
FROM Products
GROUP BY warehouseCode, productLine
ORDER BY warehouseCode, product_count DESC;

• Warehouse a: Houses a diverse range of products, with Motorcycles and Planes being the most numerous.

• Warehouse b: Specializes in Classic Cars, which is the single largest category across all warehouses.

• Warehouse c: Exclusively stores Vintage Cars.

• Warehouse d: Contains a mix of Trucks and Buses, Ships, and a smaller number of Trains.

• Specialization: Warehouse b’s focus on Classic Cars suggests it’s a specialized facility, which might be harder to consolidate due to the volume and potential need for specialized storage conditions.

• Diversification: Warehouse a’s diversified inventory could offer more flexibility in consolidation, as these products might be easier to distribute to other warehouses.

• Exclusivity: Warehouse c, being dedicated to Vintage Cars, might be similar to Warehouse b in terms of specialization and potential challenges in consolidation.

• Variety: Warehouse d, while having a variety of product lines, has lower counts, which might make it a candidate for distributing its inventory to other warehouses.

3. Are we storing items that are not moving? Are any items candidates for being dropped from the product line?

SELECT p.warehouseCode, p.productCode, p.productName, p.quantityInStock, SUM(od.quantityOrdered) AS totalSold
FROM Products p
JOIN OrderDetails od ON p.productCode = od.productCode
GROUP BY p.warehouseCode, p.productCode
ORDER BY p.warehouseCode, totalSold;

• High Stock, Low Sales: Products like “1982 Ducati 996 R” in warehouse a with a high quantity in stock (9241) but relatively low total sales (906) might indicate overstocking.

• Balanced Inventory: Items with a closer ratio of quantity in stock to total sold, such as “1957 Ford Thunderbird” in warehouse b (3209 in stock, 767 sold), suggest a healthier inventory level.

• Potential Overstock: Warehouse b has a product with an exceptionally high quantity in stock compared to sales: “1992 Ferrari 360 Spider red” (8347 in stock, 1808 sold). This could be a sign of overstock that needs addressing.

SELECT AVG(totalSold) AS averageSold
FROM (SELECT SUM(quantityOrdered) AS totalSold
      FROM OrderDetails
      GROUP BY productCode) AS subquery;

SELECT p.productCode, p.productName, SUM(od.quantityOrdered) AS totalSold
FROM Products p
JOIN OrderDetails od ON p.productCode = od.productCode
GROUP BY p.productCode
HAVING totalSold < 850

Recommendations

1. Targeted Warehouse Consolidation:
• Warehouse b: Maintain as the primary distribution center due to its high product volume.
• Warehouse a: Evaluate the possibility of transferring the Motorcycles and Planes categories to Warehouse b or c to centralize operations.
• Warehouses c and d: Assess the feasibility of merging Vintage Cars, Trucks, Buses, Ships, and Trains into a single facility to streamline inventory.

2. Strategic Inventory Reduction:
• Overstocked Products: Initiate clearance sales for products exceeding the average stock level by more than 20%. Set quarterly reviews to adjust stock levels based on sales trends.
• Understocked Products: Increase stock levels by 15% for products consistently selling 10% above average inventory to meet demand and avoid stockouts.

3. Product Line Rationalization:
• Low-Performing Lines: Discontinue product lines with sales figures consistently below 50% of the average sales figure for two consecutive quarters.
• Promotion of Slow-Movers: Bundle slow-moving products with top-sellers as special offers to boost sales.

4. Sales Benchmarking and Monitoring:
• Average Sales Threshold: Establish a dynamic sales threshold, set at 12% below the rolling average sales figure, to identify slow-moving items.
• Regular Sales Analysis: Implement a monthly sales analysis protocol to quickly identify and address slow-moving items.

5. Inventory-Sales Correlation Enhancement:
• Data-Driven Restocking: Develop an automated restocking system that uses real-time sales data to maintain optimal inventory levels.
• Sales Forecasting: Utilize predictive analytics to forecast sales trends and adjust inventory procurement accordingly.

6. Continuous Improvement:
• Feedback Loop: Create a feedback mechanism to gather insights from sales and warehouse teams to continuously improve inventory management practices.
• Inventory Health Metrics: Track key performance indicators such as inventory turnover ratio and days on hand to measure the effectiveness of inventory strategies.

Conclusion