A skincare inventory spreadsheet for a small maker needs three sheets, not one. The first lists ingredients by supplier lot rather than by name, because a new delivery of shea butter is a different lot with its own dates and price. The second is a batch log that takes each lot off by the gram as you make things. The third lists finished jars by batch, with their best-by date, how many are left and what the ingredients in each jar cost. With those three, the spreadsheet can answer the questions a skincare maker gets asked: how much is left, what a jar costs, and which ingredient lots went into the jar a customer is holding. In the example below, a 32-jar batch of whipped body butter uses six ingredient lots, costs $1.67 a jar in ingredients, and leaves the mango butter low enough to reorder. Below is what each sheet holds, the example, how to keep it right, and the free template.
The short version
- Ingredients by supplier lot: each delivery is its own row, with lot, dates, grams and cost.
- Cost per gram, grams used and grams on hand work themselves out.
- A batch log: one line per ingredient lot used, in grams.
- Finished jars by batch, with best-by, jars made, sold and on hand.
- Ingredient cost per jar from the lots actually used; add packaging and labor for full cost.
- A reorder flag when an ingredient falls below its reorder point.
- TaroStack keeps formulas, lots, batches and costs together, without the retyping. More below.
The three sheets
| Sheet | One row per | What you type | What works itself out |
|---|---|---|---|
| Ingredients | Supplier lot | Ingredient, supplier, lot, received, best by, grams, cost, reorder point | Cost per gram, grams used, on hand, reorder flag |
| Batches | Ingredient lot used in a batch | Date, batch, product, lot, grams | The cost of each line |
| Finished jars | Batch | Batch, product, made, best by, jars made and sold | Jars on hand, ingredient cost, cost per jar |
Keeping ingredients by lot is the part people skip, and it's the part that matters on the day a customer reports a reaction or a supplier recalls a lot. How a skincare batch record holds the same facts is in cosmetic batch record template.
A worked example
Hoʻola Botanicals, a made-up skincare maker I use in these examples, makes whipped body butter in 4 oz jars. Batch BB-0921, 32 jars, made September 21:
| Ingredient lot | Share | Grams used | Cost per gram | Cost |
|---|---|---|---|---|
| Shea butter, SH-0812 | 40% | 1,446 | $0.0124 | $17.93 |
| Coconut oil, CO-0830 | 20% | 723 | $0.0070 | $5.06 |
| Mango butter, MB-0901 | 20% | 723 | $0.0170 | $12.29 |
| Jojoba oil, JO-0820 | 15% | 542 | $0.0230 | $12.47 |
| Arrowroot powder, AR-0715 | 4% | 145 | $0.0090 | $1.31 |
| Lavender oil, PL-0715 | 1% | 36 | $0.1200 | $4.32 |
| Batch | 3,615 | $53.38 |
That's $1.67 of ingredients a jar. Thirty jars have sold, so two are on hand. After the batch, the mango butter is down to 1,277 g, below its 1,300 g reorder point, so the Ingredients sheet flags it. The full cost of a jar, with the jar, label, labor and overhead added, is worked out in cosmetic business profit margin, and how the label lists these ingredients is in cosmetic labeling requirements.
Keeping it right
- Log a batch the day it's made, lot by lot, in grams, from the scale, not the recipe.
- Add a new row for every delivery, even of an ingredient you already have; never overwrite the old lot.
- Use the oldest lot first, and log which one you used.
- Weigh what's left now and then, and compare with grams on hand; a gap means a batch went unlogged or a spill wasn't written down.
- Keep the finished jars' best-by dates current, and sell the oldest batch first.
What any skincare maker should ask of software, once the spreadsheet is too much, is in cosmetic inventory software.
Get the template
Download the skincare inventory spreadsheet. Three sheets, filled in with the example: Ingredients by supplier lot, a Batches log by the gram, and Finished jars by batch. Type over the example; cost per gram, grams on hand, reorder flags, jars on hand and ingredient cost per jar work themselves out. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.
Where this stops working
The spreadsheet holds up until the batch log falls behind. Three batches in a busy week go in on Sunday from memory, a lot is typed as "SH-812" instead of "SH-0812" and quietly drops out of the totals, and the finished jars sold through your online store are never subtracted. Then the grams on hand are a guess, and so is the answer to "which lots are in this jar?"
How TaroStack does it
TaroStack keeps the same three things, and fills in most of them itself.
Ingredients by lot, from the delivery. Receiving records each supplier's lot and dates, at what you paid, and units convert: buy shea butter by the kilo, use it by the gram. FIFO cost layers keep each lot's price.
Batches that take the lots off. Each product's formula is a recipe with versions. Recording a batch takes the ingredients off stock from the lots used, at what you paid, notes which lots went in automatically, and divides by the jars that came out good, so the cost per jar is the batch's real cost.
And the jars, by lot. Every batch goes into a lot with its best-by date, lot labels print with both, and orders from your Shopify store come in to take jars off; we set that up with you. One report shows which ingredient lots went into which batches and which customers got them, and alerts tell you when an ingredient falls below its reorder point.
| The job | Spreadsheet | In TaroStack |
|---|---|---|
| A new delivery of shea | A new row, typed | Received with its lot and price |
| A batch | Six lines in the log | One batch, lots taken off |
| Jars sold online | Subtracted by hand | Taken off by the store's sales |
| Which lots are in this jar? | Look it up, if logged | One report, both ways |
Recipes, lots, batches, true cost and the reorder list are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.