Recipe costing template for Excel and Google Sheets
Four linked tabs: ingredients, recipes, modifiers and drinks. Change a pack price once and every drink that uses it updates. Includes a modifier tab where oat swaps out whole milk instead of just adding to it.
Free, no email, no signup. The CSV is the first tab only (Ingredients); the Excel file has every tab. In Google Sheets, use File, then Import, and upload the .xlsx.
Ingredients
Example data. Example prices, replace with your invoices. Unit cost is a formula: pack price divided by pack size.
| Ingredient | Pack price | Pack size | Unit | Unit cost |
|---|---|---|---|---|
| Whole milk | $4.80 | 128 | fl oz | $0.0375 |
| Oat milk | $5.50 | 64 | fl oz | $0.0859 |
| Espresso beans | $62.00 | 80 | oz | $0.7750 |
| Vanilla syrup | $9.50 | 25.4 | fl oz | $0.3740 |
| 12 oz hot cup | $95.00 | 1000 | each | $0.0950 |
| 12 oz lid | $48.00 | 1000 | each | $0.0480 |
Recipes
Example data. One row per ingredient in each drink. Unit cost looks up the Ingredients tab; line cost is quantity times unit cost.
| Drink | Ingredient | Quantity | Unit | Unit cost | Line cost |
|---|---|---|---|---|---|
| Latte | Espresso beans | 0.64 | oz | $0.7750 | $0.4960 |
| Latte | Whole milk | 10 | fl oz | $0.0375 | $0.3750 |
| Latte | 12 oz hot cup | 1 | each | $0.0950 | $0.0950 |
| Latte | 12 oz lid | 1 | each | $0.0480 | $0.0480 |
| Vanilla latte | Espresso beans | 0.64 | oz | $0.7750 | $0.4960 |
| Vanilla latte | Whole milk | 10 | fl oz | $0.0375 | $0.3750 |
| Vanilla latte | Vanilla syrup | 1 | fl oz | $0.3740 | $0.3740 |
| Vanilla latte | 12 oz hot cup | 1 | each | $0.0950 | $0.0950 |
| Vanilla latte | 12 oz lid | 1 | each | $0.0480 | $0.0480 |
Modifiers
Example data. A swap, not an add-on: oat takes out 10 oz of whole milk and puts in 10 oz of oat milk. The cost change can be negative.
| Modifier | Swap out | Swap in | Quantity | Unit cost out | Unit cost in | Cost change |
|---|---|---|---|---|---|---|
| Oat | Whole milk | Oat milk | 10 | $0.0375 | $0.0859 | $0.4844 |
Drinks
Example data. Cost per drink from the Recipes tab, the cost as a share of your menu price, and the price that would hit your target.
| Drink | Cost per drink | Menu price | Cost % | Target cost % | Price at target |
|---|---|---|---|---|---|
| Latte | $1.01 | $5.00 | 20.28% | 25% | $4.06 |
| Vanilla latte | $1.39 | $5.50 | 25.24% | 25% | $5.55 |
| Latte with oat | $1.50 | $5.50 | 27.24% | 25% | $5.99 |
What it is for
Recipe costing answers one question: what does one drink or plate cost you to make? You need it before you can set a price, judge a promotion, or tell whether the latte you sell the most is also the one that makes the least.
The usual spreadsheet mistake is pricing a modifier as an add-on. An oat milk latte does not cost a latte plus oat milk. It costs a latte with the whole milk taken out. The Modifiers tab handles that as a swap: it removes 10 oz of one ingredient, adds 10 oz of another, and shows the difference, which can be positive or negative.
All prices here are examples. Overwrite them with the numbers from your last invoices. The sheet starts from pack price and pack size because that is what an invoice gives you, and works out the cost of a single ounce or a single cup from there.
How to use it
- Download the Excel file. The CSV is the Ingredients tab only, because the links between tabs need a workbook.
- On Ingredients, list every ingredient and packaging item you use, with the pack price, pack size and unit from the invoice.
- Keep one unit per ingredient and use it everywhere. If you buy milk by the gallon, enter the pack size in fluid ounces (128) and write recipes in ounces.
- On Recipes, add one row per ingredient per drink. Type the drink name, the ingredient exactly as it is on the Ingredients tab, and the quantity.
- On Modifiers, list each swap: what comes out, what goes in, and how much.
- On Drinks, enter your menu price and your target cost percentage. Read off the cost per drink and the price that would hit your target.
- Update Ingredients whenever an invoice changes a price. Everything downstream recalculates.
The columns, one by one
Ingredients
- Pack price, Pack size, Unit. What the invoice says you paid, for how many units. Use the smallest unit your recipes use (fl oz, oz, each).
- Unit cost. Formula. Pack price divided by pack size.
Recipes
- Drink, Ingredient, Quantity. One row per ingredient. The ingredient name must match the Ingredients tab exactly.
- Unit cost, Line cost. Formulas. Unit cost is looked up by name; line cost is quantity times unit cost.
Modifiers
- Swap out, Swap in, Quantity. The ingredient the modifier removes, the one it adds, and how much of each.
- Cost change. Formula. Quantity times (unit cost in minus unit cost out). Add it to the base drink.
Drinks
- Cost per drink. Formula. The sum of that drink's line costs on the Recipes tab.
- Cost %, Target cost %. Cost per drink over menu price, next to the share you are aiming for. Pick your own target.
- Price at target. Formula. Cost per drink divided by target cost %.
The formulas
These are the exact formulas in the Excel file, written for the first data row.
Unit cost
=B2/C2
Pack price divided by pack size.
Recipe unit cost
=VLOOKUP(B2,Ingredients!$A$2:$E$50,5,FALSE)
Find the ingredient by name on the Ingredients tab and read its unit cost.
Modifier cost change
=D2*(F2-E2)
Quantity times the difference between the ingredient going in and the one coming out.
Price at target
=B2/E2
Cost per drink divided by target cost %.
This template costs ingredients and packaging only. It does not include labor, rent or waste, and it does not adjust for trim or yield loss. For prep items with a yield, the recipe cost calculator has a yield column.
A worked example
Example numbers only. A gallon of whole milk is $4.80 and holds 128 fl oz, so one ounce costs 3.75 cents. A 64 oz carton of oat milk at $5.50 costs about 8.6 cents an ounce.
A latte with 0.64 oz of beans at $0.775 an ounce (49.6 cents), 10 oz of whole milk (37.5 cents), a cup (9.5 cents) and a lid (4.8 cents) costs $1.01. At a $5.00 menu price that is a 20% cost.
The oat swap removes 10 oz of whole milk (37.5 cents) and adds 10 oz of oat milk (86 cents), a change of 48 cents. The oat latte costs $1.50, so at a $5.50 price it runs 27%, higher than the plain latte even though it sells for more. At a 25% target, the sheet shows the price that restores the margin.
When the sheet stops being enough
A spreadsheet works while one person owns it and the item list stays small. It stops working when two people count at once, when invoices change your costs faster than you update the sheet, or when the order still has to be typed into a vendor’s site by hand. Our ShelfCount versus spreadsheets page is honest about where that line sits. Fill it in and upload it with the self-guided importer, which will ask you to confirm the columns before anything saves. The importing your data guide covers how.
Questions we get
How do I cost a recipe?
Work out the cost of one unit of each ingredient (pack price divided by pack size), multiply by the quantity in the recipe, and add the lines up. Include packaging for drinks and takeout items.
What food cost percentage should I aim for?
It depends on the menu. Many operators aim for roughly 25% to 35% on food, and drinks often run lower. Pick your own target and put it in the Drinks tab. The sheet will show the price that hits it.
Why are modifiers a swap and not an add-on?
Because many modifiers replace something. Oat milk replaces dairy, extra shot adds to the recipe, and no whip removes an ingredient. A swap covers all three, with the cost change as a signed number.
Do the formulas work in Google Sheets?
Yes. They use VLOOKUP, SUMIF and basic arithmetic. Import the .xlsx with File, then Import, and the links between tabs carry over.
What happens when my prices change?
Update the pack price on the Ingredients tab. Unit cost, recipe lines, modifiers and drink costs all recalculate. In a spreadsheet you have to remember to do that every invoice, which is the part ShelfCount automates.
Related
- Recipe cost calculator handles yield and cost per portion in the browser.
- Food cost calculator turns two counts and your invoices into a period food cost.
- Recipes and costing explains how recipe costs are calculated in ShelfCount.
- Building recipes covers ingredients, prep recipes and yields.
- Inventory count sheet A walk-order count sheet for a cafe or a restaurant, with blank count boxes. Print it or open it in a spreadsheet.
- Par sheet Par, order quantity and cases to order from daily usage, delivery gaps and safety stock, with the formulas built in.
- Inventory spreadsheet Items, counts and usage in three tabs: opening plus purchases minus closing, priced out for the period.
- Coffee shops shows how the same counts feed par, orders and drink cost.
Written by the ShelfCount team. We run Stella Coffee in Los Angeles and built these for our own back office first. Last checked October 2026.
ShelfCount keeps recipe cost current from your invoices, handles modifiers from your POS, and shows what each drink actually used.