Two worksheets for two questions
A recipe food cost spreadsheet estimates the ingredients needed to serve a dish. A monthly inventory worksheet measures the value of ingredients consumed during a period. Yield, waste, actual portions and changing prices can make those figures differ.
Square's cost-of-goods explanation connects opening inventory, purchases and closing inventory. This worksheet keeps its scope to food ingredients. Wages and rent remain separate rather than being mixed into a food-only percentage.
Worksheet 1: account for recipe yield
Copy these columns into a spreadsheet. The following fictional example uses kilograms throughout and Canadian dollars.
| Cell | Input | Value or formula |
|---|---|---|
| B2 | Price per purchased kilogram | 8 |
| C2 | Usable yield | 80% |
| D2 | Usable kilograms per portion | 0.15 |
| E2 | This ingredient's cost per portion | =B2/C2*D2 |
The result is 8 ÷ 0.80 × 0.15 = $1.50 per portion. You need 0.1875 purchased kilograms to serve 0.15 usable kilograms. Multiplying only 8 × 0.15 would give $1.20 and omit the yield loss.
| Fictional example | Calculation | Cost per portion |
|---|---|---|
| Without yield | 8 × 0.15 | $1.20 |
| With an 80% yield | 8 ÷ 0.80 × 0.15 | $1.50 |
Add one row per ingredient, total column E, then divide the portion cost by its selling price. Enter yield as 80% or 0.80, not the number 80. Resolve a zero yield rather than forcing a division. The restaurant food cost formula guide explains the underlying portion calculation.
Worksheet 2: measure the month's consumption
| Cell | Input | Fictional value |
|---|---|---|
| B2 | Opening food inventory | $2,000 |
| B3 | Food purchases in the period | $7,000 |
| B4 | Closing food inventory | $2,500 |
| B5 | Food sales in the same period | $20,000 |
| B6 | Ingredient value consumed | =B2+B3-B4 |
| B7 | Food cost, formatted as a percentage | =B6/B5 |
Consumption is $6,500 and food cost is 32.5%. Purchases divided by sales would instead show 35%. When sales are zero, label the rate unavailable rather than hiding a division error.
Opening inventory
$2,000
Purchases in the period
+ $7,000
Closing inventory
− $2,500
Ingredient value consumed
$6,500
Food cost
$6,500 ÷ $20,000 of sales = 32.5%
Check a variance before changing prices
Count inventory at the same point in the operating cycle and use consistent units. Separate food and beverages if their sales are tracked separately. Record transfers, staff meals and waste: stock consumption does not always represent a sold dish.
For a theoretical comparison, multiply each current recipe cost by the quantity sold and sum the results. Compare that with actual consumption for the same scope and dates. Investigate data quality before attributing a variance to staff behaviour.
Review the prime cost guide when you are ready to include labour. These worksheets provide original worked examples, not a universal target, a tax calculation or a claim that recorded expenses alone form a complete inventory system.
