Build a food cost calculator in a spreadsheet

A food cost calculator does not need to be clever. It needs to answer one question reliably: what does each dish cost to make, and what share of its price does that cost represent? A well-built spreadsheet can do that for years. A badly built one can quietly mislead a kitchen for just as long, because a formula that looks right gives the same confident answer whether its inputs are current or two seasons out of date.

This guide shows how to build a food cost calculator that stays honest. It covers the six columns every ingredient line needs, the one decision about wastage that changes every number below it, how to handle sauces and other sub-recipes, how to turn dish costs into a food cost percentage, and how to tell when the sheet has done all it can.

You can follow along in any spreadsheet tool. Nothing here needs macros or add-ons, and the structure is deliberately simple enough that someone else in the kitchen can maintain it when you are on holiday.

Before building anything, be clear about the three jobs the sheet has to do:

The third job is where most sheets fail. A recipe costing done once at menu launch is a snapshot. A food cost spreadsheet that pulls every ingredient price from a single price list and recalculates when that list changes is a tool. Everything in the layout below is designed to make the third job easy.

Keep the calculator on three tabs:

The separation matters. When a supplier raises the price of cream, you change one cell on the ingredients tab and every recipe that uses cream, and every dish built on those recipes, updates on its own. If prices are typed directly into recipes, the same change has to be made in a dozen places, and one of them will be missed.

The ingredients tab is the foundation, and an hour spent getting it right saves weeks of confusion later. Give each ingredient one row and these columns:

Choose one base unit per ingredient and stick to it. Weigh solids in kilos, measure liquids in litres, and count only the things that are genuinely bought and used by the piece, like eggs or bread rolls. Mixing grams and kilos, or litres and bottles, is the single most common source of costings that are wrong by a factor of a thousand.

It is worth adding a short "notes" column too. When a price is unusual, such as a seasonal spike or a one-off substitute brand, a note saying so stops someone treating a temporary price as the new normal when they review the sheet months later.

Lay out the recipes tab as a series of blocks, one per dish or sub-recipe, separated by a blank row. Each block starts with a header row naming the dish, its batch size and how many portions that batch makes. Below it come the ingredient lines with their six columns, and at the foot of the block a total row adds up the line costs and divides by the number of portions.

Keep every block in the same shape. When each recipe has its header in the same column and its total in the same place, the menu tab can find each dish's cost with a simple lookup on the dish name, and anyone opening the sheet can read any recipe without learning a new layout. Freeze the header row of the tab so the column names stay visible as you scroll.

Name sub-recipes clearly so they cannot be mistaken for dishes: "SUB mustard sauce" or "Base: chicken stock" makes it obvious that the line is a component rather than something guests order. When a dish uses a sub-recipe, its ingredient line looks up the sub-recipe's cost per unit exactly as it would look up an ingredient from the first tab.

On the recipes tab, each line of a recipe needs six columns. Together they turn a purchase price into a cost per portion.

Add the line costs together and you have the cost of the batch or the plate. Divide a batch cost by the number of portions it makes and you have the cost per portion.

The yield column is the one most often left out, and it is the one that changes every number below it. If you buy a whole salmon at a given price per kilo, you cannot serve the head, the bones or the skin. If only 55 percent of the weight ends up as portions, then the real cost of the usable fish is nearly double the price on the invoice. A recipe that ignores yield is costing food you never serve at the price of food you do.

That is the single most important rule in building the sheet: wastage belongs inside the recipe, as a yield percentage on each ingredient, not beside it as a vague allowance added at the end. A flat "add ten percent for waste" at the bottom of a recipe treats a carrot and a lobster the same way, and they are not the same.

Yield percentages should come from measurement, not memory. Weigh a delivery as it arrives, prepare it the way the kitchen normally does, and weigh what is usable. Do it two or three times for your most expensive ingredients and take the average. It takes an afternoon and it is the most valuable afternoon you will spend on the sheet.

Most dishes include components that are themselves recipes: a sauce, a stock, a dressing, a dough. Cost these as their own recipes, on the recipes tab, with a batch size and a yield. Then treat the finished sub-recipe as an ingredient in the dishes that use it, at its cost per millilitre, gram or portion.

This is the second most common gap in home-built sheets. A dish that lists "jus, 40ml" without costing the jus is understated by whatever the jus costs, and if four dishes use the same jus, the error appears four times. Cost each sub-recipe once and let the dishes that use it pull the number.

On the menu tab, each dish needs four more columns:

Strip tax from the selling price before calculating. If your menu shows prices including tax, the percentage will look better than it is by the full rate of tax unless you remove it first.

Here is an illustrative dish, a pan-roasted chicken supreme with crushed potatoes and a mustard sauce. The numbers are examples to show the layout, not a recommended costing.

The cost per portion is 3.79. At a selling price of 16.50 before tax, the food cost percentage is 23.0 and the cash margin is 12.71. Notice the sauce: it costs more than three times the potatoes, which is exactly the kind of thing that disappears if sub-recipes are left uncosted.

