Inventory Optimization

Smartphones, laptops, cameras, and audio gear are among the products offered by Data in Motion Tech, an electronics retailer that is expanding quickly. The business has operations all over the United States, both online and in physical stores. Data in Motion Tech is having trouble effectively managing its inventory levels due to the varied product lines and varying customer demands. Insufficient inventory results in lost sales and disgruntled customers, while excess inventory raises holding costs.

As a Data Analyst at Data in Motion Tech, it is your responsibility to assist the business in maximizing its inventory levels. You have access to a SQL database that the management team has created that contains previous sales information, inventory levels, and other pertinent metrics. Your goal in analyzing this data is to find trends, obstructions, and chances to enhance inventory control. You will pay particular attention to the following:

The SQL database contains the following tables:

1. products: Contains product details
   • product_id (Primary Key)
   • product_name
   • category
   • price
2. sales: Contains historical sales records
   • sale_id (Primary Key)
   • product_id (Foreign Key)
   • quantity_sold
   • sale_date
3. inventory: Contains current inventory levels
   • inventory_id (Primary Key)
   • product_id (Foreign Key)
   • current_stock
   • warehouse_location
4. advertising: Contains advertising spending data
   • ad_id (Primary Key)
   • product_id (Foreign Key)
   • ad_spend
   • ad_date

Now that you are familiar with the case study, let’s move on to the coding challenges.

Challenge Questions

1. What is the Monthly Sales Revenue for each Product? This metric is essential for understanding the sales performance of each product on a monthly basis.
2. What is the Advertising Cost per Sale for each Product? Understanding how much is being spent on advertising for each unit sold can help the business allocate its marketing resources more efficiently.
3. What is the Inventory Turnover Rate for each Product? Inventory turnover indicates how many times the inventory is sold and replaced over a specific period. A high turnover rate could indicate good sales, but could also mean that you are not keeping enough stock.
4. What is the Average Monthly Sales Quantity for each Product Category? Identifying the average monthly sales by product category can offer insights into which categories might need promotion or markdowns.
5. What is the Average Stock Level for each Warehouse Location? Knowing the average stock level by warehouse can help the company manage space and maybe reallocate resources.
6. What is the Monthly Growth Rate in Sales Revenue? A measure of the monthly growth rate can help the company understand its sales performance trajectory, which is crucial for strategic planning.
7. What is the Return on Advertising Spend (ROAS) for each Product? ROAS measures the effectiveness of advertising campaigns. A higher ROAS suggests a more effective advertising campaign.
8. What Percentage of Total Sales Does Each Product Category Contribute? This metric provides insights into the composition of sales, highlighting which categories are most valuable to the business.
9. What is the Monthly Revenue for the Business? Monthly revenue tracking allows for quick identification of high-performing and low-performing months, which is essential for strategic planning.
10. Which Product Categories Generate the Most Revenue? Understanding which categories are the most profitable can guide inventory and marketing decisions.