food cost calculator for excel

Professional Culinary Ingredient & Recipe Costing System

This system provides a standardized framework for tracking raw ingredient prices and calculating the precise plate cost for menu items. It is designed for chefs and restaurant managers to maintain accurate margins.

Update Cadence: Monthly or upon receipt of new vendor invoices.

Ingredient Master Data Table

Column Definitions

1. Ingredient: Name of the raw item. (Data Type: Text)
2. Purchase Unit: The unit as bought from the vendor. (Data Type: Text/Dropdown)
3. Purchase Price: Total cost per purchase unit. (Data Type: Currency; Validation: >0)
4. Unit Yield %: The percentage of the item remaining after trimming/prep. (Data Type: Percentage; Validation: 0 to 1)
5. Usable Unit: The weight or volume of the purchase unit. (Data Type: Number)
6. Usable Cost: The actual cost per base unit after yield loss. (Data Type: Currency; Calculated)

Essential Formulas

Calculate Usable Cost (Per Usable Unit): Place this in the first row of the "Usable Cost" column (assuming Row 2):

=(C2/D2)

Calculate Total Recipe Cost: Use this to sum the costs of individual ingredients in a recipe sub-sheet:

=SUMPRODUCT(Ingredient_Quantity_Used, Usable_Cost_Lookup_Range)

Calculate Suggested Menu Price (at 30% Food Cost):

=(Total_Recipe_Cost / 0.3)

Data Integrity & Formatting Rules

• Data Validation (Yield %): Select the "Unit Yield %" column. Go to Data > Data Validation > Decimal. Set Minimum to 0.01 and Maximum to 1.00.
• Conditional Formatting (High Cost Alert): Select the "Usable Cost" column. Use "Highlight Cell Rules" > "Greater Than" and set a threshold (e.g., 15.00) to turn the cell red if an ingredient exceeds your target cost limit.
• Dropdown Menu: Create a separate sheet named "Units" with a list of common units (kg, lb, Liter, Case, Bunch). Use Data Validation > List to link this to the "Purchase Unit" column.