A professional, KPI-driven Excel dashboard providing real-time visibility into stock levels, valuation, and critical restocking needs.
(Executive view of inventory health, tracking total stock value, restock alerts, and category performance.)
Effective inventory management is the backbone of operational efficiency. This project simulates a real-world scenario where a business needs to move from manual tracking to automated insights.
The Inventory Optimization Dashboard empowers stakeholders to:
- Monitor Stock Health: Instantly see which items are "Low," "Out of Stock," or "Healthy."
- Prioritize Restocking: A dedicated flag system identifies the 19.7% of products requiring immediate attention.
- Visualize Value: Understand where capital is tied up (e.g., Electronics holding the highest stock value).
| Metric | Value | Business Context |
|---|---|---|
| Total Products | 1,000 |
Scope of inventory |
| Restock Needed | 197 Items |
Actionable target for procurement |
| Critical Stockouts | 3 Items |
Immediate lost revenue risk |
| Total Stock Value | $X,XXX |
Capital locked in inventory |
This project was built entirely in Microsoft Excel, demonstrating advanced data manipulation and visualization techniques without external BI tools.
- Data Integrity: Removed null values and standardized column formats.
- Logic Fields: Implemented
IFformulas to automate status tagging:=IF([@Quantity] < [@Restock Level], "Yes", "No")=IF([@Quantity]=0, "Out of Stock", IF([@Quantity]<[@Restock Level], "Low", "In Stock"))
- Aggregation: Built four distinct Pivot Tables to summarize data by Category, Status, and Month.
- Time Analysis: Grouped dates to show monthly stocking trends (identifying dips in May/July).
- Slicers: Added interactive filters for Category, Restock Status, and Inventory Level.
- Visual Hierarchy: Designed a clean grid layout with KPI cards at the top for immediate "at-a-glance" status reading.
⚠️ Restock Risk: Approximately 19.7% of the total inventory is below the safety threshold, requiring immediate procurement orders.- 💰 Capital Allocation: The Electronics category holds the highest stock value, suggesting it is the primary driver of inventory carrying costs.
- 📉 Seasonal Trends: Stock levels showed noticeable fluctuations, with dips in mid-year (May-July), potentially indicating higher demand periods or supply chain delays.
- Download: Clone the repository or download the
.xlsxfile.git clone https://github.com/Bheki0987/Inventory-Optimization-Dashboard.git
- Open: Launch
Inventory_Optimization_Dashboard.xlsxin Excel. - Interact:
- Click the "Restock Needed: Yes" slicer to filter the list to only critical items.
- Select a specific Category (e.g., Furniture) to see its specific performance.
Inventory_Optimization_Dashboard.xlsx- The fully functional dashboard file.Dashboard-Screenshot.png- Preview image.README.md- Documentation.
Bheki Mogola Aspiring Data Analyst | Excel • SQL • Python
📍 Location: South Africa 📧 Email: bhekimogola123@gmail.com 🔗 LinkedIn: Bheki Mogola
If you found this dashboard useful, please consider starring the repository! ⭐