ElyxAI

Inventory Management in Excel: On Hand and Reorder

Logistics ManagerInventory ManagementFree Template

This inventory sheet is the warehouse list. One row is one SKU: the quantity on hand, the minimum you set, and the reorder, which is the minimum minus the quantity on hand when the quantity is below that minimum. A pharmacy batch, with an expiry date, is a different sheet. This page does not track lots. You type the quantity you counted. Elyx can add the reorder on the export you already have. The trial lasts 7 days, with no card.

The Problem

The quantity on hand and the point where you reorder are often in two files. You notice the short item when the order is already late. Keep both numbers on the SKU row. The reorder is the gap under the minimum. The sheet does not place the purchase order.

Benefits

One row is one SKU, with quantity on hand and a minimum.

The reorder is the minimum minus the quantity, when the quantity is under the minimum.

You type the quantity you counted.

A blank quantity stays blank.

Batch and expiry stay on the pharmacy stock sheet.

Step-by-Step Tutorial

1

Create the table structure

Start by creating a new Excel workbook and set up the main columns for inventory tracking. Define headers in the first row: Product ID, Product Name, Category, Current Stock, Minimum Stock Level, Unit Price, Total Value, Warehouse Location, and Last Updated Date. These columns will form the foundation of your inventory management system.

Use Ctrl+T to convert your data range into a structured table, which will automatically apply formatting and make formulas more readable with column references.

2

Add sample inventory data

Populate your template with realistic inventory data for different products across your warehouse. Include at least 10-15 sample products with varying stock levels, categories (Electronics, Raw Materials, Finished Goods), and prices. This realistic data will help you test your formulas and understand how the template performs with actual logistics scenarios.

Use consistent formatting for dates (MM/DD/YYYY) and currency values to ensure calculations work correctly and data sorting functions properly.

3

Calculate Total Inventory Value

Create a formula in the 'Total Value' column that multiplies the Current Stock quantity by the Unit Price for each product. This shows the monetary value of each item in your inventory and helps with financial reporting and asset valuation. This calculation is essential for understanding your warehouse's total asset value.

=D2*F2

Format this column as currency (e.g., $0.00) by right-clicking, selecting Format Cells, and choosing Currency format for better readability.

4

Add Stock Status indicator with IF formula

Create a new column called 'Stock Status' that automatically alerts you when inventory levels are critically low. Use an IF formula to compare Current Stock against Minimum Stock Level and display 'Low Stock', 'Adequate', or 'Overstocked' based on predefined thresholds. This helps logistics managers prioritize reordering decisions.

=IF(D2<E2,"Low Stock",IF(D2>E2*2,"Overstocked","Adequate"))

Use conditional formatting with color coding (red for Low Stock, yellow for Overstocked, green for Adequate) to make status alerts immediately visible at a glance.

5

Create a Summary Dashboard with SUMIF

Build a summary section below your main table that provides key metrics for inventory management. Use SUMIF formulas to calculate total inventory value by category, count products with low stock, and sum quantities by warehouse location. This dashboard gives logistics managers quick insights without scrolling through detailed data.

=SUMIF(C:C,"Electronics",H:H) for total value by category, or =SUMIF(J:J,"Low Stock",1) to count low stock items

Place your dashboard in a separate area (e.g., columns K-M) with clear labels and borders to distinguish it from the main inventory table.

6

Add VLOOKUP for product information lookup

Create a reference section where you can quickly look up product details by Product ID. Set up a VLOOKUP formula that searches for a Product ID in column A and returns corresponding information like Product Name, Category, or Current Stock. This feature is useful for verifying inventory data or generating picking lists.

=VLOOKUP(O2,$A$2:$I$100,3,FALSE) to find Product Name by ID

Use absolute references ($A$2:$I$100) for your lookup table so the range doesn't change if you copy the formula to other cells.

7

Calculate reorder quantity with nested formulas

Create a 'Reorder Quantity' column that automatically calculates how much inventory needs to be ordered based on current stock levels and minimum requirements. Use a nested IF formula that considers both the minimum stock level and optimal order quantities to ensure efficient purchasing decisions. This reduces manual calculation and prevents stockouts.

=IF(D2<E2,E2*3-D2,0) to order enough units to reach 3x minimum stock when below threshold

Adjust the multiplier (e.g., 3x) based on your lead times and storage capacity, faster delivery allows lower safety stock multipliers.

8

Add data validation for category selection

Protect data integrity by adding dropdown lists to the Category column using Data Validation. Create a list of valid categories (Electronics, Raw Materials, Finished Goods, Packaging, etc.) that users can select from instead of typing manually. This prevents spelling errors and ensures consistent categorization across your inventory.

Go to Data > Data Validation > List, and enter your categories separated by commas, or reference a separate list on another sheet for easier maintenance.

9

Create a Reorder Alert report using filters and SUMIF

Build a separate worksheet called 'Reorder Alert' that automatically pulls all products with low stock using filtered data and SUMIF formulas. This report shows logistics managers which items need immediate attention and the total cost of required orders. Update this report weekly or set it to refresh automatically for proactive inventory management.

=SUMIF(J:J,"Low Stock",H:H) to calculate total value of items needing reorder

Use AutoFilter on your main table (Data > AutoFilter) and filter Stock Status = 'Low Stock' to quickly generate this report manually, or create a pivot table for automated updates.

10

Implement tracking and audit columns

Add final columns for Last Updated Date, Updated By, and Notes to create an audit trail for inventory changes. Use the TODAY() function to automatically stamp when records are modified, and require staff to enter their name for accountability. This ensures data accuracy and helps trace inventory discrepancies to their source.

=TODAY() in the Last Updated Date column to automatically show current date

