Recipe Costing Spreadsheet for Restaurants: Build It With ChatGPT

Build five tabs (settings, ingredient prices, recipes, a dish summary and a price log), ask ChatGPT to write the layout and formulas, then paste in your supplier invoices to fill it. To keep prices current, repeat that paste after each delivery. The spreadsheet does the arithmetic, so every plate cost recalculates when one ingredient price changes.

Most home-made recipe costing spreadsheets fail on yield, not on arithmetic. A kilo of broccolini at $9.00 does not put a kilo on the plate, and a sheet that prices the whole kilo understates every dish that uses it. The worked dish below is a pan-roast salmon with a $5.99 plate cost; a 20% rise in the salmon price lifts its food cost from 27.2% to 30.9%, and the sheet shows it the week the invoice arrives.

Follow me on Instagram@sagnikteaches

Five tabs, one job each: settle the layout before you prompt

A vague request gets a vague sheet, so decide the layout first. This one suits a restaurant with up to a few hundred ingredient lines and a few dozen dishes, and it behaves the same in Excel or Google Sheets.

Connect on LinkedInSagnik Bhattacharya

Base units matter more than anything else here. Give every ingredient exactly one: grams for anything weighed, millilitres for anything poured, "each" for anything counted. Convert kilos, litres and cases once, in the Ingredients tab, and never again inside a recipe line. If your suppliers invoice in pounds and ounces you can use those instead, provided the whole workbook does.

Subscribe on YouTube@sagnikteaches

Two small decisions save trouble later. The first is lookups: XLOOKUP works in Google Sheets and in Microsoft 365, Excel 2021 and Excel 2024, but Microsoft's own page says it isn't in Excel 2016 or 2019, so tell ChatGPT to use INDEX and MATCH if that's your version. The second is shape: one record per row, a clear header in row 1, and no blank rows or columns. OpenAI's data-analysis help asks for exactly that, and it keeps the workbook readable if you upload it to ChatGPT later.

Prompt one: the skeleton and the formulas, written for your app

Open a new ChatGPT chat, ideally inside a Project so the layout and your later instructions stay together, and give it the layout above in plain words. Say which spreadsheet app you use and ask for formulas you can paste, not an explanation of how formulas work.

I run a restaurant and need a recipe-costing workbook for [Google Sheets / Excel 365]. Build it as five tabs.

