Hospitality Pricing Calculator: Free Excel Template for Cost of Goods, Selling Price & Contribution Margin

Around 60 % of all hospitality businesses in Germany fail within the first five years. One of the most common reasons: selling prices do not cover actual costs. Anyone who operates without sound pricing gives away margin – or worse: makes a loss on every dish sold without even realising it.

This article explains the business logic behind pricing in hospitality, highlights typical mistakes and provides a concrete calculation model that can be implemented directly in Excel. At the end you will find a free Excel template with four worksheets – from the raw-materials list to the contribution-margin analysis.

Why Incorrect Pricing Is the Main Cause of Margin Problems

Many operators set prices by feel or follow the competition. The problem: their own cost structure remains invisible. A pasta dish listed at €14.90 can be profitable at a cost of goods of €5.20 – but when supplier price increases push cost of goods to €6.80, it can suddenly fall below the profitability threshold.

The three most common pricing mistakes in practice:

No unit-price system: Purchase prices are captured per pack, but the costing works with portion quantities. Without automatic conversion (price per gram, price per millilitre), systematic errors creep in.

Shrinkage is ignored: Between purchase and plate there are losses from trimming, peeling, evaporation and spoilage. Anyone who ignores these 3–8 % losses calculates too cheaply – and only discovers it when the inventory no longer adds up.

No contribution-margin analysis: A dish with 28 % cost of goods looks healthy on paper. But if the absolute contribution margin is only €3.50 and the fixed costs per guest are €8, the business is losing money on every sale.

Calculating Cost of Goods: The Foundation of Every Hospitality Costing

Cost of goods (CoG) is the sum of all raw-material costs for a single portion. It is the starting variable for the entire pricing chain.

Cost of goods per portion = Σ (quantity per ingredient × unit price)

Practical example: Pasta Carbonara

Industry benchmarks for cost of goods:

These values serve as a guide. What matters is not cost of goods 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 remaining after deducting 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 illustrates why the cost-of-goods percentage can be misleading:

The pasta has a better cost-of-goods percentage. The entrecôte, despite 40 % cost of goods, delivers almost twice the contribution margin in euros. In practice this means: a business that mainly sells low-margin dishes with a good CoG percentage can still fall below the break-even point.

Fixed vs. Variable Costs: Differentiating Correctly

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

‍Variable costs rise proportionally with sales volume: raw materials, packaging, napkins, card-payment fees (proportional share). The more portions sold, the higher the variable costs.

‍Fixed costs are incurred regardless of revenue: rent, lease, salaries (base pay), insurance, depreciation, energy (base load), GEMA, accountant. These costs continue to run even with zero guests.

The selling-price calculation is based on 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 Pricing Model: From Purchase Price to Gross Selling Price

The calculation in our Excel tool follows a four-step process:

Step 1 – Determine the Unit Price

Every raw material is broken down to its smallest unit price:

Price per unit = Purchase price / Pack size

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

Step 2 – Calculate Recipe Cost of Goods

Every ingredient in a recipe is multiplied by its unit price. The sum gives the raw cost of goods. A shrinkage surcharge (standard: 5 %) accounts for losses during preparation.

CoG incl. shrinkage = Raw CoG × 1.05

Step 3 – Determine the Net Selling Price

The net selling price is calculated from the cost of goods and the target cost-of-goods ratio:

Mark-up factor = 1 / Target CoG %

Net selling price = CoG × Mark-up factor

Example: CoG €3.48, target 28 % → factor 3.57 → net SP €12.43

Step 4 – Add VAT

For food in German hospitality: 7 % (reduced rate). For drinks: 19 % (standard rate).

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

Example: €12.43 × 1.07 = €13.30 → menu price €13.50 or €13.90

Practical Example: Costing a Pasta Carbonara

The following example shows the Excel recipe costing for a Pasta Carbonara (1 portion):

Calculating the selling price at a target CoG of 28 %:

Mark-up factor: 1 / 0.28 = 3.57

Net SP: €2.28 × 3.57 = €8.14

Gross SP (7 % VAT): €8.14 × 1.07 = €8.71

