food cost calculator excel philippines

Professional Culinary Inventory and Portion Costing System

This system is designed to provide restaurant owners and catering professionals in the Philippines with a scalable framework to track ingredient procurement costs (inclusive of VAT/logistics) and calculate the precise plate cost for menu items.

Update Cadence: Weekly or upon receipt of new supplier invoices.

Master Ingredient Inventory & Costing Table

Column Definitions

• Ingredient Name: [Text] - The specific item as purchased from the supplier.
• Unit of Measure: [Dropdown] - Standard units (kg, g, L, ml, pc).
• Purchase Price (PHP): [Currency] - Total invoice cost for the bulk item.
• Bulk Quantity: [Number] - The total weight or volume of the bulk purchase.
• Unit Cost (PHP): [Formula] - Base cost per unit before waste.
• Yield %: [Percentage] - The usable portion of the ingredient after trimming (e.g., 0.85 for 85% yield).
• Actual Cost per Unit: [Formula] - The true cost accounting for trim loss.

Essential Calculation Formulas

Calculate Base Unit Cost: Place this in the 'Unit Cost' column (Row 2):

=C2/D2

Calculate Actual Cost per Unit (Accounting for Yield): Place this in the 'Actual Cost per Unit' column (Row 2):

=E2/F2

Calculate Total Recipe Cost: Use this to sum up the cost of ingredients for a specific dish:

=SUMPRODUCT(Quantity_Used_Range, Actual_Cost_Per_Unit_Range)

Data Validation & Formatting Rules

1. Yield Percentage Validation: Select the 'Yield %' column, go to Data > Data Validation, and set to "Decimal" between 0.1 and 1.0. This prevents input errors that would skew cost calculations.
2. Low Yield Alert (Conditional Formatting): Select the 'Yield %' column. Apply conditional formatting: If cell value < 0.70, fill with light red background and dark red text to flag ingredients with high trim loss.
3. Currency Formatting: Apply "PHP" custom number formatting to all columns involving currency (C, E, and G) to ensure clear financial reporting.