Menu Mix Analysis Workbook (spreadsheet)

Why this workbook matters

Decisions about menu items affect profit, throughput, and guest loyalty. This workbook gives you a repeatable way to turn POS and recipe data into clear recommendations: what to promote, what to test, what to redesign, and what — rarely — to remove. It focuses on protecting guest experience while improving contribution to the bottom line.

Quick start (10-minute path to value)

1. Export recent POS sales (30–90 days) and open the Sales Input sheet.
2. Confirm POS item names map to your recipe names.
3. Enter or import current ingredient prices into Recipe Cost.
4. Confirm portion size and yield factors in Portion & Weights.
5. Scan Calculated Metrics for obvious outliers (zero cost, huge margins, negative sales).
6. Review the Menu Matrix classifications and note early candidates.
7. Use the A/B Test Tracker to plan one small experiment (pick one Puzzling high-margin, low-pop item).
8. Pilot with a small staff-trained period (1–2 weeks) or until sample size targets are reached.
9. Follow the Rollout Checklist to update POS, train staff, and monitor KPIs closely for the first 4 weeks.
10. Record outcomes in the change log so organizational memory grows over time.

What this workbook does (expanded)

The workbook combines sales, recipe cost, and portion data to produce:

• Contribution margin per item (dollars and percent).
• Popularity share and trend analysis by item and category.
• Estimated contribution per labor-hour to expose throughput-sensitive items.
• GAP score — a composite prioritization score that blends margin, popularity, and throughput impact.
• Menu Matrix guidance (Stars, Puzzles, Plowhorses, Dogs) and suggested actions.
• A/B test planner and simple results tracker for controlled experiments.
• Rollout checklist and rollback triggers to protect revenue and guest experience.

Included sheets (details & mapping tips)

• Sales Input — recommended columns: Date, Location (if multi-site), POS Item ID, POS Item Name, Menu Item (canonical recipe name), Units Sold, Gross Sales Amount, Covers, Service Period (breakfast/lunch/dinner), Channel (dine-in/takeout/delivery). Tip: include a stable POS Item ID to avoid name mismatches.
• Recipe Cost — ingredient description, UoM, supplier price per unit, cost per portion, waste/yield assumptions. Tip: include a last-updated date for each ingredient price.
• Portion & Weights — raw weight, cooked weight, portion weight, yield factors and conversion notes. This sheet makes raw-to-served conversions transparent so costing aligns with what guests receive.
• Calculated Metrics — per-item food cost per portion, selling price, contribution margin (selling price minus food cost), margin %, popularity share (units / total units), contribution per unit, contribution per labor-hour estimate, trend columns, and GAP components.
• Menu Matrix — automated categorization into Stars, Puzzles, Plowhorses, Dogs. The workbook uses configurable thresholds (see suggested defaults below).
• A/B Test Tracker — hypothesis, variant definitions, sample size target, test period, KPIs to monitor, raw result rows, and a simple results summary showing deltas.
• Rollout Checklist — step-by-step pilot and rollout instructions, training prompts, POS update steps, monitoring cadence, and rollback triggers.
• Change Log — record what changed, why, who approved, expected impact, and observed outcomes over time.

Suggested default thresholds and classification rules (examples)

These are starting points you should adapt to your business. Change them in the Menu Matrix sheet.

• Popularity: Top 20% of items by unit sales → "High popularity"; bottom 20% → "Low popularity".
• Margin: Margin > 35% → "High margin"; margin < 20% → "Low margin".
• Menu Matrix categories:
  • Stars = high popularity + high margin (promote)
  • Puzzles = low popularity + high margin (test placement/description/pricing)
  • Plowhorses = high popularity + low margin (control portioning, costing, or consider small price change)
  • Dogs = low popularity + low margin (evaluate redesign or removal after testing)

GAP score (example formula)

The workbook includes a configurable GAP calculation to prioritize items. One practical pattern is to score normalized inputs and weight them:

GAP = (Wm * normalized_margin_rank) + (Wp * normalized_popularity_rank) - (Wt * throughput_penalty)