Menu price: €8.90 or €9.50 (depending on positioning)

Actual CoG at €8.90 gross: 2.28 / (8.90 / 1.07) = 27.4 %

Typical Mistakes in Excel Costings – and How to Avoid Them

‍Hard-coded prices instead of formulas. Anyone who types the purchase price directly into the recipe costing instead of referencing it from a central raw-materials list must manually update dozens of cells with every price change. The solution: a separate raw-materials list (worksheet 1) from which all recipe costings pull the current unit price via formulas.

‍

Mixing up gross and net. Purchase prices often include VAT (19 % for beverage wholesalers); selling prices may be stated net or gross. Consistency is crucial: the entire costing should run on net figures. VAT is added only in the final step.

‍

No versioning. Overwriting the same file destroys the historical record. Recommendation: filenames with dates (Costing_2026-02.xlsx) or a cloud system with version history.

‍

No protection against division by zero. Empty cells in the pack-size column cause errors. The downloadable Excel template uses IF functions to catch such cases.

The Free Excel Template: 4 Worksheets for Complete Pricing

The downloadable template contains four interlinked worksheets:

Worksheet 1 – Raw-Materials List: All ingredients with item name, purchase price, pack size and automatically calculated unit price. Blue cells are input fields, black cells contain formulas.

Worksheet 2 – Recipe Costing: For each dish, ingredients, quantities and costs are recorded. The unit price is pulled from worksheet 1 via a formula. A shrinkage surcharge (default: 5 %) is added automatically. Multiple dishes can be costed one below the other.

Worksheet 3 – Selling-Price Calculation: Enter the target CoG % → mark-up factor, net and gross selling prices are calculated automatically. VAT rates for food (7 %) and drinks (19 %) are stored as assumptions and can be changed at any time.

Worksheet 4 – Contribution-Margin Analysis & Break-Even: Shows the contribution margin per dish in euros and per cent.

Download the template for free here: Excel template download

‍

From Pricing to Ongoing Controlling

A costing is only as good as the data it is built on. Anyone who updates purchase prices in the raw-materials list once per quarter and runs the target-vs-actual comparison between calculated and real cost of goods monthly will spot variances early.

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

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

A digital recipe management tool like BarBrain’s recipe management enables exactly this integration:

Central recipe database: Every ingredient is stored with its current purchase price and automatically fed into the costing.

Automatic recipe-cost updates: When supplier prices change, the effect on contribution margin and cost-of-goods ratio is immediate.

Target-vs-actual comparison at product level: The calculated consumption per recipe is matched against actual stock consumption from the inventory.

Transparent portion control: Variances become visible not just on a monthly basis but at the level of individual items and recipes.

FAQ – Frequently Asked Questions

What should the cost of goods be in hospitality?

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

What is the difference between mark-up and margin?

The mark-up is based on the purchase price (base: cost of goods); the margin is based on the selling price (base: revenue). A mark-up of 300 % on €2 gives a selling price of €8. The margin is then 75 % (€6 / €8). In hospitality, the cost-of-goods percentage is commonly used, which follows the margin logic.

Do I need special software for pricing?

For getting started, Excel or Google Sheets is sufficient. The free template from this article covers raw-materials lists, recipe costing, selling-price calculation and contribution-margin analysis. Anyone costing more than 30 recipes or managing multiple locations benefits from specialised F&B management tools.

How often should I update my costing?

Check purchase prices at least quarterly; for volatile commodities (butter, meat, fish) monthly. The cost-of-goods ratio should be monitored monthly via inventory.

What does break-even mean in hospitality?

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

Which VAT rates apply in German hospitality?

Since January 2024, the reduced rate of 7 % applies again to food served in hospitality. Drinks (except tap water) are subject to the standard rate of 19 %. The Excel template differentiates both rates automatically.

Summary

Pricing in hospitality is not optional extra work – it is survival insurance. Anyone who does not know the cost of goods per portion cannot set a sound selling price – and loses 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 implements this process across four worksheets – free, formula-based, ready to use immediately.