An advanced Excel-based inventory management system designed for electric vehicle (Ather) dealerships to track vehicle stock allocation, availability, damage tracking, aging analysis, pre-bookings, and charger compatibility. This system provides complete transparency for sales teams to manage inventory efficiently and ensure accurate vehicle-charger pairing.
This system solves critical inventory management challenges for EV dealerships: How to track available stock, allocations, damage inventory, aging periods, pre-bookings, and ensure proper charger allocation for each vehicle model.
The system helps dealerships and sales teams by:
- Real-Time Stock Visibility: View free stock by color and model (450 X)
- Allocation Tracking: Monitor which vehicles are allocated, held, or available
- Damage Stock Management: Transparent tracking of damaged inventory
- Aging Analysis: Track how long vehicles remain in inventory
- Pre-Booking Management: Link salespeople to customer pre-bookings
- Charger Compatibility: Ensure correct charger allocation per vehicle model
- Location Tracking: Know where each vehicle is located in the dealership
Access Ather Stock Allocation System
Click the link above to access and explore the live inventory system
View free stock by color and model, allocation status, and pending allocations at a glance
Detailed vehicle-level tracking including PO numbers, chassis numbers, charger allocation, and aging analysis
Charger allocation details with customer information and dealership assignments
- Free Stock Summary: Total available vehicles by color and model
- Allocation Status: Track allocated, held, and available inventory
- Color-wise Breakdown: Visual representation of stock by color (450 X models)
- Hold Reasons: Transparent tracking of why vehicles are on hold
- Damage Stock Visibility: Separate tracking of damaged inventory
Complete vehicle lifecycle tracking:
- PO/Invoice Details: Source invoice number for each vehicle
- Vehicle Information: Model number, AC number, specifications
- Charger Assignment: Charger number allocated to each vehicle
- Location Tracking: Physical location within dealership
- Allocation Date: When vehicle was allocated to customer
- Salesperson Assignment: Who is responsible for the sale
Track inventory holding periods:
- Order Date: When vehicle was ordered from manufacturer
- Arrival Date: When vehicle reached showroom
- Days Pending: How many days vehicle has been in inventory
- Aging Period: Time since arrival without allocation
- Follow-up Alerts: Identify long-pending stock requiring action
- Customer Details: Name and contact information
- Salesperson Assignment: Who booked the customer
- Preferred Model/Color: Customer preferences recorded
- Booking Status: Track from booking to allocation
- Easy Lookup: Quickly find if allocation has been completed
Critical for EV dealerships:
- Model-Specific Chargers: Different chargers for different Ather models
- Charger Compatibility Tracking: Ensure correct charger-vehicle pairing
- Charger Number Mapping: Link specific charger to vehicle AC number
- Allocation Verification: Prevent delivery without proper charger
- Charger Availability: Track charger inventory separately
- Project-wise View: Stock allocation by project/location
- PSI Analysis: Performance Service Index tracking
- Month-wise Tracking: Temporal analysis of allocations
- Consolidated Reports: All data merged for complete visibility
- Sales teams couldn't easily identify available stock by color and model
- No visibility into why vehicles were on hold
- Damage stock was not tracked transparently
- Long-pending inventory wasn't identified for follow-up
- Risk of delivering vehicles with wrong chargers (incompatible models)
- Pre-bookings were difficult to match with arriving stock
- No clear view of vehicle location within dealership
- Aging analysis required manual calculation
✅ Instant Stock Visibility - Color and model-wise free stock at a glance
✅ Transparent Hold Tracking - Clear reasons for held inventory
✅ Damage Stock Management - Separate tracking with full transparency
✅ Automated Aging Analysis - Days pending calculated automatically
✅ Charger Compatibility System - Prevents wrong charger allocation
✅ Pre-Booking Integration - Easy matching of bookings to stock
✅ Location Tracking - Know where each vehicle is physically located
✅ Comprehensive Reporting - All data consolidated in one system
Technology Stack:
- Microsoft Excel / Google Sheets (Advanced formulas)
- SUMIFS for color and model-wise stock counting
- VLOOKUP/XLOOKUP for charger-vehicle matching
- DATEDIF for aging period calculation
- Conditional formatting for visual stock status
- Data validation for consistent data entry
- PIVOT TABLES for multi-dimensional analysis
- Complex IF statements for allocation status logic
Data Structure:
- Allocation Report Sheet: Summary dashboard with stock totals
- Allocation Data Sheet: Detailed vehicle-level information
- Pre-Booking Sheet: Customer booking records
- Charger Allocation Sheet: Charger compatibility matrix
- Aging Analysis Sheet: Time-based inventory tracking
- Damage Stock Sheet: Separate damaged inventory tracking
Key Calculations:
Free Stock = Total Stock - (Allocated + Held + Damaged)
Aging Days = TODAY() - Arrival Date
Charger Match = VLOOKUP(Model, Charger_Table, Charger_Column)
Hold Status = IF(Hold_Reason<>"", "On Hold", "Available")
- Microsoft Excel 2016 or later (recommended: Microsoft 365)
- Understanding of electric vehicle models (Ather 450 X variants)
- Dealership PO/Invoice numbering system knowledge
- Access the system
- Open in Microsoft Excel or Google Sheets
- For Excel: File → Create a Copy → Download
- Review all sheets to understand data flow
Step 1: Record New Stock Arrival
- Navigate to "Allocation Data" sheet
- Enter PO/Invoice number from supplier
- Record vehicle details (AC number, model, color)
- Note arrival date at showroom
- Select physical location in dealership
Step 2: Allocate Charger
- Check "Charger Allocation" sheet for model compatibility
- Assign appropriate charger number to vehicle
- System validates charger-model compatibility
- Record charger number in allocation data
Step 3: Manage Pre-Bookings
- Enter customer pre-booking details
- Assign salesperson responsible
- Note preferred model and color
- When stock arrives, match to pre-booking
Step 4: Allocate Vehicle to Customer
- Update allocation status in "Allocation Data"
- Link to pre-booking if applicable
- Verify charger assignment
- Mark allocation date
Step 5: Monitor Aging Stock
- Review "Aging Analysis" regularly
- Identify vehicles pending >30 days
- Follow up on long-pending inventory
- Take action on slow-moving models/colors
Step 6: Track Damage Stock
- Record damaged vehicles separately
- Note damage details and status
- Update damage resolution progress
- Keep damage stock transparent
Step 7: View Reports
- Check "Allocation Report" for summary
- Use filters for specific models/colors
- Analyze month-wise and project-wise data
- Generate reports for management
Ather Stock Allocation System
├── Allocation Report (Summary dashboard)
│ ├── Free stock by color and model
│ ├── Held stock with reasons
│ └── Damage stock tracking
├── Allocation Data (Detailed records)
│ ├── PO/Invoice information
│ ├── Vehicle details (AC#, Model, Color)
│ ├── Charger allocation
│ ├── Location tracking
│ └── Salesperson assignment
├── Pre-Booking (Customer bookings)
│ ├── Customer information
│ ├── Salesperson assignment
│ └── Booking status
├── Charger Allocation (Compatibility matrix)
│ ├── Model-charger mapping
│ └── Charger inventory
├── Aging Analysis (Time tracking)
│ ├── Order and arrival dates
│ ├── Days pending calculation
│ └── Follow-up alerts
└── Damage Stock (Damaged inventory)
├── Damage details
└── Resolution status
- Total free stock by model and color
- Allocated vs. available inventory
- Held stock with reasons
- Damage stock transparency
- Location-wise stock distribution
- Vehicles pending >7 days
- Vehicles pending >30 days
- Vehicles pending >60 days (critical)
- Average aging period by model
- Slow-moving color/model combinations
- Daily allocation count
- Month-wise allocation trends
- Salesperson-wise allocations
- Project-wise allocation distribution
- Pre-booking fulfillment rate
- Charger allocation accuracy rate
- Pending charger assignments
- Charger inventory status
- Model-charger mismatch alerts
✅ Prevents Delivery Errors: Ensures correct charger-vehicle matching
✅ Improves Cash Flow: Identifies slow-moving inventory quickly
✅ Enhances Customer Service: Fast response on stock availability
✅ Reduces Holding Costs: Aging analysis drives faster action
✅ Complete Transparency: All stakeholders see same data
✅ Eliminates Manual Work: Automated calculations save hours
✅ Better Inventory Planning: Data-driven stock ordering decisions
✅ Increases Sales Efficiency: Quick pre-booking to allocation matching
This system can be adapted for:
- Different electric vehicle brands (Ola, Bajaj, TVS)
- Multiple dealership locations
- Additional vehicle variants
- Custom hold reasons and categories
- Different charger types and specifications
- Integration with DMS systems
- Automated email alerts for aging stock
- Mobile app for real-time updates
- Web-based dashboard (Python/Flask or React)
- Automated email alerts for aging stock >30 days
- Mobile app for sales team access
- Integration with Ather's dealer management system
- QR code scanning for vehicle tracking
- Real-time stock updates across multiple locations
- Customer portal for booking status checks
- Automated charger compatibility validation
- Power BI dashboard for executive reporting
- Predictive analytics for stock demand forecasting
Skills Demonstrated:
- Advanced Excel formula development (SUMIFS, VLOOKUP, DATEDIF)
- Inventory management system design
- Data analysis and business intelligence
- Complex data relationship mapping (charger-vehicle compatibility)
- Aging analysis and time-based calculations
- Multi-dimensional reporting (project, month, salesperson)
- Process automation and optimization
- Understanding of EV dealership operations
- Problem-solving for operational challenges
Available for:
- Custom inventory management systems
- Dealership management solutions
- Excel/Google Sheets automation
- Data analysis and visualization
- Dashboard development (Excel, Power BI, Tableau)
- Process optimization consulting
- System integration projects
- Mobile app development for inventory tracking
- Training and documentation
This project demonstrates:
- Industry-Specific Knowledge: Understanding of EV dealership operations
- Critical Thinking: Identified charger compatibility as key risk
- Attention to Detail: Multiple tracking dimensions (aging, location, damage)
- Business Impact: Prevents costly delivery errors and reduces holding costs
- User-Centric Design: Built for non-technical sales teams
- Scalable Solution: Can expand to multiple locations and vehicle brands
For freelance inquiries, customization requests, or questions:
- GitHub
- Email: [email protected]
This project is available under the MIT License - feel free to use and modify for your needs.
⚡ Built for Electric Vehicle Dealerships - Ensuring Perfect Vehicle-Charger Match Every Time
⭐ If you find this useful, please star this repository!
💬 Have questions? Open an issue, and I'll respond promptly.
💼 Available for inventory management and dealership automation projects!