22.9 Pharma
Field-force interviews ask whether a visit became an order, and whether stock will expire. A day of doctor visits with no order is a vanity metric. Names below are the class list. This is not a company's secondary-sales audit.
| KPI | What the number means | Who uses it | Action it drives |
|---|---|---|---|
| Order conversion | Orders divided by doctor visits. | Area manager | A low rate means the call list or the stock, not "do more calls". |
| Expiry % | Expired stock value divided by stock value. | Stockist and the manager | High expiry stops the next dispatch to that city. |
| Secondary sales | Sales value to pharmacies in the period, in ₹. | Area manager | Read it with expiry. Sales that sit and expire were not demand. |
Fictional sample rows. One month. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblPharma (Table Design › Table Name).
| MR | City | Visits | Orders | Secondary sales | Expired | Stock value |
|---|---|---|---|---|---|---|
| Ravindra Bagale | Pune | 40 | 16 | ₹80,000 | ₹2,000 | ₹40,000 |
| Shraddha Bagale | Nashik | 24 | 12 | ₹60,000 | ₹500 | ₹30,000 |
| Raja | Kolhapur | 50 | 5 | ₹15,000 | ₹4,000 | ₹20,000 |
| Rani | Nagpur | 30 | 12 | ₹48,000 | ₹1,000 | ₹25,000 |
| Amir | Solapur | 20 | 10 | ₹40,000 | ₹0 | ₹15,000 |
| Zoya | Sambhaji Nagar | 36 | 6 | ₹18,000 | ₹3,000 | ₹18,000 |
Excel formulas for the whole file (type them in empty cells below or beside the table):
| KPI | Excel formula | Result | Number format |
|---|---|---|---|
| Order conversion | =SUM(tblPharma[Orders])/SUM(tblPharma[Visits]) |
30.5% | 0.0% |
| Expiry % | =SUM(tblPharma[Expired])/SUM(tblPharma[Stock value]) |
7.1% | 0.0% |
Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.
| New column | Formula in the first row |
|---|---|
| Order conversion | =[@Orders]/[@Visits] |
| Expiry % | =[@Expired]/[@[Stock value]] |
Expected result per row:
| MR | Order conversion | Expiry % |
|---|---|---|
| Ravindra Bagale | 40.0% | 5.0% |
| Shraddha Bagale | 50.0% | 1.7% |
| Raja | 10.0% | 20.0% |
| Rani | 40.0% | 4.0% |
| Amir | 50.0% | 0.0% |
| Zoya | 16.7% | 16.7% |
One city with SUMIFS. Order conversion for Pune only:
=SUMIFS(tblPharma[Orders],tblPharma[City],"Pune")/SUMIFS(tblPharma[Visits],tblPharma[City],"Pune")
Result for Pune: 40.0%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Raja's conversion 10.0% and expiry 20.0% | Raja's 50 visits |
| Why | Five orders from fifty visits, and one fifth of the stock value expired. | The visit card makes Kolhapur look like the hardest-working city. |
What the manager does. The Nashik manager keeps Shraddha's list (half the visits became orders). The Pune manager looks at Ravindra's expiry of ₹2,000 on ₹40,000 stock (5.0%) before sending the next carton. The Kolhapur manager stops ranking MRs by visits. Zoya's expiry is 3,000 ÷ 18,000 = 16.7%. Same action: no fresh stock until the expired batch is written off in the practice file.
Practice workbook and guide: Pharma dashboard workbook (Excel) · How to build the Pharma dashboard in Excel. The workbook data is also fictional.
Ravindra Bagale's Tip
Primary sales (company to stockist) and secondary sales (stockist to chemist) must never share one column. Ask which one the file has before you build a formula.
Ravindra Bagale's Tip – मराठी
Primary sales (company ते stockist) आणि secondary sales (stockist ते chemist) कधीच एका column मध्ये नसावेत. Formula बनवण्याआधी file मध्ये कोणते आहेत ते विचारा.
Ravindra Bagale's Tip – हिंदी
Primary sales (company से stockist) और secondary sales (stockist से chemist) कभी एक column में नहीं होने चाहिए. Formula बनाने से पहले पूछें कि file में कौन सा है.