Menu costing in Excel: a step-by-step guide

1. Start with one fixed sequence

A menu costing sheet in Excel becomes reliable when I always follow the same order: set the purchase price per base unit, enter recipe quantities, account for yield and trimming loss, total the recipe cost, choose a target food cost percentage, and only then derive the net and gross selling price. That sequence is what stops “gut-feel pricing” from taking over. I will use a mushroom risotto as an example. Each ingredient gets its own line: rice, stock, mushrooms, onion, butter, Parmesan, white wine, oil and herbs. I separate grams, millilitres and pieces from the start, because mixed units are where many spreadsheets quietly go wrong.

I usually work with two sheets. On the “Master Data” sheet I store purchasing quantities and net purchase prices, for example 1,000 g of rice for €4.80 or 2,500 ml of stock for €6.00. On the “Dishes” sheet I enter the portion, such as 180 g rice, 220 ml stock and 120 g mushrooms. Excel then multiplies the price per unit by the recipe quantity and totals the cost. That gives me more than a rough plate estimate: it shows the real material value I put on the menu. If I later want the same logic in a structured operating process, recipe and stock data can also be organised in Understand what goes into every dish on your menu.

2. Clean input data matters more than elegant formulas

The most common costing error is usually not in Excel but in poor master data. I therefore assign one clear base unit to every ingredient. Dry goods are usually calculated in grams, liquids in millilitres, and truly countable items in pieces. Take mushrooms as an example: they may arrive in a 3 kg crate, but after cleaning and sorting I might have only 2,550 g that are usable. If I simply divide the crate price by 3,000, the dish looks falsely cheap. That is why I add columns for raw quantity, yield and usable quantity. Only then do I calculate the real price per gram.

This matters even more with base recipes. If I cook 10 litres of vegetable stock and lose part of that volume through reduction and straining, I do not cost the full pot, I cost the usable final volume. I add up all ingredient costs for the stock and divide them by the millilitres actually available at the end. I do the same for jus, sauces, marinades or dessert creams. I maintain those pre-recipes once and then reuse the calculated value in several dishes. That keeps the same sauce from appearing at €0.28 on one plate and €0.44 on another just because somebody guessed in a rush. That is the point where I see whether my file is guiding decisions or merely collecting numbers.

3. Recipe cost is not profit

Once the ingredients are set up properly, I total the recipe cost per portion. For the risotto, a plausible example might look like this: rice €0.86, stock €0.53, mushrooms €1.14, onion €0.06, butter €0.14, Parmesan €0.32, wine €0.28, oil and herbs €0.17. Together that gives a net food cost of €3.50 per portion. That is not yet a selling price. It is only the material basis. From there I decide, as the operator, what target food cost percentage makes sense for this dish. A quick lunch plate often needs a different frame from a labour-intensive evening plate with a longer table time.

In Excel I add a “Target FC %” column. If I set the risotto at 28 percent, I divide €3.50 by 0.28 and arrive at a net selling price of €12.50. Many people mistake the gap between €12.50 and €3.50 for profit. It is not. That difference still has to cover labour, energy, rent, spoilage, payment fees, cleaning and business risk. So I never compare dishes in isolation. A pasta dish may work at 24 percent, while a fish dish may not, because purchase prices and portion weight fluctuate more heavily. The target percentage is not a neutral Excel answer. It is a management decision tied to my concept, service level and expected demand.

4. Keep net, gross and VAT separate

As soon as I have the net selling price, I calculate the gross menu price. For Germany, current as of 30 September 2026, restaurant and catering services for food are again taxed at 7 percent from 1 January 2026, while drinks are generally excluded from that reduced rate and usually remain at 19 percent. That follows from the BMF guidance dated 22 December 2025 and the German VAT Act. For costing, the practical consequence is simple: food cost percentages and margins must always be calculated on a consistent net or gross basis.

