Objective

Create an inventory control system to monitor stock levels.

Worksheets

  1. Product_Master
  2. Purchase
  3. Sales
  4. Stock
  5. Inventory_Dashboard

Data Fields

  • Product ID
  • Product Name
  • Category
  • Opening Stock
  • Purchase Qty
  • Sales Qty
  • Current Stock
  • Reorder Level
  • Unit Cost
  • Stock Value

Formula

Current Stock

=Opening_Stock+Purchase_Qty-Sales_Qty

Stock Value

=Current_Stock*Unit_Cost

Stock Status

=IF(Current_Stock<=Reorder_Level,”Reorder”,”Available”)

Practice

Use Conditional Formatting to highlight:

  • 🔴 Low Stock
  • 🟡 Reorder Required
  • 🟢 Available

Create an inventory dashboard with stock-value analysis.

PreviousNext