Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.8 Pharma: Stock Value and Near-Expiry Batches

By Last updated:

Example first. Ravina controls stock at fictional pharma distribution depots and must flag batches that expire within 90 days.

Pharma project — in 30 seconds

What: A Pharma tracker: one Table tblStock, the calculated column StockValue, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Pharma report: Stock Value, Near-expiry Value, Near-expiry % are the numbers a manager reads first.

Tiny example: From 6 rows: Stock Value = ₹1,81,200, Near-expiry Value = ₹78,600, Near-expiry % = 43.4%.

First look at the data (before → after):

Pharma project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated columns

Batch StockValue NearExpiry
B-101 ₹21,600 No
B-102 ₹33,600 Yes
B-103 ₹18,000 Yes
B-104 ₹57,000 No
B-105 ₹27,000 Yes
B-106 ₹24,000 No

Expected result — KPI cells (expected results)

KPI Formula Result
Stock Value =SUM(tblStock[StockValue]) ₹1,81,200
Near-expiry Value =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes") ₹78,600
Near-expiry % =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes")/SUM(tblStock[StockValue]) 43.4%
Before table and formula as text

Before — Table tblStock (fictional sample rows)

Batch City Units UnitCost ExpiryDays
B-101 Pune 1,200 18 240
B-102 Pune 800 42 60
B-103 Nashik 1,500 12 45
B-104 Nagpur 600 95 180
B-105 Solapur 900 30 75
B-106 Kolhapur 400 60 365

Formula / click path

StockValue: =[@Units]*[@UnitCost]
NearExpiry: =IF([@ExpiryDays]<=90,"Yes","No")
Stock Value: =SUM(tblStock[StockValue])
Near-expiry Value: =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes")
Near-expiry %: =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes")/SUM(tblStock[StockValue])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblStock in Table Design › Properties › Table Name.
  2. Add the column StockValue with =[@Units]*[@UnitCost]; the Table fills it down. Add the other calculated columns from the table below the same way.
  3. On a KPI sheet type =SUM(tblStock[StockValue]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and StockValue in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › NearExpiry.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a depot stock sheet: stock value per batch, a near-expiry flag (90 days or less), total stock value, near-expiry value and %, stock value by city, a chart and a NearExpiry slicer.

Calculated column formulas:

Column Formula
StockValue =[@Units]*[@UnitCost]
NearExpiry =IF([@ExpiryDays]<=90,"Yes","No")

KPI cells (check your numbers):

KPI Formula Result
Stock Value =SUM(tblStock[StockValue]) ₹1,81,200
Near-expiry Value =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes") ₹78,600
Near-expiry % =SUMIFS(tblStock[StockValue],tblStock[NearExpiry],"Yes")/SUM(tblStock[StockValue]) 43.4%

PivotTable (expected):

City Sum of StockValue
Kolhapur ₹24,000
Nagpur ₹57,000
Nashik ₹18,000
Pune ₹55,200
Solapur ₹27,000
Grand Total ₹1,81,200

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick NearExpiry and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Pharma_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Pharma dashboard guide and its workbook Pharma_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of StockValue?
  2. Filter the Table on City = Nagpur (Data › Filter): what is Stock Value for Nagpur alone?
  3. Add the Clustered Column PivotChart and the NearExpiry slicer to your Dashboard sheet.
Answer

Nagpur (₹57,000). For Nagpur only (1 row): Stock Value = ₹57,000. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Write the definition of Stock Value under its KPI card in small text. Two people reading the same dashboard should agree on what the number means.

Loading practice tasks...

  1. Total stock units (Easy): Add up Stock units in D2:D11.
  2. Average unit cost (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic (Easy): How many rows have Category "Antibiotic"?
  4. Largest stock units (Easy): The largest value in D2:D11.
  5. Stock units for Antibiotic (Medium): Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Madrid (Medium): Total of D2:D11 where B is "Antibiotic" and C is "Madrid".
  7. Best in Antibiotic (Medium): Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Sneha Patil (Medium): The Stock units for "Sneha Patil" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Antibiotic (Hard): Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →