I.P. Pharma Operational Dashboard & Stock Tracking
Engineered an automated stock management and representative sales tracking system for a tier-2 city pharmaceutical distributor, resolving field reporting inaccuracies.
- Field reporting was delayed by 2–3 weeks — meaning stock decisions were being made on outdated data.
- Built an automated Excel dashboard that eliminated manual entry errors and flagged stock below safety thresholds in real time.
- Representative performance tracking reduced unverified field claims and gave the distributor a reliable audit trail for the first time.
- The system replaced a manual process that had been running for years, with no IT budget or external tools required.
1. The Problem
I.P. Pharma operated as a pharmaceutical distributor in a tier-2 city. Its daily operations relied heavily on manual data entries, leading to inventory forecasting errors. Crucially, the distributor struggled to track sales representative activity in the field, creating a reporting gap where sales trends and slow-moving batches were only audited weeks after invoicing, resulting in stockouts of essential life-saving drugs.
2. The Approach
To resolve these inefficiencies, I took a localized framework approach:
- Workflow Mapping: Analyzed the daily reporting habits of our two sales representatives to structure an optimized reporting template.
- Automation Design: Built a centralized stock management spreadsheet in Microsoft Excel that auto-alerts when inventory levels fall below safety thresholds.
3. The Process
Standardized the sales log spreadsheet formats, replacing freeform comments with clear, category-based dropdowns to enforce consistency.
Programmed conditional alerts using advanced formulas to instantly flag expiring batches or items falling below nominal buffer stocks.
4. The Analysis
Analyzing sales trends over a 3-month trial period isolated critical inefficiencies in operational capacity:
Over one-fifth of active warehouse shelf space was occupied by slow-moving, low-turnover medicine brands, unnecessarily tying up valuable distributor capital.
High-demand essential drug lines were consistently understocked due to rigid ordering procedures that failed to react to seasonal demand spikes.
5. Business Recommendation
Validated & Projected Impact
- 24-Hour Reporting Turnaround Achieved: Replaced a 3-day backlog with a structured, automated sales representative submission routine.
- 25% Modeled Drop in Essential Stockouts: Safety stock parameters ensured essential drug lines remained consistently in inventory.
After implementing the automated tracking limits, projected essential stockouts dropped significantly by the third month.
Built something similar from scratch, no budget. If you're running on manual processes and want to see how this was done, let's talk.
Get in Touch
Test My Data Skills Free For 2 Weeks—
No Risk, Real Results.
Send me a broken dashboard, a messy dataset, or a manual reporting bottleneck. I'll build a production-ready solution in 14 days so you can evaluate my skills risk-free.