food recipe costing calculator spreadsheet

Culinary Profitability and Ingredient Costing System

This system provides a standardized framework for tracking ingredient procurement costs and calculating the precise Cost of Goods Sold (COGS) for menu items. Use this to maintain margins, track price fluctuations, and manage vendor expenses.

Update Cadence: Review and update market prices [Monthly/Quarterly] or whenever a primary vendor invoice reflects a price change > 5%.

Ingredient Master Inventory & Costing Table

Column Definitions

• Ingredient Name: [Text] - Name of the raw item as listed on invoices.
• Unit of Measure: [Dropdown List: kg, g, L, ml, unit, oz, lb] - Standardized unit for internal tracking.
• Bulk Purchase Price: [Currency] - Total price paid for the bulk container.
• Bulk Quantity: [Number] - Total units contained in the bulk purchase.
• Unit Cost: [Formula] - Calculated base cost per single unit.
• Waste Factor (%): [Percentage] - The anticipated percentage of product lost to prep, trimming, or spoilage.
• Adjusted Unit Cost: [Formula] - The final cost per unit including the waste buffer.

Calculation Formulas

Unit Cost Calculation: Paste this into the first cell of the "Unit Cost" column (e.g., E2):

=C2/D2

Adjusted Unit Cost Calculation: Paste this into the first cell of the "Adjusted Unit Cost" column (e.g., G2):

=E2*(1+F2)

Recipe Total Cost (For use in a separate Recipe Tab): To calculate the cost of a specific recipe, use this formula where [Range] is the sum of (Ingredient Quantity * Adjusted Unit Cost):

=SUMPRODUCT(B2:B10, G2:G10)

Data Validation & Formatting Rules

• Data Validation (Unit of Measure): Select the "Unit of Measure" column, go to Data > Data Validation, and set criteria to "List of items" using: kg, g, L, ml, unit, oz, lb.
• Conditional Formatting (Waste Factor): Select the "Waste Factor (%)" column. Create a rule: If cell value > 0.10 (10%), set cell background to Light Red. This highlights high-waste ingredients that may require better prep training.
• Conditional Formatting (Price Alerts): Select the "Bulk Purchase Price" column. Create a rule: If the value is > [Target Price Threshold], set text to Bold Red to flag items exceeding your budget benchmarks.