Mint Classics Company, a retailer of classic model cars and other vehicles, is looking at closing one of their 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.
The dataset was provided by Coursera as part of the final business case. After exploring the dataset, I found that it was provided without mistakes. All SQL queries used during this project are shown below:
-- Adding a column 'warehouse_available':
ALTER TABLE warehouses ADD warehouse_available VARCHAR(100);
-- Calculating warehouse available in percentage:
UPDATE warehouses
SET warehouse_available = CONCAT((100 - warehousepctcap), '%');
-- Updating the column warehousepctcap in percentage:
SELECT
warehousecode,
warehousename,
CONCAT(warehousepctcap, '%') AS warehouse_full,
warehouse_available
FROM warehouses;
-- Creating the table warehouse capacity:
CREATE TABLE warehouse_capacity AS
SELECT
warehousecode,
warehousename,
CONCAT(warehousepctcap, '%') AS warehouse_full,
warehouse_available
FROM warehouses;
-- Creating the table offices location:
CREATE TABLE offices_location AS
SELECT city, country FROM mintclassics.offices;
-- Creating the table customers_location:
CREATE TABLE customers_location AS
SELECT customernumber, city, country FROM customer_location;
-- Creating the table warehouse_capacity_average:
CREATE TABLE warehouse_capacity_average AS
SELECT
AVG(REPLACE(warehouse_full, '%', '')) AS warehouse_full_average,
AVG(REPLACE(warehouse_available, '%', '')) AS warehouse_available_average
FROM mintclassics.warehouse_capacity;
-- Adding the column added value to the average_price table:
ALTER TABLE average_price ADD added_value INT;
-- Filling added values in the new column:
SET SQL_SAFE_UPDATES = 0;
UPDATE average_price
SET added_value = average_sell_price - average_buy_price;
-- Creating the table sales_per_item:
CREATE TABLE sales_per_item AS
SELECT
SUM(orderdetails.quantityOrdered * orderdetails.priceEach) AS sales,
products.productLine
FROM mintclassics.products
JOIN mintclassics.orderdetails
ON products.productCode = orderdetails.productCode
GROUP BY products.productLine;
-- Creating the table average_price:
CREATE TABLE average_price AS
SELECT
products.productLine,
AVG(products.buyPrice) AS average_buy_price,
AVG(orderdetails.priceEach) AS average_sell_price,
(AVG(orderdetails.priceEach) - AVG(products.buyPrice)) AS added_value
FROM mintclassics.products
JOIN mintclassics.orderdetails
ON products.productCode = orderdetails.productCode
GROUP BY products.productLine;
-- Creating the table stock_value:
CREATE TABLE stock_value AS
SELECT
SUM(products.quantityInStock * products.buyPrice) AS stock_value,
products.productLine
FROM mintclassics.products
GROUP BY products.productLine;
-- Creating the table sales and stock:
CREATE TABLE sales_and_sales AS
SELECT
products.productLine,
SUM(products.quantityInStock) AS stock,
SUM(orderdetails.quantityOrdered) AS sales
FROM mintclassics.products
JOIN mintclassics.orderdetails
ON products.productCode = orderdetails.productCode
GROUP BY products.productLine;
-- Creating the table warehouse capacity:
CREATE TABLE warehouse_capacity AS
SELECT
warehouses.warehouseCode,
warehouses.warehouseName,
CONCAT(warehouses.warehousePctCap, '%') AS warehouse_full,
CONCAT(100 - warehouses.warehousePctCap, '%') AS warehouse_available
FROM mintclassics.warehouses;
-- Creating the table warehouse capacity average:
CREATE TABLE warehouse_capacity_average AS
SELECT
AVG(warehouse_capacity.warehouse_full) AS warehouse_full_average,
AVG(warehouse_capacity.warehouse_available) AS warehouse_available_average
FROM mintclassics.warehouse_capacity;
For this project, I transformed the dataset to create tables that contain core data to optimize data export to Power BI. I created a relational model to connect visualizations and find interesting insights. As you can observe below, I created a relational dashboard that connects the company’s product line to sales and stock figures, providing us with a clear view of inventory and sales.
| Observations | Findings | Insights | Recommendations |
|---|---|---|---|
| Sales represent €9.6 million, while stock is €30.5 million. | There is a surplus stock situation. | The stock and sales levels are unbalanced. | The company should reduce production levels until inventory and sales reach a healthier balance. After this step, the company should produce just 50% over sales to keep a short and manageable surplus of stock. |
| Classic car products correspond to 40.13% of sales. On the other hand, sales from vintage cars, motorcycles and trucks & buses represent important items of this product range, as each category exceeds €1 million in sales. Finally, planes, ships and trains represent less than €1 million in sales. | Classic car items are the company's flagship products. | The classic car category is a key revenue generator and should be prioritized. | Focus marketing, promotions, and product development efforts on classic car and other high-performing categories. Consider reducing low-performing categories to concentrate resources on core products. |
| These collectible products create an important added value from the best-selling product to the lowest-performing item. | The product range is competitive. | Each item creates a high added value. | Maintaining the same product range. Every product generates between €23 and €43 added value per item sold. |
| We can observe an unbalanced relationship between stock and sales. | Inventory turnover is low, meaning stock stays longer in the warehouse. | The data suggests inefficient inventory management. | Improving inventory management by reducing excess stock. |
| 66% of the warehouse is used, while 34% is free. | No warehouse is using its full capacity. | A big portion of the warehouse (66%) is used while having a surplus of 250.74%. | The company could potentially operate with fewer warehouses. |
| On the KPI chart, the stock goal represents 150% of sales. With this level of stock, we can fully supply all the customer orders and keep a 50% surplus if it's needed. However, as stock is unbalanced with sales, the stock is 250% larger than needed. | Current stock level is much higher than the target level. The company holds 250.74% more stock than necessary. | Excess stock ties up working capital and increases storage and handling costs without adding proportional value to sales. | If stock volume is optimized to 150% of sales, this company could continue its activity with fewer warehouses according to its level of sales. |
In conclusion, to answer the question “Mint Classics Company, a retailer of classic model cars and other vehicles, is looking at closing one of their 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”. Based on my data analysis, this company could balance stock and sales by steadily reducing production levels. It may target a 50% surplus stock limit to avoid unmanageable stock inventories. Getting straight to the point, yes, the company can close a warehouse to continue its activity maintaining timely service to their customers.