Piyaporn Puangprasert (Nan)

Date March 20, 2025

Time 4 hours

Project Scenario

Mint Classics Company, a retailer of classic model cars and other vehicles, is looking at closing one of its storage facilities.

To support a data-based business decision, they are looking for suggestions and recommendations for reorganizing or reducing inventory, while still maintaining timely service to their customers. For example, they would like to be able to ship a product to a customer within 24 hours of the order being placed.

1) Where are items stored and if they were rearranged, could a warehouse be eliminated?
2) How are inventory numbers related to sales figures? Do the inventory counts seem appropriate for each item?
3) Are we storing items that are not moving? Are any items candidates for being dropped from the product line?

The answers to questions like those should help you formulate suggestions and recommendations for reducing inventory to close one of the storage facilities.

Project Objectives

1. Explore products currently in inventory.
2. Determine important factors that may influence inventory reorganization/reduction.
3. Provide analytic insights and data-driven recommendations.

Skill demonstrated

1. MySQL
2. Business Analytics

Tools used

1. MySQL Workbench
2. PowerPoint
3. Excel

Database ER Diagram (craw’s foot)

Approach

Step 0:

• We need to clean the table that does not have “N/A” in the columns
• Make sure that the columns are not duplicates ( It shows some duplicates information of ‘orderNumber’ at ‘orderdetails’ and ‘orders’ table)

Step 1: we need to check which warehouse uses a few products:

SELECT warehouseCode, COUNT(productCode) AS total_products, SUM(quantityInStock) AS total_stock
FROM products
GROUP BY warehouseCode
ORDER BY total_stock DESC;

The outcome shows that warehouse d has fewer products and the number of products used

warehouseCode	total_products	total_stock
b	38	219183
a	25	131688
c	24	124880
d	23	79380

Step 2: We need to check in each warehouse how the profit is

SELECT 
    p.warehouseCode, 
    COUNT(p.productCode) AS total_products, 
    SUM(p.quantityInStock) AS total_stock, 
    SUM(od.quantityOrdered * od.priceEach) AS total_revenue,
    SUM(od.quantityOrdered * p.buyPrice) AS total_cost,
    SUM((od.quantityOrdered * od.priceEach) - (od.quantityOrdered * p.buyPrice)) AS total_profit
FROM products p
LEFT JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.warehouseCode
ORDER BY total_stock DESC;

The answer follows the first question that warehouse D makes less profits compare with other warehouse

warehouseCode	total_products	total_stock	total_revernue	total_cost	total_profit
b	38	219183	3853922.49	2327710.29	1526212.20
a	25	131688	2076063.66	1240847.65	835216.01
c	24	124880	1797559.63	1060291.30	737268.33
d	23	79380	1876644.83	1149461.12	727183.71

Step 3: we would like to track down how much profit in each product. Surprisingly! Warehouse C makes less profit than Warehouse D which means we better close Warehouse C instead of Warehouse D because we use less work but get more profit.

SELECT 
    p.warehouseCode, 
    COUNT(p.productCode) AS total_products, 
    SUM(p.quantityInStock) AS total_stock, 
    SUM(od.quantityOrdered * od.priceEach) AS total_revenue,
    SUM(od.quantityOrdered * p.buyPrice) AS total_cost,
    SUM((od.quantityOrdered * od.priceEach) - (od.quantityOrdered * p.buyPrice)) AS total_profit,
    (SUM((od.quantityOrdered * od.priceEach) - (od.quantityOrdered * p.buyPrice)) / COUNT(p.productCode)) AS profit_per_product
FROM products p
LEFT JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.warehouseCode
ORDER BY total_stock DESC;

The result shows

warehouseCode	total_products	total_stock	total_revernue	total_cost	total_profit	profilt_per_product
b	38	219183	3853922.49	2327710.29	1526212.20	1509.606528
a	25	131688	2076063.66	1240847.65	835216.01	1201.749655
c	24	124880	1797559.63	1060291.30	737268.33	1122.174018
d	23	79380	1876644.83	1149461.12	727183.71	1146.977461

Solution

1. Warehouse Optimization: Warehouse C is the least efficient, generating the lowest profit per product while taking up significant storage space. Closing this facility would improve operational efficiency.
2. Sales Seasonality: November is the peak sales month, indicating the need for early stock replenishment to maximize revenue and meet customer demand.
3. Product Performance Review: The 1985 Toyota Supra has zero sales, suggesting it may no longer be viable. A deeper investigation into pricing, marketing, or demand trends is necessary to determine whether to discontinue or reposition the product.

Summary

1. SQL Data Extraction & Exploration:
   • Queried inventory and sales data to understand product storage, sales performance, and warehouse efficiency.
   • Aggregated metrics such as total stock, total revenue, total cost, and total profit per warehouse to evaluate performance.
2. Profitability Analysis:
   • Calculated profit per product per warehouse to identify underperforming storage locations.
   • Assessed sales trends over time to determine peak sales periods.
3. Product Performance Assessment:
   • Identified slow-moving and non-selling products, such as the 1985 Toyota Supra, which had zero demand.

Business Impact

• Warehouse Optimization: Closing Warehouse C will reduce storage costs and improve overall efficiency, as it has the lowest profit per product.
• Inventory Strategy: November is the peak sales month, requiring strategic stock replenishment to maximize revenue opportunities.
• Product Line Improvements: Identifying non-selling products allows the company to optimize its product catalog and investigate potential marketing or pricing adjustments.