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:
- Join Tables (Order Details and Products):
- Operation:
sql
Copy code
SELECT*FROMorderdetails
FULLJOINproducts
ONorderdetails.productCode
=products.productCode;
- Purpose: To merge the
orderdetails
andproducts
tables using theproductCode
field. This allows us to analyze how sales performance (orders) is related to available inventory (product stock). - Aggregate Sales by Product:
- Operation:
sql
Copy code
SELECTproducts.productCode, products.quantityInStock,
SUM(orderdetails.quantityOrdered)
AStotalQuantitySold
FROMorderdetails
INNERJOINproducts
ONorderdetails.productCode
=products.productCode
GROUPBY products.productCode, products.quantityInStock;
- Purpose: To calculate the total quantity sold for each product by joining
orderdetails
andproducts
, allowing us to analyze the relationship between inventory and sales figures. - Calculate Turnover Ratio:
- Operation:
sql
Copy code
SELECTproductCode, quantityInStock, totalQuantitySold, (totalQuantitySold
/quantityInStock)
ASturnoverRatio
FROM (subquery);
- 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.
- Identify High and Low Turnover Products:
- 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.
- For Stockout Risk:
- Identify Non-Moving Products:
- Operation:
sql
Copy code
WHEREtotalQuantitySold
=0;
- Purpose: To find products that have not sold at all. These non-moving products can be considered for deeper discounting or discontinuation.
- Warehouse Capacity Utilization:
- Operation:
sql
Copy code
SELECTwarehouseCode,
SUM(quantityInStock)
AStotalQuantityInStock
FROMproducts
GROUPBYwarehouseCode;
- 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.
- Warehouse Efficiency:
- Operation:
sql
Copy code
HAVINGslowMovingPercentage
>1.0;
- Purpose: To calculate the percentage of slow-moving inventory in each warehouse. This helps identify which warehouses are holding too much
- inventory relative to their utilization, aiding in strategic decisions like consolidation.
Reasons for Closing Warehouse D:
- 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.
- 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.
- 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.