Recipe costing template: every column, and the four places it breaks
You wanted a recipe costing template, not an essay. Fair enough. Here is every column the sheet needs, why each one earns its place, and a real dish costed all the way through it.
Then the part the download sites leave out. Every recipe costing spreadsheet I have built, and I have built several, breaks in the same four places. Not on day one, when it is beautiful and you are proud of it. Quietly, over about three months, while every number in it still looks perfectly tidy.
So: the columns first, then the breakages.
The recipe costing template, column by column
A working sheet has two parts. A master ingredient list, one row per thing you buy, and a recipe sheet per dish that pulls from it. Most free templates give you the second and skip the first, which is why they stop working the moment you have more than about six recipes.
On the master ingredient list
Ingredient. One row per thing you buy, named the way you say it out loud, not the way the invoice spells it. This column is where the sheet is won or lost, because the same product entered twice under two spellings splits one price history in half. That is the duplicate ingredient problem that quietly wrecks recipe costs, and it starts here, in a text cell, on a busy Tuesday.
Supplier. Skipped by almost everyone. Keep it. When a price moves you want to know whose, and a swapped line leaves the old row visible for comparison.
Pack size and pack unit. Two cells, not one. "2.27kg" typed as free text is a label. 2.27 in one cell and kg in the next is data you can divide by. It is break number two below.
Pack price ex-VAT. The figure from the invoice line, net. Not the invoice total, not the gross.
Unit cost. Pack price divided by pack size, expressed per gram, per millilitre or per each. This is a formula cell. If anyone is typing a number into it by hand, the sheet is already drifting.
Price date. The date on the invoice that gave you this price. One column, four keystrokes, and it turns "I think that is about right" into a fact you can check.
On the recipe sheet
Quantity used and unit. What actually goes in a portion, in the same unit family as the ingredient row. Weighed, not remembered.
Yield. The percentage of what you bought that reaches the plate, after peel, trim, bone and the oven. Most templates have no such column, and it is the single biggest reason a costed dish comes out cheaper on paper than it does in life.
Line cost. Quantity divided by yield, times unit cost. One formula, used identically on every row.
Sub-recipe rows. A line whose unit cost comes from another sheet rather than an invoice: the house pesto, the tomato sauce, the sponge base. Same shape as any other line, different source.
Batch size and portions. How many sellable portions this recipe actually makes. Sellable, not theoretical.
Total recipe cost, cost per portion, selling price ex-VAT, gross profit, GP%. The five cells you actually look at. Everything above exists to make these five true.
A worked dish: chicken and house pesto ciabatta
Illustrative UK 2026 trade prices, ex-VAT, not sourced from any one supplier. Supplier and price date are dropped from the printed table for width, but they live in the sheet.
| Ingredient | Pack size | Pack price | Unit cost | Yield | Qty used | Line cost |
|---|---|---|---|---|---|---|
| Ciabatta roll | 24 per case | £8.40 | £0.35 each | 100% | 1 | £0.35 |
| Chicken breast, roasted | 2.5kg | £21.50 | £0.0086/g | 75% | 70g cooked | £0.80 |
| House pesto (sub-recipe) | 780g batch | £9.29 | £0.0119/g | n/a | 25g | £0.30 |
| Rocket | 500g bag | £2.40 | £0.0048/g | 95% | 15g | £0.08 |
| Mayonnaise | 2.5kg tub | £6.20 | £0.0025/g | 100% | 10g | £0.02 |
| Deli wrap and bag | 500 per box | £30.00 | £0.06 each | 100% | 1 | £0.06 |
| Total | £1.61 |
The pesto row needs its own sheet, because its unit cost is derived rather than invoiced.
| Pesto ingredient | Quantity | Cost |
|---|---|---|
| Basil (£16.00/kg) | 200g | £3.20 |
| Hard cheese (£9.60/kg) | 150g | £1.44 |
| Pine nuts (£24.00/kg) | 100g | £2.40 |
| Garlic (£6.40/kg) | 20g | £0.13 |
| Cold-pressed rapeseed oil (£6.00/kg) | 350g | £2.10 |
| Salt and pepper | pinch | £0.02 |
| Batch cost | £9.29 |
About 820g goes into the blender. About 780g comes out into the tub once you have scraped the jug. £9.29 divided by 780g is £0.0119 per gram, so a 25g spread is 30p.
Now the same dish costed the way most templates do it, with no yield column and the pesto divided by what went in rather than what came out: chicken at 60p, rocket at 7p, pesto at 28p. Total £1.38.
The difference is 23p a sandwich. Sell 30 a day, six days a week, and that is roughly £2,150 a year of cost you left out of the sheet you built to stop exactly that happening.
Break 1: VAT lands in the wrong column
This is the commonest error in UK food cost spreadsheets, and it happens on both sides of the sum.
On the cost side, if you are VAT registered the VAT you pay on purchases is not your money, it is reclaimable. Cost ex-VAT. Most raw food is zero-rated anyway, so the mistake hides until you get to the lines that are not: packaging, cleaning products, some drinks. Paste a gross figure into pack price on those rows and the sheet overstates your cost.
On the selling side, the sheet needs the ex-VAT price. Your board price is what the customer pays. Your GP is calculated on what you keep.
And here is the bit no downloadable template handles, because it is a British problem. The rate depends on how the customer takes it. HMRC's Notice 709/1 says you must always charge VAT at the standard rate on food and drink for consumption on the premises it is supplied in (section 3.1), that hot takeaway food and drink meeting its tests is standard-rated (section 4.1), and that cold takeaway food and drink is zero-rated as long as it is not a type that is always standard-rated, such as crisps, sweets and some beverages.
Run our ciabatta through that at a £6.50 board price:
| Where it is eaten | VAT | Price ex-VAT | Cost | GP | GP% |
|---|---|---|---|---|---|
| Cold, taken away | Zero-rated | £6.50 | £1.61 | £4.89 | 75.2% |
| Eaten in | 20% | £5.42 | £1.61 | £3.81 | 70.3% |
Same sandwich, same cost, five points of margin between them and £1.08 a sale in cash. A template with one "selling price" cell cannot tell you that. Add a VAT rate column next to the selling price and let the ex-VAT price be a formula, so a dish that sells both ways gets two lines rather than an average nobody can defend.
Break 2: pack size to unit conversion
Suppliers quote you in the shape of their own packaging, and none of those shapes agree.
Bacon comes in a 2.27kg pack, because somebody once sold five pounds of it and the number never quite went away.
Flour comes in a 16kg sack. Milk in 2 litre bottles, six to a crate. Ciabatta by the case of 24. Oil by the 5 litre drum. Your recipes are written in grams and millilitres, so every single row needs a division before it means anything.
Three things go wrong, and all three look fine in the cell:
- The unit gets lost.
16typed into a column your formula reads as grams, when the sack is 16kg. That one you notice. The 2.27kg pack entered as 2.27 and then read as grams is subtler, and you will not. - Pack and case get muddled. £8.40 for 24 ciabatta is 35p a roll. Enter it as the price of one roll and the sandwich costs £9.66, so you spot it. A case price treated as a pack price on something cheap just sits there.
- The pack quietly changes size. The 500g bag becomes 450g at the same money. The unit cost column only tells the truth if the pack size cell was updated when the pack was.
The rule is one unit, held constant. Everything to cost per kilogram, per litre or per each before you form an opinion, on the master list, once, so no recipe sheet ever has to do it again.
Break 3: yield, cooking loss and sub-recipes
Break two is arithmetic. This one is about what the sheet cannot see.
You buy 100g of chicken. You do not serve 100g of chicken. Some goes in the trim and a good deal more goes up the extraction fan as steam, and neither of them asks for a refund.
The part that catches roasts, reductions and rendered bacon is set out in costing on finished cooked yield. In the table above, the yield column is the difference between 60p of chicken and 80p of chicken.
Sub-recipes are the same problem wearing a different hat. The pesto is not an ingredient you buy, so nothing on any invoice tells you what a gram of it costs. You have to make it, weigh what comes out, and divide. That is why it needs its own sheet, costed once and used as a line in every dish it touches.
For a pesto the yield correction is small, about 2p on this sandwich. For a jus you reduce by three-quarters, or a pan of onions that collapses to a tenth of itself, it is the whole game.
The spreadsheet-specific danger is the link. A sub-recipe cost on tab three, referenced by four dishes on tabs four to seven, is fine until somebody inserts a row on tab three. Then four dishes are wrong at once, in the same direction, invisibly. One wrong sub-recipe is never one wrong dish.
Break 4: the sheet is right on day one and wrong by month three
This is the one that gets everybody, including me, and no amount of column design fixes it.
You build the template on a quiet Sunday. Every price is fresh, every formula works, the GP% column is a thing of beauty. Then the invoices keep arriving and nobody re-keys them, because re-keying forty ingredient prices from a fortnight of invoices is nobody's idea of a Tuesday.
Three months on, the sheet still looks exactly as good as it did. That is the problem. There is no visual difference between a price from last week and a price from March, and you go on setting menu prices off both. It is worth finding out how old your recipe costs already are before assuming they are current. The answer is usually older than you think.
The only real fix is the invoices themselves. Not a reminder, not more discipline. A route from the price on the invoice to the cell in the sheet that does not depend on you feeling energetic. Everything else is a promise to your future self, and your future self is busy.
Where to get the recipe costing template
Honest answer first: the free Excel downloads that rank for this are mostly American, and several want your email before they hand anything over. The best of them, Spreadsheet123's recipe cost calculator, splits primary and secondary ingredients and adds lines for labour and utilities. It is decent work. It does not deal with yield, and it does not deal with VAT, because it was never built for a country where the same sandwich has two rates.
Our version of the sheet is online rather than downloadable: the free recipe costing calculator does the same columns in the browser. Pack size and pack price in, unit cost calculated for you, batch quantities and the portions that batch makes, and an ex-VAT selling price with a VAT rate you set, so the GP% it gives you is the one you keep. No sign-up, no email.
Be clear about what it is not. It holds one recipe at a time, it has no yield column, so adjust your unit cost or your quantity yourself, and it does not link sub-recipes. It is the sheet, done properly, for one dish.
When you have forty recipes and invoices landing weekly, the limit stops being the sheet and starts being break four. That is what CostingBrik is for: you upload the invoice, it reads the lines, and the new price flows through to every recipe that uses that ingredient, sub-recipes included. What it does not do is weigh your finished pesto or decide your portion sizes. Those stay yours, and they are the numbers that decide whether the sheet is true.
Build the columns once, master list first, then the recipe sheet. Get one dish all the way through and the second one takes five minutes.
Then be honest about which of the four breaks you are living with. Most operators I talk to have all four at once. On one sandwich, the yield and sub-recipe break alone was 23p, about £2,150 a year at 30 a day, before the VAT column or a stale price gets involved. You have a menu full of sandwiches.
Ed O'Brien has run Hunters Cake Company for 17 years across cafés in Witney, Burford, and a bakery in Carterton, Oxfordshire. He's building Brikly - modular tools that give independent café owners the same data the big chains have, without the big chain price tag.