Back to Projects

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.

My Role Business Operations Analyst
Analytical Tools & Models
PAPAO Operational Narrative Inventory Controls Excel Modeling
For in-depth analysis
View GitHub

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

Step 1: Input Standardization

Standardized the sales log spreadsheet formats, replacing freeform comments with clear, category-based dropdowns to enforce consistency.

Step 2: Stock Formula Integration

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:

Shelf Space Stagnation (20% Frozen)

Over one-fifth of active warehouse shelf space was occupied by slow-moving, low-turnover medicine brands, unnecessarily tying up valuable distributor capital.

Inelastic Ordering Parameters

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.