Build a Recipe Cost Calculator in Excel

A recipe cost calculator in Excel tells you what one batch costs, what each serving costs and what to charge for it. You keep one list of ingredient prices, and every recipe looks its costs up from that list, so when butter gets more expensive you change one cell and every recipe updates. This guide builds one step by step, using a batch of 12 muffins. It's for cooks, café and bakery owners, catering students and anyone applying for a hospitality job that uses Excel.

How the calculator is laid out#

The calculator uses two sheets:

• Ingredients: one row per ingredient, with what you pay for a pack and how much is in it.
• Recipe: one row per ingredient in the recipe, with the quantity it uses.

Keeping prices on their own sheet is the key idea. A recipe never contains a typed price; it always looks the price up. That way, one price change flows into every recipe.

Step 1: Work out the cost per unit for each ingredient#

Recipes use grams, millilitres and single items, but you buy packs. So first turn each pack price into a cost per unit. On a sheet called Ingredients:

Type =B2/C2 in E2 and fill it down to E6 (Windows: Ctrl+D; Mac: ⌘D). Flour costs 1.80 for 1,000 g, so each gram costs 0.0018. Don't round these numbers: tiny unit costs need every decimal, or the totals drift.

Always enter pack size in the same unit the recipe uses. If you buy flour by the kilogram but the recipe uses grams, enter 1000, not 1.

Step 2: Pull each unit cost into the recipe with VLOOKUP#

On a sheet called Recipe, list the ingredients and quantities. Then let VLOOKUP fetch each unit cost from the Ingredients sheet:

=VLOOKUP(A2,Ingredients!$A$2:$E$6,5,FALSE)

Here's what each part of the VLOOKUP does:

• A2 is the ingredient to find.
• Ingredients!$A$2:$E$6 is the price table. The $ signs lock it, so it stays the same as you fill down.
• 5 returns the fifth column of the table, Cost per unit.
• FALSE means exact match only. Without it, VLOOKUP can return the wrong ingredient's price.

The ingredient names must match exactly, apart from capitals. "Flour " with a trailing space returns #N/A. The VLOOKUP function reference explains the arguments, and the VLOOKUP lesson lets you practise them.

Step 3: Total the batch cost with SUMPRODUCT#

Each line cost is quantity × cost per unit: =B2*C2 in D2, filled down. Add the column with SUM and you have the batch cost.

SUMPRODUCT does both in one formula, with no helper column:

=SUMPRODUCT(B2:B6,C2:C6)

It multiplies each quantity by its unit cost and adds the results: 0.90 + 3.00 + 1.05 + 0.50 + 0.30 = 5.75. The two ranges must be the same size, or you get #VALUE!. The SUMPRODUCT function reference shows other uses, such as weighted averages.

Step 4: Calculate the cost per serving with ROUND#

Keep the summary in a small block to the right of the recipe: labels in column F, values in column G. Put the number of servings in G2, and in G3 divide the batch cost by it:

=ROUND(SUMPRODUCT(B2:B6,C2:C6)/G2,2)

With 12 muffins, 5.75 / 12 = 0.47916..., and ROUND to 2 decimal places gives 0.48. ROUND changes the stored value, not just how it looks, so totals built on it add up to the cents you see. The ROUND function reference and the guide to ROUND, ROUNDUP and ROUNDDOWN explain when to round up instead.

If you build several recipes side by side and copy the summary formulas, write fixed cells such as the servings with $ signs, like $G$2, so they don't shift. The absolute references lesson practises exactly this.

Step 5: Find the food cost percentage and a menu price#

Food cost percentage is the share of the selling price that goes on ingredients:

=G3/G4

If the cost per serving in G3 is 0.48 and the selling price in G4 is 2.50, the result is 0.192. Format it as a percentage to show 19.2%.

To go the other way and set a price from a target food cost, divide the cost by the target. With a 30% target:

=ROUND(G3/30%,2)

The result is 1.60. Your target percentage is a business decision; 30% here is only an example. Food cost leaves out labour, rent and packaging, so check your full margin too. The guides to the profit margin formula and the markup formula show the difference between the two.

Allow for waste and trim#

Some ingredients lose weight before you use them: peel, bones, trimmings. The share you can actually use is called the yield. Add a Yield column to the Ingredients sheet and divide by it:

=B2/C2/F2

If carrots cost 1.00 per 1,000 g and only 80% is usable after peeling (F2 = 80%), each usable gram costs 0.001 / 0.8 = 0.00125, not 0.001. Skipping yield makes recipes look cheaper than they are.

Common recipe costing mistakes#

1. Mixing units. A pack size in kilograms with a recipe quantity in grams makes costs 1,000 times too big. Pick one unit per ingredient.
2. Typing prices into the recipe. They go stale. Always look prices up from the Ingredients sheet.
3. Leaving out FALSE in VLOOKUP. An approximate match on an unsorted list returns the wrong price without any error.
4. Rounding the unit costs. Round only the final money results, such as cost per serving and menu price.
5. Forgetting small items. Salt, oil and baking powder are cheap per recipe but add up across a menu. List them anyway.

For price lists with discounts and bulk prices, see price lists and discounts in Excel. For the other spreadsheets used in kitchens and hotels, read about Excel skills for hospitality jobs.

Practise it#

The calculator rests on three skills: an exact-match lookup, rounding and locked references. Practise VLOOKUP in a live spreadsheet, then practise ROUND, ROUNDUP and ROUNDDOWN. Each formula is checked instantly. Then try the exercise on this page to cost a muffin from scratch.