Built a comprehensive inventory management system in Microsoft Excel to handle stock tracking, transaction logging, and data analysis across three warehouse locations.
What it does:
Tracks 10 SKUs across Bandung, Jakarta, and Bekasi with real-time stock quantity updates
Logs every Stock In/Out transaction with date, week number, and supplier info
Flags stock discrepancies between expected and actual physical count
Monitors reorder thresholds and discontinuation status per item
Visualizes stock movement through daily, weekly, and monthly pivot dashboards
Supports dynamic filtering by location and inventory ID
Formulas used:
SUMIFS: auto-calculates net stock per item by netting all Stock In and Stock Out transactions from StockTracker, no manual updates needed
WEEKNUM: converts transaction dates into week numbers to feed the weekly pivot breakdown
SUM aggregates total inventory value across all SKUs in real time
Cross-sheet references: VarianceTracker pulls live quantities from MasterInventory to detect discrepancies without duplicating data entry
Direct multiplication (Quantity × Price): keeps per-item inventory value in sync with every stock movement
Scope: 8 interconnected sheets · 10 SKUs · Total inventory value Rp 20,320,000 · 3 warehouse locations