What should I practice after reading this tutorial?

Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.

Which Excel version do I need for the formulas in this article?

Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.

Tutorials - 2026-01-21

Excel for Inventory Management

Track stock levels, set reorder points, and manage inventory efficiently with Excel.

Excel for Inventory Control

Excel is a powerful tool for inventory management—from basic stock tracking to sophisticated demand forecasting and ABC analysis. This guide covers everything you need to manage inventory effectively. See Excel Use Cases for more industries.

Basic Stock Tracking

Current Stock Level \\\ ' Simple stock calculation =StartingInventory + Received - Sold ' With returns =StartingInventory + Received + Returns - Sold - Damaged ' Running balance (for transaction log) =PreviousBalance + IF(Type="IN", Quantity, -Quantity) \\\ Stock Value Calculations \\\ ' Total inventory value =SUMPRODUCT(Quantities, UnitCosts) ' Value by category =SUMPRODUCT((CategoryCol=TargetCategory)*(QuantityCol)*(CostCol)) ' Average cost per unit =TotalInventoryValue / TotalUnits ' Weighted average cost =SUMPRODUCT(Quantities, UnitCosts) / SUM(Quantities) \\\ Stock Movement Summary \\\ ' Total received this month =SUMIFS(QuantityCol, TypeCol, "Received", DateCol, ">="&MonthStart, DateCol, "<="&MonthEnd) ' Total sold this month =SUMIFS(QuantityCol, TypeCol, "Sold", DateCol, ">="&MonthStart, DateCol, "<="&MonthEnd) ' Net change =TotalReceived - TotalSold \\\

Reorder Point Management

Calculating Reorder Points The reorder point tells you when to place a new order: \\\ ' Basic reorder point =AverageDailyUsage * LeadTimeDays ' With safety stock =AverageDailyUsage * LeadTimeDays + SafetyStock ' Safety stock calculation (based on service level) =NORM.S.INV(ServiceLevel) * STDEV(DailyUsage) * SQRT(LeadTimeDays) ' Example: 95% service level =NORM.S.INV(0.95) * STDEV(UsageRange) * SQRT(7) \\\ Reorder Alerts \\\ ' Simple alert =IF(CurrentStock<=ReorderPoint, "REORDER NOW", "OK") ' With urgency levels =IF(CurrentStock<=SafetyStock, "CRITICAL", IF(CurrentStock<=ReorderPoint, "REORDER", "OK")) ' Days of stock remaining =CurrentStock / AverageDailyUsage ' Conditional formatting formula (for red highlighting) =CurrentStock<=ReorderPoint \\\ Economic Order Quantity (EOQ) \\\ ' EOQ formula =SQRT((2 * AnnualDemand * OrderCost) / HoldingCostPerUnit) ' Example: 10,000 annual demand, $50 order cost, $2 holding cost =SQRT((2 * 10000 * 50) / 2) ' Result: 707 units per order ' Number of orders per year =AnnualDemand / EOQ ' Time between orders (days) =365 / NumberOfOrders \\\

ABC Analysis

ABC analysis categorizes inventory by value to prioritize management efforts: A items: Top 20% of items = 80% of value (high priority) B items: Next 30% of items = 15% of value (medium priority) C items: Bottom 50% of items = 5% of value (low priority) Setting Up ABC Analysis \\\ ' Step 1: Calculate item value =Quantity * UnitCost ' Step 2: Calculate percentage of total =ItemValue / SUM(AllItemValues) ' Step 3: Sort by value (descending) and calculate cumulative % =SUM($C$2:C2) / SUM($C$2:$C$100) ' Step 4: Assign classification =IF(CumulativePercent<=0.80, "A", IF(CumulativePercent<=0.95, "B", "C")) \\\ ABC Summary Statistics \\\ ' Count of A items =COUNTIF(ClassificationCol, "A") ' Value of A items =SUMIF(ClassificationCol, "A", ValueCol) ' Percentage of total value =SUMIF(ClassificationCol, "A", ValueCol) / SUM(ValueCol) \\\

Inventory Turnover Analysis

Turnover Calculations \\\ ' Inventory turnover ratio =CostOfGoodsSold / AverageInventory ' Average inventory =(BeginningInventory + EndingInventory) / 2 ' Days inventory outstanding (DIO) =365 / InventoryTurnover ' Or directly: =(AverageInventory / CostOfGoodsSold) * 365 \\\ Turnover by Category \\\ ' Category turnover =SUMIF(CategoryCol, "Electronics", COGSCol) / AVERAGEIF(CategoryCol, "Electronics", InventoryCol) ' Slow-moving items (turnover < 2) =IF(Turnover<2, "Slow Moving", "OK") ' Dead stock (no sales in 90 days) =IF(DaysSinceLastSale>90, "Dead Stock", "Active") \\\

