Handmade Shop Inventory Tracker: Separating Supplies from Finished Goods

Handmade Shop Inventory Tracker: Separating Supplies from Finished Goods (Spreadsheet Setup + Reorder Alerts)

Keep supplies and finished goods on separate sheets, map BOMs, and use a reorder flag plus one alert to avoid production bottlenecks. This guide outlines sheets, formulas, and simple automation.

TL;DR: Keep supplies and finished goods on separate sheets with clear SKU mapping. Flow material usage from supplies into finished-goods cost and stock calculations. Add a simple reorder flag column and one alert so you reorder materials before production bottlenecks occur.

Handmade Shop Inventory Tracker: Separating Supplies from Finished Goods

Direct answer: Build two linked sheets, Supplies and Finished Goods, map a BOM (bill of materials) for each finished item, and use simple threshold flags to trigger reorder alerts.

This guide gives a practical spreadsheet layout, example formulas using placeholders, and simple alert options to keep materials moving and finished stock accurate. Expect clearer cost of goods sold tracking, fewer production surprises, and a setup you can maintain without special software.

Why separate supplies from finished goods?

Separating raw supplies from finished goods is practical, not fussy. You get:

• More accurate COGS by allocating materials into finished units cleanly.
• Clearer reordering, because you base purchases on material consumption rather than quirks in finished stock.
• Cleaner tax and inventory reporting when raw materials and finished inventory are treated differently.
• Better production planning, since you can spot material bottlenecks before customers wait.

When to separate sheets: if your products use discrete, trackable materials or you hold more than a handful of units, two-sheet tracking pays off. If you truly make one-off items with no repeatable materials, a single-sheet log may be enough.

Spreadsheet setup: essential sheets and structure (worked example included)

Required sheets and their roles:

• Supplies: Tracks material SKU, qty on hand, unit cost, reorder point, supplier.
• Finished Goods: Tracks assembled item SKU, qty on hand, sell price, cost per unit (pulled from BOM and supplies costs).
• BOM/Recipe: For each finished SKU, list material SKUs and quantities required per finished unit.
• Purchases: Log material purchases and received quantities with date and cost.
• Sales/Usage: Log finished goods sales or usage entries when you consume materials in production.
• Production/Assembly: Optional sheet to log assembly runs, quantities produced, and materials consumed, useful for batch builds.
• Summary/Dashboard: Rollups for low-stock flags, total inventory value, and alerts.

How the sheets relate:

• Supplies feeds BOM, BOM feeds Finished Goods cost calculations, Purchases update Supplies qty, Production/Usage deducts Supplies and increases Finished Goods, Sales deducts Finished Goods and records revenue.

Where to record transactions:

• Purchases increases Supplies.[qty_on_hand].
• Production/Assembly consumes materials per BOM, decreasing Supplies and increasing Finished Goods.[qty_on_hand].
• Sales/Usage logs finished-item sales, decreasing Finished Goods.[qty_on_hand].

Worked example, using placeholders:

1. Purchase received: a Purchases row adds to Supplies.[qty_on_hand].
2. BOM shows FG-XYZ needs [qty_used_per_unit] of MAT-ABC.
3. Production entry for 1 unit of FG-XYZ consumes [qty_used_per_unit] of MAT-ABC. Supplies.[qty_on_hand] reduces by [qty_used_per_unit], and Finished Goods.[qty_on_hand] increases by 1.
4. Sales entry for FG-XYZ reduces Finished Goods.[qty_on_hand] by 1, and COGS for that sale is the summed material costs from the BOM.

This loop shows purchases replenish supplies, production consumes supplies to create finished goods, and sales finalize cost recognition.

Key columns, formulas, and naming conventions

Recommended column headers:

• Supplies: SKU, Name, Unit, [qty_on_hand], Unit_Cost, Reorder_Point, Supplier, Notes
• Finished Goods: SKU, Name, [qty_on_hand], Sell_Price, Cost_per_Unit (formula), BOM_Link
• BOM/Recipe: Finished_SKU, Material_SKU, Quantity_per_Unit
• Purchases: Date, Material_SKU, Quantity_Received, Unit_Cost, Total_Cost, Reference
• Production/Assembly: Date, Finished_SKU, Quantity_Produced, Status, Notes
• Sales/Usage: Date, Finished_SKU, Quantity_Sold, Sale_Price, Reference
• Summary: Low_Stock_Count, Total_Inventory_Value, Open_Reorders

Example formula patterns with placeholders:

• Available basic calc on Supplies: Available = SUM(Purchases!Quantity_Received where Purchases!Material_SKU = Supplies!SKU) - SUM(Production!Quantity_Consumed where Production!Material_SKU = Supplies!SKU) - SUM(Adjustments) Example spreadsheet form: =SUMIFS(Purchases!C:C, Purchases!B:B, A2) - SUMIFS(Production!D:D, Production!B:B, A2)

