Free Cosmetics & Skincare Inventory Spreadsheet

A ready-made Excel template for formulated products — base oils, butters, emulsifiers, preservatives, actives, fragrance and packaging, each with an INCI name, a landed cost per gram, and a batch record behind every production run.

Ingredient register with INCI names and tracking units
Actives costed per gram, at usage rates under 1%
Packaging tracked as inventory, not an afterthought
Free to download — Excel, Google Sheets, and Numbers

Free cosmetics and skincare inventory spreadsheet showing ingredient stock, INCI names and cost per unit

Join the Stocksmith mailing list and we'll send you our cosmetics inventory spreadsheet template for free! We typically send 2-4 emails a month about small batch production topics which we hope are useful to our readership.

What's included

Formulated products break generic inventory templates

Thirty ingredients per formula, several of them used at a fraction of a percent, all with different shelf lives and units — and packaging that costs more than the contents. A stock list with a quantity column doesn't survive contact with that. This template is built around it.

Every ingredient, individually

Base oils, butters, emulsifiers, preservatives, actives, fragrance, colourants — one row each, with an INCI name alongside the common name so labelling and records draw on the same source.

INCI name recorded per ingredient
Tracking unit per ingredient — g, ml, or each
Preferred supplier per line, for reordering

Actives costed at real usage rates

A peptide used at 0.5% is still a meaningful share of your unit cost when it's the most expensive thing in the jar. Tracking in grams — not "one pot" — is what makes those costs land in the right place.

Landed cost per gram, freight included
Each active tracked separately, not grouped by supplier
Cost per batch and per unit, calculated

Packaging as inventory

Jars, bottles, pumps, caps, labels and cartons go in the register the same way ingredients do — because they're often the largest single line in a unit cost, and they run out on a completely different schedule.

Each component costed and counted individually
Consumed per production run alongside ingredients
Visible in cost per unit rather than buried in expenses

Template walkthrough

What each tab of the spreadsheet tracks

The workbook is divided into seven linked tabs, each covering one part of your inventory workflow. It's built to run over a single calendar year, so it can calculate your start and end-of-year inventory values. Calculated columns are shaded in the file — enter your data everywhere else and let the formulas do the work.

Ingredient Inventory

A complete list of the ingredients you hold — the foundation the rest of the workbook builds on. For each one:

SKU and name — identify each ingredient clearly (e.g. "Hyaluronic Acid Powder — LMW")
INCI name — the standardised ingredient name, kept alongside your common name so labelling and compliance stay straightforward
On-hand quantity — current stock, updated as you count
Stock status — a calculated in-stock / out-of-stock flag
Unit cost — the fully landed cost per tracking unit
Tracking unit — grams, millilitres, or units — whatever you actually produce in
Preferred vendor — where you usually buy it
Starting quantity and value — feeds your start-of-year valuation

Personal Use

A log for anything withdrawn from stock that didn't go into a product you sold — samples, testing, or personal use. Keeping it separate means your COGS only reflects what genuinely went into production:

Date removed and ingredient — what left inventory, and when
Quantity and unit cost — valued at the ingredient's landed cost
Total cost — calculated, and deducted from your year-end tallies

Purchases

A full record of every ingredient purchase — one row per item bought, so five items from one supplier means five rows:

Purchase date and vendor — when and where you bought it
Item cost — what you paid, excluding shipping and tax
Quantity purchased — in the ingredient's tracking unit
Proportional shipping and tax — allocated per item across the order
Landed unit cost — calculated automatically: the true cost per unit including freight

Manufactures

The step most inventory spreadsheets skip entirely: recording what you made, and which ingredients each production run consumed:

Manufacture date — when the run was made
Product made — SKU and name of the finished product
Ingredients used — one row per ingredient consumed in the run
Quantity used — how much to deduct from stock
Total usage cost — calculated per row, building your cost per batch

Product Inventory

Your finished goods — what's made, priced, and ready to sell:

SKU, name, and category — identify each sellable product
Unit price — your retail price per item
Manufacture cost — the ingredient cost to make one unit, tallied from your Manufactures tab
On-hand quantity and stock status — what's actually available to sell
Inventory values — calculated starting and current stock value

Orders

Every sale, recorded one product per row so manufacture costs can be tallied against revenue:

Order date and ID — match rows back to your sales channels
Product and quantity sold — what went out the door
Unit manufacture cost — feeds your COGS tally for the year
Tax and shipping — proportional amounts per line item
Totals — calculated line totals and grand totals

Reports

The tab that makes the rest worth maintaining — your key totals, tallied automatically through the year. Every figure here is calculated:

Revenue totals — orders and tax, from your Orders tab
Expense totals — purchases and tax, from your Purchases tab
Inventory valuation — start-of-year value, plus purchases, less personal use and cost of product sold
Year-to-date inventory total — the number your accountant asks for at tax time

Every inventory figure is derived from the unit costs you enter during the year, so the accuracy of your reports depends on the accuracy of those costs.

Know what every formula costs to make

Download the free spreadsheet and you'll have your ingredient register, batch records, and cost per unit tracked from today — no software required.

Download the free spreadsheet →

Formulation spreadsheet or inventory spreadsheet?

These are two different documents and most formulators need both. A formulation spreadsheet is a development tool: ingredient percentages, INCI names, phases, usage-rate limits. It answers "does this formula work." An inventory spreadsheet is a business tool: how much of each ingredient you hold, what it landed at, how much a batch consumed, and what the finished unit costs. It answers "is this formula worth making."

