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:
- ABC Classification: Categorizes inventory based on importance and value.
- XYZ Classification: Groups inventory by demand variability.
- Inventory Turnover Ratio: Measures how efficiently inventory is utilized.
- Safety Stock Levels: Maintains buffer stock to avoid stockouts.
- Reorder Level Estimation: Calculates reorder and safety stock levels.
- Average Weekly Demand: Provides insights into demand patterns.
- Stock Status: Displays current inventory status.
- 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:
LOOKUPVALUE(Stock[Unit Price], Stock[SKU ID], 'Weekly Demand Sheet'[SKU ID])
:- Purpose: This part retrieves the
Unit Price
of an item from theStock
table.How It Works:- It looks into the
Stock
table’sUnit Price
column.Matches theSKU ID
from theStock
table with the correspondingSKU ID
in theWeekly Demand Sheet
.Returns the unit price for the matchingSKU ID
.
- It looks into the
If theStock
table contains:- Purpose: This part retrieves the
SKU ID Unit Price
101 $10
102 $20
SKU ID Weeks Demand
101 5
102 3
- return
$10
for SKU ID101
and$20
for SKU ID102
. * 'Weekly Demand Sheet'[Weeks Demand]
:- Purpose: Multiplies the retrieved
Unit Price
by the corresponding value in theWeeks Demand
column from theWeekly Demand Sheet
table. - Result: Provides the total sales amount for the given demand.
- 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
- Purpose: Multiplies the retrieved
Key Points:
- Data Relationship: The formula assumes that
Stock
andWeekly 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
andStock
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.