amzker / mint-classic-data-analysis

Data Analytics Project of coursera

Mint-Classic-Data-analysis

Data Analytics Project of coursera

Live Report

Abbreviations and custom metrics

metrics	Full form	Formula	Increased indicates	Decreased indicates
QP	Profit per Quantity	Profit/Sells	Profitable product	Less Profitable Product
SO	Orders Per Stock	Orders/stock	Low stock compared to demand	High stock compared to Demand

Recommendation

Can we afford to close one warehouse?

• General summary

Yes , you can , i have done exploratory data analysis on given dataset and here are my main finding:

1. We are heavily overstocked

2. We have 1 non moving asset ( Toyota supra , 7.6k units , 0 sold )

3. We also have lot of slow moving assets

   Click

Which warehouse to close?

Based on my analysis we can close “a” and “d” warehouse. Given that B and C has most storage closing them would be harmful in heavy demand. Also dataset has no information regarding warehouse location other than their name east , west, noth , south. I tried to locate warehouse based on customer orders and delivery time but given that dataset does not contain information regarding mode of transportation, i was not able to locate them. So here is few information regarding warehouses which will help you in making decision.

• Warehouse Near France & Germany should not be close, if warehouse is in USA east then it is best location given that we can delivery product fast to high demand country
• Warehouse near Ocean ( which i suspect is d) can be closed if other warehouse is also located near Ocean, due to inventory of D is filled with planes , ships & trains
• Delivery time to Japan will always be 4-7 days so trying to reduce that amount is unnecessary as it will impact other parts of bussiness

Here is list of ProductLine of each warehouse

Warehouse	ProductLine	Count
a	motorcycles	13
	Planes	12
b	Classic Cars	38
c	Vintage Cars	24
d	Trucks and buses	11
	Ships	9
	Trains	3

USE mnt2;
SELECT  p.productLine, COUNT(p.productCode) AS counts
FROM products AS p 
WHERE p.warehouseCode = "d"
GROUP BY p.productLine

Products in Each Warehouse: sheet

How to close warehouse?

I have adjusted Products in Stock , increased understocked products and decreased overstocked products setting SO near 0.8 for understock and 0.4 for overstock products. Here are steps