What Is an Inventory On Hand Report?
An inventory on hand report is the foundational operational report of any warehouse, distribution center, or manufacturing facility. It answers the most basic and most important question in inventory management: what do we have, where is it, and how much of it is available right now?
The report presents the current quantity of every item in stock — organized by item number or
SKU, by warehouse location, by storage bin or rack position, by lot number, by serial number (where applicable), and by stock condition (sellable, damaged, quarantine, returns). It goes beyond a simple count to show the allocation status of each item: how much is committed to open customer orders, how much is reserved for production, how much is in transit from receiving to putaway, and how much is genuinely available for new orders or production runs.
An inventory on hand report that is current and accurate is the prerequisite for nearly every other operational decision in a product-based business:
Purchasing and procurement — cannot determine what to order without knowing what is already in stock
Order fulfillment — cannot promise delivery dates without knowing available quantities at each location
Production planning — cannot schedule production runs without confirming raw material availability
Customer service — cannot answer availability questions without real-time stock visibility
Finance and accounting — cannot produce an accurate inventory valuation without a reliable quantity basis
The challenge is that inventory changes constantly. Receiving adds stock. Picking removes it. Transfers move it between locations. Returns add back to available. Adjustments correct discrepancies. An inventory on hand report that was accurate this morning is likely different this afternoon — and the decisions made on stale inventory data are consistently more expensive than the cost of building real-time reporting.
Why Organizations Use Inventory On Hand Reports
- Purchasing and Replenishment Decisions The inventory on hand report is the primary input to every purchasing and replenishment decision. Buyers use it to determine which items are approaching their reorder point, which items have sufficient stock to cover upcoming demand, and which items are over-stocked relative to current demand patterns. Without accurate, current on-hand data, purchasing teams either over-buy (accumulating excess inventory and working capital) or under-buy (creating stockouts that lose sales and damage customer relationships).
- Order Fulfillment and Available-to-Promise E-commerce businesses, distributors, and wholesale suppliers use real-time inventory on hand data to calculate available-to-promise (ATP) quantities — the amount that can actually be committed to a new customer order without creating a backorder. An ATP calculation requires not just the gross on-hand quantity but the net-available quantity after deducting all existing allocations. Automated, real-time inventory on hand reporting is the foundation of reliable order commitment.
- Warehouse Space and Slotting Optimization Warehouse operations teams use inventory on hand data — organized by location and item — to optimize slotting: placing fast-moving items in prime pick locations, consolidating fragmented stock of the same item, identifying empty or under-utilized locations, and planning seasonal inventory expansions. Without a systematic inventory on hand report by location, warehouse space planning is done on intuition rather than data.
- Physical Count Preparation and Cycle Count Management Before conducting a physical inventory count or a cycle count, the counting team needs a current inventory on hand report to serve as the expected quantity against which count results will be compared. Post-count, the inventory on hand report is compared to the count sheets to identify variances — items that are over or under what the system shows — which are then investigated, adjusted, or escalated for further review. The quality of the physical count process depends directly on the accuracy and currency of the pre-count on-hand report.
- 3PL Client Reporting Third-party logistics providers are contractually obligated to give their clients accurate, timely visibility into their inventory positions. 3PL operators use automated inventory on hand reports — often delivered through client portals or scheduled email reports — to fulfill this obligation without manual data pulls for each client. For 3PLs managing dozens of clients across multiple facilities, automated per-client inventory reporting is an operational requirement.
- Kitting and Assembly Operations Businesses that assemble finished goods from components — electronics assemblers, packaging operations, promotional product companies — use inventory on hand reports at the component level to determine how many finished units can be assembled from current component stock (available-to-build calculations). This requires accurate, real-time on-hand reporting at the component level, with multi-level BOM explosion to derive kit availability.
- Lender and Investor Reporting Asset-based lenders use inventory on hand reports as the basis for inventory borrowing base calculations — determining eligible inventory that can be used as collateral. Equity investors and PE portfolio managers use inventory on hand data alongside days-on-hand metrics to assess working capital efficiency and inventory management quality.
- Both audiences require accurate, current, consistently formatted inventory reports.
Common KPIs and Data Elements
Inventory On Hand Report — Full Data Structure Item Master Data
- Item Number / SKU
- Item Description
- Product Category / Product Line
- Unit of Measure (each, case, pallet, pound, liter)
- Item Status (active, discontinued, seasonal)
- ABC Classification (A = high volume/value, B = medium, C = low)
- Reorder Point
- Safety Stock Level
- Standard Lead Time (days)
- Preferred Supplier
Quantity Fields
Gross On-Hand Quantity (total physical units in all locations)
Reserved / Allocated Quantity (committed to open orders)
Available Quantity (on hand minus reserved)
In-Transit Quantity (received, not yet put away)
Quarantine / Hold Quantity (awaiting quality inspection)
Damaged / Non-Sellable Quantity
Net Available Quantity (available minus in-transit-out)
Location Detail
- Warehouse / Facility Code and Name
- Zone / Aisle / Rack / Bin Location
- Pallet ID (if pallet tracking is used)
- License Plate Number (if LPN tracking)
- Primary vs. Overflow Location Flag
- Location Capacity (max units or pallets)
- Location Utilization % (current ÷ capacity)
Lot and Traceability Fields
- Lot Number / Batch Number
- Receipt Date (date this lot was received into stock)
- Production Date / Manufacture Date
- Expiry Date / Best-Before Date
- Country of Origin
- Supplier / Vendor Reference
- Recall / Hold Status
Demand and Coverage Metrics
- Average Daily Usage (trailing 30, 60, 90 days)
- Days On Hand (DOH) — on-hand quantity ÷ average daily usage
- Weeks of Supply
- Open Purchase Order Quantity (on order, not yet received)
- Open Sales Order Quantity (committed, not yet shipped)
- Projected Stock-Out Date (based on current DOH and open orders)
- Reorder Trigger Flag (yes/no based on reorder point comparison)
Multi-Warehouse Consolidation
- Total On Hand Across All Locations
- By-Location Breakdown
- Inter-Location Transfer In-Transit
- Network Available Quantity (consolidated available across all locations)
Common Filters and Parameters
- As-Of Date / Time — real-time or point-in-time historical snapshot
- Warehouse / Location — single warehouse, location group, or all locations
- Item / SKU — single item detail or all-items summary
- Product Category / Product Line — filter by category hierarchy
- Supplier / Vendor — all items from a specific supplier
- ABC Class — A items only, B items only, or all
- Stock Status — sellable only, all statuses, holds and quarantine only
- Lot Number — specific lot detail or all lots for an item
- Expiry Date Range — items expiring within X days
- DOH Threshold — items with less than X days of supply remaining
- Reorder Flag — show only items at or below reorder point
- Zero Stock Flag — show only items with zero on-hand (stockouts)
- Excess Stock Flag — show items with DOH exceeding X days (overstocked)
- 3PL Client — filter by client for 3PL operator reporting
Common Reporting Challenges
Real-Time vs. Batch Update Lag
Many ERP and WMS systems update inventory records in batch — processing transactions every hour, every few hours, or overnight. During these windows, the inventory on hand report reflects stale data. For businesses with high transaction velocity — high-volume distributors, 24/7 warehouses, or same-day fulfillment operations — batch update lag creates a persistent gap between reported quantities and actual stock. Building real-time or near-real-time inventory transaction feeds is the architectural solution, but it requires data pipeline infrastructure beyond what standard ERP reports provide.
Multi-System Reconciliation
When the WMS manages physical location and movement while the ERP manages financial valuation and order management, the on-hand quantities in the two systems must agree at all times. In practice, they frequently do not — due to timing differences in posting, adjustment transactions that post to one system but not the other, or integration gaps. Producing an inventory on hand report that is trusted by both operations and finance requires a reconciled data model that sources from both systems and flags discrepancies automatically.
Reserved vs. Available Quantity Confusion
Many inventory systems show on-hand quantity but do not clearly distinguish available quantity from reserved quantity. Operations teams who fulfill orders based on gross on-hand rather than net available routinely create over-commitment situations — promising delivery on inventory that is already allocated to other orders. Building an inventory on hand report that clearly shows gross, reserved, and available in distinct columns — and that updates allocations in real time as orders are created and modified — requires more sophisticated data architecture than a standard ERP inventory report provides.
Multi-Unit-of-Measure Complexity
Items that are received in one unit of measure (case), stored in another (each), and sold in a third (pallet) require unit-of-measure conversion logic throughout the inventory on hand report. An item showing 50 cases on hand means something very different from 50 each. Incorrect UOM handling is one of the most common sources of inventory reporting errors and purchasing mistakes.
Lot and Expiry Tracking at Scale
Food, pharma, chemical, and cosmetics businesses must track inventory at the lot level — showing not just how many units are on hand, but which lots they came from, when those lots were received, and when they expire. Standard inventory on hand reports from most ERP systems do not provide clean lot-level on-hand data in a format that supports FEFO rotation management, near-expiry alerts, or regulatory compliance reporting.
Shrinkage and Unrecorded Adjustments
Physical inventory counts frequently reveal that actual quantities differ from what the system shows — items that have been damaged, consumed, mislocated, or stolen without a recorded transaction. These shrinkage variances are only visible when a physical count is conducted and compared to the on-hand record. Building automated variance reporting that compares cycle count results to on-hand records — and flags unexplained shrinkage patterns by location or item category — turns physical counts from a reconciliation exercise into an operational intelligence tool.
Kitting and BOM-Driven Availability
For businesses that assemble or kit products from components, the inventory on hand report must be extended to show not just component quantities but assembled-product availability — how many finished units can be built from current component stock. This BOM explosion and kit availability calculation is not provided by standard ERP on-hand reports and requires custom reporting logic.
Automation and Scheduling Options
- Real-Time ERP / WMS Data Feed We build SQL Server data pipelines that extract inventory transaction data from your ERP or WMS in real time or on a frequent refresh schedule — receiving, shipping, transfers, adjustments, returns — and update the inventory on hand database automatically, without manual report runs or data exports.
- Available Quantity Calculation Engine We build available quantity calculation engines that derive net available stock by subtracting all active allocations — open sales orders, production reservations, transfer commitments — from gross on-hand quantity. Available quantity is recalculated every time an allocation is created, modified, or closed, ensuring the available quantity figure is always current.
- Lot and Expiry Tracking Layer We build lot-level inventory tracking data models that maintain on-hand quantities at the lot level — with receipt dates, expiry dates, and lot-level movement history — supporting FIFO, FEFO, and LEFO rotation reporting and near-expiry alert automation.
- Multi-Warehouse Consolidation We build multi-location inventory consolidation pipelines that aggregate on-hand data from all warehouse facilities — including locations running different WMS or ERP instances — into a unified SQL Server inventory database, producing both locationlevel and network-level on-hand reports from the same data model.
- Reorder Point Alert Automation We build reorder point monitoring queries that identify items at or below their reorder point at each reporting cycle and generate automated purchase requisition alerts — delivered to buyers by email or surfaced in a Power BI reorder dashboard — without manual review of every SKU.
- Daily and Weekly Scheduled Delivery Inventory on-hand summaries, reorder alerts, and zerostock notifications can be scheduled for automatic generation and email delivery — to warehouse managers, buyers, customer service teams, and finance directors — on daily or weekly schedules.
Delivery Methods
SSRS (SQL Server Reporting Services)
Parameterized inventory on-hand reports that any buyer or warehouse manager can run for their own product line, location, or supplier — filtering by any combination of parameters — without IT involvement. Scheduled subscriptions deliver daily on-hand snapshots and reorder alerts automatically.
Crystal Reports
Many ERP and WMS platforms generate inventory reports through Crystal Reports templates. ReportingGuru modernizes Crystal Reports inventory on-hand packages — improving location detail, adding lot traceability, incorporating available quantity calculations, and migrating to SSRS or Power BI where appropriate.
Power BI
Interactive inventory on-hand dashboards with real-time stock position views, location heat maps showing inventory concentration, DOH trend analysis, reorder point monitoring, ABC class distribution, and multi-warehouse comparison. Refreshed from the SQL Server inventory data model on a defined schedule.
Excel Automation
Excel inventory on-hand workbooks driven by live SQL Server data — for purchasing teams that work in Excel for buying decisions, for finance teams that need inventory data for balance sheet reporting, and for lenders requiring borrowing base certificate support — automatically populated without manual ERP exports.
Scheduled PDF / Email Delivery
Daily on-hand summary reports, weekly reorder alerts, and zero-stock notifications automatically emailed to the appropriate recipients — warehouse managers, buyers, customer service leads, 3PL clients — with location-specific and product-linespecific filtering by recipient.
Customer / Client Portal Integration
For 3PL operators, we integrate inventory on-hand reports with client-facing portals — giving each client real-time, self-service access to their own inventory position across all storage locations, without submitting data requests to the warehouse operations team.
Related Report Pages
Budget vs actual reporting connects directly to every financial, GL, and investor reporting workflow ReportingGuru supports. Explore the complete library of related solutions:
Financial & GL Reporting Pages
Balance Sheet — Automate balance sheet reporting alongside budget vs actual analysis — actual asset, liability, and equity balances vs. budgeted positions with lender covenant ratio tracking.
Income Statement / Profit & Loss — P&L actuals that feed every revenue and expense line in every budget vs actual report — with departmental drill-down, cost allocation, and EBITDA variance analysis.
Statement of Cash Flows — Cash flow actuals vs. projected cash flows and free cash flow forecasts — the liquidity and capital deployment dimension of budget vs actual management.
Trial Balance — The validated, exception-checked GL data that feeds every actual line in every budget vs actual report — the foundational data layer for all variance analysis.
Investor & Fund Reporting Pages
Financial Statements & KPI Reporting — Fund and portfolio company financial statements incorporating inventory on-hand balances and turnover metrics.
Fund Performance Reporting — Days-on-hand, stockout rates, and inventory turnover tracked as PE portfolio company operational KPIs.
Inventory & Warehouse Pages
Inventory & Warehouse Reporting — Overview of all inventory and warehouse reporting solutions from ReportingGuru.
Inventory Aging — Days-in-stock by item and lot — the obsolescence detection tool that identifies items requiring LCM write-down consideration.
Inventory Valuation — The financial value of aged inventory — cost method valuation by lot that determines the dollar impact of the obsolescence reserve.
Frequently Asked Questions
What is the difference between on-hand quantity, available quantity, and reserved quantity?
On-hand quantity is the total physical units of an item in the warehouse — every unit recorded in inventory regardless of its allocation status. Reserved quantity (also called allocated or committed quantity) is the portion of on-hand stock that has already been committed to open customer orders, production orders, or transfer requests and is no longer available for new commitments. Available quantity is on-hand quantity minus reserved quantity — the amount that can genuinely be promised to new orders. A clear inventory on-hand report shows all three quantities in separate columns.
How can inventory on-hand reports be made real-time?
Real-time inventory on-hand reporting requires a data architecture that captures inventory transactions — receipts, shipments, transfers, adjustments — as they occur and immediately updates the reporting database. We build SQL Server data pipelines that process inventory transactions from the WMS or ERP event stream in near-real-time — so that inventory on-hand reports reflect completed transactions within minutes, not hours or days.
Can inventory on-hand reports cover multiple warehouses simultaneously?
Yes. We build multi-location inventory consolidation pipelines that pull stock data from all warehouse locations — even locations on different WMS or ERP instances — into a centralized SQL Server database. Reports can be viewed at the individual location level, by region, or as a network-wide consolidated view. Items that exist across multiple locations show both the location-specific quantities and the total network available quantity.
How are lot numbers and expiry dates tracked in the inventory on-hand report?
We build lotlevel inventory tracking data models that maintain on-hand quantities at the lot level — recording the receipt date, expiry date, production date, and supplier lot reference for each lot. On-hand reports can be filtered by lot number or expiry date range, sorted by FEFO (First Expiry, First Out) sequence, and configured to generate automatic alerts for lots approaching a defined expiry threshold.
Can the report show reorder alerts for items below their reorder point?
Yes. We build reorder point monitoring logic that compares the current available quantity to each item’s defined reorder point — factoring in safety stock, open purchase order quantities, and average daily usage — and flags items that require purchasing action. Reorder alerts can be surfaced in a Power BI dashboard, included as a filtered section of the inventory on-hand report, or delivered as a daily automated email to buyers.
How does the inventory on-hand report connect to the balance sheet?
The inventory on-hand quantities are the quantity input to the inventory valuation report — which calculates the financial value of all stock by applying a cost method (FIFO, LIFO, weighted average) to the onhand quantities. This valuation becomes the inventory asset balance on the balance sheet. An inventory on-hand report with inaccurate quantities produces an incorrect inventory valuation, which produces an inaccurate balance sheet.
Can on-hand reports be produced for 3PL clients in their own branded format?
Yes. We build per-client inventory on-hand report templates for 3PL operators — each formatted with the client’s preferred data layout, column structure, and branding — generated automatically from the SQL Server inventory data model and delivered to each client through a secure portal or scheduled email. Each client sees only their own inventory data.