Picture this: a pharma distribution company in Sangli is losing money to expired medicines sitting on shelves, while some fast-moving drugs keep going out of stock. Their records? A beautiful mess of Excel sheets, inconsistent naming, and stock entries that somehow violate basic math. Now imagine someone handing you this data and saying — "figure it out."
That someone was me. And yes, I panicked. But then I opened Python, took a deep breath, and got to work.
This is the story of my BDM Capstone Project — how I worked with Sevam Pharma LLP, a real B2B pharmaceutical distributor, to bring order to their operational chaos using data analysis. Spoiler: it involved ABC analysis, expiry risk scores, and discovering that a single retailer was responsible for 39% of all revenue. Wild, right?
Meet Sevam Pharma — The Underdog of Medicine Distribution
Sevam Pharma LLP is not some giant multinational. It's a lean, B2B pharmaceutical distributor tucked in Sangli, Maharashtra, supplying medicines — mostly from Sun Pharma — to retail medical stores in the region. Think of them as the invisible backbone between the medicine factory and your local pharmacy.
Founded in 2022, they had 12 employees, a warehouse full of boxes, and a growing list of problems that come with growth: too much of some medicines, not enough of others, and retailers returning stock left and right.
"The warehouse was running on gut feel and old habits. My job was to replace that with data — and make the owner actually trust it."
Getting the data wasn't glamorous. It meant having real conversations with the owner, getting an official authorization letter signed, and sitting through the slightly terrifying experience of explaining what "data analysis" means to someone who's been doing business without it for years. But they said yes — and that was everything.
Four Problems That Were Quietly Draining Money
Before touching any code, I spent time understanding the business. Four core problem statements emerged:
Slow-moving medicines rotting on shelves = money literally expiring. Fast-moving ones running out = missed sales. Both happening simultaneously — a classic lose-lose.
Some retailers were receiving stock but returning a shocking 25–35% of it. Why? Nobody had stopped to find out. Until now.
Procurement was based on habit, not data. February demand ≠ October demand. Nobody had mapped this out clearly.
Stock records that violated the formula: Closing = Opening + Purchase − Sales. Missing material codes. Same medicine, different names. The dataset was a puzzle.
The Arsenal — What I Actually Did With the Data
Once the data was cleaned (which took longer than I'd like to admit), I deployed a mix of inventory management techniques and statistical analysis. Here's what I used and why:
ABC Analysis
Classified all medicines by revenue contribution. Turned out 9 products = 80% of revenue. Classic Pareto in action.
FSN Analysis
Split medicines into Fast, Slow, Non-moving. 33 fast movers, 11 slow, 7 non-moving. Non-movers = dead capital.
Expiry Risk Scoring
Built a custom formula: Months of Stock ÷ Months to Expiry. Score > 1 = danger zone. 21.5% of products were high-risk.
Inventory Turnover
Sales ÷ Average Inventory. Low ratio = stock sitting idle. Flagged the worst offenders for immediate action.
Retailer Contribution Analysis
Mapped which retailers actually drive revenue vs which ones just return stock. One retailer = 39% of revenue. Mind-blowing.
Return Impact Analysis
Quantified how much returned stock was eating into realized sales. The numbers were uncomfortable to present.
Demand & Trend Analysis
Mapped demand month by month. February = peak. April = valley. October = tricky — high demand, lower margins. A seasonal story emerged.
Correlation + Anomaly Detection
Opening-Closing stock correlation: 0.90. Sales-Closing stock: −0.71. Found records where stock math simply didn't add up.
What the Data Actually Revealed
Here's where things got genuinely interesting. I expected to find some inventory issues. I didn't expect the data to tell such a clear, almost dramatic story.
The 9-Product Empire
Just 9 medicines out of 51 were responsible for 80% of total revenue. The top product alone — PRAMIPEX ER 0.375 TA — contributed nearly 39% of all revenue by itself. The business was essentially built on a handful of medicines. One supply disruption and the whole thing wobbles.
The Retailer Who Runs Everything
A single retailer called Pooja Palus was generating 39.2% of all revenue. The next biggest? 14.1%. Most others? A rounding error labeled "Others." But here's the twist — when I looked at sales quantity, the distribution was much more even. It's not that Pooja Palus buys more medicines; it's that they buy more expensive ones. Fascinating.
Rule: Closing = Opening + Purchase – Sales
Reality: Several records: NOPE. Just... nope.
# Products with zero movement? Not in the dataset at all.
# They should be. With equal opening & closing stock.
Verdict: Data reliability issues. Flagged. Reported.
Returns Are a Silent Killer
Several retailers were returning 350 to 450 units out of every 1,100–1,400 units supplied. That's 25–35% of stock coming back unrealized. The business was shipping more than retailers could actually sell — a textbook mismatch between supply push and actual demand pull.
High Sales ≠ High Profit (Surprise!)
February had the highest revenue. But March and May had larger profit bubbles. October saw rising demand paired with shrinking margins. The relationship between volume and profitability was more complicated than anyone realized — and now there's data to prove it.
What This Project Actually Felt Like
Finding a willing business
The hardest part. Approached Sevam Pharma, explained the project, got the authorization letter signed. Not as easy as it sounds when you're a student with a laptop and an IIT letterhead.
Data collection: the beautiful mess
Received 36 monthly Excel sheets — sales data, purchase data, stock statements. Each slightly different. Some missing columns. Some with duplicate entries. Adventure mode: activated.
Cleaning, consolidating, crying a little
Pandas became my best friend. Removed constant-value columns, handled missing material codes, merged 36 files into unified annual datasets. The satisfaction was real.
Analysis: where it all clicked
Matplotlib, Seaborn, and a lot of coffee. Each chart revealed something new. The correlation heatmap showing −0.71 between sales and closing stock was a legitimate eureka moment.
Recommendations: making it actionable
The point isn't fancy graphs. It's: what does the owner DO now? Sales-based replenishment, expiry-based prioritization, retailer-specific allocation. Real, implementable changes.
What I Told the Business to Do Differently
Data without recommendations is just decoration. Here's what I proposed — grounded in the findings:
Adopt a sales-based replenishment strategy — buy 1.2–1.5× the average of the last 2–3 months, not based on gut feel. Set clear reorder levels per product so stockouts become an alert, not a surprise. For overstocked items? Discount them, redistribute them, or pause procurement. For the high-return retailers? Set maximum supply limits based on their actual consumption history.
The big one: move from uniform distribution (sending the same stock to every retailer) to retailer-specific allocation based on historical sales performance. Not every pharmacy is the same. The data makes that embarrassingly clear.
"The data wasn't broken. The decisions being made without it — those were broken."
Want to See the Full Analysis?
All reports, Python code, datasets (anonymized), and the presentation are available below. Pull it apart, learn from it, build on it.
Reports available on request · IIT Madras BS Program · Roll: 23F3001400