A new sheet should be tested like any other piece of equipment before it goes into service. Three quick checks catch most errors.

First, cost one dish by hand on paper, from the invoices, and compare it with the sheet. If the two differ by more than a few cents, trace the difference line by line until you find the cell that is wrong. It is usually a unit conversion or a yield typed as 85 instead of 0.85.

Second, change one ingredient price deliberately and watch what moves. Every recipe that uses the ingredient, and every dish built on those recipes, should update. Anything that does not update has a typed number where a formula should be.

Third, sense-check the extremes. Sort the menu tab by food cost percentage. A dish showing 4 percent or 90 percent is almost certainly a mistake rather than a discovery, and the top and bottom of the list are where errors gather.

Once the sheet is trusted, it becomes a pricing tool as well as a costing one. When the kitchen develops a new dish, cost it in the recipes tab first, before it goes on the menu, and then decide the price with the cost in front of you.

There are two ways to approach the price. The first starts from a target: if you want a dish to sit at around a quarter of its price in food, divide the cost per portion by 0.25 to get a starting price, then adjust it to where similar dishes sit on your menu. The second starts from the margin: decide how much cash the dish needs to contribute and add it to the cost. The second approach is often better for expensive dishes, where a percentage target would push the price too high, and for cheap ones, where it would leave money on the table.

Whichever you use, write the result into the menu tab and keep the costing. When the dish has been on the menu for a month, compare its cost with what actually sold and decide whether it earned its place.

A food cost calculator is only as good as its prices. Build a habit that keeps them current without a monthly afternoon of retyping.

Our guide on nine ways to reduce food costs without a new menu covers what to do when the updated sheet shows a dish has slipped.

The menu tab tells you what each dish should cost. To compare that with what the kitchen actually spent, you need two more things: how many of each dish sold in the period, and your stock-based actual food cost for the same period. Multiply each dish's cost by the number sold, add them up, and you have your theoretical food cost. Compare it with the actual figure and the difference is the gap worth investigating.

Our guide to actual vs theoretical food cost explains how to read that gap, and the step-by-step guide to calculating food cost percentage covers the stock-based side.

A spreadsheet that looks tidy can still be wrong. These are the errors that most often slip in.

The last one is worth a rule of its own: protect the formula columns so they cannot be typed over, and keep inputs and calculations visibly separate. In most spreadsheet tools you can colour input cells one way and calculated cells another, which makes it obvious which ones a person should change.

A good food cost spreadsheet can serve a single venue with a stable menu for a long time. It starts to struggle when any of these become true:

None of these means the spreadsheet was a mistake. It means the business has grown past what a single file can hold. A spreadsheet's flexibility is also its weakness: it lets anyone change anything, as the long history of spreadsheet errors in businesses of every size shows.

The recipe build-up in this guide is work your spreadsheet does well, and many kitchens keep it there. Where a system helps is on the sales side and the menu itself.

In servearound, each dish is one record in a library shared by every menu it appears on, with its own price on each menu where you need that. Once a dish's cost is set, the menu report in Analytics shows its units sold, revenue and margin from what actually went out during service, so the "how many of each dish sold" column fills itself from the same orders the kitchen cooked. Changing a price happens once and reaches every channel the menu is on. If you are weighing up whether to move, our pricing page sets out what a venue pays, with no fees per cover or per order.

Two is enough for the final cost per portion, but keep the full precision in the calculation columns. Rounding each line before adding them together creates small errors that add up across a recipe.

Keep it separate. A food cost calculator measures ingredients. If you want to compare buying in against making in house, add labour in a separate column so the food cost percentage stays comparable with your stock-based figure.

Re-cost your twenty best sellers whenever their main ingredient prices move, and the full menu at least at every menu change. A quarterly full review suits most kitchens.

Yes, at least once. A microherb, a drizzle of good oil or a few shavings of hard cheese can cost more than the starch on the plate, and because garnish is added by eye it drifts upward over time. Cost it properly when you build the recipe, then decide whether it earns its keep. Many kitchens find one or two garnishes that add cost without anyone noticing them.

Cost the board as it is meant to leave the kitchen, with each component weighed, and then check it against reality by weighing three or four boards on a busy night. Sharing dishes drift faster than plated ones because nobody owns the exact quantity, so a quick weigh every few weeks keeps the costing honest.

It can, with a venue column on the menu tab and shared recipes, but it gets fragile quickly. Once prices or recipes differ between sites, version control becomes the main problem.

Build the food cost calculator on three tabs: ingredients, recipes and menu. Give every ingredient line six columns, put wastage inside the recipe as a measured yield, cost every sub-recipe once, and strip tax from selling prices. Pull prices from one list so a single change updates every dish.

Kept current, that sheet will tell you what every plate costs and what it earns. When keeping it current starts to cost more time than it saves, that is the signal to move the sales side, and eventually more, into a system built for it.