For my risotto, with a net selling price of €12.50 and 7 percent VAT, the gross price is €12.50 multiplied by 1.07, which is €13.38. In reverse, at 7 percent VAT, gross divided by 1.07 gives net. I must not apply the same tax logic to a drink simply because it appears next to the dish on the menu. That is why my Excel file has a separate VAT-rate column for each item. With menus, bundled offers or doubtful cases, I check the classification with accounting or tax advisers. Neither Excel nor a POS makes a tax rate legally correct by itself. In daily practice, though, this discipline already prevents many errors: maintain food and drinks separately, and never mix net purchase data with gross sales calculations.

5. Labour and sales role belong next to the calculation, not inside one percentage

Many templates fail because they force everything into a single percentage. I deliberately separate food cost from the other decisions around a dish. An item may have inexpensive ingredients and still be operationally expensive because it needs close attention on the stove, several components or delicate plating. Next to the costing columns, I therefore keep internal rating fields for production effort, spoilage risk, likelihood of complaints and the dish’s role on the menu. A cheap plate with a high error rate is often worse for me than a slightly more expensive plate that the kitchen can send quickly, safely and consistently during peak service.

For example, a risotto and a baked potato may each show a recipe cost of €3.50. I still would not price them blindly in the same way if the risotto demands much more pan time and coordination in the evening rush. The real question is not only what material value sits on the plate, but how reliably we can produce that plate when the pass is under pressure. So I do not discuss prices without kitchen and service input. If a dish repeatedly slows the pass, that belongs in the price decision. If I want to make that connection more visible in daily work, I can also structure the handover between ordering and production with Give service, kitchen and bar a shared view of orders.

6. Build the Excel file so it stays usable in real life

For daily use, I keep the file deliberately simple. On the “Master Data” sheet I use columns for item name, supplier, purchase quantity, purchase unit, net purchase price, yield, usable quantity, price per base unit and last change date. On the “Dishes” sheet I keep dish name, ingredient, recipe quantity, base unit, price per base unit, ingredient cost, target food cost, net selling price, VAT rate and gross selling price. The core formulas are straightforward: usable quantity equals purchase quantity times yield, unit price equals purchase price divided by usable quantity, ingredient cost equals recipe quantity times unit price. At the beginning, I do not need anything more complicated than that.

What matters are the control columns. I highlight ingredients with no base unit, yield figures above 100 percent or prices that have not been updated through several supplier rounds. I also log a reason for every relevant change, such as “supplier price increase”, “portion adjusted” or “new measured yield”. That makes it traceable why a dish costs €14.20 today instead of €13.50. A scenario column is also useful: what would the same plate look like at 26, 28 or 30 percent food cost? Then I can see immediately which price level is both operationally sound and psychologically realistic for guests, instead of reacting only when margin pressure is already visible at the end of the month.

7. The typical mistakes are usually organisational

The same problems come up again and again: gross prices entered as if they were net purchase prices, no yield reduction for trimming loss, portion sizes based on feel, inconsistent units and outdated supplier prices. Then there is an operational mistake behind the spreadsheet: the kitchen changes portion sizes under pressure, but the file stays old. In that case, the formula is correct while reality is not. I organise this fairly with clear ownership. One person updates purchasing prices, another is responsible for recipe quantities, and before a new menu goes live we review both together. If mushroom cleaning loss rises in one season, that cannot remain silent kitchen knowledge.

When a dish clearly needs to become more expensive on paper, I still have several clean options: adjust the portion, rework the recipe, replace a component, run the dish as a seasonal item or raise the price. The weakest option is usually to give away margin and ignore the gap. That is where Excel is genuinely useful: not only for arithmetic, but for preparing decisions. I use the sheet weekly for volatile products such as mushrooms, fish or cream, fully before a menu change, and spot-check it during service for sensitive dishes at the pass. That turns a spreadsheet into a fair, understandable process for team, kitchen and guests instead of an isolated office exercise.