Coffee Roaster Inventory Spreadsheet (Free Template)

A coffee roaster's inventory lives in three places at once: green coffee in bags, roasted coffee in bins, and packaged coffee in bags of two or three sizes, with roast loss sitting between the first two. A spreadsheet that works has one tab for each, and the roast log is the engine: every batch moves weight from green to roasted and records the loss, so green on hand is received minus roasted, roasted on hand is roasted minus packed, and packaged on hand is packed minus sold. Here's a filled-in example for a two-person roastery going through about four bags of green a week, the formulas, and the file (Excel format, opens in Google Sheets, no sign-up).

The short version

• Three tabs: Green (one row per lot), Roast log (one row per batch), Packaged (one row per product and size).
• Weigh every batch in and out. Loss runs from about 10% for a very light roast to 25% for a very dark one, and a batch outside your usual range is a scale, a moisture or a roast problem.
• Bag weights differ by origin (60, 69 and 70 kg; 100 lb for Hawaiʻi), so record the weight received, not the bag count.
• Once a week, paste sales by product from your till or shop and count what's on the shelf.
• Green on hand, in kg and dollars, is the number you're doing all this for.

Green: one row per lot

The "kg roasted" column is a formula that adds up the roast log for that lot, so it changes every time a batch is logged. The lot code is yours: origin and the date received is enough, and it's what the roast log refers to. Bag weights are worth a row in the read-me: Brazil, Ethiopia, Kenya, Indonesia and Vietnam ship 60 kg bags (132 lb); Guatemala, Mexico and Peru 69 kg (152 lb); Colombia 70 kg (154 lb); and Hawaiʻi and Puerto Rico 100 lb (45.4 kg), per Mill City Roasters' cheat sheet. "Four bags" can be anything from 180 to 280 kg, which is why the sheet keeps kilograms.

Download the sheet, or build it from here.

Roast log: one row per batch

The first six of the week's twenty batches, in a 12 kg drum:

Loss is =1-E2/D2. Over the whole week: 240 kg of green in, 204.5 kg of roasted out, 35.5 kg lost, an average of 14.8%. By lot it's more useful: the Brazil lost 15.3%, the Ethiopia 12.5%, the Guatemala 14.6% and the Colombia, taken darker, 17.1%. Those are the numbers to watch. Royal Coffee's Chris Kornman puts very light roasts at "only about 10%" loss and roasts taken well past second crack at "as much as 25%", with a light Kenya around 12%, a medium Brazil around 15% and a dark Sumatra around 20%, and his advice is the same as mine: "weigh every batch before and after roasting" and set a benchmark per roast level (Royal Coffee, 2022). A Brazil batch that comes out at 18% one Tuesday is a question, not a footnote.

The batch code doubles as the roasted lot. Put the roast date on the bag, and the roast log is the record that connects a customer's bag to a green lot, which is what an organic inspector's mass balance asks for and what a lot tracking spreadsheet does for any other product.

Packaged: one row per product and size

On hand is =D2+E2-F2. Roasted used is =E2*C2/1000. Sold comes from wherever you sell: the week's sales by product from Shopify, Square or the café till, pasted in. Then the roasted bins reconcile:

If the bins hold 31 kg when you weigh them on Saturday, something's off by 6 kg, and the candidates are a batch that wasn't logged, a bag count that was, or samples nobody wrote down. Small roasters lose more coffee to "nobody wrote it down" than to roasting.

What a bag of coffee costs

The roast log gives you the one number a price depends on. The Brazil cost $6.10 a kg green and lost 15.3%, so roasted it cost $6.10 ÷ 0.847 = $7.20 a kg, and the coffee in a 12 oz bag is 0.34 × $7.20 = $2.45, before the bag, the label, the gas, the labor and the samples. Roast loss and cost per bag carries that through to a price, and how to count inventory is the Saturday routine that keeps the bins honest.

Where this stops working

The roast log lives in a notebook next to the roaster, and the sheet lives on the laptop, and they meet on Sunday if they meet at all. Sales come from two systems, and a shop that sells 12 oz and 5 lb bags of the same roast counts them as two products with no idea they came from one bin. The inspector wants the path from a green receipt to a specific bag, and the sheet can give it for last week but not for March. Two people roasting on different days is the end of it. It's the usual story with spreadsheets, and the sheet is still where to start.

How TaroStack does it

In TaroStack, green coffee is bought by the bag, stocked by the kilogram and sold by the 12 oz, and the conversions are done for you. Roasting is a recipe with a yield: 12 kg of green from lot G-0907-BR becomes a batch of roasted with its own lot and its roast date, and the loss is recorded on the batch. Packing is a second recipe, so a 12 oz bag comes out of the roasted lot and the roasted lot comes out of the green lot, which is the trace the organic inspector wants, in one report. Cost per bag comes from FIFO cost layers, freight included, so the $2.45 is on the screen rather than in your head. The shop side connects to your Shopify store, which we set up with you. Recipes, batches and true cost are part of Standard, at $99 a month.

Questions people also ask

How do you calculate roast loss?

Weigh the green going in and the roasted coming out. Loss is one minus roasted divided by green: 12 kg in and 10.2 kg out is 1 − 10.2 ÷ 12 = 15%. Log it per batch and compare each batch with your benchmark for that roast level.

How many pounds are in a bag of green coffee?

It depends on the origin. Most of Africa, Brazil and Indonesia ship 60 kg (132 lb) bags; Guatemala, Mexico and Peru 69 kg (152 lb); Colombia 70 kg (154 lb); Hawaiʻi and Puerto Rico 100 lb. Record the weight received, not the number of bags.

What is a good roast loss percentage?

There's no single good number, only a normal range for your roast level: around 10% to 13% for light roasts, 14% to 16% for medium, and up to 20% or beyond for dark. What matters is consistency batch to batch. A batch two or three points outside your usual range deserves a look at the scale, the green's moisture and the roast curve.

How do coffee roasters track inventory?

Three quantities: green by lot, roasted by batch, packaged by product and size. The roast log connects the first two and the sales export connects the last two. Small roasters do it in a spreadsheet like the one above until the notebook and the laptop stop agreeing; then it moves into software that records the roast and the packing as they happen.