Inventory System with Stock Levels & Reorder Alerts

An inventory system tracks stock levels, warns when items run low, and shows movement in and out. You build it in Excel and use Copilot and ChatGPT for the reorder alerts and stock formulas.

Table of Contents

• Project Overview
• Features
• Prerequisites
• Folder Structure
• Step-by-Step Development
• Complete Source Code
• Running the Project
• Testing
• Common Errors
• Improvements
• Challenge Exercises

Project Overview

• What it does: It holds products with current stock, records incoming and outgoing moves, and flags low stock.
• Where it is used: Warehouses, shops, and labs that manage physical items.
• What you learn: SUMIFS for movement, current stock math, conditional formatting, and AI prompts.

Features

• Product list with current stock
• Incoming and outgoing logs
• Low-stock alert with red highlight
• Reorder quantity suggestion
• AI-written restock note

Prerequisites

• Microsoft Excel (Microsoft 365)
• ChatGPT account
• Basic tables and SUMIFS knowledge

Folder Structure

inventory-system.xlsx
│
├── Sheet: Products   (SKU, Name, Stock, ReorderLevel)
├── Sheet: Moves     (Date, SKU, Type, Qty)
├── Sheet: Report    (low-stock list)
└── Module: InvMacros (optional VBA)

• Products: current stock per item.
• Moves: every in/out transaction.
• Report: items needing reorder.
• InvMacros: optional helpers.

Step-by-Step Development

Step 1 – Product and move tables

What are we building?

Two tables: Prod (products) and Move (transactions with Type In/Out).

Why do we need it?

Stock is the starting number plus ins minus outs.

How do we build it?

Create both tables. Add a dropdown for Type with In,Out.

=SUMIFS(Move[Qty], Move[SKU], A2, Move[Type], "In")

• What it does: sums quantities where SKU matches and Type is In.
• Why needed: total items received for that product.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"How do I add a dropdown list In,Out in an Excel column?"

Step 2 – Current stock formula

What are we building?

A Stock column that computes starting plus incoming minus outgoing.

Why do we need it?

It shows the live quantity on hand.

How do we build it?

Assume starting stock in C2.

=C2
 +SUMIFS(Move[Qty], Move[SKU], A2, Move[Type], "In")
 -SUMIFS(Move[Qty], Move[SKU], A2, Move[Type], "Out")

• What it does: starting stock plus ins minus outs for the SKU in A2.
• Important lines: the two SUMIFS pull only the matching SKU and type.
• Why needed: one formula gives current stock from the logs.

Use AI (Copilot & ChatGPT)

Ask Copilot:

"Calculate current stock as starting plus SUMIFS In minus SUMIFS Out for SKU A2."

Step 3 – Low-stock alert

What are we building?

A rule that turns stock red when it drops below the reorder level.

Why do we need it?

You must reorder before selling out.

How do we build it?

Select the Stock column, then Conditional Formatting > New Rule > Use a formula:

=B2 < D2

Where B2 is Stock and D2 is ReorderLevel.

• What it does: shades the cell red when stock is under the reorder level.
• Important line: B2 < D2 is the trigger condition.
• Why needed: instant visual warning.

Use AI (Copilot & ChatGPT)

Ask ChatGPT:

"Write a conditional formatting formula that highlights a cell when stock is less than reorder level."

Step 4 – AI restock suggestion

What are we building?

A note telling you what to order.

Why do we need it?

Saves time deciding quantities.

How do we build it?

List low-stock SKUs and quantities, then ask ChatGPT.

Use AI (Copilot & ChatGPT)

Paste the low-stock list into ChatGPT:

"For each item, suggest a reorder quantity to reach at least 50 units. Keep it in a table."

Complete Source Code

Stock formulas

=SUMIFS(Move[Qty], Move[SKU], A2, Move[Type], "In")
=SUMIFS(Move[Qty], Move[SKU], A2, Move[Type], "Out")
=C2 + InSum - OutSum

Conditional formatting

=B2 < D2

AI prompts

ChatGPT: "Dropdown In, Out."
Copilot: "Current stock starting plus In minus Out for SKU A2."
ChatGPT: "Conditional format when stock below reorder level."
ChatGPT: "Suggest reorder quantity to reach 50 each."

Running the Project

1. Open inventory-system.xlsx.
2. Enter products and moves (In/Out).
3. Stock computes; low items turn red.
4. Expected output: live stock numbers and a clear reorder alert.

Testing

Test Case 1

Input: Start 10, In 5, Out 3. Expected Output: Stock 12. Explanation: 10 + 5 − 3.

Test Case 2

Input: Stock 4, ReorderLevel 10. Expected Output: Cell red. Explanation: 4 < 10 triggers the rule.

Test Case 3

Input: No moves for a SKU. Expected Output: Stock equals starting number. Explanation: Both SUMIFS return 0.

Common Errors

Stock negative

Problem: Shows below zero. Reason: More Out than In plus start. Solution: Check move entries; add a receiving In.

Alert not showing

Problem: Low item not red. Reason: Rule points at the wrong cell. Solution: Re-apply the formula rule to the Stock column.

SUMIFS counts both types

Problem: Stock wrong. Reason: Type spelled differently from dropdown. Solution: Use the dropdown so "In"/"Out" stay exact.

Improvements

• Add supplier and lead-time columns
• Auto-email low stock with Power Automate
• Use Power Query to import scans
• Ask ChatGPT for economic order quantity
• Export reorder list to PDF

Challenge Exercises

1. Add a value column (stock × unit cost) for total inventory value.
2. Write a formula that shows days of stock left based on average daily out.
3. Use ChatGPT to write VBA that emails the low-stock report.
4. Build a chart of stock level per product.
5. Let ChatGPT suggest par levels from past move history.