Free hospitality pricing template in Excel: cost of goods, selling price, contribution margin – with free download

A common reason hospitality businesses run into trouble: their selling prices do not cover their actual costs. If you work without clean costing, you give away margin – or worse, you lose money on every dish you sell without noticing.

This article explains the business logic behind pricing in hospitality, shows typical mistakes and provides a concrete calculation model you can build straight into Excel. At the end there is a free Excel template with four worksheets – from the ingredient list to the contribution margin analysis.

Why poor costing is the main cause of margin problems

Many operators set prices by gut feeling or by looking at the competition. The problem: their own cost structure stays invisible. A pasta dish listed on the menu at €14.90 can be profitable with a cost of goods of €5.20 – but if supplier price increases push the cost of goods to €6.80, it can suddenly drop below the profitability threshold.

The three most common costing mistakes in practice:

1. No unit price system: purchase prices are recorded per pack, but the costing works with portion quantities. Without automatic conversion (price per gram, price per millilitre), systematic errors creep in.
2. Shrinkage is ignored: between purchase and plate there are losses from trimming, peeling, evaporation and spoilage. If you do not factor in these 3–10%, you underestimate your real cost of goods.
3. No VAT differentiation: food is once again subject to the reduced rate of 7%, drinks to 19%. Costing that does not distinguish between net and gross leads to wrong margins.

Calculating cost of goods: the basis of all hospitality costing

Cost of goods (food cost) is the share of material costs in the selling price. It is expressed as a percentage and is the key management metric in hospitality.

The formula:

Cost-of-goods ratio (%) = material costs per portion / net selling price × 100

Industry benchmarks for the cost-of-goods ratio:

These figures are a guide. What matters is not the cost-of-goods ratio alone, but the absolute contribution margin per dish.

Contribution margin in hospitality: why the percentage is not enough

The contribution margin (CM) is the amount left over after deducting the cost of goods from the net selling price. It is available to cover fixed costs and generate profit.

Contribution margin (CM) = net selling price – cost of goods

An example shows why the cost-of-goods percentage can be misleading:

The pasta has the better cost-of-goods percentage. Despite a 40% cost-of-goods ratio, the entrecôte delivers almost twice as much contribution margin in euros. In day-to-day terms: a venue that mainly sells low-margin dishes with a good cost-of-goods percentage can still stay below break-even.

Fixed vs. variable costs: telling them apart properly

For pricing, the distinction between variable and fixed costs is fundamental:

• Variable costs rise in proportion to sales volume: ingredients, packaging, napkins, card fees (pro rata). The more portions you sell, the higher the variable costs.
• Fixed costs are incurred regardless of revenue: rent, lease, salaries (base pay), insurance, depreciation, energy (base load), GEMA music licensing, accountant. These costs keep running even with zero guests.

The selling price is calculated from the cost of goods (variable costs). Fixed costs are covered by the contribution margin. The break-even point marks the sales volume at which the sum of all contribution margins exceeds fixed costs.

The costing model: from purchase price to gross selling price

The costing in the Excel tool we built follows a four-step process:

Step 1 – Determine the unit price

Every ingredient is broken down to its smallest unit price:

Price per unit = purchase price / pack size

Example: 1 litre of olive oil for €7.90 → 7.90 / 1,000 ml = €0.0079/ml

Step 2 – Recipe costing (cost of goods per portion)

For each ingredient, the required quantity is multiplied by the unit price. The total is the cost of goods per portion. A shrinkage surcharge of 3–5% covers trimming, peeling and cooking losses.

Step 3 – Mark-up factor and net selling price

Mark-up factor = 1 / target cost-of-goods ratio (%)

Net selling price = cost of goods × mark-up factor

Example: cost of goods of €3.48 with a target cost-of-goods ratio of 28% → factor 3.57 → net selling price = €12.43

Step 4 – Gross selling price

Gross selling price = net selling price × (1 + VAT rate)

Food: €12.43 × 1.07 = €13.30 → menu price e.g. €13.90

Drinks: €8.50 × 1.19 = €10.12 → menu price e.g. €10.50

The calculated gross selling price is rounded to a marketable menu price. The actual cost-of-goods ratio is then worked out backwards from the menu price you choose.

Worked example: costing a pasta carbonara

The following example shows the Excel recipe costing for a pasta carbonara (1 portion):

Calculating the selling price with a target cost-of-goods ratio of 28%:

• Mark-up factor: 1 / 0.28 = 3.57
• Net selling price: €2.28 × 3.57 = €8.14
• Gross selling price (7% VAT): €8.14 × 1.07 = €8.71
• Menu price: €8.90 or €9.50 (depending on positioning)
• Actual cost-of-goods ratio at €8.90 gross: 2.28 / (8.90 / 1.07) = 27.4%