Where normalized ranks convert raw margin and popularity into a 0–1 scale, Wm/Wp/Wt are weights you choose (e.g., Wm=0.5, Wp=0.4, Wt=0.1), and throughput_penalty increases when prep-time, remakes, or long ticket times are detected. Higher GAP means higher priority for action (promote/test/redesign).

Tip: use percentiles rather than raw values when your menu has extreme outliers.

A/B test practical guidance

• Define a clear hypothesis: e.g., "Putting Item X in the top-right quadrant will increase units sold by 15% without reducing average check."
• Pick KPIs: units sold, revenue, contribution margin, average check, throughput indicators, and guest feedback score.
• Sample size rule of thumb: aim for at least 30–50 sales per variant to observe directional effects; larger samples improve confidence. If volume is low, extend the test window until you reach a practical sample size or run sequential tests across comparable days/times.
• Run across comparable periods (same days of week and service periods) to avoid bias from daypart or promotion differences.
• If you track statistical significance, use proportion tests for units sold or t-tests for average check; when unsure, treat early tests as exploratory and look for consistent practical effects over time.
• Record qualitative guest/staff feedback in the A/B Test Tracker — sometimes feedback explains numeric changes.

Data quality & POS mapping checklist

• Confirm POS Item ID → Recipe name mapping; map many-to-one POS items (e.g., substitutes or modifiers) explicitly.
• Check for modifiers and combos — split combo sales if you want per-item contribution clarity or treat combos separately.
• Flag zero-price or zero-cost records for manual review.
• Validate ingredient unit consistency (lbs vs kg vs each) and document conversion factors.
• Reconcile totals: units sold * selling price vs gross sales amount — large differences indicate discounts, voids, or mapping problems.

Common pitfalls and how the workbook helps avoid them

• Removing customer favorites based only on margin — the workbook forces you to view popularity and guest feedback alongside margin and recommends testing first.
• Inaccurate costing — the Recipe Cost and Portion sheets make yield and portion assumptions visible so you can correct mistakes before making decisions.
• Seasonal distortions — use appropriately sized time windows and compare like-for-like periods; the workbook supports filtering by date ranges.
• POS mapping errors — the Sales Input and mapping checklist surface mismatches early so you don’t draw wrong conclusions.

Rollout checklist (practical steps and monitoring cadence)

1. Pilot on a limited scope (timeslot or locations) and train staff on updated recipes and portions.
2. Update POS SKUs and menus; ensure modifiers and combo logic are correct.
3. Publish quick reference for front- and back-of-house with photos and plating notes.
4. Monitor daily for obvious revenue or operational impacts; review aggregated KPIs weekly for 4 weeks.
5. Define rollback triggers before rollout (example triggers: revenue for test period drops >5% vs control, guest satisfaction drops >5 points, or remakes increase >X%).
6. If triggers fire, pause changes, investigate root causes, and use A/B Test data plus guest/staff feedback before deciding to rollback or iterate.

Practical examples (brief)

Example: Item A has 40% margin but low popularity (top-30%). The workbook flags it as a Puzzle. Use the A/B tracker to test menu placement and wording for 3 weeks focused on similar service periods. Track units sold and contribution per labor-hour. If units increase without negative guest feedback, promote; otherwise, redesign or re-cost.

Adapting this workbook

It's a starting point. For multi-location operations, include Location and Channel columns and either filter or aggregate results. For high delivery/takeout mixes, treat channels separately. Adjust GAP weighting, popularity percentiles, classification thresholds, and rollout risk tolerance to fit your business model.

Next steps & recommended practice

• Run the analysis monthly or after major price or supplier changes.
• Keep the A/B tests small and iterative — treat them as learning experiments not final verdicts.
• Maintain a change log documenting hypothesis, actions, and results; this creates organizational memory and reduces repeated mistakes.

Capability opportunities (how this can get better)

Consider adding interactive import screens for POS and recipe prices, and saving test and rollout submissions so results build a searchable organizational history. Integrations with POS and inventory systems would reduce mapping errors and make near-real-time monitoring possible. See CapabilityEnhancementNotes for more detail.

Image

Suggested image search phrase: "menu engineering spreadsheet"