Running out of stock costs you sales. Overstocking ties up cash you could use elsewhere. A working inventory system hits the middle — you always have what you need, and you’re not sitting on piles of slow-moving product.
The good news: you don’t need expensive software to get there. A well-built Google Sheets inventory tracker will serve most small businesses well, especially in the early stages.
What a Good Inventory Tracker Does
Before building anything, know what you’re solving for. A useful inventory spreadsheet answers four questions:
- What do I currently have? — quantity on hand for every item
- What do I need to reorder? — items below your minimum stock level
- What has this stock cost me? — total value of on-hand inventory
- What’s moving vs. what’s sitting? — turnover by product
Simple enough to maintain weekly. Detailed enough to make real decisions.
The Template Structure
Set up your spreadsheet with these columns:
Tab 1: Master Inventory
| Column | What to Enter |
|---|---|
| Item ID | Unique code (e.g., SKU-001) |
| Product Name | Clear description |
| Category | Group type (e.g., Raw Materials, Finished Goods) |
| Unit | Each, box, lb, etc. |
| Current Quantity | On-hand right now |
| Reorder Point | Minimum before you need to order |
| Reorder Quantity | How much you order at a time |
| Unit Cost | What you paid per unit |
| Total Value | =Current Quantity × Unit Cost |
| Supplier | Name and contact |
| Lead Time | Days from order to delivery |
| Last Updated | Date of last count |
The reorder point formula: Lead time (days) × average daily usage. If you use 5 units per day and your supplier takes 7 days to deliver, your reorder point is 35 units. When quantity drops to 35, you order.
Tab 2: Transactions Log
Every time stock comes in or goes out, log it:
| Column | What to Enter |
|---|---|
| Date | Transaction date |
| Item ID | Links to master inventory |
| Product Name | For readability |
| Transaction Type | Received / Sold / Adjusted / Written Off |
| Quantity | Positive for received, negative for sold |
| Unit Cost | Purchase cost (for received items) |
| Notes | PO number, reason for adjustment, etc. |
This log creates your audit trail and feeds your master inventory counts.
Tab 3: Reorder Alerts (Auto-Generated)
Use a simple formula to pull items that need reordering:
In cell A2, enter this array formula (Ctrl+Shift+Enter in Excel, or it works natively in Google Sheets):
=FILTER(MasterInventory!A:L, MasterInventory!E:E <= MasterInventory!F:F)This pulls every row where Current Quantity ≤ Reorder Point. Your reorder list, always up to date, zero manual effort.
Setting Up in 30 Minutes
Step 1 (10 min): Enter your products Copy the Master Inventory structure above. Enter every product you stock. Set unit costs from your last purchase invoices.
Step 2 (5 min): Set reorder points For each item, calculate reorder point: lead time × daily usage. If you’re not sure, start with a conservative estimate and adjust after a few months of data.
Step 3 (10 min): Enter your current counts Do a physical count. Enter real numbers. Your spreadsheet is only as accurate as the count you start with.
Step 4 (5 min): Set up the reorder alerts tab Add the FILTER formula. Confirm it pulls the right items.
From here, maintenance is about 10–15 minutes per week.
Maintaining Accuracy Over Time
The biggest risk with inventory spreadsheets isn’t building them — it’s keeping them accurate. Spreadsheets drift when people skip logging transactions or forget to update quantities.
Build the habit of logging every transaction in real time. Every shipment received, every item sold or used. Not at the end of the day, not at the end of the week — when it happens.
Do a physical count monthly. Compare your spreadsheet quantities to actual shelf counts. When they differ (and they will), log an adjustment and note why: theft, damage, miscounting, free samples given.
Lock the formulas. Protect your formula cells (Total Value, Reorder Alerts) from accidental edits. In Google Sheets: Format → Protected ranges.
Tracking Inventory Value and COGS
For financial purposes, you need to know the value of your inventory and your Cost of Goods Sold (COGS) — the cost of items actually sold.
Average Cost Method (simplest): Track your average cost per unit: (Total value of existing stock + cost of new purchases) ÷ total units. This smooths out price fluctuations over time.
FIFO (First In, First Out): Assume you sell the oldest stock first. More accurate for perishables or items with significant price fluctuations over time. More complex to track.
For most small businesses starting out, average cost is accurate enough and far simpler to maintain.
COGS formula: Beginning inventory value + Purchases − Ending inventory value = COGS
Track this monthly. It feeds directly into your P&L statement.
When to Upgrade to Inventory Software
A spreadsheet works until it doesn’t. Signs it’s time to upgrade:
- You’re processing more than 20–30 transactions per day
- Multiple people need to update inventory simultaneously
- You need inventory to sync with your point-of-sale system
- You’re losing money to stockouts or expired/obsolete inventory
- Reconciling your spreadsheet takes more than an hour per week
QuickBooks tracks inventory automatically as sales are recorded and generates reorder alerts. Shopify’s inventory system handles e-commerce. Square’s POS has built-in inventory tracking for retail.
The upgrade pays for itself when the time and error cost of the spreadsheet exceeds the software cost.
Frequently Asked Questions
How do I handle inventory for items I make myself? Track both raw materials and finished goods as separate inventory items. When you produce a batch, log the raw materials as consumed (subtract from raw material inventory) and the finished units as received (add to finished goods inventory).
What if I sell in different channels (store, online, markets)? Add a Location column to your master inventory. Track quantities per location. When you move inventory between locations, log it as a transfer (one location decreases, the other increases).
How do I deal with items that get damaged or go missing? Log them as adjustments with a “Written Off” transaction type and note the reason. This keeps your counts accurate and gives you data on shrinkage over time.
Do I need a separate spreadsheet for each location? Not necessarily — a Location column in a single spreadsheet is usually simpler than managing multiple files. Multiple files mean reconciliation headaches.
My inventory changes daily and I can’t keep up. What do I do? Focus first on your most valuable items. Track your top 20% of products by value (likely 80% of your total inventory value) daily, and do weekly spot checks on the rest. Perfect tracking of everything is less valuable than accurate tracking of what matters most.
5 Google Sheets Every Small Business Needs
Cash flow, P&L, mileage log, invoice tracker, and payroll — all free.
No spam. Unsubscribe any time.