A stock register is a continuously updated record of goods received, stored, issued, sold, transferred, returned, damaged or otherwise adjusted by a business.
For accurate financial valuation, the costing method should agree with the company’s accounting policy.
Common methods include:
FIFO
Weighted average
Specific identification
MRP value should not be treated as accounting stock value.
Weighted Average Cost
Suppose:
Existing stock: 100 units at ₹50
New purchase: 200 units at ₹60
Total value:
100 × ₹50 = ₹5,000
200 × ₹60 = ₹12,000
Total value = ₹17,000
Total quantity:
100 + 200 = 300 units
Weighted average rate:
₹17,000 ÷ 300
= ₹56.67 per unit
Reorder-Level Formula
A basic reorder formula is:
Reorder Level
= Average Daily Consumption × Supplier Lead Time
+ Safety Stock
Example:
Average daily sale: 10 units
Supplier lead time: 15 days
Safety stock: 50 units
Reorder Level
= 10 × 15 + 50
= 200 units
When stock reaches approximately 200 units, the purchase process should begin.
Suggested Purchase Quantity
A basic formula is:
Suggested Purchase
= Maximum Stock
− Current Available Stock
− Pending Purchase Quantity
+ Pending Sales Order Quantity
The formula may be adjusted according to:
Supplier MOQ
Case size
Seasonal demand
Credit availability
Expiry risk
Stock-Cover Formula
Stock cover indicates how long current stock may last.
Stock Cover in Months
= Current Stock ÷ Average Monthly Consumption
Example:
Current stock: 600 units
Average monthly sale: 200 units
Stock Cover
= 600 ÷ 200
= 3 months
Near-Expiry Formula
Suppose expiry date is in cell K2.
Days remaining:
=K2-TODAY()
Status formula:
=IF(K2<TODAY(),”EXPIRED”,
IF(K2<=TODAY()+30,”EXPIRING WITHIN 30 DAYS”,
IF(K2<=TODAY()+90,”EXPIRING WITHIN 90 DAYS”,
IF(K2<=TODAY()+180,”EXPIRING WITHIN 180 DAYS”,”OK”))))
Negative Stock Alert
Suppose current balance is in T2:
=IF(T2<0,”CHECK NEGATIVE STOCK”,””)
Negative stock may indicate:
Sale entered before purchase
Wrong product code
Wrong batch selected
Unit-conversion problem
Duplicate outward entry
Missing opening balance
Unrecorded stock transfer
Negative stock should be investigated rather than ignored.
Manual Stock Register Format
A manual stock register may use one page or section per item and batch.
Date
Particulars
Invoice/Challan
Party
Receipt
Issue
Balance
Signature
Remarks
For pharmaceutical products, add:
Batch number
Expiry date
Pack size
Storage location
How to Maintain a Manual Stock Register
Step 1: Assign Product Codes
Give every item a unique code.
Example:
FG-SYP-001
FG-CAP-002
RM-HRB-003
PM-BTL-004
Step 2: Record Opening Balance
Enter the verified opening quantity.
Do not copy the previous closing stock without physical confirmation at the start of a new register.
Step 3: Record Every Receipt
Mention:
Date
Supplier
Invoice number
Batch
Expiry
Quantity
Rate, where needed
Step 4: Record Every Issue
Mention:
Date
Customer or department
Invoice or challan
Batch
Quantity
Purpose
Step 5: Calculate the Balance
After every entry:
New Balance
= Previous Balance
+ Receipt
− Issue
Step 6: Sign Corrections
Do not use correction fluid.
For a correction:
Draw one line through the incorrect entry.
Write the correct information.
Mention the reason where material.
Sign and date the correction.
Step 7: Verify Physically
Compare physical and book quantity at defined intervals.
How Often Should Stock Be Updated?
Stock should be updated at the time of the transaction—not several days later.
Update the register when:
Goods are received
Goods are dispatched
Samples are issued
Goods are transferred
Returns are accepted
Damage is identified
Goods expire
Stock is destroyed
Physical differences are approved
A register updated only at month-end is not a live stock register.
Goods-Receipt Procedure
When goods arrive:
Check supplier and purchase order.
Count packages.
Inspect damage.
Verify invoice or challan.
Check product and pack size.
Verify batch and expiry.
Check quantity.
Check storage conditions.
Record shortages or excess.
Enter stock in the register.
Place goods in the correct status area.
Obtain quality approval where required.
Goods should not automatically become saleable merely because they have arrived.
Stock-Issue Procedure
Before issuing stock:
Receive an authorized order or requisition.
Verify available stock.
Select the correct batch using FEFO.
Check minimum remaining shelf life.
Verify quantity.
Prepare invoice or challan.
Record the outward transaction.
Update the stock balance.
Obtain dispatch or departmental acknowledgement.
Physical Stock Verification
Physical stock verification means counting actual inventory and comparing it with the register.
It may be conducted:
Daily for selected high-value items
Weekly for fast-moving items
Monthly
Quarterly
Annually
Through continuous cycle counting
Physical Stock Format
Product
Batch
Book Stock
Physical Stock
Difference
Value Difference
Reason
Approved By
Difference formula:
Difference
= Physical Stock − Book Stock
A positive difference means physical stock is greater than book stock.
A negative difference means shortage.
Stock Adjustment Procedure
Do not change the balance simply to match physical stock.
Every adjustment should record:
Product
Batch
Book quantity
Physical quantity
Difference
Reason
Financial value
Investigation
Approval
Adjustment date
Possible reasons include:
Counting mistake
Wrong unit
Unrecorded sample
Wrong batch invoiced
Damage
Theft
Duplicate entry
Packing variation
Return not entered
Cycle Counting
Cycle counting means verifying selected items throughout the year instead of waiting for one annual count.
Suggested classification:
A-Class Items
High-value or critical items.
Count frequently.
B-Class Items
Medium-value items.
Count at moderate intervals.
C-Class Items
Low-value items.
Count less frequently.
Classification should also consider:
Regulatory risk
Expiry risk
Theft risk
Sales importance
Cold-chain status
Fast-, Slow- and Non-Moving Stock
Fast-Moving Stock
Products with regular and high consumption.
Slow-Moving Stock
Products with low or irregular movement.
Non-Moving Stock
Products with no movement during a defined period.
A company may classify:
No movement for 3 months
No movement for 6 months
No movement for 12 months
The period should suit the business.
Expiry Management
Maintain a separate expiry report containing:
Product
Batch
Expiry
Quantity
Cost value
MRP value
Supplier
Customer return eligibility
Proposed action
Possible actions include:
Stop further purchase
Transfer to another territory
Inform the sales team
Return to supplier
Collect from distributors
Replace
Destroy after authorization
Do not increase market loading merely to dispose of near-expiry stock.
Returned Goods
Returned goods should not be directly added to saleable stock.
Record:
Customer
Original invoice
Product
Batch
Expiry
Quantity
Return reason
Storage history
Seal condition
Credit-note status
Final decision
Possible final statuses:
Approved for resale
Returned to supplier
Rejected
Expired
Destroyed
Under investigation
Free Samples and Promotional Stock
Free samples reduce inventory even though no sales value is collected.
The stock register should record:
Product
Batch
Expiry
Quantity
Recipient or employee
Authorization
Date
Purpose
Do not issue free stock without an authorized document merely because it has no invoice value.
Damaged, Lost or Destroyed Stock
Maintain a separate controlled record showing:
Product
Batch
Quantity
Reason
Date identified
Photographs, where appropriate
Insurance claim
Approval
Disposal method
Financial adjustment
Examples include:
Fire
Flood
Leakage
Breakage
Theft
Pest damage
Temperature excursion
Transport damage
Warehouse-Wise Stock
Where the company has multiple locations, stock should be separately identifiable for each:
Warehouse
Branch
C&F
Distributor-owned location
Manufacturing unit
Quarantine area
Cold room
A transfer should create:
Transfer Out at the sending location
Transfer In at the receiving location
The stock should not disappear from one location and appear in another without a transfer document.
Unit-of-Measurement Control
Unit errors are a common cause of wrong inventory.
Examples:
One carton = 50 bottles
One box = 10 strips
One strip = 10 tablets
One case = 24 jars
The system should define:
Base unit
Purchase unit
Sales unit
Conversion factor
Example:
5 cartons × 50 bottles
= 250 bottles
Do not sometimes enter cartons and sometimes bottles in the same quantity field without conversion.
Stock Valuation
Inventory can be viewed at:
Purchase cost
Landed cost
Weighted average cost
Manufacturing cost
MRP value
For management:
Stock Cost Value
= Quantity × Applicable Cost Rate
Stock MRP Value
= Quantity × MRP
MRP value represents potential consumer value, not company profit or accounting inventory value.
GST Stock Records
A GST-registered business should be able to account for:
Opening stock
Goods received
Goods supplied
Goods lost
Goods stolen
Goods destroyed
Goods written off
Gifts
Free samples
Closing stock
Raw materials
Finished goods
Scrap
Wastage
Records should be maintained separately for relevant business activities and locations.
The inventory system should therefore not record only purchases and sales.
Pharmaceutical Stock Controls
Pharmaceutical stock records should additionally support:
Product name
Strength
Dosage form
Pack size
Batch
Manufacturing date
Expiry
Manufacturer
Purchase source
Customer
Drug-licence details where relevant
Temperature requirement
Recall status
Quarantine status
Additional product-specific records may be required for certain scheduled or controlled medicines.
Temperature-Controlled Stock
For temperature-sensitive products, maintain:
Storage location
Required temperature
Actual temperature records
Data logger details
Excursion details
Quality assessment
Final disposition
Inventory software may show stock quantity correctly while the product is unusable because of a temperature excursion.
Quantity and quality status must therefore be controlled separately.
Software vs Excel vs Manual Register
Manual Register
Suitable when:
Very few products exist.
Transaction volume is low.
Only one person maintains stock.
Computer access is limited.
Limitations:
Difficult to search
Slow reporting
Difficult batch tracking
No automatic expiry alerts
Higher calculation risk
Difficult multi-location control
Excel or Spreadsheet
Suitable when:
Product and transaction volume is moderate.
The company has disciplined data entry.
A small team uses the file.
Advanced software is not yet affordable.
Advantages:
Customizable
Searchable
Filterable
Low additional cost
Supports formulas and PivotTables
Limitations:
Accidental deletion
Weak audit trail
Duplicate files
Multi-user conflicts
Formula errors
Limited access control
Inventory or ERP Software
Suitable when:
There are many products or batches.
Several employees use the system.
Multiple locations exist.
Barcode scanning is required.
Purchase, sales and accounts need integration.
Batch and expiry control is critical.
Useful functions include:
Purchase integration
Sales integration
Batch allocation
FEFO
Expiry alerts
Barcode scanning
Reorder reports
User permissions
Audit trail
Stock valuation
Recall reporting
When Should a Business Move From Excel to Software?
Consider moving when:
Hundreds of active products exist.
Daily transactions are increasing.
More than one warehouse is operating.
Negative stock occurs frequently.
Batches are selected incorrectly.
Employees overwrite formulas.
Reports take too long.
Accounts and inventory do not agree.
Expiry losses are increasing.
Real-time stock is required.
Stock-Register Controls
Use the following controls:
Unique product codes
Mandatory batch entry
Date validation
Restricted drop-down lists
Locked formula cells
User access controls
Daily backup
Audit trail
Document-number validation
Negative-stock blocking
Duplicate-invoice alerts
Period locking after monthly closing
Approved adjustment workflow
Data Backup
Electronic stock records should be backed up regularly.
A practical policy may include:
Daily automatic backup
Weekly external backup
Restricted cloud or server access
Periodic restoration testing
Password protection
Version history
A backup is useful only when it can actually be restored.
Common Stock-Register Mistakes
Avoid:
Creating one worksheet for every product
Entering product names with different spellings
Failing to record batches
Combining batches with different expiries
Updating stock several days late
Ignoring free samples
Ignoring damaged goods
Adjusting balances without authorization
Using MRP as stock cost
Mixing cartons, boxes and pieces
Allowing negative stock
Treating returned stock as immediately saleable
Not separating expired stock
Maintaining several uncontrolled Excel copies
Not reconciling stock with purchase and sales records
Conducting no physical verification
Keeping stock at an unrecorded location
Step-by-Step Stock Management Process
Step 1: Prepare the Product Master
Create standard codes, names, packs, units and storage conditions.
Step 2: Verify Opening Stock
Conduct a physical count and enter product- and batch-wise balances.
Step 3: Define Transaction Types
Create standard inward, outward and adjustment categories.
Step 4: Enter Every Receipt
Link the receipt with the supplier invoice or production document.
Step 5: Enter Every Issue
Link the issue with the sales invoice, challan or authorized requisition.
Step 6: Track Batch and Expiry
Do not maintain only product-level totals for expiry-controlled goods.
Step 7: Review Negative Stock Daily
Investigate every negative quantity.
Step 8: Review Near-Expiry Stock Monthly
Take action before stock becomes unsaleable.
Step 9: Conduct Physical Verification
Use cycle counting and periodic complete stock takes.
Step 10: Approve Adjustments
Investigate and authorize differences.
Step 11: Reconcile With Accounts
Compare:
Purchases
Sales
Returns
Credit notes
Stock value
GST records
Step 12: Review Reorder Requirements
Purchase according to demand, lead time and safety stock.
Practical Stock Register Example
Suppose the opening balance is 100 units.
Transactions during the month:
Purchase: 50 units
Sale: 80 units
Sales return: 5 units
Free samples: 3 units
Damaged stock: 2 units
Calculation:
Closing Stock
= 100
+ 50
− 80
+ 5
− 3
− 2
= 70 units
The closing balance should therefore be 70 units.
If physical stock is 68 units:
Difference
= Physical Stock − Book Stock
= 68 − 70
= −2 units
The shortage should be investigated and approved before adjustment.
Frequently Asked Questions
1. What is a stock register?
A stock register is a continuously updated record of inventory received, issued, transferred, returned, damaged and remaining in the business.
2. What is the basic stock formula?
Closing Stock
= Opening Stock
+ Receipts
− Issues
± Adjustments
3. Can a stock register be maintained in Excel?
Yes. Excel is suitable for a small or medium inventory when the file is properly designed, controlled and backed up.
4. Should every product have a separate worksheet?
Not normally. A single transaction database with product and batch fields is easier to manage and analyse.
5. Is batch-wise stock necessary for pharmaceutical products?
Yes. Batch-wise records support expiry control, complaints, returns and recalls.
6. What is the difference between FIFO and FEFO?
FIFO issues the earliest received stock first. FEFO prioritizes the earliest-expiring acceptable stock.
7. How often should the register be updated?
It should be updated when each inventory transaction occurs.
8. What is physical stock verification?
It is the process of counting actual stock and comparing it with the recorded book balance.
9. Should free samples be entered in the register?
Yes. Samples reduce inventory and should be supported by authorized records.
10. Should expired stock remain in the regular balance?
Expired stock should remain traceable but should be blocked and segregated from saleable inventory.
11. Can stock be maintained only at product level?
For simple non-batch goods, product-level records may be sufficient. Pharmaceutical and expiry-controlled goods should be maintained batch-wise.
12. How is minimum stock calculated?
Minimum stock should consider average consumption, supplier lead time, demand variability and safety stock.
Final Thoughts
An effective stock register should answer five questions immediately:
What stock is available?
Which batch and expiry are available?
Where is the stock located?
How did the stock enter or leave?
Does the recorded stock agree with physical stock?
The most reliable stock-control sequence is:
Create product master → Record opening stock → Enter every receipt → Enter every issue → Track batches and expiry → Verify physical stock → Investigate differences → Review reorder and expiry reports
A stock register becomes useful only when it is:
Accurate
Current
Batch traceable
Supported by documents
Physically verified
Regularly reviewed
Looking for Ayurvedic Franchise or Distribution Opportunities?
Looking to start an Ayurvedic franchise, become a distributor, or launch your own herbal product range?
Elzac Herbal India offers:
Ayurvedic & Herbal Product Range
Franchise & Distribution Opportunities
Third-Party Manufacturing Services
Product Development Support
Marketing Guidance
PAN India Business Opportunities
Whether you are an entrepreneur, retailer, distributor, or healthcare professional, our team can help you explore the right business opportunity in the growing Ayurvedic sector.
Ajay Kamboj is an entrepreneur and business owners associated with many Ayurvedic and Pharmaceutical start-ups. With years of experience in Ayurvedic product marketing, pharmaceutical distribution, franchise development, and client relationship management, he regularly shares practical business insights based on real-world experiences. His articles focus on business growth, entrepreneurship, customer management, and lessons learned from the healthcare and wellness industry.
2 Responses
Thank you So much for posting all of your favorites!! My usually flares up when the weather changes too!
Thank you So much for posting all of your favorites!! My usually flares up when the weather changes too!
Mai Madicine Ka Stockist Lena Chahta Hoon