Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| Report month (from Settings) | 46327 | to | 46356 | Change the month on the Settings sheet. Stock figures show today's position, not the month. | |
|---|---|---|---|---|---|
| Revenue this month | 2,472.10 | Green coffee on hand (value) | 2,138.55 | ||
| Profit this month (after bag cost) | 1,391.76 | Green lots to reorder | 3 | ||
| Profit margin % | 56.3% | Roasted lots to roast again | 4 | ||
| Bags sold | 157 | SKUs to pack | 13 | ||
| Roasted weight sold | 152.2 | Green lots listed | 8 | ||
| Batches roasted | 14 | SKUs listed | 15 | ||
| Green roasted this month | 335.0 | Batches outside the loss range | 0 | ||
| Average weight loss this month | 16.0% | Best retail margin % | Huila Washed 12 oz | ||
| Customer type | Revenue | Bags | Profit | Margin % | Share of revenue |
| Retail | 1,319.50 | 82 | 838.32 | 63.5% | 53.4% |
| Wholesale | 996.00 | 63 | 458.11 | 46.0% | 40.3% |
| Subscription | 156.60 | 12 | 95.33 | 60.9% | 6.3% |
| Total | 2,472.10 | 157 | 1,391.76 | 56.3% | 100.0% |
Example from the “Dashboard” tab, with the numbers and formulas that are in the file.
What is inside
- Dashboard: The report month's revenue, profit, margin, bags sold, batches roasted and average weight loss; green stock value and how many lots, roasted lots and SKUs need action. Revenue by customer type (retail, wholesale, subscription), weight loss by roast level, and four charts: revenue and profit by customer type, weight loss % by roast level, margin % per SKU, and green on hand against reorder level.
- Sales: One row per order line: date, customer, customer type, SKU and bags. The list price comes from the SKU (wholesale price for wholesale customers); type a price only when you charged something different. Revenue, bag cost, profit and roasted weight follow. Room for 1,000 lines.
- Packing: Bags filled by date and SKU. Each row takes the roasted weight out of the coffee it belongs to, a lot or a blend split by its shares. Room for 500 rows.
- Roast Log: One row per batch: date, batch, lot, green in, roasted out, roast level and time. Weight loss %, the green cost of the batch and the cost per roasted unit are calculated, and a Check column flags a batch outside the loss range you set. Room for 500 batches.
- Green Lots: Your green coffee: origin, producer, process, purchase date, weight bought, price per unit and freight or duty per unit. The sheet adds landed cost, green roasted, green on hand and its value, the lot's actual weight loss, cost per roasted unit and a status (OK, Reorder, Empty). Room for 60 lots.
- Blends: A list of blends and their lines: lot and share. It checks that shares total 100% and costs each blend per roasted unit. Room for 20 blends and 100 lines.
- SKUs: Each product: a coffee in a bag size with packaging cost, retail price and wholesale price. Cost per bag, margin per bag and margin % for retail and wholesale, bags packed, sold and on hand, and a Pack more alert. Room for 60 SKUs.
- Stock: Green and roasted stock by lot, and finished bags by SKU, each with its minimum and a status: Reorder, Roast more or Pack more.
- Settings: Business name, currency symbol, the weight unit (lb or kg), expected weight loss, loss alert range, report month, roast levels, bag sizes (12 oz, 1 lb, 5 lb, 250 g and 1 kg, all editable) and customer types.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
Most roasting logs record batches and stop there. This one connects the whole chain: green lot cost becomes cost per roasted unit using the lot's own measured weight loss, blends take their cost from the lots inside them, a bag takes its cost from the coffee and its size, and sales, packing and roasts all draw down the same stock. The sample is a small roastery with 8 lots, 28 batches, 3 blends, 15 SKUs and 48 order lines.
How do I calculate roasted coffee cost per pound?
Green coffee is bought by the green pound or kilo but sold roasted, and roasting drives off weight. So the cost of a roasted pound is the landed green cost divided by one minus the weight loss. In the sample, lot ETH-01 costs 6.80 per lb plus 0.45 freight, so 7.25 landed. Across its three batches 65 lb of green became 56.5 lb roasted, a 13.1% loss, so a roasted lb costs 8.34.
The sheet uses each lot's own measured loss once it has been roasted, and the expected loss from Settings for a lot you have not roasted yet. Landed means the price plus what it costs to bring the lot to your door, so type freight and duty per unit on Green Lots. Check your own figures for anything else you add to the cost, such as energy or labour; the sheet leaves them out.
How do I work out weight loss and what is a normal range?
Weight loss % is 1 minus roasted weight divided by green weight. A 25 lb batch that comes out at 21.2 lb lost 15.2%. The Roast Log shows this for every batch and the Dashboard averages it by roast level: in the sample, Light batches average 13.1%, Medium 15.2%, Medium-Dark 17.0% and Dark 18.8%.
What is normal depends on your machine, the lot's moisture and your roast level, so there is no universal number. Type your own low and high alerts on Settings and the Check column flags any batch outside them, which makes a mis-keyed weight easy to spot.
How do I cost a coffee blend and set bag prices?
List the blend and its lines on the Blends sheet: each lot and its share. The sheet multiplies each share by that lot's cost per roasted unit and adds them up. In the sample, House Blend is 50% BRA-01, 30% COL-01 and 20% GUA-01 and costs 5.81 per roasted lb. Blends here are mixed after roasting, from lots roasted separately.
On SKUs choose the coffee and the bag size. Cost per bag is bag weight times cost per roasted unit plus packaging: a 12 oz House Blend bag costs 5.11 and sells for 14.50 retail and 10.50 wholesale, margins of 64.8% and 51.4%. Those margins are before labour, energy, shipping and fees, so judge them with your other costs in mind. For the cash side of the business see the cash flow forecast template and the business expenses budget.
How to use it
- On Settings type your business name, pick the weight unit (lb or kg) that you buy and roast in, the expected weight loss % and the first day of the month to report. Edit the bag sizes and roast levels if yours differ.
- On Green Lots add every lot you buy: origin, producer, process, weight bought, price per unit and freight or duty per unit. Set the level at which you reorder green and the minimum roasted stock.
- On Roast Log add one row per batch: date, lot, green weight in, roasted weight out, roast level and time. Weight loss % and the green cost per roasted unit appear on their own.
- On Blends list each blend and then its lines: the lot and its share. The shares must total 100%.
- On SKUs list what you sell: a coffee (a lot or a blend) in a bag size, with packaging cost, retail price and wholesale price. The sheet shows cost per bag and margin.
- Record bags filled on Packing and each order line on Sales (customer type Retail, Wholesale or Subscription). Stock and the Dashboard update, and the Stock sheet flags what to reorder, roast or pack.
Before you start:
- Capacity: 60 green lots, 500 roast batches, 20 blends with 100 blend lines, 60 SKUs, 500 packing rows and 1,000 sales rows.
- Enter every weight in the weight unit chosen on Settings (lb or kg). Bag sizes are converted for you: 12 oz, 1 lb, 5 lb, 250 g and 1 kg are listed and you can change or add sizes.
- Weight loss % = 1 - roasted weight / green weight. Cost per roasted unit = landed green cost per unit / (1 - weight loss %). A lot that has not been roasted yet uses the expected weight loss on Settings.
- Blends are made from lots that are roasted separately and mixed after roasting. A blend costs the sum of each lot's share times that lot's cost per roasted unit.
- Cost per bag = bag weight x cost per roasted unit + packaging. Labour, energy, rent, shipping to customers and fees are not included, so look at the margin with them in mind.
- Roasted stock = roasted weight out of the lot - weight packed. Chaff, samples and cupping roasts that leave the roaster should be recorded as packed bags or deducted in the Roast Log by typing the weight you keep.
- Use one currency in one workbook; the symbol on Settings is only a label. The report month on Settings picks the month shown on the Dashboard.
Still running this on Excel?
This sheet calculates requirements for the orders you type in yourself. In MESwiro, material requirements planning (MRP) breaks planned production orders down into their components and works out shortages against stock.
| In this sheet | In MESwiro |
|---|---|
| You type in every order and every balance by hand. | Material requirements planning (MRP) breaks planned production orders down into their components and calculates shortages against stock. |
| You ask suppliers for quotes by phone. | It requests quotes from several suppliers and compares price, lead time and minimum order side by side. |
| Line scheduling in a sheet hides conflicts. | It assigns work orders to lines and shifts by drag and drop, with a Gantt chart and a panel for unscheduled orders. |
| Efficiency and order cost are guesswork. | It calculates OEE for each line automatically and shows the material, labor and machine cost of each order. |
Frequently asked questions
Is this coffee roasting log free?
Yes. Download it, use it in your roastery and change anything you like. There is nothing to sign up for to open the file.
Does it work in lb and in kg?
Yes. Pick lb or kg on Settings and enter every weight and green price in that unit. Bag sizes can be in oz, lb, g or kg and are converted to your unit for you.
Can it track green coffee inventory and reorder alerts?
Yes. Each lot shows green on hand (weight bought minus green roasted), its value and a status. Set a reorder level on Green Lots and the lot turns red when it is reached. Roasted stock and finished bags have minimums too.
Can I record wholesale and subscription customers?
Yes. Each sales line has a customer type: Retail, Wholesale or Subscription. Wholesale lines use the wholesale price; type a different price on the line if you charged one. The Dashboard splits revenue and profit by type.
How many batches and lots can it hold?
500 roast batches, 60 green lots, 20 blends, 60 SKUs, 500 packing rows and 1,000 sales lines. For more than that, see the custom system option below.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
All the templates in this group: Factory management sheets: production, cost and quality
More free templates for: Factories and production
More free templates
- Production Tracking Spreadsheet for Excel
- Equipment Inspection Log Template for Excel
- Electronics factory sheet: BOM, production and quality
- Food factory sheet in Excel: recipes, batches, cost and shelf life
- Biscuit factory sheet: dough, lines, packing and scrap
- Detergent factory sheet: formulas, batches, cost and filling
- Detergent ingredients and their names: function, chemical name and use
- Pharma factory sheet: batch records, materials, QC and release
- Industrial waste management sheet: quantities, classification, recycling and cost
- Hotel Guest Folio Template for Excel (Rooms, Reservations, Billing)
- Auto Parts Inventory and Sales Spreadsheet for Excel
- Clothing Store Inventory Spreadsheet (Size and Color) for Excel
- Multi-Location Inventory Spreadsheet for Excel
- Production tracking sheet for home textiles factories
- Fabric requirements and marker sheet
- Customer accounts, credit sales and customer statement sheet
- Cash book and customer accounts sheet