Settings: target food cost % (30%), sundries allowance % (3%), price-change alert % (5%).
Ingredients: Ingredient, Supplier, Pack description, Pack price, Pack size, Base unit (g, ml or each), Price per base unit, Yield %, Usable cost per base unit, Last checked, Note.
Recipes: Dish, Ingredient, Quantity (in that ingredient's base unit), Line cost.
Dishes: Dish, Menu price (net of sales tax), Ingredient cost, Sundries, Plate cost, Food cost %, Gross profit per plate, Price at target, Unmatched lines, Status.
PriceLog: Date, Ingredient, Old pack price, New pack price, % change, Source.

Rules:
- Give me the row-2 formula for every calculated column in A1 notation, ready to paste, and tell me which cells are inputs.
- Use XLOOKUP for lookups [or INDEX/MATCH if my Excel is 2019 or older]. Anchor every range with dollar signs.
- Line cost = quantity x usable cost per base unit of that ingredient. If the ingredient name is not found, the line must show CHECK NAME, not 0.
- Unmatched lines = the number of CHECK NAME lines for that dish. Status = CHECK RECIPE if that is above 0, otherwise Over target if food cost % is above the target, otherwise OK.
- Ingredient cost = sum of the dish's line costs. Plate cost = ingredient cost x (1 + sundries %).
- Food cost % = plate cost / menu price. Price at target = plate cost / target food cost %.
- Add data validation so Base unit only accepts g, ml or each, and Yield % only accepts 1% to 100%.
- Add conditional formatting: Status turns red for Over target and CHECK RECIPE.
- Do not invent any prices, yields or quantities. Leave every input cell blank except the three settings.

Return one tab at a time as tab-separated text I can paste, headers first. Ask me one question if anything is ambiguous.

What comes back is text, not a finished file, and that is fine. Depending on your plan and settings ChatGPT may offer a downloadable workbook, but OpenAI's own help for that feature tells you to check the file's formulas before relying on it, and pasting tab-separated text into a blank sheet gets you the same result on any plan. Paste the headers, then the row-2 formulas, then fill them down. If you want more help with the formula side, the ChatGPT and Excel guide covers it in depth.

Read the formulas before trusting them. Four faults are the ones that go wrong quietly:

• A missing ingredient that returns 0. The line silently costs nothing and the dish looks cheaper than it is. That is why the prompt asks for CHECK NAME and an unmatched-lines counter. A text result is ignored by a sum, so without the counter the dish still looks fine.
• Lookup ranges without anchors. A range such as A2:A200 slides to A3:A201 on the next row when you fill down, and lookups near the bottom start missing ingredients that are plainly there.
• Percentages stored as whole numbers. A yield typed as 85 where the formula expects 0.85 makes the usable cost 85 times too high. Format the column as a percentage and type 85%.
• Sundries applied twice. Once in the ingredient cost and again in the plate cost is an easy slip when ChatGPT tidies the formulas. Sundries belong in exactly one place.

Then run a dummy test before any real data goes in. Add an ingredient called Test flour at $10.00 for a 1,000 g pack with 100% yield, and a dish that uses 250 g of it. The line cost should be $2.50. Change the yield to 50% and it should become $5.00. Mistype the ingredient name in the recipe line: the line should say CHECK NAME and the dish status should say CHECK RECIPE, not show a cheaper dish. Delete the dummy rows when the three results are right.

Fill the price table from invoice lines, not from memory

Prices typed from memory make the sheet wrong on day one. Use what the supplier actually billed. Lines pasted from an emailed invoice or a portal export work best, a CSV or spreadsheet price list comes next, and a photo of a paper invoice is the last resort: OpenAI's data-analysis help page warns that ChatGPT may not reliably extract exact values from scanned files or image-based tables, so check every figure from a photo against the paper. The tutorial on what ChatGPT can and can't read in PDFs, spreadsheets and photos covers where that breaks.

Here are the lines from today's invoice, and the invoice total. For each line return one row in my Ingredients column order: Ingredient, Supplier, Pack description, Pack price, Pack size (in base units), Base unit, Last checked. Work out the pack size in grams, millilitres or each yourself and show the working in a final column called Working. If the pack or case size is unclear, write ASK in Pack size instead of guessing. At the end, total quantity x price for all lines and compare it with the invoice total.

Five illustrative lines from one delivery show how it works, pasted exactly as the invoice prints them:

SALMON SIDE SKIN-ON 2-2.5KG     6.40 kg  @ 19.50   124.80
POTATO WAXY 10KG SACK           2        @ 12.00    24.00
BROCCOLINI 1KG BOX              3        @  9.00    27.00
LEMON 4KG BOX (APPROX 36)       1        @ 10.80    10.80
BUTTER UNSALTED 250G X 20       1        @ 46.00    46.00
INVOICE TOTAL                                      232.60

A first answer from ChatGPT (illustrative) comes back as a tidy table, and one row in it is wrong:

Ingredient            | Pack price | Pack size | Base unit | Working
Salmon side, skin-on  | 19.50      | 1000      | g         | priced per kg
Potatoes, waxy        | 12.00      | 10000     | g         | 10 kg sack
Broccolini            | 9.00       | 1000      | g         | 1 kg box
Lemon                 | 10.80      | 36        | each      | about 36 per box
Butter, unsalted      | 46.00      | 250       | g         | 250 g block

The butter row is the trap. The invoice says 250G X 20, a case of twenty 250 g blocks, and the draft read it as a single 250 g block at $46.00. That puts butter at $0.184 a gram, which is $184 a kilo and twenty times too high. The right pack size is 5,000 g, which gives $0.0092 a gram. Nothing in the table looks odd until you look at the price per kilo.

Three quick checks catch nearly every error of this kind, and together they take under two minutes:

1. Look at the price per kilo or litre, not the pack price. Butter at $184 a kilo fails a glance test. Keep an "expected range" column for your twenty biggest lines and let a conditional format turn the cell orange when the price per kilo falls outside it.
2. Multiply it back. Ask for quantity times price across all lines and compare it with the invoice total, $232.60 here. A mismatch means a line was dropped or misread.
3. Compare with last time. The PriceLog shows a jump straight away, and the weekly step below turns that into a list for you.

Yield and waste: turning the invoice price into the plated price

Yield is the share of what you buy that ends up as usable product. Salmon sides lose skin, belly and pin bones, broccolini loses its woody ends, and parsley is mostly stalk. The usable cost of a gram is the price per gram divided by the yield, so a 55% yield makes parsley cost about 1.8 times its invoice price.

Test it rather than look it up. Weigh the product as it arrives, trim it the way your kitchen trims it, weigh what's usable, and divide usable weight by as-purchased weight. A scale and ten minutes per ingredient is the whole method. These results are illustrative, from one prep session:

There are two refinements. If the kitchen portions the cooked weight (a roast joint, braised short rib), test the cooked yield and cost the cooked weight, so the test happens at the point where the portion is weighed. And once a yield is in the sheet, keep the Recipes tab strictly to the usable quantity that reaches the plate, because the Ingredients tab has already paid for the trim.

ChatGPT is useful for the arithmetic of the test: paste the weights, get the yields back. It is not a source of yields. If you ask it for typical figures to get started, write "guess" in the Note column of every row it fills and replace each one within a fortnight. A guessed 90% on something your cooks really get 78% from makes the dish look cheaper than it is, and the sheet will never tell you.

One dish from invoice to plate cost: pan-roast salmon

Here is the full costing for one dish, built from the invoice lines and yield tests above. Everything is illustrative: pan-roast salmon with lemon-butter potatoes and greens, plated for one, with line costs shown to three decimals so they add up.

Follow the first row to see the mechanism. Salmon is $19.50 a kilo, so $0.0195 a gram. At an 85% yield each usable gram costs $0.0195 divided by 0.85, which is $0.02294. The plate carries 170 g, so the line costs $3.90. Every other row repeats that pattern, and the sheet does it for all of them with one formula that you never retype.

The lines total $5.819. A 3% sundries allowance covers salt, pepper, cooking spray and the other things nobody weighs; it adds $0.175 and makes the plate cost $5.99. Priced at $22.00 net of sales tax, the food cost is 27.2% and the gross profit is $16.01 a plate. At a 30% target the sheet's price at target is $19.98, so the dish clears the target with room to spare.

Sauces, stocks and batch recipes need their own cost per ml

Anything the kitchen makes in a batch and uses across several dishes gets costed as a batch first. Take a house lemon vinaigrette (illustrative figures). The 600 ml of olive oil at $0.0076 a ml is $4.56. The juice of five lemons at $0.30 each is $1.50 and measured 210 ml. Forty grams of mustard at $0.012 a gram is $0.48, and 40 g of honey at $0.009 a gram is $0.36. The batch costs $6.90, and after mixing it measured 850 ml, so it costs $0.00812 a ml. The 30 ml that goes on a side salad costs $0.24.

In the workbook, the batch is a "dish" on the Recipes tab whose lines are those four ingredients. The Ingredients tab gets one row called Lemon vinaigrette whose pack price points at the batch cost and whose pack size is the measured 850 ml. A side salad then uses it like any bought ingredient, and when the olive oil price changes, the vinaigrette and every dish that uses it follow without anyone touching them.

Measure the real yield of every batch at least once. A reduction, a stock or a dough rarely gives what the recipe card says, and the card is the number most likely to be out of date. The same approach covers stocks, pickles, marinades and doughs. Decide once how to treat by-products such as bones for stock (free, or a notional price) and write the rule in the Note column so the next person doesn't have to guess.

Set the target, then read the dish list for decisions

The target food cost percentage is your rule, and ChatGPT cannot know it. A sensible starting point is your actual figure from last quarter's books or POS reports, then nudged by what you want each dish to do. Rent, labour and the mix of dishes all feed into the right number, so a target copied from another restaurant means little. This sheet uses 30% purely as an example.

With four dishes costed, the Dishes tab reads like this (illustrative figures, prices net of sales tax):

The sirloin is the dish that needs a decision. At $28.00 it runs at 34.3% against the 30% target, and the sheet says the price at target would be $32.00. The options are the usual four: raise the price, trim the portion, change what comes with it, or keep it as the dish that sells the rest of the menu. Look at gross profit as well as the percentage. The sirloin earns $18.40 a plate, more than any other dish here, while the tagliatelle's 21.8% earns $10.95. The percentage tells you how exposed a dish is to price moves, and the dollars tell you what is left to pay for everything else.

The tutorials on menu engineering with AI and on testing a price rise dish by dish take the decision from here. You can also ask ChatGPT to read the Dishes tab for you:

Here is my Dishes tab. Which three dishes have the thinnest gross profit per plate, and what would a $0.50 price rise do to the food cost % of each? Show the arithmetic for every figure.

Spot-check one of its answers against the sheet before you act on any of them. It is a reading aid, not a replacement for the formulas.

The weekly price-refresh step that keeps every plate cost current

This is the part that keeps the sheet alive. A costing sheet that is right in October is wrong by December if nobody refreshes it, and that is the usual fate of a downloaded template. Make the refresh a ten-minute job on a fixed day, the morning after the main delivery:

1. Collect the invoices since last time, as pasted lines or the supplier's price file (2 minutes).
2. Paste them into the Project chat, where the standing prompt below is saved as the Project's instructions, along with the current Ingredients tab as pasted text or an attached file (3 minutes). A Project holds 5 files on a Free account, 25 on Go or Plus and 40 on higher plans, according to OpenAI's file uploads FAQ.
3. Read the three lists it returns: changes over your alert threshold, lines it couldn't match, and the reconciliation (2 minutes).
4. Paste the updated rows into the input columns of the Ingredients tab, copy the changes into the PriceLog, and update Last checked (2 minutes).
5. Open the Dishes tab and look for red (1 minute).

You maintain my restaurant's ingredient price table. I will paste supplier invoice lines, with the invoice total, and attach or paste the current Ingredients tab.

For every invoice line:
1. Match it to an ingredient name already in my table. If nothing matches, write NEW in the Ingredient cell and do not invent a name.
2. Work out the pack price and the pack size in base units (g, ml or each), and show the working in a column called Working. If the case size is unclear, write ASK in Pack size.
3. Return the matched rows in my Ingredients column order as tab-separated text.

Then give me three lists:
A. Changes: every ingredient whose price per base unit moved by more than 5% against my table, with old price, new price and the % change.
B. Unmatched or unclear lines (NEW or ASK).
C. Reconciliation: your total of quantity x price across all lines, next to the invoice total I gave you.

Never calculate plate costs or food cost percentages: my spreadsheet does that. Never change a yield. If I give you no invoice total, say so.

A later delivery might produce this (illustrative):

A. CHANGES OVER 5%
Salmon side, skin-on: 19.50 to 23.40 per kg (+20.0%)
Mushrooms, chestnut: 5.50 to 6.05 per kg (+10.0%)

B. UNMATCHED OR UNCLEAR
NEW: CELERIAC 5KG BOX at 14.00 (no match in your table; pack size 5,000 g)

C. RECONCILIATION
Lines total 165.20; invoice total 165.20. Match.

Then the spreadsheet does what ChatGPT shouldn't: it recalculates. At $23.40 a kilo the salmon portion costs $4.68 instead of $3.90, so the plate cost moves from $5.99 to $6.80 and the food cost from 27.2% to 30.9%. Status turns red, and the price at target reads $22.66. There are two ways to respond, and both take a minute to test in the sheet. Cutting the portion to 160 g brings the plate cost to $6.51 and the food cost to 29.6%. Moving the menu price to $23.00 also gives 29.6%. Or you accept a month at 30.9% because the dish is bringing people in. What matters is that you decide this week, not when the stocktake lands. For a wider view of supplier creep across every invoice, see the tutorial on catching price creep on supplier invoices.

Two housekeeping rules keep the refresh honest. Add a conditional format that turns Last checked amber when it is older than five weeks, so a forgotten line shows itself. And check the lines that carry the most spend every week and the rest monthly; your PriceLog will show which lines actually move.

Where ChatGPT gets costing wrong, and how each error shows itself

The butter case and the silent zero are two of the failures. These are the others worth a check, with the one-minute test for each.

• Cups and spoons. A recipe card says two cups of rice, and ChatGPT converts to an average gram weight that may not match your rice, your scoop or the way your cooks fill it. Weigh one cup of your own product, write the weight in the recipe, and stop converting after that.
• Weight against volume. Forty grams of honey and 40 ml of honey are different quantities, because honey is denser than water. If a base unit is g, the recipe line is in g. The test is to look at any ingredient measured in ml that your suppliers sell by weight, and fix the base unit once.
• Regenerated tabs with changed formulas. Ask for "the Dishes tab again" and the formulas may come back written differently. Paste ChatGPT's output into input columns only, protect the calculated columns so a paste can't overwrite them, and save a dated copy of the workbook once a month so you can compare.
• Duplicate ingredients. When it can't match a name, a model may quietly create a new one, so you end up with "Lemon" and "Lemons, loose" and only one gets updated. The prompt above tells it to write NEW instead of inventing a name; scan the NEW list every week and merge duplicates by hand.
• Dropped lines. A long invoice can lose a line, and the dish that uses it keeps its old price. The reconciliation total catches it, which is why that list exists.

Prove the sheet against a week of real trading

The costing sheet gives you the theoretical food cost: what the food should have cost, given what you sold. A stocktake gives you the actual. The gap between them is where waste, over-portioning, mistakes and unrecorded giveaways sit, and it is the best test of whether the sheet is right. One illustrative week shows the sum, with the four dishes standing in for a whole menu (a real menu has more rows, and the sheet does the same sum):

The theoretical food cost is $2,796.30 over $10,515.00 of sales, which is 26.6%. The stocktake says opening stock $4,200, plus purchases $3,350, minus closing stock $4,380, so $3,170 of food was actually used. That is 30.1% of sales. The gap is $373.70, or about 3.5 points, of food that left the building without being sold or that cost more than the recipes say.

The sheet won't say where it went, but it gives you a short list of suspects. Weigh ten portions of the biggest-ticket dish. If the salmon averages 185 g instead of 170 g, the extra 15 g costs about $0.34 a plate, which is roughly $41 across 120 portions. Then look at unrecorded staff meals and comps, spoilage, specials that aren't in the sheet, and any yield your cooks beat in the test but miss on a busy night. Repeat the comparison every month. The trend in the gap tells you more than any single week, and a gap that shrinks after you act on one suspect is proof that the sheet is doing its job.

Questions that come up when you cost recipes with ChatGPT

Can ChatGPT look up today's wholesale prices for me?

Not usefully. Your prices come from your own suppliers' invoices and price lists, and OpenAI's help page says the data-analysis environment cannot make external web requests. Paste the invoice lines or upload the supplier's price file instead, and let ChatGPT turn them into rows. Public market prices say little about what your supplier actually charges you.

Does this need a paid ChatGPT plan?

Not to start. The prompts return text you paste into the sheet, and pasting invoice lines avoids file uploads altogether. OpenAI's help page limits Free accounts to three uploads a day and five files per Project, while Go and Plus allow 25 files per Project. Getting a finished workbook file back depends on your plan and settings, so check what yours offers.

How often should I redo yield tests?

Test each ingredient once when you add it, then again whenever the supplier, the cut or the prep method changes. Retest your ten biggest lines by spend every quarter, because a small yield error on salmon or beef moves more money than a large one on parsley. Write the test date in the Note column so stale yields are easy to see.

Should menu prices in the sheet include sales tax?

Enter the price net of sales tax, because tax is not your revenue. If your menu prices include it, divide by one plus the tax rate first: a $22.00 menu price with 10% tax included is $20.00 net. Food cost percentage and gross profit are then measured on money you keep. Ask your accountant which rate applies to your menu.

Further reads

• Restaurant Inventory Management With AI: Par Levels and Orders — Reuse the same price table to set par levels and order quantities.
• Food Truck Food Cost: Use ChatGPT Before Paying for Software — The same costing idea for a short menu on a truck.
• Restaurant Labour Cost Percentage: Test Rota Changes With AI First — Food is one half of prime cost; test the labour half next.
• How to Price Catering: A Cost-per-Person Sheet With AI — Turn plate costs into a per-head price for events.
• How to Forecast Covers and Cut Food Waste With AI — Shrink the gap between theoretical and actual food cost.
• AI Food Insights Tool: Toast IQ, Square AI or a Spreadsheet? — Compare this sheet with the food reports inside a POS.
• AI Food Menu Generator: Build a Seasonal Menu From Your Supplier List — Paste your supplier sheet into ChatGPT, draft dishes from what is deliverable, cost every plate and check allergens before printing.
• AI in Food Service: A Four-Week Menu Cycle With Costs and Allergens — Draft a 20-day cycle, cost it per cover in a spreadsheet and roll allergens up from supplier spec sheets, with the AI drafting and never supplying the facts.
• Bakery Pricing Calculator: Build One With ChatGPT and a Spreadsheet — Four spreadsheet tabs, ChatGPT-written formulas and one loaf worked through, from ingredient and labour costs to wholesale versus retail margins.
• Bubble Tea Inventory: Forecast Tapioca, Milk and Cups by Weekday — A filled-in weekday sheet for a bubble tea shop: pearl batches, milk cartons and cup orders worked out from cups sold, with exam-week and weekend spikes.
• Bubble Tea Staff Training: Drink Recipe Cards and Quizzes With AI — Record each drink once, let AI format it into a recipe card with sweetness and ice levels, then check it and build a ten-question staff quiz from the cards.
• Catering Menu Planning: Scale Recipes and Shopping Lists With AI — One 120-guest menu taken from recipe sheets to a supplier-ready produce order, with yield factors, pack-size rounding and the checks to run before ordering.
• Cloud Kitchen Margins per Brand: Costing After Delivery Commission — Cost each virtual brand line by line after platform commission, packaging, promotions and refunds, and see which brand really earns its place in the kitchen.
• Coffee Shop Pricing: Cost per Cup and Margins Worked Out With AI — Cost five drinks gram by gram, set a margin target from overheads, round to menu prices without losing margin, and have AI build and check the sheet.
• How to Price Ice Cream: Cost per Scoop and Menu Prices — Test real scoops per tub, cost every cone, sundae and pint, then set prices against a margin target worked out from your own overheads.
• How to Use ChatGPT for Restaurants: Five Jobs to Hand It Each Week — A Monday-to-Sunday routine: ordering, prep and specials, rota, weekend promotion and the weekly review, with allergen answers kept human-checked.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: OpenAI help pages (file uploads FAQ; data analysis with ChatGPT; creating and editing spreadsheets with ChatGPT Work); Microsoft Support (XLOOKUP function); Google Docs Editors Help (XLOOKUP).