📅 Published by Subham Ghadge  |  Category: Excel Data Architecture, Inventory Management, Showroom Operations  |  Free Download Excel Arrays EV Logistics Operations Automation

EV Dealership Stock Allocation & Inventory Tracker in Excel

Managing electric vehicle (EV) inventory is more detailed than tracking traditional gas vehicles. When a transport truck arrives at an EV dealership (for brands like Ather, Ola, or TVS), stock managers aren't just logging a vehicle chassis number—they also need to match the specific battery variant, software features, and the correct home charging unit for each customer.

If a showroom tracks inventory on whiteboards or in WhatsApp groups, stock mix-ups happen easily. A salesperson might promise a specific scooter color to a walk-in customer, only to discover later that the vehicle was already allocated to a bank-financed buyer the night before.

To solve this, I designed the Ather Stock Allocation System in Excel. It calculates live "Free Stock" automatically using COUNTIFS formulas, pairs charger hardware to specific vehicle chassis, tracks stock aging using DATEDIF, and quarantines damaged inventory cleanly.

Live Real-Time Inventory Allocation

Sales teams need to know exactly what vehicles are available on the showroom floor without having to call the stockyard every time. The main dashboard in this tracker provides a real-time view of unencumbered physical stock.

The system categorizes vehicles by model (e.g., Ather 450X vs 450 Plus) and exact manufacturer color names (Space Grey, Mint Green, True Red).

When a transport truck arrives, staff log the new shipment into the 'Allocation Data' tab with PO numbers, chassis numbers, and arrival dates. New vehicles are marked as "Available" by default.

When a customer places a booking deposit, the status changes to "Hold" (pending finance approval) or "Allocated" (ready for delivery). The moment that status changes, background COUNTIFS formulas deduct the vehicle from the main 'Free Stock' count automatically—preventing other sales staff from double-booking the same vehicle. This mirrors the data integrity safeguards used in our Monthly Accessories Sales Tracker.

The Main Allocation Dashboard. Displays available vehicle stock by color and model in a color-coded grid.

EV Charger Hardware Matching Matrix

One of the costliest logistics errors for an EV dealership is sending the wrong charging unit to a customer's home. EV manufacturers supply different chargers—portable units, wall dot chargers, or high-capacity fast chargers—depending on the vehicle trim.

If stockyard staff accidentally dispatch a fast charger to a customer whose vehicle trim only supports standard AC charging, the installation technician has to make a second trip to swap the hardware, adding unnecessary delivery costs.

This tracker prevents hardware mix-ups using a Charger Allocation Matrix. When a vehicle is added to the system, Excel requires a Charger Serial Number to be paired directly with the Chassis number. Data Validation dropdowns restrict which chargers can be paired with specific vehicle models, eliminating guesswork during delivery preparation.

The Hardware Matching Matrix. Pairs vehicle chassis numbers directly with compatible charger serial numbers.

Automated Stock Aging Analysis

Unsold vehicles sitting in a yard for long periods increase holding costs, insurance fees, and bank interest (floor plan financing). If a vehicle sits unsold for 60 days, cumulative holding costs eat into the profit margin.

To monitor unsold inventory, the workbook uses an automated Aging Analysis engine powered by the formula: =DATEDIF(Arrival_Date, TODAY(), "d").

This formula calculates the exact number of days a vehicle has been in stock, updating automatically every day. Conditional Formatting highlights older stock: vehicles in stock for over 30 days turn yellow, and vehicles reaching 60 days turn red. Managers can spot aging vehicles at a glance and offer targeted sales incentives to move older stock quickly.

Quarantine Protocols for Damaged Stock

Vehicles sometimes arrive with minor transit damage, such as paint scratches or software glitches. If a damaged vehicle gets assigned to a customer by mistake, it leads to delivery delays and complaints.

This tracker includes a built-in quarantine process. If staff spot transit damage upon delivery, they set the vehicle status to "Damaged." The system automatically removes the vehicle from the 'Free Stock' count and adds it to a separate 'Damage Stock' report.

This prevents sales staff from assigning damaged units while keeping a clear digital record for claiming transport insurance or requesting factory replacements from the manufacturer.

Managing Pre-Booking Waitlists (FIFO)

During festive rush periods like Diwali or Dhanteras, customers place pre-bookings weeks before shipments arrive. The tracker manages waitlists using First-In-First-Out (FIFO) chronological rules.

When a shipment of scooters arrives, managers cross-reference incoming colors against the Pre-Booking tab. The system helps allocate vehicles sequentially based on booking timestamps, ensuring fair and orderly customer deliveries during peak seasons.

Customizing the Template for Your Showroom

You can easily adjust this open-architecture Excel template to fit your dealership's setup:

• Multi-Brand Adaptation: If you sell Ola Electric, TVS iQube, or Chetak models alongside Ather, update the model names in the backend validation lists. The underlying formulas (DATEDIF and COUNTIFS) work the same way.
• Multi-Location Tracking: If your dealership has a main city showroom and a secondary yard, add a "Location" column to track whether a vehicle is at Branch A or Branch B.
• Custom Status Rules: You can add status categories like "Pending RTO Registration" or "Pending Finance Clearance" to match your operational workflow.

Connect For Custom Business Tools

If your multi-brand dealership needs custom inventory spreadsheets, automated sales tracking, or Power BI executive dashboards, feel free to reach out.