The problem
The dish price was set two years ago with chicken at five euros. Chicken is at six twenty and nobody has recalculated, because recalculating means opening the spreadsheet, finding the latest invoice, copying the price, and doing that for forty dishes. It does not get done. Real food cost only shows up at month end, as a surprise.
Data model
No new tables. Recipes and lines already existed from stock; dated prices already existed from purchasing. Food cost is what you see when you join the two. That is why the four systems were built on the same ingredients: a chicken rise at the supplier reaches the dish without anyone copying anything.
| Table | What it holds |
|---|---|
| demo_ingredients | Shared with stock and purchasing: name, unit, category, shelf life. |
| demo_supplier_prices | Shared with purchasing: every supplier's every price, dated. Never deleted; the new one is added. |
| demo_recipes | The recipe card: portions, sale price, target food cost, demo sales. |
| demo_recipe_lines | Shared with stock: how much of each ingredient goes into one batch. |
Which price is the price
Each line's cost uses the ingredient's reference price: the lowest among all suppliers' current prices. It is what an attentive buyer would pay today. It could be the last price paid, or the usual supplier's: those are business decisions, and the function swaps in one line. What does not change is that the price comes from a dated table, never from a number typed on the recipe.
-- O preço de referência de um ingrediente: o mais baixo entre os
-- preços actuais de cada fornecedor. Vem de uma tabela com data,
-- nunca de um número escrito na ficha.
create or replace function demo_ingredient_price(p_ingredient integer)
returns integer language sql stable as $$
select min(price_cents) from (
select distinct on (supplier_id) price_cents
from demo_supplier_prices
where ingredient_id = p_ingredient
order by supplier_id, since desc, id desc
) latest;
$$;The arithmetic
Batch cost is the sum of the lines, quantity times price. Cost per portion is the batch divided by portions. Food cost is cost per portion over selling price. Gross margin is price minus cost per portion: before labour, rent and VAT. Presenting that as profit would be a lie, and the screen says so next to the number.
-- Uma função custeia todas as fichas ao preço de hoje.
-- Lote = soma das linhas; dose = lote / doses; food cost = dose / preço.
create or replace function demo_recipe_costs()
returns table (recipe_id int, batch_cents int, portion_cents int,
food_cost_pct numeric, margin_cents int)
language sql stable as $$
with batch as (
select r.id, r.portions, r.price_cents,
sum(round(l.qty * demo_ingredient_price(l.ingredient_id)))::int as batch_cents
from demo_recipes r join demo_recipe_lines l on l.recipe_id = r.id
group by r.id
)
select id, batch_cents,
round(batch_cents / portions)::int,
round(100.0 * (batch_cents / portions) / price_cents, 1),
price_cents - round(batch_cents / portions)::int
from batch;
$$;The scenario runs in the browser
Moving the chicken price and watching the dish change is not a record: it is a question. So it runs in the browser, over the same numbers, without writing anything. The suggested selling price to keep the margin is arithmetic, cost divided by one minus the target food cost, and the screen calls it a calculation, not a decision. Deciding the price is the owner's job.
// O cenário corre no browser: os mesmos números, nada gravado.
const unit = override[line.ingredient_id] ?? referencePrice(line.ingredient_id);
const lineCents = Math.round(line.qty * unit);
const portion = Math.round(batch / recipe.portions);
const foodCost = (portion / recipe.price_cents) * 100;
// Preço para manter o food cost alvo: uma conta, não uma decisão.
const suggested = portion / (1 - recipe.target_pct / 100);What I would do differently for a real client
Preparation loss per ingredient, because a kilo of onion is not a thousand grams of chopped onion. Recipe versions, to know what changed and when. Sub-recipes, the sauce that goes into five dishes. Real sales coming from the till instead of demo numbers. And an alert when a dish crosses its target food cost, instead of waiting for someone to open the dashboard.