Free Inventory & COGS Spreadsheet for Excel

Cost of goods sold is the number most product businesses reconstruct from memory in April. This free Excel template calculates it as a by-product of tracking you should be doing anyway — materials, purchases, production runs, and orders, with beginning and ending inventory values worked out for you.

Seven linked tabs — materials through to a calculated COGS figure
Beginning inventory + purchases − ending inventory, calculated
Landed unit costs including shipping and tax
Free to download — Excel, Google Sheets, and Numbers

Free inventory and cost of goods sold spreadsheet for Excel, showing material costs and COGS tallies

Join the Stocksmith mailing list and we'll send you our inventory and cost of goods sold 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

A COGS figure built from records, not reconstruction

Most COGS templates are a single tab: type in three numbers, get a result. That works if you already know your beginning inventory, your purchases, and your ending inventory. If you make what you sell, you don't — those numbers have to come from somewhere. This template is the somewhere.

The COGS formula, wired up

Beginning inventory, plus purchases made during the year, less ending inventory. Each of those three inputs is tallied from the tabs behind it rather than estimated at year end.

Start-of-year inventory value from your opening counts
Purchase totals accumulated as you log them
Personal-use withdrawals kept out of production costs

Landed costs, not sticker prices

What you paid for a material and what it cost you are different numbers. The purchases tab allocates shipping and tax proportionally across an order, so your unit costs reflect what actually left your bank account.

Proportional shipping and tax per line item
Landed unit cost calculated automatically
Full purchase history per material and vendor

Cost of manufacture per unit

The bridge between what you bought and what you sold. Log each production run and the materials it consumed, and the template gives you a material cost per batch and per unit — the figure your pricing should be built on.

Materials consumed recorded per production run
Cost per batch and per unit manufactured
Manufacture cost carried through to every order line

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.

Material Inventory

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

SKU and name — identify each material clearly (e.g. "Shea Butter — Refined")
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, metres, 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 material — what left inventory, and when
Quantity and unit cost — valued at the material's landed cost
Total cost — calculated, and deducted from your year-end tallies

Purchases

A full record of every material 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 material'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 materials each production run consumed:

Manufacture date — when the run was made
Product made — SKU and name of the finished product
Materials used — one row per material 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 material 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.

Get your COGS on a proper footing

Download the free spreadsheet and you'll have a working inventory and cost-of-goods-sold system in place today — no software required.

Download the free spreadsheet →

How cost of goods sold works when you make what you sell

Cost of goods sold is the direct cost of producing the items you actually sold in a period. The formula is short: beginning inventory + purchases − ending inventory. The difficulty isn't the arithmetic — it's that a manufacturing business has to produce all three inputs, and none of them are sitting in a bank feed.

A retailer can read purchases straight off supplier invoices, because what they bought is what they sell. A product business buys materials, converts them into something else, and sells that. Your inventory value at any moment is part raw material, part finished goods, and the conversion between them is a production record you either kept or didn't.

That's the gap this template closes. Materials and finished products each get their own tab with a valuation. Purchases accumulate through the year with landed costs. Manufactures record what each run consumed. Personal-use withdrawals come back out, so you're not claiming materials that never went into a sale. The reports tab then assembles those into the figure your accountant asks for.

Worth being clear about one distinction, because it's the most common filing mistake: COGS is not the same as business expenses. COGS covers direct production costs — raw materials, components, direct labour. Marketplace fees, packaging, software, shipping supplies, and your workspace are operating expenses. Both reduce taxable income, but they're reported in different places, and mixing them changes your gross margin picture entirely.

Who uses this

Who should use this COGS spreadsheet?

Built for small-batch product businesses that buy materials and manufacture finished goods — the case where COGS can't be read off an invoice.

Businesses filing their first inventory-based return

The year you start carrying inventory is the year COGS stops being optional. Starting the records in January is dramatically less painful than assembling them the following April.

Anyone whose margins stopped making sense

