Find restaurant food-cost and waste drivers from POS and inventory data
Separate theoretical ingredient usage from actual stock movement so price, mix, portion, waste, and recording gaps can be investigated independently. Start with the what one row represents and definitions, calculate only from eligible records, and check the result against the source before using it.
What changes the answer
- Separate theoretical ingredient usage from actual stock movement so price, mix, portion, waste, and recording gaps can be investigated independently.
- Inventory movement must reconcile by ingredient and location, and theoretical usage must trace to sales quantities and the correct recipe version.
- A negative stock count can signal timing, unit conversion, or missed transfers; it is not evidence of theft without source investigation.
From the field · Restaurant operator
Separate theoretical ingredient usage from actual stock movement so price, mix, portion, waste, and recording gaps can be investigated independently.
POS sales are menu-item records, invoices are ingredient purchases, and counts are snapshots. Recipe changes, substitutions, staff meals, voids, spoilage, and inconsistent units make a single food-cost percentage hard to diagnose.
Columns used in the example
date · location · menu_item · quantity_sold · ingredient_id · recipe_quantity · purchase_quantity · unit_cost · waste_quantity · closing_stock
Follow it with your own file
- Standardize ingredient units and effective recipe versions.
- Calculate theoretical usage from menu sales and reconcile actual usage from opening stock, purchases, transfers, waste, and closing stock.
- Bridge cost variance into purchase price, sales mix, recipe, recorded waste, and unexplained usage.
- Review material ingredient and location exceptions with kitchen context before changing menus or controls.
Check: Inventory movement must reconcile by ingredient and location, and theoretical usage must trace to sales quantities and the correct recipe version.
Build the result step by step
- Define the decision: Standardize ingredient units and effective recipe versions.
- Prepare the evidence: Calculate theoretical usage from menu sales and reconcile actual usage from opening stock, purchases, transfers, waste, and closing stock.
- Calculate the result: Bridge cost variance into purchase price, sales mix, recipe, recorded waste, and unexplained usage.
- Review the exceptions: Review material ingredient and location exceptions with kitchen context before changing menus or controls.
What needs resolving first
POS sales are menu-item records, invoices are ingredient purchases, and counts are snapshots. Recipe changes, substitutions, staff meals, voids, spoilage, and inconsistent units make a single food-cost percentage hard to diagnose.
What the file needs to answer
Separate theoretical ingredient usage from actual stock movement so price, mix, portion, waste, and recording gaps can be investigated independently.
The assumption to make explicit
A negative stock count can signal timing, unit conversion, or missed transfers; it is not evidence of theft without source investigation.
Three ways to approach the same file
| Approach | What it answers | What remains at risk |
|---|---|---|
| Quick spreadsheet total | A narrow descriptive question | Definitions, duplicate rows, timing, and exceptions may be hidden |
| Reviewed analysis | The stated decision with reconciled evidence | Human judgment is still required for policy and action |
| Ongoing monitored workflow | The same decision across new periods | unexpected column changes and changed business rules still need review |
Warning signs in the file
- The source files do not share one documented what one row represents or cutoff.
- The decision owner cannot trace the headline result to eligible source records.
- A negative stock count can signal timing, unit conversion, or missed transfers; it is not evidence of theft without source investigation.
Make the output easy to check
- what one row represents and eligible population documented
- Dates, units, currencies, and identifiers validated
- Inventory movement must reconcile by ingredient and location, and theoretical usage must trace to sales quantities and the correct recipe version.
- Exceptions retained for human review
- Published result links back to its evidence
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Analyze restaurant food cost and waste drivers. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Unpivot Tool · Analysis Anomalies · AI Charts · Report Drafts
A decision-ready result for “Analyze restaurant food cost and waste drivers,” with its assumptions, exceptions, and source totals still visible.
Questions readers usually ask next
Can this restaurant operator analysis be done in Excel?
Yes for a small, stable file. Keep the source unchanged, document formulas and exclusions, and check outputs against the source.
What should not be automated?
Do not automate the final policy or business judgment. A negative stock count can signal timing, unit conversion, or missed transfers; it is not evidence of theft without source investigation.