The Problem That Breaks Every Distributor's Operations
Picture this: a mid-sized FMCG distributor in Pune with 4 warehouses, 12 sales depots across Maharashtra, and 8 delivery vehicles on the road at any given time. Their operations manager starts every morning with a 45-minute ritual - calling each depot supervisor, collecting stock counts over WhatsApp, and manually updating a master Excel sheet.
By 10 AM, the data is already stale. A depot in Nashik sold 200 units of a fast-moving SKU. The Pune warehouse dispatched a replenishment truck at 9 AM. The Excel sheet shows neither. A sales rep in Aurangabad promises a customer 500 units for next-day delivery - units that don't actually exist at the nearest depot.
This is the multi-location inventory problem. It's not a process problem. It's an engineering problem. And solving it requires thinking carefully about data architecture, synchronisation, and the realities of Indian distribution networks - including unreliable internet connectivity at depots.
Why Excel Fails at Multi-Location Inventory
Excel is a single-user, single-location tool. When you try to use it for multi-location inventory, you're fighting against its fundamental design:
Concurrency: Excel has no concept of concurrent updates. When two depot managers update the same file simultaneously, one overwrites the other. You lose data.
No real-time sync: Even with shared drives or Google Sheets, there's no mechanism to reflect a stock movement at one location instantly at another. You're always working with stale data.
No transaction log: Excel doesn't record who changed what and when. When stock discrepancies appear - and they always do - you have no audit trail to investigate.
No business logic enforcement: Excel can't prevent a sales rep from booking stock that doesn't exist. It can't automatically trigger a replenishment order when stock falls below a threshold. It can't enforce FIFO (First In, First Out) for perishable goods.
No mobile access: Depot supervisors and delivery drivers need to update stock on their phones. Excel is not designed for this.
The Engineering Architecture for Multi-Location Inventory
Solving this properly requires a purpose-built system with the following components:
1. The Core Data Model
The foundation of any inventory system is its data model. Get this wrong and you'll be rewriting the system in 18 months.
Locations table: Every physical location where stock can exist - warehouses, depots, vehicles, even customer consignment locations - is a record in the locations table. Each location has a type (warehouse, depot, vehicle, transit), a parent location (for hierarchical reporting), and attributes like address and capacity.
SKU master: Every product variant is a unique SKU. The SKU master stores product name, category, unit of measure, barcode, reorder point, and reorder quantity. Critically, it stores whether the SKU is batch-tracked (for pharmaceuticals, food products) or serial-tracked (for electronics).
Stock ledger: This is the heart of the system. Every stock movement - receipt, dispatch, transfer, adjustment, return - is a row in the stock ledger. The ledger is append-only. You never update or delete rows. Current stock at any location is always calculated as the sum of all ledger entries for that location and SKU.
stock_ledger:
id (UUID)
sku_id (FK → sku_master)
location_id (FK → locations)
movement_type (RECEIPT | DISPATCH | TRANSFER_OUT | TRANSFER_IN | ADJUSTMENT | RETURN)
quantity (positive for inbound, negative for outbound)
reference_id (FK → purchase_order | sales_order | transfer_order | adjustment)
batch_no (nullable, for batch-tracked SKUs)
created_at (timestamp with timezone)
created_by (FK → users)This append-only ledger design gives you several things for free: a complete audit trail, the ability to reconstruct stock at any point in time, and natural concurrency safety (inserts don't conflict the way updates do).
Current stock view: A materialised view (or a cached table updated by triggers) that aggregates the ledger to show current stock by SKU and location. This is what the UI queries for performance.
2. The Transfer Order Workflow
Stock moving between locations is the most complex operation to model correctly. A transfer order has a lifecycle:
- 1Created: Transfer order raised (e.g., Pune warehouse to Nashik depot, 500 units of SKU-X)
- 2Picked: Warehouse staff confirms items picked and loaded
- 3In Transit: Vehicle departs - stock is now "in transit" (a virtual location)
- 4Delivered: Depot confirms receipt
- 5Discrepancy Resolved: If delivered quantity differs from dispatched quantity, discrepancy is recorded and resolved
The critical insight: stock should be deducted from the source location when picked (not when delivered), and added to the destination location when received (not when dispatched). The "in transit" virtual location holds the stock during movement. This prevents double-counting and gives you accurate stock at all times.
3. Offline-First Mobile Architecture
This is where most inventory systems fail for Indian distribution networks. Depot supervisors in tier-2 and tier-3 cities often have unreliable internet. A system that requires constant connectivity is useless in the field.
The solution is an offline-first mobile architecture:
Local SQLite database on the device: The mobile app maintains a local copy of the relevant data - SKU master, current stock for that location, pending orders. All operations are written to the local database first.
Background sync: When connectivity is available, the app syncs local changes to the server and pulls down server changes. The sync uses a timestamp-based conflict resolution strategy: the server is the source of truth, but local changes are queued and applied when connectivity returns.
Conflict resolution: The most common conflict is two locations both claiming to have dispatched the same stock. The system resolves this by checking the stock ledger - if the source location doesn't have sufficient stock at the time of sync, the transaction is flagged for manual review rather than silently failing.
Barcode scanning: The mobile app integrates with the device camera for barcode scanning. Every stock movement is initiated by scanning the product barcode, eliminating manual entry errors.
4. Real-Time Dashboard Architecture
The operations manager needs a live view of stock across all locations. Building this efficiently requires:
Event-driven updates: Every stock movement publishes an event to a message queue (Redis Pub/Sub or a simple WebSocket server). The dashboard subscribes to these events and updates in real time without polling.
Aggregation layer: The dashboard doesn't query the raw ledger (which can have millions of rows). It queries the materialised current-stock view, which is updated by the event processor.
Alert engine: The system monitors stock levels against reorder points. When stock at any location falls below the reorder point for a SKU, it triggers an alert - email, SMS, or in-app notification - to the relevant manager.
5. Handling the Indian Distribution Reality
Indian distribution networks have specific characteristics that generic inventory software doesn't handle well:
Returnable packaging: Many FMCG distributors track returnable crates, bottles, and pallets separately from product inventory. The system needs a separate ledger for packaging assets.
Credit notes and returns: Retailers return damaged or expired goods. The system needs a returns workflow that creates a credit note, updates stock (with a quality flag for damaged goods), and triggers a supplier return if applicable.
Scheme tracking: Distributors run promotional schemes (buy 10 get 1 free, etc.). The system needs to track scheme stock separately and apply scheme logic during order booking.
GST compliance: Every stock movement that crosses state lines is a supply under GST and requires an e-way bill. The system should integrate with the GST e-way bill API to generate e-way bills automatically for inter-state transfers.
Implementation Roadmap
Phase 1: Foundation (Weeks 1–4)
- •Set up location hierarchy and SKU master
- •Implement stock ledger with basic receipt and dispatch
- •Build mobile app with offline-first architecture for depot supervisors
- •Migrate opening stock balances
Phase 2: Transfer Workflow (Weeks 5–8)
- •Implement transfer order workflow with in-transit tracking
- •Build barcode scanning for all movements
- •Set up real-time dashboard for operations manager
- •Implement reorder alerts
Phase 3: Advanced Features (Weeks 9–12)
- •Returns and credit note workflow
- •Batch/serial tracking for applicable SKUs
- •GST e-way bill integration
- •Reporting and analytics
The Results You Can Expect
A distributor who implements this system properly can expect:
Stock accuracy: From 85–90% accuracy (typical for manual systems) to 98–99% accuracy within 3 months of go-live.
Stockout reduction: Real-time visibility and automated reorder alerts reduce stockouts by 60–70%. Sales reps stop promising stock that doesn't exist.
Operations manager time: The morning stock-collection ritual drops from 45 minutes to 5 minutes (reviewing the dashboard and acting on alerts).
Discrepancy resolution: When discrepancies occur, the audit trail reduces investigation time from hours to minutes.
Working capital: Better stock visibility typically reveals 15–25% of stock that's been sitting idle at the wrong location. Rebalancing this stock reduces the need for emergency purchases.
Common Implementation Mistakes to Avoid
Starting with the UI: Many teams start by designing screens. Start with the data model. A beautiful UI on top of a broken data model will fail.
Ignoring opening balances: The migration of opening stock balances is always messier than expected. Plan 2 weeks for this, not 2 days.
Not training depot supervisors: The best system fails if the people using it don't understand why accuracy matters. Invest in training and explain the business impact of accurate stock data.
Over-engineering the sync: Start with simple timestamp-based sync. You can add sophisticated conflict resolution later if you need it. Most businesses don't.
Not planning for growth: Design your location hierarchy to accommodate new depots and warehouses. Adding a new location should be a configuration change, not a code change.
See how IdeaSprout builds custom inventory systems for Indian distributors →