Revenue up, profit flat is almost always a costing problem. An accurate cost of manufacture per unit is the first place to look, and it's what this template produces.

Businesses about to reprice

Repricing against competitors without knowing your own batch costs is guesswork. Cost first, price second — and material prices move more than most people track.

Anyone handing numbers to a bookkeeper

A shared workbook with a defensible audit trail behind each figure is a much shorter conversation than a folder of receipts and a best guess at closing stock.

Frequently Asked Questions

How do I calculate COGS for a product I manufacture?

The formula is beginning inventory + purchases made during the period − ending inventory. For a manufacturing business, "inventory" spans both raw materials and finished goods, and the purchases figure should use landed costs — the price paid plus proportional shipping and tax. The spreadsheet calculates all three inputs from the data you log through the year, so the final figure comes out of the reports tab rather than an estimate.

What's the difference between COGS and business expenses?

COGS covers only the direct cost of producing the goods you sold — raw materials, components, and direct manufacturing labour. Business expenses are everything else: marketplace and payment fees, packaging, shipping supplies, tools, software, and workspace costs. Both reduce taxable income, but they're reported separately, and only COGS affects your gross margin. Classifying a material cost as an expense (or vice versa) will quietly distort the margin figures you make decisions on.

How do I set up a spreadsheet to track inventory and COGS?

At minimum you need four connected tabs: materials with unit costs, purchases recording what you bought and when, manufactures recording what you made and which materials it consumed, and orders recording what sold. A reports tab then pulls those together into a COGS figure. The template above has all of that pre-built, plus product inventory and personal-use tabs — enter your own materials and costs and it works from day one.

Can I use this spreadsheet for my tax return?

A well-maintained spreadsheet produces the figures a cost-of-goods-sold calculation needs: beginning inventory value, total purchases for the year, and ending inventory value. The requirement is consistency — every purchase, production run, and stocktake logged through the year rather than reconstructed at filing time. What your particular return needs, and how it should be presented, is a question for your accountant; this template's job is to make sure the underlying numbers exist and are defensible.

Does it work in Google Sheets and Numbers?

Yes. It's delivered as an Excel file that opens in Google Sheets and Apple Numbers with its formulas intact — upload it to Drive and open with Sheets, or open it directly in Numbers. Enter your email above and we'll send the download link along with a written instruction guide. The workbook covers a single calendar year; at year end you copy it, carry over your material and product tabs, and update the starting quantities.

When does a COGS spreadsheet stop being enough?

Two signals, usually arriving together: you're selling on more than one channel, so orders have to be re-keyed by hand; and your product range has grown past the point where a missed row is noticeable. One unlogged production run skews the year's COGS, and spreadsheets don't tell you when a formula has stopped covering a new range. Stocksmith syncs orders from your sales channels, deducts materials automatically when you record a manufacture, and keeps the COGS calculation current without the data entry.

When you outgrow the spreadsheet

Stocksmith keeps COGS current all year

A spreadsheet gives you an accurate COGS figure once a year, if you maintained it faithfully. That's genuinely useful — and it's also the ceiling. Between January and December, the number is only as fresh as the last time you sat down with it.

Stocksmith runs the same workflow as connected software. Record a production run and materials come out of stock at their real landed cost. Orders sync in from your sales channels. Your cost of goods sold, gross margin, and inventory valuation are current whenever you look at them — not just in April.

The discipline you build with this spreadsheet transfers directly. Stocksmith removes the data entry that comes with it.

Start my free 14-day trial → See how Stocksmith tracks costs

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

With the spreadsheet

COGS accurate once a year, if the year's entries were complete
Every order typed in by hand from each sales channel
Material price rises invisible until you re-check unit costs
One mistyped formula quietly corrupting a year of margins

With Stocksmith

COGS and margin current whenever you look
Orders flow in from every channel — no re-keying
Landed costs recalculated with every purchase
COGS and inventory valuation reports ready at tax time

Related resources

Free Template