Demand Forecasting

Moving Average \\\ ' 3-month moving average =AVERAGE(OFFSET(CurrentCell, -3, 0, 3, 1)) ' Or with specific cells =(Month1Sales + Month2Sales + Month3Sales) / 3 ' Weighted moving average (recent months weighted more) =(Month1*0.5 + Month2*0.3 + Month3*0.2) \\\ Trend Analysis \\\ ' Linear trend forecast =FORECAST(FuturePeriod, SalesRange, PeriodRange) ' Growth rate =(CurrentPeriod - PreviousPeriod) / PreviousPeriod ' Compound growth rate =(EndValue/StartValue)^(1/NumberOfPeriods) - 1 \\\ Seasonal Adjustment \\\ ' Seasonal index =PeriodAverage / OverallAverage ' Seasonally adjusted forecast =BaselineForecast * SeasonalIndex ' Example: If December index is 1.4 and baseline is 1000 =1000 * 1.4 ' Forecast: 1400 units \\\

Stock Aging Analysis

Age Calculation \\\ ' Days in inventory =TODAY() - ReceivedDate ' Age bucket =IF(DaysInInventory>180, "180+ Days", IF(DaysInInventory>90, "91-180 Days", IF(DaysInInventory>30, "31-90 Days", "0-30 Days"))) \\\ Aging Report Summary \\\ ' Value by age bucket =SUMIFS(ValueCol, AgeBucketCol, "180+ Days") ' Percentage of old stock =SUMIFS(ValueCol, DaysCol, ">180") / SUM(ValueCol) ' Obsolescence risk flag =IF(AND(DaysInInventory>180, Turnover<1), "HIGH RISK", "") \\\

Warehouse Location Tracking

Location Management \\\ ' Full location code =Warehouse & "-" & Aisle & "-" & Shelf & "-" & Bin ' Find item location =XLOOKUP(SKU, SKUCol, LocationCol, "Not Found") ' Items in specific location =COUNTIF(LocationCol, "WH1-A*") ' Available space =TotalCapacity - SUMIF(LocationCol, TargetLocation, QuantityCol) \\\

Inventory Valuation Methods

FIFO (First In, First Out) \\\ ' FIFO cost of goods sold ' (Requires tracking individual lot costs) ' Sell oldest inventory first ' Simplified FIFO ending inventory =SUM(MostRecentPurchases) ' Up to current quantity \\\ LIFO (Last In, First Out) \\\ ' LIFO cost of goods sold ' Sell newest inventory first ' Simplified LIFO ending inventory =SUM(OldestPurchases) ' Up to current quantity \\\ Weighted Average \\\ ' Weighted average cost =SUMPRODUCT(Quantities, Costs) / SUM(Quantities) ' COGS using weighted average =UnitsSold * WeightedAverageCost ' Ending inventory value =UnitsOnHand * WeightedAverageCost \\\

Key Inventory Formulas Summary

Metric Formula Current Stock =Starting + Received - Sold Inventory Value =SUMPRODUCT(Qty, Cost) Reorder Point =DailyUsage × LeadTime + SafetyStock Days of Stock =CurrentStock / DailyUsage EOQ =SQRT((2×Demand×OrderCost)/HoldingCost) Turnover Ratio =COGS / AverageInventory Days Inventory =365 / Turnover ABC Class =IF(CumPct<=0.8,"A",IF(CumPct<=0.95,"B","C"))

Related Resources

Excel Use Cases Math & Trig Functions → SUMPRODUCT Function → IF Function → COUNTIFS Function → XLOOKUP Function →

Apply this tutorial in your workbook

Copy one example from this article into a blank sheet. Change the sample ranges to match your column letters. Press F2 and Enter after edits so Excel recalculates. Compare your result to the expected output in the article. Related hubs: Excel functions directory · Formula guides · Function comparisons

Frequently asked questions

What should I practice after reading this tutorial? Rebuild the main example on a copy of your file, then change one argument at a time to see how the result changes. That builds muscle memory faster than rereading the steps.
Which Excel version do I need for the formulas in this article? Most steps work in Excel 2016 and later. If the article mentions FILTER, UNIQUE, or XLOOKUP, you need Microsoft 365 or Excel 2021 — check the linked function pages for compatibility.