A recipe cost calculator in Google Sheets needs exactly two sheets: an Ingredients sheet that turns every pack price into a cost per gram, and a Recipes sheet that looks up those costs and multiplies them by what your recipe uses. The whole thing runs on four formulas, and you can build it in about thirty minutes. Here they are.
This is a genuinely useful tool and you should build it. It’s also worth knowing where it stops working — there’s an honest section on that at the end, because the failure isn’t obvious until you’ve lived with it for a few months.
Sheet 1 — Ingredients (your price list)
Create a tab called Ingredients. Five columns:
| Col | Header | Example |
|---|---|---|
| A | Ingredient | Butter |
| B | Pack price | 180 |
| C | Pack size | 225 |
| D | Unit | g |
| E | Cost per unit | 0.80 |
Column E is the only formula on this sheet. In E2, enter:
=IFERROR(B2/C2, "")
Drag it down the column. The IFERROR wrapper keeps the sheet clean while rows are still empty.
That single division is the whole idea behind costing: you never cost by the pack, you cost by the base unit the recipe actually uses. Butter at ₱180 per 225 g block is ₱0.80 per gram. Eggs at ₱225 a tray of 30 are ₱7.50 per piece — same formula, different unit.
Fill in every ingredient you buy, including packaging. A cake box is an ingredient as far as your costs are concerned.
Sheet 2 — Recipes (the calculator)
Create a tab called Recipe. Four columns, and one formula that does the work:
| Col | Header | Example |
|---|---|---|
| A | Ingredient | Butter |
| B | Quantity | 200 |
| C | Unit | g |
| D | Cost | 160.00 |
In D2, this pulls the per-unit cost from your Ingredients sheet and multiplies it by the quantity:
=IFERROR(B2 * VLOOKUP(A2, Ingredients!$A:$E, 5, FALSE), 0)
Drag down. The $A:$E is locked with dollar signs so the lookup range doesn’t shift as you copy the formula.
Reading it in plain language: find this ingredient’s row on the Ingredients sheet, grab the 5th column (cost per unit), and multiply by how much this recipe uses. Then total it up — say your ingredient rows run to 25:
=SUM(D2:D25)
Add labor, overhead, and your selling price
Ingredient cost alone will underprice you badly, so put four more rows underneath the total. Assume your ingredient total landed in D26, your hours go in B27, and your hourly rate in B28:
| Row | Label | Formula |
|---|---|---|
| D27 | Labor | =B27*B28 |
| D28 | Overhead | 120 (a flat estimate to start) |
| D29 | Total cost | =D26+D27+D28 |
| D30 | Selling price | =D29/(1-B30) |
Put your target margin in B30 as a decimal — 0.35 for 35%. That last formula is the one that separates a real price from a guess, because it makes profit a share of the selling price rather than a tip added on top of cost.
Selling price = Total cost ÷ (1 − margin)
At 35% margin you divide by 0.65. A ₱888 cake becomes ₱1,366 — round it to ₱1,400. Full walkthrough in how to price your home-baked goods.
Two upgrades that stop it breaking
Do these two things or the sheet will quietly betray you within a month.
1. Make column A a dropdown, not free text. VLOOKUP matches on exact text, so "Butter" and "butter " (with a trailing space) are different ingredients to a spreadsheet. One typo and your cake silently costs ₱160 less than it should. Select column A on the Recipe sheet → Data → Data validation → Dropdown (from a range) → point it at Ingredients!A2:A100. Now you can only pick real names.
2. Copy the whole Recipe tab per recipe. One tab per product — Ube Cake, Ensaymada, Brownies. They all read from the same Ingredients sheet, so updating a price updates every recipe at once. That shared price list is the single best thing about this build.
Where the spreadsheet starts to fight you
This calculator is real and it works. Here’s what it can’t do, laid out honestly, so you recognise the moment rather than blaming yourself:
- It only knows your latest price. Overwrite butter’s ₱180 with ₱200 and the old number is gone. You lose the history, so you can never see that butter has climbed 18% since March.
- It can’t do weighted averages. If you have stock bought at two different prices, the honest cost is a blend of both — a spreadsheet just holds whichever number you typed last. (More on why that matters in tracking your ingredient costs.)
- It doesn’t know what’s in your kitchen. A cost calculator isn’t an inventory. It will happily cost a cake you don’t have the eggs to bake.
- Orders live somewhere else. Pickup dates, deposits, and which orders actually earned stay outside the sheet, so you can’t see margin per order.
- It’s miserable on a phone. Which is exactly where you are when a customer asks for a quote.
- Errors are silent. A broken
VLOOKUPreturns#N/Aif you’re lucky and a wrong number if you’re not. Nothing warns you that a price is stale.
None of that matters much at five recipes. At twenty recipes and a shifting market, updating prices by hand becomes a monthly evening you didn’t plan on — and the month you skip it is the month you quote from stale numbers. If you want the decision framed properly, see spreadsheet or app.
OvenSync does the same job without the maintenance: log a purchase once and every recipe using that ingredient re-costs itself, with weighted-average prices, live stock, and margin on every order. Start free → — 30-day Baker trial, no card.
Build the sheet. It will teach you more about your own numbers than any tool can, and the per-gram habit it drills is the one that makes every price you quote defensible. Just log your prices as you shop, keep the dropdown honest, and switch when the upkeep starts costing more than it saves.