Stock IN OUT & BALANCE Tracker in Excel for inventory management with detailed stock data and status indicators.

The image shows an Excel spreadsheet titled "Stock IN OUT & BALANCE Tracker in Excel - Inventory Management", designed to track inventory levels based on stock inflows (purchases), outflows (sales), and current balances. The goal is likely to understand the logic behind this tracker, identify any issues, or determine how it calculates values.

Let’s break down the components of the sheet and explain how it works:

---

🔍 Overview of the Spreadsheet

#### 1. Summary Section (Row 2)
- Purchase Value: Rs. 16,800
- Sales Value: Rs. 5,790
- Stock Value: Rs. 74,195
- Month: OCT, 2022

These are summary values that reflect the financial status of inventory for the month.

> ⚠️ Note: The "Stock Value" seems inconsistent with the sum of purchases and sales unless there's a cost per unit involved. We'll come back to this.

---

#### 2. Stock IN (Columns A–C)
This section logs incoming inventory.
| Date | Product Description | Qty |
|------------|---------------------|-----|
| 10/24/2022 | Product AB | 120 |
| 10/25/2022 | Product EF | 20 |
| 10/26/2022 | Product AB | 24 |

This means:
- 120 units of Product AB were purchased on Oct 24.
- 20 units of Product EF on Oct 25.
- 24 more units of Product AB on Oct 26.

---

#### 3. Stock OUT (Columns D–F)
This records outgoing inventory (sales).
| Date | Product Description | Qty |
|------------|---------------------|-----|
| 10/24/2022 | Product CD | 15 |
| 10/25/2022 | Product AB | 25 |
| 10/26/2022 | Product AB | 12 |

So:
- 15 units of Product CD sold on Oct 24.
- 25 units of Product AB sold on Oct 25.
- 12 units of Product AB sold on Oct 26.

---

#### 4. Stock Balance (Columns G–I)
This shows current stock levels.
| Product Description | Op. Stock | Balance Stock |
|---------------------|-----------|---------------|
| Product AB | 5 | 112 |
| Product CD | 20 | 5 |
| Product EF | 12 | 32 |
| ... | ... | ... |

Op. Stock (Opening Stock): Initial stock at the beginning of the month.
Balance Stock: Current stock after accounting for IN and OUT.

Let’s verify one example: Product AB
- Opening Stock = 5
- In: 120 + 24 = 144
- Out: 25 + 12 = 37
- Expected Balance = 5 + 144 – 37 = 112 ✔ Matches

Similarly, Product CD:
- Opening = 20
- In: 0
- Out: 15
- Balance = 20 – 15 = 5 ✔ Matches

Product EF:
- Opening = 12
- In: 20
- Out: 0
- Balance = 12 + 20 = 32 ✔ Matches

✔ So far, the balance calculations appear correct.

---

#### 5. Safety Stock & Stock Status (Columns J–K)

| Safety Stock | Stock Status |
|--------------|------------------|
| 20 | Stock Sufficient |
| 50 | Re Order |
| 30 | Stock Sufficient |
| ... | ... |

- Safety Stock: Minimum threshold below which reordering is needed.
- Stock Status:
- If `Balance Stock >= Safety Stock` → "Stock Sufficient"
- Else → "Re Order"

Let’s check:
- Product AB: Balance = 112, Safety = 20 → 112 ≥ 20 → ✔ Sufficient
- Product CD: 5 vs 50 → 5 < 50 → ✘ Re Order
- Product EF: 32 vs 30 → 32 ≥ 30 → ✔ Sufficient
- Product GH: 3 vs 80 → Re Order
- Product IJ: 18 vs 70 → Re Order
- Product KL: 14 vs 30 → Re Order
- Product MN: 55 vs 25 → Sufficient
- Product OP: 60 vs 150 → Re Order
- Product QR: 75 vs 60 → Sufficient
- Product ST: 30 vs 30 → Sufficient (equal)

✔ All statuses seem logically correct.

---

💡 Potential Issue: Stock Value (Rs. 74,195)

This value appears suspicious because:
- Total Purchase Value = Rs. 16,800
- Total Sales Value = Rs. 5,790
- But Stock Value is Rs. 74,195, which is much higher than purchases.

This suggests that Stock Value might not be calculated as:
```
Stock Value = Purchase Value - Sales Value
```

Instead, it likely represents the total value of current inventory, i.e.,:
```
Stock Value = Σ (Balance Stock × Cost per Unit)
```

But cost per unit is missing from the sheet.

Unless we assume a unit cost, we cannot validate this number.

For example:
Suppose Product AB has a cost of Rs. 10/unit:
- Balance = 112 → Value = 112 × 10 = 1,120
Repeat for all products...

But since no unit costs are shown, this value (Rs. 74,195) may be manually entered or derived from another source.

---

🛠️ How This Sheet Works

1. Data Entry:
- Users enter new purchases under "Stock IN".
- Enter sales under "Stock OUT".

2. Auto-Calculation:
- Balance Stock is updated using formulas like:
```
=Op. Stock + SUMIF(Stock IN, Product, Qty) - SUMIF(Stock OUT, Product, Qty)
```
- Or possibly using Pivot Tables or VLOOKUPs.

3. Status Logic:
```excel
=IF(Balance Stock >= Safety Stock, "Stock Sufficient", "Re Order")
```

4. Summary Values:
- Purchase Value: Sum of all IN quantities × unit cost (assumed or hidden).
- Sales Value: Sum of all OUT quantities × selling price.
- Stock Value: Sum of all Balance Stock × cost per unit.

---

✔ Recommendations

1. Add Unit Cost Column: To accurately compute Stock Value.
2. Use Formulas Instead of Manual Input: For Balance Stock and Status.
3. Highlight Re-Order Items: Use conditional formatting (already done).
4. Add Validation: Prevent negative stock.
5. Link Summary Cells: Automatically calculate total purchase/sales values.

---

✔ Final Answer: Problem Solved?

Yes — the spreadsheet is functioning correctly for tracking stock movement and determining reorder needs. The only ambiguity is the Stock Value (Rs. 74,195), which likely requires additional data (unit costs) to verify.

If you have a specific question about this sheet (e.g., “How do I calculate Stock Value?” or “Why is Product CD showing ‘Re Order’?”), feel free to ask!