Protect these audit columns (Format > Cells > Protection) and enable sheet protection (Tools > Protect Sheet) so only authorized personnel can modify critical tracking information.

Template Features

Real-time Stock Level Monitoring

Automatically calculates current inventory by subtracting outbound shipments from inbound receipts, alerting managers when stock falls below minimum thresholds

=SUM(Receipts!C:C)-SUM(Shipments!C:C)

Reorder Point Automation

Triggers automatic purchase order recommendations when inventory reaches predefined reorder levels, preventing stockouts and overstock situations

=IF(CurrentStock<=ReorderPoint, "Order Now", "Adequate Stock")

Inventory Turnover Ratio Calculation

Measures how efficiently inventory is being managed by calculating the ratio of cost of goods sold to average inventory value

=COGS/AVERAGE(BeginningInventory, EndingInventory)

Conditional Color-Coding by SKU Status

Automatically highlights critical items (red), low stock (yellow), and optimal levels (green) for quick visual assessment during daily operations

Expiration Date Tracking with Auto-Alerts

Flags items nearing expiration dates and calculates days remaining, helping prevent waste and ensure FIFO compliance

=IF(ExpirationDate-TODAY()<=30, "Alert: Expires Soon", "OK")

Inventory Valuation Report

Automatically calculates total inventory value using weighted average cost method, providing accurate financial reporting and budget forecasting

=SUMPRODUCT(Quantity, UnitCost)

Concrete Examples

Stock Level Monitoring Across Multiple Warehouses

Thomas, a Logistics Manager for a distribution center, oversees inventory across 3 regional warehouses. He needs to track stock levels daily to prevent stockouts and identify overstocking situations that tie up capital.

Warehouse A - SKU#2847 (Industrial Bearings): Current Stock: 450 units, Reorder Point: 200 units, Lead Time: 5 days, Unit Cost: $12.50. Warehouse B - Same SKU: Current Stock: 85 units (below reorder point). Warehouse C - Same SKU: Current Stock: 680 units (excess).

Result: A dashboard showing all warehouses with color-coded alerts (red for below reorder point, yellow for overstocking >150% of reorder point). Automatic calculation suggests transferring 200 units from Warehouse C to B, and a purchase order recommendation for 300 units for Warehouse A. Total inventory value across locations: $17,937.50.

Supplier Lead Time and Delivery Performance Tracking

Sarah manages relationships with 8 key suppliers and needs to monitor their reliability. Delayed deliveries impact her warehouse operations and customer fulfillment rates. She tracks on-time delivery percentage and average lead time variance.

Supplier A: 47 orders in Q1, 45 on-time deliveries (95.7%), avg lead time 6 days vs promised 5 days. Supplier B: 52 orders, 48 on-time (92.3%), avg variance +2 days. Supplier C: 38 orders, 36 on-time (94.7%), avg variance -0.5 days.

Result: A performance scorecard ranking suppliers by reliability. Conditional formatting highlights Supplier B in yellow (below 95% threshold). A trend chart shows Supplier A improving month-over-month. The template calculates an 'Overall Reliability Score' (weighted by order volume) and flags Supplier B for performance review discussion. Export-ready report for quarterly supplier meetings.

ABC Analysis for Inventory Prioritization and Storage Allocation

Marco, overseeing a 50,000-unit inventory, needs to classify products by value and demand to optimize warehouse layout and picking efficiency. High-value, fast-moving items (A-class) should be in prime picking zones.

Product Category A: 12 SKUs representing $487,000 annual value (42% of total), 68% of order volume. Category B: 34 SKUs representing $398,000 (34% of total), 25% of volume. Category C: 89 SKUs representing $215,000 (24% of total), 7% of volume.

Result: The template auto-categorizes all 135 SKUs using cumulative percentage analysis. A visual breakdown shows A-items occupy 8% of warehouse space but generate 42% of revenue, these get premium shelf locations. B-items get standard zones. C-items are stored in deep storage with slower access. Space allocation recommendation: 40% of high-traffic area to A-items, 35% to B-items, 25% to C-items. Expected picking efficiency improvement: 23% reduction in average pick time.

Pro Tips

Real-Time Stock Level Alerts with Conditional Formatting

Set up dynamic color-coded alerts to instantly identify stock levels below reorder points. Create a helper column with IF formulas comparing current stock to minimum thresholds, then apply conditional formatting with color scales. This eliminates manual monitoring and prevents stockouts. Use Ctrl+Shift+L to quickly toggle AutoFilter and sort by alert status.

=IF(B2<C2,"REORDER",IF(B2<C2*1.5,"LOW","OK"))

VLOOKUP Chain for Multi-Warehouse Inventory Consolidation

Consolidate inventory across multiple warehouse sheets using nested VLOOKUP or INDEX/MATCH formulas. This allows you to pull stock quantities from different locations into one master dashboard without manual copying. Combine with SUMIF to get total available inventory by SKU across all warehouses instantly.

=SUMIF(Warehouse1!A:A,A2,Warehouse1!B:B)+SUMIF(Warehouse2!A:A,A2,Warehouse2!B:B)

Pivot Tables for Trend Analysis & ABC Inventory Classification

Create a Pivot Table from your transaction history (Ctrl+F1 > Insert > Pivot Table) to analyze stock movement by product, location, and time period. Use it to classify inventory into A (high-value/fast-moving), B (medium), and C (slow-moving) categories. This data-driven approach optimizes storage space and capital allocation. Refresh with Ctrl+Shift+F5.

Automated Inventory Aging Report with DAYS Function

Calculate how long items have been in stock using DAYS function to identify obsolete or slow-moving inventory. Flag items exceeding target holding periods for clearance decisions. Combine with conditional formatting to highlight aging risks before they become write-offs.

=DAYS(TODAY(),B2)

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Excel inventory management logistics, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions

See also