Excel with AI for Inventory Teams: Stock Analysis, Reorder Prediction & Warehouse Reports

Manage stock smarter in Excel with Microsoft Copilot and ChatGPT — analyze current levels, predict when to reorder, and report across warehouses.

Table of Contents

• 1. Stock Analysis
• 2. Reorder Prediction
• 3. Warehouse Reports

How to Use AI in This Tutorial

Every task below follows the same two-step AI pattern:

1. Microsoft Copilot (inside Excel) — open the Copilot pane from the ribbon (or press Alt + the Copilot key). Type your request in plain English; Copilot builds the table, pivot, or chart for you.
2. ChatGPT (alongside Excel) — when you need an exact formula or logic, paste your column layout into ChatGPT and ask for the precise Excel formula, then paste it back into the cell.

Use Copilot to build and explore, ChatGPT to get the exact formula. Both are shown in every section.

1. Stock Analysis

Stock analysis reviews what you hold: how much is in stock, its value, and how fast it moves (turnover). It surfaces dead stock and stockouts.

Stock value (Units × Unit Cost):

=Units * UnitCost

Inventory turnover (COGS ÷ Average Stock Value):

=COGS / AverageStockValue

Copilot prompt:

"From this stock list, calculate total inventory value and flag items with zero sales in 90 days as dead stock."

ChatGPT prompt:

"Write an Excel formula that flags a product as Low Stock when units fall below its reorder point in another column."

=IF(Units < ReorderPoint, "Low Stock", "OK")

Simple explanation: Multiplying units by cost gives the money tied up in each item. The IF compares current units to a reorder point so you see shortages before they happen.

Common mistake: Forgetting units and cost in different rows. Keep one product per row with all metrics in columns.

2. Reorder Prediction

Reorder prediction estimates when stock will run out and how much to order, using average daily sales and lead time.

Days of stock left:

=Units / AverageDailySales

Recommended order quantity (cover lead time + safety stock):

=AverageDailySales * (LeadTimeDays + SafetyDays) - Units

Copilot prompt:

"Predict reorder date for each item using average daily sales and supplier lead time, and list what to order this week."

ChatGPT prompt:

"Write an Excel formula that returns the reorder date by adding days-of-stock-remaining to today's date."

=TODAY() + (Units / AverageDailySales)

Simple explanation: Dividing units by daily sales tells you how many days you're covered. Adding that to today gives the date you'll hit zero — your reorder deadline.

Best practice: Let Copilot pull AverageDailySales from your sales history instead of typing it, so the prediction stays current.

3. Warehouse Reports

Warehouse reports roll stock up by location so you can balance inventory and spot concentration risk.

Stock value per warehouse (column B = Warehouse):

=SUMIF(B:B, "WH-North", ValueColumn)

Units on hand per warehouse:

=SUMIF(B:B, "WH-North", UnitsColumn)

Copilot prompt:

"Create a warehouse report showing total units and value per location, with a chart of stock distribution."

ChatGPT prompt:

"Write an Excel formula that finds the warehouse holding the most of a given product."

=INDEX(WarehouseColumn, MATCH(MAX(UnitsColumn), UnitsColumn, 0))

Simple explanation: MATCH finds the row with the highest units, and INDEX returns that row's warehouse name — a quick way to see where each product is concentrated.