• Pull unit cost from Supplies into BOM or Finished Goods: VLOOKUP example: =VLOOKUP(MaterialSKU, Supplies!A:E, 4, FALSE) INDEX-MATCH example: =INDEX(Supplies!Unit_Cost_Column, MATCH(MaterialSKU, Supplies!SKU_Column, 0))

• Calculate COGS per finished unit using BOM: =SUMPRODUCT(BOM!Quantity_per_Unit_Range * (VLOOKUP(BOM!Material_SKU_Range, Supplies!SKU_to_Cost_Table, Cost_Column_Index, FALSE))) In words, multiply each material quantity by that material's current unit cost and sum the results.

• Finished Goods qty on hand: =SUMIFS(Production!Quantity_Produced, Production!Finished_SKU, A2) - SUMIFS(Sales!Quantity_Sold, Sales!Finished_SKU, A2)

• Reorder flag formula (basic): =IF([qty_on_hand] <= [reorder_point], "Reorder", "")

Naming tips to avoid errors:

• Use short, consistent SKU prefixes such as MAT-### for materials and FG-### for finished goods.
• Keep column headers identical across sheets when you reference them in formulas.
• Avoid spaces in key named ranges or use consistent named ranges.

Reorder alerts: how to flag, deliver, and automate

Practical methods to surface low-stock items include conditional formatting, a Reorder_Flag column, and simple automation like a script or add-on. The following describes the common approaches.

Logical rule for a reorder flag:

• If [qty_on_hand] <= [reorder_point], flag with a formula like =IF([qty_on_hand] <= [reorder_point], "Reorder", "").

How to surface alerts on a dashboard or visually:

• Conditional formatting: Highlight the Supplies row when Reorder_Flag equals "Reorder" for an immediate visual cue.
• Summary rollup: Use FILTER or QUERY to list Supplies rows where Reorder_Flag = "Reorder".
• Dashboard widget: Show a COUNTIF(Supplies!Reorder_Flag_Column, "Reorder") and a linked mini-table of flagged rows.

Automation options and trade-offs:

• Built-in spreadsheet notifications: Many platforms can email when a sheet changes. It is low effort, but can be noisy and not specific to reorder logic.
• Script or add-on: A lightweight script that runs daily can collect rows where [qty_on_hand] <= [reorder_point] and send one aggregated email. This is more precise but requires a bit of maintenance.

Qualitative comparison:

• Manual visual checks are lowest tech and fine for tiny shops, but easy to miss when life gets busy.
• Spreadsheet notification rules are quick and require no code, but they may email on any edit rather than on threshold crossing.
• Scripted alerts give a scheduled report of actual reorder candidates and can include lead time and supplier info, but they need upkeep and credentials.

Refer to the worked example earlier to follow how a single supply unit flows from purchase to BOM consumption to finished stock, using placeholders like [qty_on_hand], [qty_used_per_unit], and [reorder_point].

Maintenance, workflows, and best practices

Suggested cadence:

• Daily: Record sales and production runs, update purchases when receipts arrive.
• Weekly: Quick reconciliation of Supplies quantities and update reorder points if stockouts recur.
• Monthly: Full stocktake and inventory valuation for accounting.

Change logs and roles:

• Keep an audit column that records who edited and why, such as Adjustment_Reason and Adjusted_By.
• After a sale: Update the Sales sheet so formulas decrement Finished Goods automatically.
• After production: Record a Production/Assembly row documenting materials consumed and finished quantity produced.
• For returns or damages: Create Adjustment entries in Supplies or Finished Goods with a reason so stock and accounting stay consistent.

Integrating with sales channels or POS later:

• Start with manual CSV imports if you use multiple sales channels.
• Keep SKU conventions consistent between your tracker and sales platforms to simplify imports and reconciliation.

FAQ

Q1: Can I track multiple suppliers for a single material?

A1: Yes, add a Supplier column on the Supplies sheet and a separate Supplier Master if you want lead times and preferred reorder sources.

Q2: How often should I review reorder points?

A2: Review reorder points when your lead times or average production volume changes, and perform a periodic stocktake (daily/weekly cadence is a workflow choice covered above).

Q3: Can this system handle made-to-order items or custom one-offs?

A3: Yes, use a temporary BOM row or production log to deduct used supplies when you finish a custom order and record its cost to the Finished Goods sheet.

Q4: What’s the simplest way to turn a reorder flag into an email or task?

A4: Use the spreadsheet’s notification rules or a lightweight script/add-on to send a list of flagged rows; the article outlines a non-technical option and a more automated option.

If you want a starter Google Sheet or Excel template, sketch the column headers above into a blank workbook, wire the simple formulas shown, and test the flow with a fake purchase, a fake production run, and a fake sale. That three-step test proves your math and highlights any naming or referencing errors before you rely on the tracker for real orders.