Typical mistakes in Excel costing – and how to avoid them

1. Hard-coded prices instead of formulas. If you type the purchase price straight into the recipe costing instead of referencing it from a central ingredient list, you have to update dozens of cells by hand every time a price changes. The fix: a separate ingredient list (worksheet 1) from which every recipe costing pulls the current unit price via formula.
2. Mixing up gross and net. Purchase prices often include VAT (19% at drinks wholesalers), and selling prices can be meant net or gross. Consistency is key: the whole costing should run on net figures. VAT is only added in the final step.
3. No versioning. If you overwrite the same file, you lose the history. Recommendation: file names with a date (Kalkulation_2026-02.xlsx) or a cloud system with version history.
4. No protection against division by zero. Empty pack-size cells cause errors. The downloadable Excel template uses IF functions to catch these cases.

The free Excel template: 4 worksheets for complete costing

The downloadable template contains four linked worksheets:

1. Worksheet 1 – Ingredient list: All ingredients with item name, purchase price, pack size and an automatically calculated unit price. Blue cells are input fields, black cells contain formulas.
2. Worksheet 2 – Recipe costing: Ingredients, quantities and costs are recorded per dish. The unit price is pulled from sheet 1 via formula. A shrinkage surcharge (default: 5%) is added automatically. Several dishes can be costed one below the other.
3. Worksheet 3 – Selling price calculation: Enter your target cost-of-goods % → mark-up factor, net and gross selling price are calculated automatically. VAT rates for food (7%) and drinks (19%) are stored as assumptions and can be changed at any time.
4. Worksheet 4 – Contribution margin analysis & break-even: Shows the contribution margin per dish in euros and as a percentage.

You can download the spreadsheet for free here: Excel sheet to download (German)

From costing to ongoing controlling

Costing is only as good as the data it is based on. If you update the purchase prices in the ingredient list once a quarter and run a monthly target-vs-actual comparison between calculated and actual cost of goods, you will spot variances early.

The actual cost of goods comes from the stocktake: opening stock + purchases – closing stock = consumption. This consumption divided by net revenue gives the real cost-of-goods percentage. If it is above the calculated value, there are three possible causes: supplier price increases, excessive shrinkage or uncontrolled portion sizes.

For even more precise and reliable controlling, a static Excel costing is not enough in the long run. What matters is connecting recipe costing, cost-of-goods tracking and stocktake data in one system.

A digital recipe management tool such as the recipe management in BarBrain (available as an add-on for €29 a month) enables exactly this link:

• Central recipe database: every ingredient is stored with its current purchase price and pulled into the costing automatically.
• Automatic update of recipe costs: when supplier prices change, the effect on ingredient costs and the cost-of-goods ratio is immediate.
• Target-vs-actual comparison at product level: with the POS integration, target consumption from sales and recipes is compared with actual consumption from the stocktake.
• Transparent portion control: variances become visible not just monthly, but per item and per recipe.

FAQ – frequently asked questions

What should the cost-of-goods ratio be in hospitality?

For food, 25–32% is considered healthy, for alcoholic drinks 15–22%, for coffee 10–18%. What matters most, though, is the absolute contribution margin per dish, not just the percentage.

What is the difference between mark-up and margin?

Mark-up refers to the purchase price (basis: cost of goods), margin to the selling price (basis: revenue). A mark-up of 300% on €2 gives a selling price of €8. The margin is then 75% (€6 / €8). In hospitality, people usually work with the cost-of-goods percentage, which follows the margin logic.

Do I need special software for costing?

Excel or Google Sheets is enough to get started. The free template from this article covers the ingredient list, recipe costing, selling price calculation and contribution margin analysis. If you cost more than 30 recipes or manage several sites, you will benefit from specialised F&B management tools.

How often should I update my costing?

Check purchase prices at least quarterly, and monthly for volatile ingredients (butter, meat, fish). The cost-of-goods ratio should be checked monthly with a stocktake.

What does break-even mean in hospitality?

The break-even point is the sales volume at which the sum of all contribution margins covers monthly fixed costs. Everything above that is profit. The Excel template calculates this value automatically once you enter fixed costs and estimated sales volumes.

Which VAT rates apply in hospitality?

Since January 2026, food served in hospitality in Germany has permanently been subject to the reduced rate of 7%. Drinks (except tap water) are subject to the standard rate of 19%. The Excel template differentiates between the two rates automatically.

Summary

Pricing in hospitality is not optional busywork – it is what keeps you in business. If you do not know the cost of goods per portion, you cannot set a well-founded selling price – and you lose margin without noticing.

The principle is simple: calculate the unit price → cost the recipe → apply the mark-up factor → add VAT → check the contribution margin. The accompanying Excel template turns this process into four worksheets – free, formula-based and ready to use.