How to Track Materials and Inventory for a Handmade Business

Handmade inventory goes wrong in two directions, and most makers only notice one of them.

The loud failure is running out mid-order: three necklaces to ship by Friday, and four clasps in the drawer where you thought there were forty. Now you're paying express shipping on a $12 pack of findings, or messaging a customer to apologise.

The quiet failure costs more. It's the $400 of yarn in colours you no longer use, the 200 branded boxes you bought at a bulk discount for a product you discontinued, the resin that went cloudy before you got to it. That money is gone, it never shows up in any monthly total, and it feels like nothing happened.

A decent spreadsheet fixes both, and it doesn't need to be clever. What follows is a system you can build in an afternoon.

What counts as inventory for a maker

Four different things get lumped together as "stock", and they behave differently enough that mixing them in one list will confuse you within a month.

• Raw materials — bought by length, weight or volume, consumed in fractions. Fabric, clay, yarn, wax, resin, wire. These need a cost per usable unit, which is the awkward bit.
• Components — bought and used as whole countable items. Clasps, zips, wicks, jars, blanks. Easiest to track; just count them.
• Packaging — boxes, mailers, tissue, cards, stickers, labels. Easy to forget, because you buy it separately from the thing you're making — and rarely trivial once you total a single parcel's worth at your own supplier's prices.
• Finished stock — made and ready to sell. Its value isn't its price tag; it's the materials and packaging locked inside it.

Keep raw materials, components and packaging on one sheet — they're all bought and consumed — and finished stock on another. One sale should deduct from both.

Step 1 — Build the materials list

One row per material, and only these columns to start:

Two decisions matter here. First, put shipping into the cost — a $12.50 pack of clasps with $4.95 delivery cost you $17.45, and pretending otherwise understates every product that uses them. Second, give everything a code. Six months in you will have three near-identical greys, and "the silver chain" won't be enough.

Step 2 — Work out cost per usable unit

This is where costing quietly goes wrong: makers divide by what they bought rather than by what they can use. Offcuts, end-of-roll, spillage and duds are real, and their cost belongs on the good pieces. Three worked examples:

A 10 m roll of ribbon, $8.40 delivered. Naive cost is $0.84/m. But the last 40 cm is unusable tail and you lose a few centimetres per cut — call it 8% waste, so 9.2 usable metres. Real cost: 8.40 ÷ 9.2 = $0.91/m. A bow using 60 cm costs $0.55, not $0.50.

A 500 g bag of polymer clay, $18.00 delivered. That's $0.036/g. Conditioning and trimming lose maybe 5%, giving 475 usable grams and $0.0379/g. A pendant using 22 g costs $0.83. The gap looks trivial until you've sold 300 of them, at which point it's $12 of margin you didn't know you'd given away.

A 100-pack of clasps, $12.50 + $4.95 shipping = $17.45. Nothing is consumed in fractions, but two or three per pack are usually misaligned or won't close. Assume 97 usable: $0.18 each.

Waste allowances are a judgement call, not a formula. Start at 5% for things you cut cleanly and 10% for anything with pattern matching or offcuts, then adjust after a real batch. Guessing 10% and being wrong beats using 0% and being confidently wrong.

Step 3 — Give every product a recipe

A bill of materials — a recipe — is what makes one sale deduct the right quantities without you doing arithmetic. One block per product, listing every material and how much of it goes in:

Packaging is a line in the recipe, not an afterthought — here it's nearly a third of the total. Note what isn't in the table: your time. Material cost isn't what the piece costs to produce, and it certainly isn't a price. It's the floor you build a price on.

In a spreadsheet, each recipe line multiplies quantity by a VLOOKUP (or XLOOKUP) against the materials sheet, so a supplier price change updates every product that uses it the moment you type the new number. That single link is most of the value of doing this at all.

The deduction works the same way, one layer up. Keep a sales log with a row per sale — date, product code, quantity — then give each material a "used to date" column that adds up quantity sold multiplied by the recipe quantity for that material (a SUMPRODUCT across the log, or a SUMIF per product if your catalogue is small). Qty on hand becomes opening stock, plus anything you've received, minus used to date. Nothing is ever subtracted by hand, and logging the sale is the only step you have to remember.

Step 4 — Set a reorder level for each material

A reorder level is the quantity at which you place the next order — not the quantity at which you panic. The arithmetic:

Reorder level = (average weekly usage × lead time in weeks) × safety multiplier

Say you use about 30 clasps a week and your supplier takes 14 days door to door. That's 2 weeks × 30 = 60 clasps consumed while you wait. Add a 50% safety buffer for a busy fortnight or a late shipment and your reorder level is 90. When the count drops to 90, you order — even though 90 feels like plenty, which is exactly why people don't.