The gap between them is where margins quietly disappear. A formula at 2% of an active looks the same on paper whether that active costs $40 or $400 a kilo. Only the inventory side tells you which one you're making, and what happened to your unit cost when the supplier repriced.

This template is the inventory half. Develop your formulas wherever you already do, then bring the ingredient list into the register with a tracking unit and a landed cost per unit. Log each production run in the manufactures tab — ingredients and packaging together — and you get a cost per batch and per unit that reflects what you actually paid.

One habit worth building early: track each active separately even when they arrive from the same supplier on the same invoice. Niacinamide, hyaluronic acid and a peptide blend have different usage rates, different costs and different shelf lives. Grouping them makes every one of those three figures wrong.

Who uses this

Who should use this cosmetics inventory template?

Built for businesses formulating and filling their own products — where the ingredient list is long, the usage rates are small, and the unit cost has to be defensible.

Skincare and serum brands

High-value actives at low inclusion rates. Getting cost per gram right is what separates a real margin from an assumed one.

Lotion, cream and balm makers

Emulsions mean oils, waters, emulsifiers and preservatives in fixed ratios. Tracking each phase's ingredients keeps batch costs stable as oil prices move.

Makers building GMP habits

Batch numbers, received dates and lot records in one place. The workbook won't make you compliant on its own, but it gets the underlying records into a defensible shape.

Formulators moving from hobby to business

The point where "roughly how much do I have left" stops being an acceptable answer — usually the first time a preservative runs out mid-batch.

Frequently Asked Questions

How do I calculate cost per unit for a skincare product?

List every ingredient in the batch with its landed cost per gram (purchase price plus proportional shipping, divided by the quantity received). Multiply each ingredient's quantity used by that cost and sum the results. Add the packaging — jar, pump, cap, label, carton. Divide the total by the number of units the batch filled. The manufactures tab does the arithmetic; what it needs from you is accurate landed costs and an honest batch yield.

What's the difference between a cosmetics inventory spreadsheet and a formulation spreadsheet?

A formulation spreadsheet helps you develop and refine a formula — percentages, INCI names, usage-rate limits, phase structure. An inventory spreadsheet tracks the business side: how much of each ingredient you hold, what it cost, how much production consumed, and what your unit cost and COGS look like. You need both. Develop in one, then bring the ingredient list into the other to find out whether the formula is commercially viable.

Should I track packaging separately from ingredients?

Yes — as separate rows in the same register. Jars, bottles, pumps, caps, labels and cartons are frequently a larger share of unit cost than the formula itself, particularly in glass, and you can't price sensibly if that cost is hidden in general expenses. They also deplete on a different schedule to ingredients: you can be well stocked on every oil and still be unable to fill an order because the 50ml pumps ran out.

How do I manage ingredient shelf life in the spreadsheet?

Add a received date and a best-before date column to the ingredient register, taking the shelf life from your supplier's documentation rather than a general rule — it varies substantially between a refined butter and an unrefined cold-pressed oil, and between suppliers for the same material. Log the received date whenever stock arrives and work oldest-first. The spreadsheet won't warn you as a date approaches; that's one of the clearer limits of the format.

Should I record batch numbers?

Yes — add a batch number to each row in the manufactures tab and note it on the product label. It's the only practical way to answer "which units contained the affected ingredient lot" if a supplier issues a recall, and traceability records of this kind are a standard expectation under Good Manufacturing Practice. Recordkeeping obligations for cosmetics sellers have tightened in several markets and vary by where you sell and your size, so check what applies to your business rather than assuming — but keeping batch records is worth doing regardless of what's strictly required.

How do I know when to reorder an ingredient?

Work from your own batch usage and your supplier's lead time. If a production run consumes 50g of an active and that supplier takes a week to ship, your reorder point wants to cover the runs you'll make in that week plus a buffer — and specialty actives with long or unreliable lead times want a bigger one. The template gives you the consumption history that calculation needs; our reorder point formula guide walks through turning it into a number.

When should I move from the spreadsheet to software?

The usual trigger for formulators is the ingredient count. Thirty materials across six formulas is 180 relationships to maintain by hand, and each new SKU multiplies it again. Add expiry dates and batch records and the maintenance overtakes the value. Stocksmith holds each formula as a bill of materials, deducts ingredients and packaging automatically when you record a batch, tracks lots through to the orders that shipped them, and recalculates unit costs as supplier prices move.

When you outgrow the spreadsheet

Stocksmith holds your formulas and does the deducting

The spreadsheet is a genuinely good place to start — it teaches you which numbers matter. What it can't do is scale with your ingredient list. Every formula is a set of relationships you maintain by hand, and every new SKU multiplies them.

Stocksmith holds each formula as a bill of materials. Record a batch and every ingredient and packaging component comes out of stock at its current landed cost. Lots are tracked through to the orders that shipped them, so a supplier recall is a query rather than an afternoon. Unit costs update as prices move.

The discipline transfers directly — same ingredients, same units, same costing logic, minus the maintenance.

Start my free 14-day trial → See how bills of materials work

No credit card required. Takes hours to set up, not weeks.

With the spreadsheet

Every ingredient deducted by hand after each batch
No warning as a preservative or active runs low
Expiry dates that only help if you go looking
A recall means reading back through batch rows by hand

With Stocksmith

Formulas held as bills of materials, deducted automatically
Low-stock alerts before an ingredient stops a batch
Lot traceability from ingredient through to order
Unit costs recalculated as supplier prices move

Related resources

Free Template