Stock On Hand

Best Buy: Work out how much of each product each warehouse is holding, and where stock needs rebalancing.

Assignment

Supply chain is planning the next quarter's replenishment and wants to know where each product actually sits across the four warehouses before placing any orders. The stock system does not hold a running balance, so they have shared the movement log for the first half of the year along with a table of the warehouses.

Deliverables

• The stock each warehouse is holding of each product at the end of June.
• Which products are running low, and which warehouse is holding more than it needs.
• Your recommendation on what to rebalance, and a short closing report for supply chain covering the reasoning and anything they should fix in the stock system.

Data Description

stock_movements.csv is the movement log for January to June:

• movement_id
• warehouse_id
• sku - the product code
• movement_date
• movement_type - what kind of movement it was
• quantity

warehouses.csv has one row per warehouse:

• warehouse_id
• warehouse_name
• opening_stock_date - the date the warehouse began holding stock

Practicalities

You can solve this in Python, SQL, or a mix of both — StrataScratch notebooks support both languages in the same notebook.

Assume this data comes directly from the stock system and hasn't been cleaned, so review it carefully before relying on the numbers. When something looks off, work out what it is and decide how to handle it; when a definition or a final decision is genuinely ambiguous, choose a reasonable option and explain why. There's often more than one valid way to compute a result, so explain the approach you take and the reasoning behind your choices.

What's assessed: your results, and how clearly you explain what you see in the data and justify the choices you make.