Categories: Data Analytics / Practice Datasets
Tags:

Inventory Management Dashboard

This project delivers an Inventory Management solution using Power BI, designed to assist Warehouse and In-Plant Inventory Managers in efficiently managing inventory levels while ensuring optimal service levels.

Tools Used:

  • Power BI Software
  • Excel

Dataset and project was found on this github url : https://github.com/GoodbyeKittyy/Inventory-Management-Dashboard/ , All credit goes to this user for creating these wonderful DAX Columns and Measures.

Key Components of the Inventory Analysis Dashboard:

  1. ABC Classification: Categorizes inventory based on importance and value.
  2. XYZ Classification: Groups inventory by demand variability.
  3. Inventory Turnover Ratio: Measures how efficiently inventory is utilized.
  4. Safety Stock Levels: Maintains buffer stock to avoid stockouts.
  5. Reorder Level Estimation: Calculates reorder and safety stock levels.
  6. Average Weekly Demand: Provides insights into demand patterns.
  7. Stock Status: Displays current inventory status.
  8. Forecasting: Predicts inventory needs for upcoming periods.

Visualizations Used:

  • Stack Area Chart: Displays averages and cumulative data for ABC classification.
  • Cards, Gauges, and Slicers: Showcase requirements for specific areas.
  • Tables, Matrices, and Line Charts: Present distribution trends, stock reports, and turnover ratios.
  • DAX Functions: Analyze data using advanced calculations.
  • Calculated Columns and Rows: Combine and manipulate data, such as date-based calculations.

This dashboard integrates analytical tools and visualizations to streamline inventory management processes, ensuring actionable insights and data-driven decisions.

Explanation of : Sales Amount = LOOKUPVALUE(……..)

This DAX formula calculates the Sales Amount for each item by combining data from two tables: Stock and Weekly Demand Sheet. Here’s a breakdown of the formula:

Formula:

Sales Amount = LOOKUPVALUE(Stock[Unit Price], Stock[SKU ID], 'Weekly Demand Sheet'[SKU ID]) * 'Weekly Demand Sheet'[Weeks Demand]

Explanation:

  1. LOOKUPVALUE(Stock[Unit Price], Stock[SKU ID], 'Weekly Demand Sheet'[SKU ID]):
    • Purpose: This part retrieves the Unit Price of an item from the Stock table.How It Works:
      • It looks into the Stock table’s Unit Price column.Matches the SKU ID from the Stock table with the corresponding SKU ID in the Weekly Demand Sheet.Returns the unit price for the matching SKU ID.
    Example:
    If the Stock table contains: 
SKU ID Unit Price
101 $10
102 $20
SKU ID Weeks Demand
101 5
102 3
  1. return $10 for SKU ID 101 and $20 for SKU ID 102.
  2. * 'Weekly Demand Sheet'[Weeks Demand]:
    • Purpose: Multiplies the retrieved Unit Price by the corresponding value in the Weeks Demand column from the Weekly Demand Sheet table.
    • Result: Provides the total sales amount for the given demand.
    Example Calculation:
    • For SKU ID 101:
      Unit Price = $10, Weeks Demand = 5
      Sales Amount = $10 * 5 = $50
    • For SKU ID 102:
      Unit Price = $20, Weeks Demand = 3
      Sales Amount = $20 * 3 = $60

Key Points:

  • Data Relationship: The formula assumes that Stock and Weekly Demand Sheet share a common identifier (SKU ID) to enable matching.
  • Dynamic Calculation: The result changes based on the values in the Weekly Demand Sheet and Stock tables.
  • Efficient Aggregation: Ensures that the correct Unit Price is matched with the corresponding demand to calculate the total sales.

This formula is useful for inventory or sales analysis where pricing and demand data are maintained in separate tables.