A Recipe Costing Template for Google Sheets, Step by Step

A recipe costing sheet needs two tabs. One lists every ingredient you buy with its price per gram. The other lists a recipe's ingredients by weight and looks up each price from the first tab. Set it up that way and a new price for butter gets typed once, and every recipe that uses butter updates itself. Here's the whole build, with the formulas, using a batch of cookies that comes out to $14.40.

The short version

1. Tab one, Ingredients: name, what you paid, how many grams you got, price per gram.
2. Tab two, Recipe: ingredient name, grams used, cost (a lookup times the grams).
3. Total the batch, divide by yield, add packaging.
4. Work in grams for everything. It's the one decision that prevents most errors.
5. Put a date next to every price so you can see which ones are stale.

Tab 1: Ingredients

Make a tab called Ingredients with these columns:

The only formula is in column E: price divided by grams. Copy it down. (If costing is new to you, the steps in recipe costing are laid out separately.)

A few things that aren't obvious until they bite you:

Price paid means delivered. If the flour was $22 and the delivery charge worked out to $3 a bag, the price is $25. Freight is part of what the ingredient costs you.

Grams in it, always. One pound is 453.6 g. One ounce is 28.35 g. A kilo is 1,000. For liquids you measure by volume, weigh a cup once and write it down. For things you count, like eggs, put the count in column D (180 eggs in a 15-dozen case) and remember that row is priced per egg and not per gram. It works fine as long as the recipe tab uses the same unit.

Name things exactly once. The lookup in the next tab matches on the name. "Butter, unsalted" and "Butter unsalted" are different ingredients as far as a spreadsheet is concerned. Pick a style and stick to it.

Tab 2: Recipe

Make a second tab named for the recipe. This one's Choc chip cookies:

The formula in C2 says: find the name in column A on the Ingredients tab, bring back the fifth column (price per gram), and multiply by the amount. FALSE on the end means exact match only. Without it, a typo gets you a confident wrong number where you'd want an error.

With the prices above, the rows come out to $1.10, $6.00, $1.60, $4.50 and $1.00. Add the 20-cent flat line and the batch is $14.40. Divide by 46 sellable cookies and it's 31 cents each, 39 with the bag and sticker.

To make the column A cells a dropdown, so nobody can mistype an ingredient, select them and go to Data > Data validation > Dropdown from a range, then choose Ingredients!A2:A.

Tiny quantities like salt and baking soda aren't worth a lookup. A flat line for "the small stuff" is honest enough and saves you maintaining prices for things that cost a penny a batch.

Yield: the number that quietly matters most

Row 9 is doing more work than it looks. It's the number you can sell, which is the number you scooped minus the broken ones, the samples and the one you ate. Weigh or count a few real batches and use the average. If your "48 cookies" is really 46, your cost per cookie is 4% higher than the recipe claims, on every batch, forever.

For things sold by weight, do the same with weight. Granola loses water in the oven. If 10 kg of raw mix comes out as 8.6 kg, your yield is 86% and your cost per kilo is the batch cost divided by 8.6.

Recipes inside recipes

Buttercream goes into six of your cakes. Don't retype its ingredients six times. Cost the buttercream on its own tab, work out its cost per gram (batch cost divided by grams made), then add a row to the Ingredients tab:

Now any cake can list "Buttercream (ours)", 400 g, and when butter goes up the change runs through the buttercream into every cake. That's the single best trick in recipe costing, and it's also where spreadsheets start to strain, as we'll get to.

Adding labor and getting to a price

This sheet gives you ingredient and packaging cost. Labor and overhead are the other half and they're usually bigger. How to price baked goods picks up from this exact cookie, adds $30 of labor and 30 cents of overhead, and gets to a wholesale and a retail price. If you're costing batches that run through several steps, batch costing has its own wrinkles. And if your accountant is asking for cost of goods sold, that's a different calculation built on the same numbers.

If you'd rather not build it, here's the finished sheet, with the labor and pricing rows already added. It's an Excel file that opens in Google Sheets (File > Import), there's no sign-up, and it's yours to change.

Where this stops working

I like this sheet and I'd still tell you when it breaks.

It breaks when someone renames an ingredient and six recipes start showing #N/A. It breaks at the third level of nesting, when the caramel goes into the filling that goes into the tart, and one circular reference takes down the file. It breaks quietly when prices go stale, because nothing in a spreadsheet knows you bought butter again yesterday at a different price. And it never knew what you have on the shelf, so it can tell you what a batch costs and can't tell you whether you can make one.

None of that matters with eight recipes. It starts to matter around thirty.

How TaroStack does it

TaroStack does the same arithmetic, connected to your actual purchasing. When a delivery is received against a purchase order, the price paid, freight included, becomes the cost of that stock, so recipe costs follow what you really paid without anyone updating a tab. You buy by the case, stock by the each and cook by the gram, and the conversions are handled for you. Recipes can contain other recipes as deep as you need, and they're versioned, so changing the cookie in March doesn't rewrite what February's cookie was. And when you record a batch you see its real cost next to the recipe's expected cost, which is where a slipping yield shows up first.

You don't have to start over. Items and recipes import from a spreadsheet, with a preview of every row before anything is saved. Recipes and costing are part of the Standard plan at $99 a month.

Questions people also ask

What is recipe costing?

Working out what one batch, and one unit, of a recipe costs to make, from the price and quantity of every ingredient. It's the base that pricing, margins and "should we even sell this" all stand on.

What are the steps in recipe costing?

Convert every ingredient's purchase price to a price per gram. Multiply by the grams in the recipe. Total the batch. Divide by the sellable yield. Add packaging. Then add labor and overhead before you set a price.

What is the food cost formula?

Food cost percentage is ingredient cost divided by selling price. A cookie with 31 cents of ingredients that sells for $3.25 has a food cost of about 10%. It's a useful check, and on its own it's misleading for handmade goods, because it ignores labor.

Is there a free recipe cost calculator?

The sheet on this page is one, and building it yourself means you'll understand it when a number looks wrong. Google Sheets is free. If you'd like one that stays current with your purchases on its own, that's what inventory software with recipes is for.