Recipe Costing and Menu Pricing for a Cafe (Excel): Cost per Drink and Dish

Project Desciption

About the job
I run a small café in Yan, Kedah, serving coffee, matcha, chocolate drinks and simple food. I need each menu item costed properly, then suggested selling prices based on a target margin. Right now many items have no cost recorded, so I can’t see which ones actually make money.

What you’ll do

• Build a recipe costing sheet in Excel: one row per ingredient per recipe (quantity, unit, unit cost)
• Work out the cost per serving for every drink and dish on my menu ([__] items)
• Include packaging per serving (cup, lid, straw, container) and other direct costs I list
• Convert purchase units to recipe units (e.g. 1 kg of beans, 1 L of milk, a carton of cups)
• Calculate gross profit and food cost % per item
• Suggest selling prices to hit my target margin of [__%], with a short note where my current price is too low or too high
• Rank items by profit contribution (best and worst performers)

What I’ll provide

• Current menu and prices
• Recipes or portion notes (I’ll fill gaps by call or message)
• Supplier invoices or price lists (photos or PDFs)
• My sales export from the POS for item popularity [optional]

Deliverables

1. Excel workbook with these tabs:
   • Ingredients: purchase price, purchase unit, recipe unit, cost per recipe unit (formulas, no hardcoded results)
   • Recipes: each item’s ingredients, quantities and cost per serving
   • Menu pricing: current price, cost, profit, margin %, suggested price
   • Assumptions: waste/yield %, target margin, any rounding rule
2. A one-page summary: top 5 profit items, bottom 5, and items I should reprice
3. A short guide so I can update ingredient prices and see the costs change automatically

Requirements

• Strong Excel: formulas, unit conversion, lookups (INDEX/MATCH or SUMIFS), clear layout
• Understanding of COGS, gross profit and margin; accounting students are welcome
• Attention to detail, with every assumption written down
• F&B or café costing experience is helpful but not required

Rules

• Use formulas so changing an ingredient price updates every recipe
• Mark any guessed quantity or price clearly instead of hiding it
• Keep the sheet simple enough for a non-accountant to maintain
• All files and work remain mine