Analyzing Mint Classics Inventory with MySQL Workbench

Analyzing Mint Classics Inventory with MySQL Workbench

Data Analysis
Business Strategy
Parent item
May 12, 2024
Rachmat Ramadhan H


As I embarked on a journey to hone my data analysis skills, I stumbled upon a fascinating project titled “Analyze Data in a Model Car Database with MySQL Workbench,” offered by the Coursera Project Network. This wasn’t just any project; it was a voyage into the heart of a fictional company known as Mint Classics, where I assumed the role of a novice data analyst tasked with a mission that could alter the company’s operational landscape.

The Challenge at Hand 

Mint Classics, a purveyor of classic model cars, faced a pivotal decision: the potential closure of one of their storage facilities. The company’s leadership sought data-driven insights to reorganize or reduce inventory while maintaining their commitment to customer service—ensuring products shipped within 24 hours of an order.

Demonstrated Skills

To navigate this challenge, I armed myself with SQL, wielding it to extract and analyze data from the Mint Classics relational database. My quest was not just about numbers and queries; it was about understanding the very fabric of Mint Classics’ business processes to offer solutions that resonated with their goals.
Throughout this project, I demonstrated the following skills:
  1. Data Analysis: The art of conversing with data through SQL to uncover the stories it holds.
  1. Business Understanding: A deep dive into the database structure and business operations of Mint Classics to ensure my analyses were grounded in reality.
  1. 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

To start, I'll use the "Import from Self-Contained File" option in the Data Import tool of MySQL Workbench. This allows me to create the Mint Classics database on my platform. Here's how it works: I download an SQL file containing the script needed to set up and fill the database. Then, I import this script into MySQL Workbench. Once that's done, I'll double-check to ensure the database was imported correctly.
notion image
notion image

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

Next, I'll dive into understanding the structure of the Mint Classics database and how data is organized in each table. To help with this, I'll use an Entity-Relationship Diagram (EER) to visualize how the tables relate to each other. Then, I'll explore the contents of each table to get a better grasp of the business data.
notion image

Task 3 — Investigating Business Challenges and Identifying Relevant Tables

Now, let's tackle the main challenge faced by Mint Classics: the decision to close one of its storage facilities. I'll pinpoint the tables that are most relevant to this issue and use SQL queries to gather the necessary data.
Here are the questions I'll be answering:
1. Where are items stored and if they were rearranged, could a warehouse be eliminated?
Analyzing Storage Locations
To find out where items are stored and if they could be rearranged to potentially eliminate a warehouse, we can use the following query:
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;
notion image
Warehouse b has the highest number of products, suggesting it is the most utilized. Warehouses a, c, and d have similar product counts, indicating potential for consolidation.
2. How are inventory numbers related to sales figures? Do the inventory counts seem appropriate for each item?
Relating Inventory to Sales Figures 
To understand how inventory numbers relate to sales figures, we can compare the quantity in stock with the sales data. Here’s a revised query:
SELECT warehouseCode, productLine, COUNT(productCode) AS product_count FROM Products GROUP BY warehouseCode, productLine ORDER BY warehouseCode, product_count DESC;
notion image
Here’s what we can infer from the data:
  • 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 BusesShips, 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?
Sales and Inventory Correlation
Look at the sales data in conjunction with inventory to identify products that have high stock but low sales.
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;
notion image
our Sales and Inventory Correlation analysis provide a comprehensive look at the stock levels and sales volumes for products across different warehouses. Here are some key insights and recommendations based on the data:
  • 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.
Identifying Slow-Moving Items
To identify slow-moving items, we first need to establish a benchmark for average sales. This benchmark will serve as a threshold to determine which products are moving slower than expected.
SELECT AVG(totalSold) AS averageSold FROM (SELECT SUM(quantityOrdered) AS totalSold FROM OrderDetails GROUP BY productCode) AS subquery;
notion image
After analyzing the sales data, we’ve calculated an average sales figure of 968.0367 units per product. Based on this average, we’ve set a threshold of 850 units. Products with total sales falling below this threshold are considered slow-moving. Such products, having sales figures under 850, may not be performing as well as other items in our inventory. This insight provides a solid foundation for initiating inventory reduction strategies.
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
notion image


Actionable Recommendations for Inventory Management:
  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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.


The analysis provided actionable insights into the inventory and sales performance at Mint Classics Company. Based on the findings, strategic decisions can be made to optimize inventory levels, potentially close a storage facility, and improve overall operational efficiency.
This report reflects my personal analysis and findings, including a step-by-step breakdown of the SQL queries used, the business questions they address, and the recommendations based on the results obtained. If you need any further details or adjustments, please let me know.