An inventory tracker helps you answer a simple question: How much of each item do I have right now? A useful tracker should also show which items are running low, where they are stored, and why the recorded quantity changed.
In this project, you will build a Microsoft Excel workbook for three practice inventory items. Instead of typing over the current quantity each time something changes, you will record every opening balance, receipt, use, return, and correction in a stock-movement log. Excel will calculate the quantity on hand from that history.
By the end, you will have an item list, a movement log, a stock summary, reorder alerts, and a clear record of how each quantity was calculated.
What the finished inventory tracker includes
The downloadable workbook contains three main working areas:
- Item Master: one row for each item, including its SKU, unit, location, reorder point, and active status.
- Movements: one row for each opening balance, receipt, use, return, or correction.
- Stock Summary: the calculated quantity on hand, reorder point, and status for each item.
The practice example tracks three items:
- SKU-A ends with 20 units on hand.
- SKU-B ends with 6 units on hand after an opening balance, receipt, use, and correction.
- SKU-C ends with 0 units on hand after all four available units are used.
Use the accompanying Inventory Tracker workbook to follow the project and inspect the finished setup. It includes made-up practice data, working formulas, drop-down controls, a quantity chart, instructions, documented checks, a failure-and-repair example, and version history.
Workbook note: The file uses made-up practice data that contains no real personal information. Make a separate copy before entering your own inventory. Review formulas, units, reorder points, permissions, and compatibility before using the workbook for live business work.
Start with the item master
Open the Item Master sheet. Keep one row per item and give every item a unique SKU. A SKU is a stable identifier that helps Excel connect the item to its stock movements even if the item name changes later.
| Field | What to enter |
|---|---|
| SKU | A unique code such as SKU-A, SKU-B, or SKU-C. |
| Item | A clear item name such as Fastener Kit or Safety Gloves. |
| Unit | The measurement used for the item, such as box, pair, pack, or each. |
| Location | Where the item is stored, such as Shelf A or Shelf B. |
| Reorder Point | The quantity at which the item should be reviewed for replenishment. |
| Active | Whether the item is still being tracked. |
Do not use the item name as the only identifier. Names can be shortened, corrected, or reused. The SKU should remain stable.
Record every stock movement
Open the Movements sheet. Each row explains one change in quantity.
The practice workbook uses these movement types:
- Opening: the starting quantity when tracking begins.
- Receipt: stock that was received and added.
- Use: stock that was consumed, issued, or removed.
- Return: stock that was returned to inventory.
- Correction: an adjustment made after a physical count or discovered error.
The workbook uses positive quantities for stock coming in and negative quantities for stock going out. For example, a receipt of five units is entered as 5, while use of seven units is entered as -7.
Use one sign convention throughout the workbook. Mixing positive and negative rules makes the quantity on hand difficult to verify.
Understand the SKU-B example
SKU-B shows why a movement log is more useful than typing a current quantity directly into the item list.
| Movement | Quantity | Running result |
|---|---|---|
| Opening balance | 10 | 10 |
| Receipt | 5 | 15 |
| Use | -7 | 8 |
| Correction after physical count | -2 | 6 |
The final calculated quantity is 6. The correction remains visible, so anyone reviewing the workbook can see why the recorded quantity changed from 8 to 6.
Do not rewrite the earlier receipt or use records to force the total to match. Add a correction with a clear reason instead.
Calculate quantity on hand
The Stock Summary sheet uses SUMIFS, an Excel function that adds values meeting one or more conditions.
The formula follows this pattern:
=SUMIFS(Movements[Quantity],Movements[SKU],A6)
This tells Excel to add every movement quantity connected to the SKU shown in the summary row.
The practice workbook produces these on-hand quantities:
| SKU | Item | Quantity on hand | Reorder point | Expected status |
|---|---|---|---|---|
| SKU-A | Fastener Kit | 20 | 6 | OK |
| SKU-B | Safety Gloves | 6 | 10 | Reorder |
| SKU-C | Cleaning Pads | 0 | 4 | Reorder |
Add reorder and negative-stock alerts
The status formula compares the calculated quantity with the reorder point:
=IF(C6<0,"Negative stock",IF(C6<=D6,"Reorder","OK"))
The formula returns:
- Negative stock when the calculated quantity falls below zero.
- Reorder when the quantity is at or below the reorder point.
- OK when the quantity remains above the reorder point.
A Reorder result is a prompt to review the item. It is not an automatic purchasing decision. Before ordering, you may still need to consider supplier lead time, minimum order size, expected demand, available storage, budget, and whether the item is still active.
Review the tracker in a useful order
Use the Stock Summary as a starting point, then inspect the underlying records.
- Check negative quantities first. A negative result may indicate a missing receipt, duplicate use record, incorrect SKU, or timing issue.
- Review items at or below the reorder point. Confirm that the reorder point still makes sense.
- Look for unknown or blank SKUs. Every movement should connect to an item in the Item Master.
- Check units. Do not mix boxes, individual pieces, pairs, or packs without a documented conversion.
- Review corrections. Each correction should explain what was found and why the change was needed.
Try five practical checks
| Check | What to do | Expected result |
|---|---|---|
| Normal movement history | Enter the supplied opening balances, receipts, uses, and correction. | SKU-A is 20, SKU-B is 6, and SKU-C is 0. |
| Exact reorder point | Set an item’s quantity equal to its reorder point. | The status shows Reorder. |
| Unknown SKU | Try to enter a movement for a SKU that is not approved. | The drop-down or review process prevents or flags the entry. |
| Quantity check | Add the opening balance and all later movements for one SKU. | The result matches the Stock Summary. |
| Formula recovery | In a copy, replace one SUMIFS formula and then restore it. | The correct quantity returns. |
Customize the tracker for your inventory
After you understand the practice workbook, make a copy and adapt it carefully.
- Replace the three practice items with your own item list.
- Create a unique SKU for every item.
- Use one unit of measure for each SKU.
- Update storage locations and reorder points.
- Add approved SKUs to the movement-entry drop-down.
- Create a Stock Summary row for every added SKU.
- Confirm that new records remain inside the Excel Tables.
- Review formulas and chart ranges after adding items.
The workbook is a completed learning example, not an unlimited inventory application. Adding a new SKU requires updates to the approved SKU list and the summary. It does not automatically handle purchasing, supplier management, barcode scanning, inventory valuation, taxes, or accounting.
Common mistakes to avoid
- Typing current quantity into the item list: This removes the history that explains how the quantity changed.
- Using changing item names as identifiers: Use a stable SKU instead.
- Mixing units: A box and an individual item are not interchangeable unless you document the conversion.
- Editing old movements to fix a count: Add a correction with a reason.
- Allowing duplicate movement IDs: Every movement should have its own identifier.
- Treating Reorder as an automatic purchase: Review demand, timing, cost, and availability first.
- Ignoring negative stock: Investigate the source records before accepting the result.
What this project helps you practice
This project gives you experience with Excel Tables, stable identifiers, drop-down controls, SUMIFS, IF formulas, movement-based calculations, reorder logic, corrections, and quantity checks. It also shows why a visible transaction history is more reliable than repeatedly overwriting a current balance.
Continue the project workflow
- Clean an inventory dataset before importing it when item names, SKUs, units, or dates are inconsistent.
- Report inventory measures each month when you want a recurring view of stock levels and exceptions.
- Turn the inventory tracker into a spreadsheet case study if you want to explain the structure, calculations, and checks behind the workbook.