Pharmaceutical Stocktake and Inventory Reconciliation: A Complete Guide

Introduction

Conducting accurate stocktakes in a pharmaceutical environment is critical to ensure inventory integrity, prevent shortages, and support audit compliance. This guide outlines a professional end-to-end process, from barcode scanning to final reconciliation, including how to visualize stock data in Power BI.

---

1. Extract Current Inventory

Before starting a stocktake:

• Pull the current inventory status from the system.
• This represents the system stock you will compare against physical counts.
• Ensures preparation for accurate stock verification.

---

2. Perform Physical Stocktake

Using handheld devices (e.g., iVend):

1. Scan barcodes of all items.
2. Manually count medicines to verify physical stock.
3. Save counted data on the handheld device for later merging.

Tip: Always check batch numbers and expiry dates during counting.

---

3. Merge Counted Data with System Inventory

• Transfer the handheld data to the system.
• Merge with the system inventory in Excel for analysis.
• This step forms the initial reconciliation dataset.

---

4. Generate Variance Reports in Excel

Classify stock into categories for analysis:

Category	Definition
Balancing	Physical count matches system stock
Negative Variance	Physical < System (missing stock)
Positive Variance	Physical > System (extra stock)
Uncounted Products	Not physically counted

• Generate an Excel report with these classifications.
• This report forms the basis for verification and adjustments.

---

5. Recount and Verify Variances

• Recount items flagged as negative, positive, or uncounted.
• Verify whether discrepancies are due to errors or real stock issues.
• Correct the initial counts with verified numbers.

---

6. Update Inventory in the System

• Once verified, update the initial stock counts.
• Perform final reconciliation in the iVend system.
• Ensures that system stock matches verified physical inventory.
• Results in accurate, audit-ready inventory records.

---

7. Visualize Stock Data with Power BI

Step 1: Prepare Excel Data

Ensure your Excel file includes:

Item Code | Item Name | System Stock | Counted Stock | Variance | Category

Step 2: Load Data into Power BI

1. Open Power BI Desktop
2. Click Home → Get Data → Excel
3. Load your stocktake file

Step 3: Create Visualizations

• Column Chart: Display variance per item, colored by category
• Pie Chart: Show percentage of balancing, negative, positive, and uncounted items
• Table: Detailed view of item stock and variance
• Cards: Show total items, positive/negative variances, and uncounted products

Step 4: Apply Filters & Slicers

• Add Slicer for Category to filter by variance type
• Optional: Filter by Item Name or Item Code

Step 5: Format Dashboard

• Use color coding for clarity (Red = Negative, Green = Balancing, Orange = Positive)
• Arrange metrics at the top, charts in the middle, table at the bottom

Step 6: Publish & Share

• Click Home → Publish to Power BI Service
• Share dashboards with your team or management

---

8. Key Benefits

• Accurate stock counts and reconciliation
• Early identification of missing or uncounted items
• Audit-ready inventory records
• Visual insights for better decision-making
• Streamlined reporting for management

---

9. Conclusion

By following this professional workflow, combining manual stocktake, handheld device scanning, Excel variance reports, and Power BI dashboards, pharmaceutical inventory management becomes:

• Accurate
• Transparent
• Efficient
• Audit-compliant

This workflow demonstrates real-world data analysis, **inventory control.