An accurate physical stock count helps you spot shortages, overages and errors before they affect purchasing decisions. You don't need an expensive system to begin. For a small business, a clearly organized spreadsheet can be a practical starting point.
Here is a five-step method for counting inventory in Excel.
1. PREPARE YOUR ITEM LIST
Before counting, create one row for each product or SKU. Include a clear item name, unit of measure, system quantity and unit cost. Separate similar products and pack sizes so they aren't mixed up.
2. SET A COUNT CUTOFF
Choose the date and time for your count. Record any stock received or sold during the count separately. If you don't use a cutoff, movements between the system snapshot and the physical count can create misleading differences.
3. COUNT WHAT IS PHYSICALLY THERE
Record actual quantities in a physical-count column. Use the same unit of measure as your item list. Have a second person recount unusually large differences where possible. A blank entry should mean 'not counted yet,' not zero stock.
4. CALCULATE YOUR DIFFERENCES
The basic formula is:
Quantity variance = Physical quantity - System quantity.
Variance value = Quantity variance x Unit cost.
A negative variance indicates a shortage. A positive variance indicates an overage. Review both directions—an overage can reveal a receiving error, a product mix-up or a transaction posted to the wrong SKU.
5. INVESTIGATE BEFORE ADJUSTING
For the largest differences, check recent receipts, transfers, sales, returns, unit conversions and possible product mix-ups. Document the likely cause and any approval before posting a stock adjustment.
GET THE FREE EXCEL INVENTORY COUNT SHEET
Start with our free, editable worksheet for up to 30 products, with quantity and dollar-variance calculations and example data:
https://payhip.com/b/YB54I
NEED A MORE COMPLETE WORKBOOK?
Inventory Control Pro ($29) includes an opening-stock catalog, dated stock movement ledger, physical count analysis, reorder indicators and dashboard, designed for up to 100 SKUs in one location:
https://payhip.com/b/gANiJ
Other helpful tools:
Purchase Order & Receiving Tracker ($12): https://payhip.com/b/Iiz9Q
Expiry & Batch Tracker ($12): https://payhip.com/b/b7igC
These templates are manual-entry Excel tools. They do not automatically connect to your POS or accounting system. Always verify counts and accounting records before making inventory adjustments.
AutoProfit Digital — Practical Excel tools for inventory control and operations.