Manage F&B quantity and cost of goods sold using Excel – know profit and loss per dish without needing expensive software.

7 min read

A pho restaurant in Ho Chi Minh City sells 300 bowls per day, generating an average daily revenue of 30 million VND – a figure that would satisfy any restaurant owner. However, by the end of the month, after deducting ingredient costs, labor, rent, and utilities, only about 3 million VND remains. The owner is puzzled – revenue has increased by 15% from the previous year, but profit has decreased. The issue is not about selling less or more; it’s about never having accurately calculated the cost of goods for each bowl of pho, from the amount of noodles and meat to spices and daily waste.

This is a common scenario in the Vietnamese F&B industry. Most restaurant owners price their menus intuitively – taking the main ingredient cost, doubling or tripling it, and rounding. This approach neglects dozens of small but cumulative components: cooking oil, spices, packaging, and secondary ingredients. The result is that the theoretical profit differs significantly from the actual profit.

Food cost ratio – the most important figure that most F&B establishments overlook

In the F&B industry, the standard food cost ratio ranges from 28-35% depending on the segment. Casual restaurants often accept 30-35% due to lower selling prices, while mid-to-high-end restaurants aim for 25-30% as higher selling prices allow for lower ingredient ratios while maintaining quality. When the food cost ratio exceeds 40%, the restaurant is experiencing hidden losses – revenue may increase, but actual profit decreases over time.

The problem is that most restaurant owners in Vietnam do not calculate the food cost ratio for each dish. They calculate the total – how much ingredients are purchased, how much is sold, and subtract to find the profit. This “total-to-total” approach hides an important fact: 60% of the menu might have a food cost below 30% (very good), but the remaining 40% exceeds 45% and is dragging down the overall profit. Without calculating the food cost ratio for each dish, you won’t know which dishes are “subsidizing” others.

Three mistakes that lead to incorrect cost of goods from the start

The first and most common mistake: overlooking secondary ingredients and seasonings. A bowl of pho is not just noodles, meat, and broth. It also includes fried shallots, chili, lime, herbs, sauce, pickles, paper towels, take-out boxes, and plastic bags. Each item is small – 200-500 VND per serving – but multiplied by 300 servings per day becomes 60,000-150,000 VND per day, or 1.8-4.5 million VND per month. Many restaurant owners neglect this because it seems insignificant, but it accounts for 3-5% of the total food cost.

The second mistake: not accounting for waste. Buying 10kg of vegetables but only using 7-8kg after removing damaged leaves and trimming. Purchasing 5kg of beef but having 15-20% waste (tendons, fat, bone fragments). Expired ingredients must be discarded – especially for perishable items like seafood, dairy, and ice cream. The average waste in the Vietnamese F&B industry is around 8-15% of the input value, but few places accurately record this in their cost of goods.

The third mistake: not updating the cost of goods when input prices change. Pork prices may increase by 10-15% in two months, while vegetable prices fluctuate seasonally. Many restaurants still use cost of goods calculated six months prior to set menu prices – creating an increasingly large discrepancy between actual and theoretical cost of goods. A good cost of goods system must allow for continuous updates of input prices and automatic recalculation of the food cost ratio for each dish.

Not every popular dish is worth keeping

Stars are high-profit, high-sales dishes – these should be heavily marketed, prominently placed on the menu, and protected at all costs. Puzzles have high profit but low sales – they need increased visibility: renaming, repositioning on the menu, or bundling with promotions. Workhorses have high sales but low profit – this group generates revenue but not proportional profit; consider slight price increases or reducing ingredient costs. Dogs have low sales and low profit – prime candidates for removal from the menu.

A restaurant with 50 menu items typically finds that only 10-15 dishes generate 70-80% of the total profit. The rest are either Workhorses (high sales but low profit) or Dogs (should be removed to reduce operational complexity and inventory). This classification can only be done with accurate food cost data for each dish – which is why calculating the food cost ratio is the first step.

Inventory reports may be sufficient, but actual inventory is lacking – the problem of inventory variance

Inventory variance refers to the difference between the system-calculated inventory (based on inputs and outputs) and the actual physical inventory. In the F&B industry, a variance of 2-5% is considered normal. Exceeding 5% indicates a problem: inaccurate portioning, unrecorded waste, unit measurement errors, or theft.

A good inventory control system requires three elements: recording each purchase (Purchase log), automatically calculating the quantity of ingredients used based on the number of dishes sold (linking Recipes to Sales), and scheduling periodic inventory checks to reconcile with system data. When variance is measured weekly instead of monthly, restaurants can detect issues early before they accumulate into significant losses.

From ingredients to P&L – a 15-sheet linked system

The entire process – from calculating the cost of goods, setting portion sizes, recording sales, managing inventory, to generating profit and loss reports – can be operated on a single Excel workbook with 15 linked sheets. The main flow: Ingredients (price list) links to Recipes (portion sizes for each dish, automatically calculating food cost). Recipes link to Sales (each sale automatically calculates the cost of goods). Sales and Purchase are consolidated into the P&L Statement. The Dashboard displays overall KPIs: revenue, cost of goods, gross profit, and average food cost ratio.

The key difference from using multiple standalone Excel files or manual records is that any change in one sheet automatically updates the entire system. If meat prices increase by 10%, updating the Ingredients sheet will automatically recalculate the food cost of all dishes containing meat. Selling 50 bowls of pho today? Entering the data into Sales will automatically decrease the inventory of Ingredients and update the P&L statement. No need to re-enter data or copy-paste between files.

Frequently asked questions

What is food cost ratio and how is it calculated?

Food cost ratio is the percentage of the cost of ingredients for a dish compared to its selling price. Formula: (total ingredient cost / selling price) x 100%. Example: a bowl of pho with an ingredient cost of 25,000 VND and a selling price of 50,000 VND has a food cost ratio of 50%. The standard for the F&B industry is 28-35%. Above 40% requires re-examining the quantity or price.

Does a small restaurant with under 10 employees need a food cost management system?

Yes. The smaller the restaurant, the thinner the profit margin – a 5% error in food cost can be the difference between profit and loss. The system doesn't need to be complex: start by calculating the food cost ratio for the top 10 best-selling dishes, then expand gradually. A well-structured spreadsheet is sufficient to manage a restaurant with under 30 employees.

What is Menu Engineering and how is it applied?

Menu Engineering is a method of categorizing dishes based on two axes: gross profit and sales volume. The result divides the menu into four groups: Stars (high profit, high sales – promote), Puzzles (high profit, low sales – increase visibility), Workhorses (low profit, high sales – increase price or reduce cost), and Dogs (low profit, low sales – eliminate). Accurate food cost data for each dish is required for categorization.

How does this Excel system differ from POS software?

POS (Point of Sale) systems focus on recording sales transactions and payments. This system focuses on controlling food costs, calculating the cost of ingredients for each dish, analyzing profit margins, and managing inventory and variance. The two systems complement each other: POS records "how many sales," while this system answers "how much profit and why."

Can it be used offline?

Completely offline. The system runs on Excel Desktop (2016 or later, or Microsoft 365), no internet required. Data is stored on your computer, not uploaded to the cloud. Suitable for restaurants, kitchens, and warehouses with unstable internet connections.

Reference: Michael Kasavana & Donald Smith – Menu Engineering: A Practical Guide (1982) · National Restaurant Association – Restaurant Industry Operations Report (2024)