Free Coffee Shop Inventory Spreadsheet
A ready-made template for tracking everything behind the counter — beans, milk, syrups, packaging and food — plus what it all costs. Seven linked tabs cover stock, purchases, production, orders and end-of-year COGS. Works for cafes, espresso bars, mobile carts and roasteries.
- Opens in Excel, Google Sheets, or Numbers
- Tracks supplies and finished products, with stock status flags
- Automatic cost calculations — landed unit costs, cost per batch, COGS
- Free to download, with a detailed instruction guide
Join the Stocksmith mailing list and we'll send you our coffee 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
Everything a coffee business needs to track, in one file
Most inventory templates only count what's on the shelf. This one connects what you buy to what you make and sell — so if you roast your own beans, batch your own syrups, or bake on site, your costs actually reflect how the business works.
Track supplies and products
See current stock levels for beans, dairy, syrups, packaging and finished goods at a glance, with an automatic out-of-stock flag.
Any unit of measure
Track in grams, kilos, litres, shots, bottles or individual units — whatever matches how you actually buy and use each item.
Automatic COGS
The Reports tab tallies cost per batch, unit cost and cost of goods sold for the year from the rows you've already entered.
- ✓ A multi-tabbed spreadsheet file, openable in Excel, Numbers, or Google Sheets
- ✓ A detailed instruction guide
- ✓ Supply and product tracking, including "out of stock" status
- ✓ Expense logging, with personal use deductions
- ✓ Cost of production calculations
- ✓ Revenue and expense totals
- ✓ Inventory valuation tallies for COGS
What should a coffee shop inventory spreadsheet track?
A coffee shop inventory spreadsheet should track six categories: beans, dairy, syrups, food items, disposables, and equipment consumables.
Cafe inventory covers a lot more than coffee. These are the categories most coffee businesses need on the sheet:
- Coffee and espresso beans — Track by bag, weight, or blend. Note supplier, roast date and stock on hand so you never run short during a morning rush.
- Milk and dairy — Whole milk, oat, almond and cream typically turn over faster than anything else behind the counter. Track in litres or gallons with reorder thresholds.
- Syrups and flavourings — Vanilla, caramel, hazelnut and seasonal syrups. Track by bottle so you can spot which flavours move fastest.
- Food and bakery items — Pastries, cakes, sandwiches and grab-and-go snacks. If you bake on site, track raw ingredients separately from finished goods.
- Disposables and packaging — Cups, lids, sleeves, straws, napkins and takeaway bags. Easy to forget until you run out mid-service.
- Equipment consumables — Cleaning tablets, filter papers, descaler and maintenance supplies for your machine and grinders.
The template includes tabs for all six, so everything from green beans to takeaway cups sits in one place.
How do I calculate COGS for a coffee shop?
Coffee shop COGS equals opening stock value plus purchases during the period, minus closing stock value.
Cost of goods sold tells you what it actually cost to produce the drinks and food you sold. Without it, you can't know whether a given menu item is making money. For a coffee business, COGS typically includes:
- ✓ Cost of beans or espresso per drink
- ✓ Cost of milk, syrups and flavourings per serve
- ✓ Proportional cost of disposables (cup, lid, sleeve)
- ✓ Cost of food ingredients per item sold
The Reports tab tallies COGS for the year automatically from what you've purchased and sold, so there's no separate calculation to run at tax time.
If you roast, blend or batch anything yourself, per-item costing gets harder as volumes grow — recipe costing software automates the per-unit maths and keeps costs current as supplier prices move.
How to use the coffee shop inventory spreadsheet
This is a simple periodic system covering the fundamentals of inventory tracking for cafes and coffee businesses. Enter your email above and we'll send you a download link. Once unzipped, the file opens in Excel, Google Sheets, or Numbers.
The spreadsheet is divided into seven tabbed sheets, each covering a different part of tracking your stock: Material Inventory, Personal Use, Purchases, Manufactures, Product Inventory, Orders and Reports.
It's designed to run for a single calendar year, since it calculates start and end-of-year inventory values. At year end, create a new copy, carry over your product and material tabs, then update stock levels and starting numbers.
Note: some columns are calculated and should not be edited — they're marked in a light turquoise colour.
Material Inventory
A complete record of your supplies — beans, dairy, syrups, packaging and consumables. Their cost feeds your estimated start-of-year inventory value. Use it to record current stock of each item and see stock status at a glance. The sheet has 11 columns:
- SKU — optional, if you use unique codes for each supply item.
- Name — a name that identifies it clearly (e.g. "Oat milk 1L").
- Description — a brief description, for identification.
- On Hand Qty — how much you currently have. Count regularly to keep this accurate.
- Stock Status — calculated; shows what's in stock and what's out.
- Starting Quantity — what you had on hand at the start of the year, for valuation. Leave blank if you don't track valuation.
- Unit Cost — the fully landed cost for a single tracking unit, including shipping and discounts.
- Starting Inventory Value — calculated, do not edit.
- Tracking Unit — how you're tracking the item, so the unit cost is unambiguous (grams, litres, bottles, units).
- Preferred Vendor — the supplier you usually buy from. Detailed purchase history lives on the Purchases tab.
Personal Use
For logging anything withdrawn from stock for personal use, so you aren't claiming for supplies that didn't go into what you sold. Records the date removed, SKU, name, description, unit cost, quantity used, and a calculated total cost.
Purchases
A full record of every supply purchase, so the year's buying feeds your end-of-year inventory value. Create one row per item purchased — five different items from the same supplier is five rows.
- Purchase Date, SKU, Name, Description, Vendor — the identifying details.
- Item Total Cost (ex Shipping, Tax) — what you paid for the item alone, before shipping, tax or order-level discounts.
- Quantity Purchased — how many tracking units you bought.
- Shipping Cost (proportional) — this item's share of the order's shipping. Split by relative cost or weight, whichever fits.
- Tax (proportional) — this item's share of tax, if not itemised on the invoice.
- Total Cost (inc Shipping), Unit Cost, Landed Unit Cost — all calculated, do not edit.
Manufactures
For recording anything you make in house — roasted batches, syrups, baked goods, bottled cold brew — so you can track supply usage and cost. Add one row per supply used, or calculate the total separately and enter a single row for the whole batch. Columns cover manufacture date, product SKU and name, material SKU and name, unit cost, quantity used, and a calculated total usage cost.
Product Inventory
A complete record of your finished products, feeding start-of-year inventory value and showing current stock status. Covers SKU, name, category, description, unit price, starting quantity, on-hand quantity, a calculated stock status, production cost per unit, and calculated starting and current inventory values.
Orders
Records every sale. Capturing the estimated production cost against each one is what lets the sheet tally COGS for the year. One row per product sold. Covers order date and ID, SKU, name, unit price, quantity sold, unit production cost, plus calculated total production cost, total price, tax and shipping shares, and a grand total.
Reports
Tallies the revenue, expense and inventory figures worth watching through the year. Every figure is calculated — do not edit. Inventory calculations are based on the unit costs you entered during the year, so accuracy there flows through to everything here.
When a spreadsheet stops being enough
A spreadsheet works well while the counting is periodic and the recipes are simple. It starts to strain at the point where you're producing rather than just reselling — roasting green beans into labelled retail bags, batching syrups, or baking to a recipe. Then every batch changes two sets of numbers at once, and keeping them in sync by hand is where the errors creep in.
If that's the direction you're heading, Stocksmith's coffee roaster inventory software does the same job automatically: green bean stock comes down and roasted stock goes up as you record each roast, with cost per bag calculated from what you actually paid for the beans.
Frequently Asked Questions
What should I track in a coffee shop inventory spreadsheet?
Track six categories: coffee beans (by weight or bag), milk and dairy (by litre or gallon), syrups and flavourings (by bottle), food and bakery items (raw ingredients and finished goods separately), disposables such as cups and lids, and equipment consumables like filter papers and descaler. Covering all six gives you a complete picture and stops anything running out mid-service.
How do I calculate COGS for a coffee shop?
COGS is opening stock value, plus purchases during the period, minus closing stock value. For a single drink, add the cost of beans, milk, syrup and packaging per serve. The spreadsheet's Reports tab calculates the annual figure automatically from what you've purchased and sold, so there's nothing to work out separately at year end.
Can I use this spreadsheet for a cafe?
Yes. The template suits any cafe, espresso bar, mobile coffee cart or roastery. You define your own items and units of measure, so it adapts to exactly what you stock and sell. A neighbourhood cafe and a roaster with a retail front use the same tracking structure — only the items on the sheet differ.
Is this coffee costing spreadsheet template free?
Yes, completely free. Enter your email above and we'll send the download link straight away. It works in Excel, Google Sheets and Apple Numbers with no paid software needed, and includes seven tabs covering supplies, purchases, production, product inventory, orders and a summary report with automatic COGS.
Does Stocksmith have inventory software for coffee businesses?
Yes. Stocksmith is inventory and production software for businesses that make things in batches, including coffee roasters and cafes that roast, batch or bake in house. It tracks stock automatically as you produce, calculates cost per batch, and generates COGS reports. This spreadsheet is a good starting point; Stocksmith is the next step when you outgrow it.
Get the free coffee inventory spreadsheet
Enter your email and we'll send it straight over.
Download the free spreadsheet →