building an inventory tracking sheet

---

1. Key Features of the Inventory Tracking Sheet

Your inventory tracking sheet should include the following:

1. Product Details:
2. Product name, SKU (Stock Keeping Unit), ASIN, supplier details.
3. Stock Levels:
4. Current inventory, inbound inventory, units sold, and units available.
5. Reorder Point:
6. Automatically calculate when to reorder based on sales trends and lead times.
7. Sales Tracking:
8. Monitor daily/weekly sales to forecast inventory needs.
9. Shipment Tracking:
10. Keep track of shipments sent to Amazon warehouses.

---

2. Setting Up the Sheet

Step 1: Create Columns for Product Details

• Columns for basic product information:
• Product Name: Name of the product.
• SKU: Stock Keeping Unit (unique identifier for inventory tracking).
• ASIN: Amazon Standard Identification Number.
• Supplier Name: Name of the supplier or manufacturer.

Example:

| Product Name | SKU | ASIN | Supplier Name |
|------------------------|-----------|---------------|----------------------|
| Bamboo Cutting Board | BC12345 | B08XY12345 | XYZ Supplies |
| Reusable Water Bottle | WB56789 | B07DF56789 | EcoGoods Inc. |

---

Step 2: Add Stock Levels and Sales Tracking

• Columns for tracking inventory:
• Starting Inventory: Units in stock at the start of a period.
• Units Sold: Total units sold during the period.
• Current Inventory: Remaining units in stock.
• Inbound Inventory: Units currently in transit to Amazon FBA warehouses.
• Total Available: Remaining inventory plus inbound inventory.

Example:

| Starting Inventory | Units Sold | Current Inventory | Inbound Inventory | Total Available |
|-------------------------|----------------|------------------------|------------------------|----------------------|
| 200 | 50 | 150 | 100 | 250 |

---

Step 3: Add Formulas for Automation

1. Calculate Current Inventory:
   Formula:
   [ {Current Inventory} = {Starting Inventory} - {Units Sold} ]
   Example:
   If Starting Inventory = 200 and Units Sold = 50, then:
   [ 200 - 50 = 150 ]

2. Calculate Total Available:
   Formula:
   [ {Total Available} = {Current Inventory} + {Inbound Inventory} ]
   Example:
   If Current Inventory = 150 and Inbound Inventory = 100, then:
   [ 150 + 100 = 250 ]

---

Step 4: Add Reorder Point Calculation

• Reorder Point: The inventory level at which you should place a new order.
• Formula:
  [ {Reorder Point} = ({Daily Sales Avg.} * {Lead Time (Days)}) + {Safety Stock} ]
  Example:
• Daily Sales Avg. = 10 units
• Lead Time = 15 days
• Safety Stock = 50 units
  [ (10 * 15) + 50 = 200 ]

Columns:

| Daily Sales Avg. | Lead Time (Days) | Safety Stock | Reorder Point | Status |
|-----------------------|----------------------|------------------|-------------------|------------------|
| 10 | 15 | 50 | 200 | Place Reorder |

---

Step 5: Add Shipment Tracking

Track shipments sent to FBA warehouses: - Shipment ID: Unique ID for each shipment.
- Shipment Date: When the shipment was sent.
- Expected Delivery Date: When it will reach the warehouse.
- Units Shipped: Number of units sent in the shipment.
- Status: Delivered, In Transit, Pending.

Example:

| Shipment ID | Shipment Date | Expected Delivery | Units Shipped | Status |
|------------------|-------------------|------------------------|-------------------|------------------|
| FBA12345 | Jan 15, 2025 | Jan 20, 2025 | 100 | Delivered |
| FBA12346 | Jan 20, 2025 | Jan 25, 2025 | 200 | In Transit |

---

3. Example Layout for Inventory Tracking Sheet

Here’s a complete example layout:

| Product Name | SKU | ASIN | Starting Inventory | Units Sold | Current Inventory | Inbound Inventory | Total Available | Daily Sales Avg. | Lead Time (Days) | Safety Stock | Reorder Point | Status |
|------------------------|-----------|---------------|-------------------------|----------------|------------------------|------------------------|----------------------|-----------------------|----------------------|------------------|-------------------|------------------|
| Bamboo Cutting Board | BC12345 | B08XY12345 | 200 | 50 | 150 | 100 | 250 | 10 | 15 | 50 | 200 | In Stock |
| Reusable Water Bottle | WB56789 | B07DF56789 | 300 | 120 | 180 | 50 | 230 | 12 | 20 | 60 | 300 | Reorder Soon |

---

4. Automate and Visualize with Conditional Formatting

Use conditional formatting to highlight critical inventory levels: - Low Inventory: Highlight rows in red when "Current Inventory" is below "Reorder Point."
- Healthy Inventory: Highlight rows in green when stock levels are above reorder thresholds.

---

5. Expand with Dashboards

Turn your inventory data into a dashboard for at-a-glance insights: - Use bar graphs for stock levels by product.
- Create a pie chart for product sales distribution.
- Add trendlines for sales growth over time.

---

6. Tools to Supplement Your Inventory Sheet

• RestockPro: Advanced inventory forecasting and tracking.
• SoStocked: Streamlined inventory management for FBA businesses.
• InventoryLab: Combines inventory tracking with profitability analysis.

---

❤ If you liked this, consider supporting us by checking out Tiny Skills - 250+ Top Work & Personal Skills Made Easy