Inventory Management Project
Build a dependable Excel inventory system that converts item masters, purchase receipts, issues, returns and adjustments into live stock balances, reorder alerts, ageing analysis, supplier insights and management-ready dashboards.
After Completing This Project, You Will Be Able To
Combine Advanced Excel techniques into a practical stock-control and inventory-analysis solution.
Structure Inventory Data
Design reliable item, supplier, warehouse and stock-movement tables with stable business keys.
Calculate Stock
Build transparent calculations for opening, receipts, issues, closing, available and committed quantities.
Control Reordering
Use demand, lead time, safety stock and service priorities to create actionable reorder alerts.
Analyze Inventory
Measure ageing, movement, supplier delivery, inventory value and management exceptions.
1 Understand the Inventory Management Project Brief
Assume that a growing distribution business receives goods from multiple suppliers, stores them across warehouses and issues stock against customer orders or internal requirements. Management wants one Excel workbook that can answer: What is in stock? What requires reordering? Which items are not moving? Where is inventory value concentrated? Which supplier or warehouse requires attention?
Project boundary
This chapter teaches Excel-based inventory modelling and analysis. Valuation methods, accounting treatment, reorder policy, acceptable shortages, quality rules and approval limits differ by organization. Use only approved business and accounting policies when applying the workbook in a real operation.
Practical Experiment 1: Define Inventory Questions and Controls
Convert the operational requirement into a clear project document.
List purchase, warehouse, sales, finance and management users and their decisions.
Document units, warehouses, transaction types, lead times, reorder rules and adjustment approvals.
Specify stock register, reorder list, ageing report, supplier report and dashboard requirements.
2 Design the Workbook and Data Architecture
A professional inventory workbook separates reference data, transaction records, calculations, reports and controls. This prevents mixed-grain errors and allows the same movement table to support stock position, purchasing, ageing and management reports.
Master Layer
Items, categories, units, warehouses, suppliers and approved planning parameters.
Transaction Layer
Receipts, issues, returns, transfers, adjustments and purchase-order records.
Calculation Layer
Stock balances, availability, reorder points, ageing bands and inventory values.
Output Layer
Stock register, reorder plan, exception report, supplier analysis and dashboard.
| Recommended Table | Important Fields | Grain / Key |
|---|---|---|
| Item Master | Item Code, description, category, unit, standard cost, reorder rule, status | One row per item; unique Item Code |
| Warehouse Master | Warehouse Code, location, owner, capacity, status | One row per warehouse |
| Supplier Master | Supplier Code, item, standard lead time, minimum order, preferred status | One row per approved supplier-item combination |
| Stock Movements | Transaction ID, date, type, item, warehouse, quantity, unit cost, reference | One row per stock movement |
| Purchase Orders | PO Number, supplier, item, order date, promised date, ordered and received quantity | One row per PO line |
3 Build and Validate the Item Master
The Item Master is the foundation of inventory control. Item Code should be unique and permanent. Description, category, unit, status, cost, preferred supplier and planning parameters should follow controlled standards.
=COUNTIF(Item[Item Code],[@[Item Code]])A result greater than 1 identifies a duplicated item code.
=IF(OR([@[Item Code]]="",[@Description]="",[@Unit]=""),"Review","Ready")Checks mandatory item-master fields.
=XLOOKUP([@[Supplier Code]],Supplier[Supplier Code],Supplier[Supplier Name],"Missing")Retrieves supplier information and exposes unmatched codes.
=IF([@[Standard Cost]]<=0,"Invalid Cost","OK")Flags zero or negative standard costs before valuation.
Recommended master-data controls
- Use controlled dropdown lists for category, unit, warehouse, status and ABC class.
- Do not reuse an old item code for a different product.
- Keep base unit and conversion factors clearly documented.
- Store minimum order quantity, pack size, lead time and safety stock as approved planning inputs.
- Deactivate obsolete items without deleting historical records.
Practical Experiment 2: Audit the Item Master
Check whether the item database is ready for inventory processing.
Use COUNTIF and conditional formatting to locate repeated codes and descriptions.
Filter blank units, categories, costs, reorder parameters and supplier mappings.
Create an item-quality report with issue, owner, correction and status.
4 Record and Standardize Stock Movements
Every inventory change should enter one Stock Movements table with a unique Transaction ID, valid date, approved type, item, warehouse and quantity. A signed quantity column makes calculations easier: receipts are positive and issues are negative.
=SWITCH([@Type],"Receipt",[@Quantity],"Issue",-[@Quantity],"Return In",[@Quantity],"Return Out",-[@Quantity],"Adjustment +",[@Quantity],"Adjustment -",-[@Quantity],0)Converts transaction types into signed stock quantities.
=[@[Item Code]]&"|"&TEXT([@Date],"yyyymmdd")&"|"&[@[Reference No]]Creates a review key for detecting possible duplicate transactions.
=IF(OR([@Date]="",[@[Item Code]]="",[@Warehouse]="",[@Quantity]<=0),"Review","Ready")Validates the minimum fields required for a stock transaction.
=XLOOKUP([@[Item Code]],Item[Item Code],Item[Unit],"Missing")Returns the approved unit and identifies invalid items.
Movement types to control
- Opening: Approved starting balance at the implementation cut-off.
- Receipt: Supplier or production receipt supported by reference documents.
- Issue: Customer dispatch, consumption or approved internal issue.
- Return: Customer return, supplier return or internal return with reason.
- Transfer: Separate negative and positive lines for source and destination warehouses.
- Adjustment: Authorized correction for count difference, damage, expiry or other reason.
Practical Experiment 3: Create a Controlled Movement Register
Build a clean movement table for multiple transaction types.
Create sample receipts, issues, returns, transfers and adjustments.
Use SWITCH or XLOOKUP against a transaction-type mapping table.
Identify duplicates, missing references, invalid items, negative dates and transfer mismatches.
5 Calculate Stock Balance, Availability and Value
Closing stock should be calculated from authorized movements, not typed manually. Use SUMIFS by item and warehouse, then compare the result with committed quantity, physical counts and valuation inputs.
=SUMIFS(Movement[Signed Qty],Movement[Item Code],[@[Item Code]],Movement[Warehouse],[@Warehouse],Movement[Date],"<="&ReportDate)Calculates closing quantity by item, warehouse and reporting date.
=[@[Closing Qty]]-[@[Committed Qty]]Calculates available-to-promise quantity before planned receipts.
=[@[Closing Qty]]*[@[Standard Cost]]Calculates inventory value using an approved standard-cost method.
=[@[Physical Qty]]-[@[System Qty]]Calculates physical-count variance requiring investigation.
Important balance views
| Measure | Meaning | Use |
|---|---|---|
| System Closing | Net signed movement through the reporting date | Book-stock position |
| Available Stock | Closing less committed or reserved quantity | Promise and allocation decisions |
| Projected Stock | Available plus confirmed incoming less planned demand | Near-term planning |
| Inventory Value | Quantity multiplied by approved valuation rate | Financial and working-capital analysis |
| Count Variance | Physical quantity less system quantity | Cycle-count and loss control |
Practical Experiment 4: Build the Stock Position Report
Calculate stock for every item and warehouse as of a selected date.
Prepare unique item-warehouse combinations with UNIQUE or a reference table.
Use SUMIFS to calculate receipts, issues, adjustments, closing and available stock.
Confirm opening plus net movement equals closing and investigate negative stock.
6 Calculate Reorder Levels and Suggested Purchase Quantity
Reordering should balance service availability with working-capital cost. A practical model combines average demand, lead time, safety stock, current availability, open purchase orders, pack size and minimum order quantity.
=([@[Average Daily Demand]]*[@[Lead Time Days]])+[@[Safety Stock]]Calculates a basic reorder point.
=MAX(0,[@[Reorder Point]]-[@[Available Stock]]-[@[Confirmed Incoming]])Calculates the preliminary purchase requirement.
=CEILING(MAX([@[Suggested Qty]],[@[Minimum Order Qty]]),[@[Pack Size]])Rounds the order recommendation to approved commercial quantities.
=IF([@[Available Stock]]<=0,"Stock-Out",IF([@[Available Stock]]<=[@[Reorder Point]],"Reorder","OK"))Creates a clear replenishment status.
Planning controls
- Use representative demand periods and exclude one-time abnormal demand where approved.
- Keep supplier lead times current and review actual versus standard lead time.
- Do not count unapproved or overdue purchase orders as dependable incoming stock.
- Use different safety-stock policies for critical, seasonal, expensive and slow-moving items.
- Document manual overrides with reason, owner and approval.
Practical Experiment 5: Build an Automated Reorder List
Create an action list for items that require purchase review.
Calculate average daily or weekly issue quantity from a selected history period.
Use lead time, safety stock, availability and confirmed incoming quantities.
Filter stock-outs and reorder items, then rank by criticality, shortage and value.
7 Analyze Movement, Ageing, Value and Suppliers
Inventory analysis should identify where attention and working capital are needed. Use PivotTables, formulas and controlled classification rules to turn stock records into operational decisions.
| Analysis | Example Calculation | Management Question |
|---|---|---|
| Days Since Last Issue | =ReportDate-MAXIFS(Movement[Date],Movement[Item Code],[@[Item Code]],Movement[Type],"Issue") | Which items have stopped moving? |
| Inventory Turnover | Annual usage cost ÷ average inventory value | How efficiently is inventory being used? |
| Days of Cover | Available stock ÷ average daily demand | How long can current stock support demand? |
| Supplier Lead-Time Variance | Actual receipt date − promised date | Which suppliers create replenishment risk? |
| Stock Accuracy | 1 − absolute count variance ÷ physical quantity | How reliable are system balances? |
Ageing interpretation
Inventory ageing can be defined by receipt lots, last movement, last issue or weighted age. Choose one method suitable for the business and state it clearly on the report. Do not present a last-issue report as exact lot ageing.
Practical Experiment 6: Build Inventory Analysis PivotTables
Create management reports from the stock position and movement tables.
Create category, ABC class, movement class, ageing band, warehouse and supplier fields.
Create PivotTables for value, movement, ageing, reorder status and supplier performance.
Write three findings and three practical actions supported by the reports.
8 Design the Inventory Management Dashboard
The dashboard should summarize stock health and make exceptions visible. Use aggregated KPIs on the main page and keep transaction-level evidence in supporting reports.
Recommended dashboard controls
Use a report-date selector, plus warehouse, category, supplier and ABC-class filters. Keep the dashboard focused: show only KPIs and visuals that support stock, purchase, transfer, disposal or investigation decisions.
Practical Experiment 7: Build the Interactive Inventory Dashboard
Create a one-screen dashboard for stock and purchase review.
Build cards for inventory value, stock-outs, reorder items, excess stock and ageing value.
Add movement trend, category value, warehouse status, ageing and supplier views.
Connect report date, warehouse, category and supplier filters to compatible reports.
9 Reconcile, Count and Release Inventory Reports
An inventory report is reliable only when transaction, quantity, warehouse and value controls are complete. Reconcile the workbook before distributing reorder or financial outputs.
Recommended release checks
- All item and warehouse codes match approved masters.
- No unexplained duplicate transaction IDs or references remain.
- Transfer-out and transfer-in quantities reconcile.
- Negative stock is reviewed and corrected through valid source transactions.
- Physical-count variances have evidence, reason and approval.
- Inventory value agrees with the approved costing method and control total.
- Reorder recommendations exclude inactive, blocked or obsolete items.
- Open purchase-order quantities are current and not duplicated.
- Dashboard KPIs reconcile to detailed stock and exception reports.
Practical Experiment 8: Prepare a Controlled Inventory Release Pack
Create the final report set and validate every total.
Prepare stock register, valuation report, reorder plan, ageing report and dashboard.
Match quantity and value totals across detail, PivotTables, dashboard and control sheet.
Lock formulas, record adjustments, preserve evidence and publish an approved version.
Choose the Right Inventory Workbook Architecture
Select the project situation to receive a practical design recommendation.
Build a Complete Inventory Management Workbook
Create a portfolio-ready solution for a distributor, retailer, manufacturer or service-spares operation.
Inventory Control, Replenishment and Analysis System
Create Item, Warehouse and Supplier master tables with validation and exception checks.
Record opening, receipt, issue, return, transfer and adjustment movements.
Calculate closing, committed, available, projected and physical-count variance quantities.
Calculate demand, lead-time requirement, safety stock, reorder point and suggested order quantity.
Create movement, ageing, ABC, stock-cover, supplier and warehouse analyses.
Build KPI cards, trends, rankings, filters and a management action table.
Create quantity, value, transfer, count, negative-stock and duplicate-transaction controls.
Add data dictionary, assumptions, refresh steps, exception ownership and final sign-off.
Complete These Inventory Project Tasks
Use a realistic sample dataset and preserve evidence of each task.
Include categories, units, costs, suppliers, lead times, pack sizes and status.
Include receipts, issues, returns, transfers and adjustments across at least two warehouses.
Detect duplicate items, invalid warehouses, missing references and incorrect quantities.
Show opening, receipts, issues, net movement, closing and available stock.
Use demand, lead time, safety stock, incoming quantity, pack size and minimum order quantity.
Create ABC, movement and ageing classifications with documented thresholds.
Measure promised versus actual delivery, short receipt and open-order ageing.
Create warehouse, category, movement, ageing, value and supplier reports.
Add KPI cards, trend charts, slicers and a priority action list.
Validate quantity and value totals and write three management recommendations.
Inventory Project Errors You Should Avoid
These mistakes can create incorrect stock, poor purchases and misleading financial reports.
Wrong Practices
- Typing closing stock manually
- Using duplicate or changing item codes
- Mixing different units without conversion
- Recording transfers as only one movement
- Deleting adjustment or return history
- Counting overdue purchase orders as confirmed stock
- Using arbitrary reorder quantities
- Ignoring negative stock and count variances
- Calling last-issue age exact lot ageing
- Publishing dashboards without reconciliation
Correct Practices
- Calculate balances from authorized movements
- Maintain permanent unique item codes
- Use approved base units and conversion rules
- Balance transfer-out and transfer-in records
- Preserve transaction history and audit evidence
- Validate incoming purchase quantities and dates
- Base reorder logic on demand, lead time and safety stock
- Investigate exceptions before release
- State the exact ageing method used
- Reconcile detail, PivotTables, dashboard and value totals
Test Your Inventory Project Understanding
Select one answer for each question, then submit the quiz.
1. What is the best source for calculating closing stock?
2. Which field should remain unique and stable for each item?
3. How should a warehouse transfer normally be recorded?
4. Which formula pattern is suitable for stock by item and warehouse?
5. A basic reorder point usually combines which elements?
6. What does available stock generally mean?
7. Which analysis helps identify items that have not been issued recently?
8. Which incoming quantity should be treated cautiously in reorder calculations?
9. What should happen when physical stock differs from system stock?
10. Which dashboard item is most actionable?
11. Why should ageing methodology be stated on the report?
12. What should be completed before releasing the inventory report?
Remember These Inventory Project Principles
Review the essentials before moving to the Financial Analysis Project.
Use Permanent Item Codes
Connect masters, movements, orders and reports with stable identifiers.
Record Every Movement
Calculate stock from authorized receipts, issues, returns, transfers and adjustments.
Control Units and Warehouses
Use approved units, conversions and balanced transfer transactions.
Reorder from Evidence
Use demand, lead time, safety stock, availability and reliable incoming quantity.
Convert Analysis into Action
Link movement, ageing, value and supplier insights to clear responsibilities.
Reconcile Before Release
Validate quantity, value, count variances, exceptions and dashboard totals.