Inventory Management Dataset for Excel

• inventory_levels.product_id →references products.product_idmany to one: each Inventory levels row points to one Products row
• inventory_levels.warehouse_id →references warehouses.warehouse_idmany to one: each Inventory levels row points to one Warehouses row
• transactions.product_id →references products.product_idmany to one: each Transactions row points to one Products row
• transactions.warehouse_id →references warehouses.warehouse_idmany to one: each Transactions row points to one Warehouses row

1. Use Excel filters to find all products in the 'Electronics' category with a stock quantity below 50 in any warehouse.

   Filter the products table by category, then link to inventory_levels and filter by stock_quantity.

   tables: products, inventory_levels

2. Calculate the total value of inventory for each warehouse using Excel SUMIF or SUMIFS functions.

   Join products and inventory_levels, then use SUMIFS with warehouse_id and product_id to calculate total value per warehouse.

   tables: products, inventory_levels, warehouses

3. Create a pivot table in Excel to summarize the total quantity of incoming and outgoing transactions for each product per month.

   Extract month from transaction_date, group by product and month, summing quantities for IN and OUT transactions.

   tables: products, transactions

4. Determine the top 5 products by total stock value across all warehouses.

   Calculate stock value per product (unit_price * stock_quantity) and sum across all warehouses, then rank.

   tables: products, inventory_levels

Table names match the SQLite file and the SQL script.

SELECT p.product_name, w.warehouse_name, il.stock_quantity FROM inventory_levels il JOIN products p ON il.product_id = p.product_id JOIN warehouses w ON il.warehouse_id = w.warehouse_id WHERE il.stock_quantity < 50 LIMIT 20;

SELECT w.warehouse_name, SUM(il.stock_quantity * p.unit_price) AS total_value FROM inventory_levels il JOIN products p ON il.product_id = p.product_id JOIN warehouses w ON il.warehouse_id = w.warehouse_id GROUP BY w.warehouse_name ORDER BY total_value DESC LIMIT 20;

SELECT strftime('%Y-%m', t.transaction_date) AS transaction_month, p.product_name, t.transaction_type, SUM(t.quantity) AS total_quantity FROM transactions t JOIN products p ON t.product_id = p.product_id GROUP BY transaction_month, p.product_name, t.transaction_type ORDER BY transaction_month, p.product_name LIMIT 20;

SELECT p.product_name, SUM(il.stock_quantity * p.unit_price) AS total_stock_value FROM inventory_levels il JOIN products p ON il.product_id = p.product_id GROUP BY p.product_name ORDER BY total_stock_value DESC LIMIT 5;

Excel

Each table is provided as a separate sheet. Use XLOOKUP to join tables based on product_id or warehouse_id. Create PivotTables to summarize transaction data by month and product.

• CSV (zip)One CSV file per table, zipped
• Excel workbookOne worksheet per table
• SQLite databaseA ready-to-query database file with every table
• Parquet (zip)One Parquet file per table, zipped
• SQL scriptCREATE TABLE with primary and foreign keys, then INSERTs
• CSVA single CSV file
• JSONA single JSON file