The buffer is a judgement about risk, not a calculation. As a starting point: a quarter on top for a reliable local supplier with steady demand, half again for overseas shipping or anything seasonal, and double for a single material that would halt production entirely if it ran out — then move your own numbers as you learn how often you cut it fine. Update the lead-time column whenever an order actually runs late; that's the figure that quietly drifts.

Then make the sheet shout at you. A conditional format that turns the row amber at the reorder level and red below it costs one minute to set up and is the single highest-value thing in the whole system.

Step 5 — Count what you actually have

Every deduction-based system drifts. Breakages, samples, remakes, a miscount on arrival — within a couple of months the sheet and the shelf disagree. Stocktakes resync them.

1. Print or open the materials list sorted by storage location, not alphabetically. You count by drawer, not by name.
2. Count physically and write the real number in a "counted" column. Don't look at the expected figure first — you will unconsciously agree with it.
3. Check the variances. A few percent on small consumables is normal; 40% on something expensive means a recipe quantity is wrong or something isn't being recorded.
4. Overwrite qty on hand with the counted number, note the date, move on. Don't reconstruct where each unit went.
5. Recheck reorder levels while you're there, especially after a seasonal spike.

Frequency: full count quarterly, plus a spot-check of your ten highest-value or most order-critical materials monthly. If a full count takes more than an hour, your list is too granular — see the FAQ on beads.

How accurate material costs change your pricing

Good inventory tracking isn't an admin exercise; it feeds straight into whether you're making money. Marketplace fees stack up — listing, transaction, payment processing, often a share of the shipping you charge — and they're percentages of the sale price, charged on top of your material cost rather than instead of it. Those fee structures change periodically, so check the current rates on your platform rather than trusting a figure you memorised last year.

Once you have a real material cost per product, drop it into the Etsy Fee & Profit Calculator to see what's left after fees. Run it twice — once with the material figure you were using before, once with packaging and waste included — and the gap between the two answers is the margin you'd been giving away without seeing it. Better to find that in a spreadsheet than after a hundred sales.

Stock for craft fairs and markets

Markets break inventory systems because stock leaves the building and some of it comes back. The fix is a packing list per event: product, quantity out, quantity back, quantity sold — plus a line for display props and float that never sell but must return.

Reconcile the same evening, while it's fresh. Out minus back should equal sold; when it doesn't, you have a breakage, an unrecorded sale, or a giveaway you forgot. Then run the sold quantities and the material costs behind them through the Craft Fair Profit Calculator to see whether the day cleared its stall fee, travel and materials. A "good day" of $600 in sales can still lose money, and the only way to know is to walk in with the material cost per item already worked out.

If you sell print-on-demand

Print-on-demand sellers have no materials list at all, and the maths is genuinely different: the supplier's base cost replaces raw materials, packaging and labour in one line, and nothing sits on a shelf tying up cash. The trade-off is that your cost per unit is set by someone else, so the work moves from tracking stock to watching the gap between base cost, fees and price — which is what the Print-on-Demand Profit Calculator is for. If you sell both handmade and POD, keep them on separate sheets; blending them produces an average margin that describes neither.

Five mistakes worth avoiding

• Pricing off old material costs. Suppliers raise prices quietly. Re-check your top ten materials twice a year and re-price anything that moved more than 10%.
• Leaving packaging out. On a small item the box, tissue and card can rival the materials inside them — in the example recipe above, packaging is the second-largest line on the sheet. It stays invisible because it isn't part of the thing you made. Total one parcel's worth and see where yours lands.
• Bulk buying on discount logic alone. A 40% saving on 12 months of stock is only a saving if you use all of it in 12 months. Otherwise you converted cash into shelf.
• No reorder point. "I'll notice when it's low" is the mechanism that produces express-shipping fees.
• Tracking everything. The list you maintain beats the perfect list you abandon.

When a spreadsheet stops being enough

Later than you'd probably guess. A spreadsheet comfortably handles a solo maker with a few dozen materials and a few hundred orders a year, and switching before you need to mostly moves the same work into an unfamiliar interface.

Move to dedicated inventory software when at least two of these are true: you sell the same stock across more than two channels and overselling has actually happened; someone other than you needs to update stock; you have enough active materials and SKUs that finding the right row is itself the slow part; you make in batches big enough that work-in-progress needs its own tracking; or updating the sheet has become a job you're avoiding. Any one of those alone usually means your sheet needs tidying, not replacing.