Analyze Data in a Model Car Database with MySQL Workbench

Executive Summary of Analysis:

The objective of this analysis was to assess the relationship between inventory levels and sales performance to identify potential risks of stockouts and overstock situations, with recommendations to manage inventory efficiently.

Operations and Explanations:

1. Join Tables (Order Details and Products):
2. Operation:

sql

Copy code

SELECT*FROM orderdetails FULLJOIN products ON orderdetails.productCode = products.productCode;

1. Purpose: To merge the orderdetails and products tables using the productCode field. This allows us to analyze how sales performance (orders) is related to available inventory (product stock).
2. Aggregate Sales by Product:
3. Operation:

sql

Copy code

SELECT products.productCode, products.quantityInStock, SUM(orderdetails.quantityOrdered) AS totalQuantitySold

FROM orderdetails INNERJOIN products ON orderdetails.productCode = products.productCode

GROUPBY products.productCode, products.quantityInStock;

2. Purpose: To calculate the total quantity sold for each product by joining orderdetails and products, allowing us to analyze the relationship between inventory and sales figures.
3. Calculate Turnover Ratio:
4. Operation:

sql

Copy code

SELECT productCode, quantityInStock, totalQuantitySold, (totalQuantitySold / quantityInStock) AS turnoverRatio

FROM (subquery);

3. Purpose: To compute the turnover ratio, which is the ratio of products sold to the quantity in stock. This ratio helps identify how fast products are moving through inventory.
4. Identify High and Low Turnover Products:
   1. Operation (High Turnover):

sql

Copy code

WHERE (totalQuantitySold / quantityInStock) >10;

• Purpose: To find products that have a high turnover ratio (>10), which indicates a risk of stockouts as these products are selling quickly.
  • Operation (Low Turnover):

sql

Copy code

WHERE (totalQuantitySold / quantityInStock) <1;

• Purpose: To identify products with a low turnover ratio (<1), indicating overstock risk, meaning products are sitting in inventory without selling.
• Stock Management Actions:
  • For Stockout Risk:
    Products with high turnover require reordering to avoid running out of stock. Sales trends should also be monitored to adjust stock levels accordingly.
  • For Overstock Risk:
    Products with low turnover suggest the need for promotions or discounts to reduce excess inventory. Future reordering should be minimized to prevent overstocking.
• Identify Non-Moving Products:

6. Operation:

sql

Copy code

WHERE totalQuantitySold =0;

6. Purpose: To find products that have not sold at all. These non-moving products can be considered for deeper discounting or discontinuation.
7. Warehouse Capacity Utilization:
8. Operation:

sql

Copy code

SELECT warehouseCode, SUM(quantityInStock) AS totalQuantityInStock FROM products GROUPBY warehouseCode;

7. Purpose: To assess how much space is being utilized in each warehouse. This allows the business to understand which warehouses are underutilized and where consolidation or reorganization might be needed.
8. Warehouse Efficiency:
9. Operation:

sql

Copy code

HAVING slowMovingPercentage >1.0;

8. Purpose: To calculate the percentage of slow-moving inventory in each warehouse. This helps identify which warehouses are holding too much
9.  
10.  
11. inventory relative to their utilization, aiding in strategic decisions like consolidation.

    Reasons for Closing Warehouse D:

1. Low Inventory Levels: Warehouse D has the lowest overall stock levels compared to the other warehouses. This makes it a strong candidate for closure as its inventory can be consolidated into other warehouses without significantly disrupting operations.
2. Space Optimization: Warehouse C has the capacity to absorb the inventory from Warehouse D, as it is currently underutilized, operating at only 50% capacity. This makes it feasible to move the products from Warehouse D to Warehouse C without impacting customer service.
3. Product Type and Sales: The products stored in Warehouse D, such as trucks, buses, and ships, can be relocated without major logistical concerns since these product lines don’t have the highest demand compared to other warehouses.

Conclusion for Executive Summary:

In the analysis of Mint Classics’ inventory and warehouse data, Warehouse D is identified as the best candidate for closure. Its low inventory levels and the capacity available in Warehouse C make it possible to consolidate operations without negatively affecting customer service or inventory management. This move will allow the company to optimize space, reduce operational costs, and maintain service levels. Additionally, products with high turnover rates should be prioritized for restocking to prevent stockouts, while slow-moving products need promotional strategies to clear excess stock.

These decisions ensure that Mint Classics can maintain efficient operations while strategically downsizing its storage facilities.

Key Points:

• Warehouse D has the lowest stock levels, making it the least efficient and most suitable for closure.
• Warehouse C can absorb the inventory from Warehouse D without straining its capacity.
• Closing Warehouse D optimizes space and reduces operational costs without disrupting service to customers.