Menu Mix & Contribution Spreadsheet

What this workbook does

This spreadsheet helps you identify the menu items that actually drive profit (contribution margin) and those that your guests love (popularity). It computes item-level contribution, ranks items by popularity and profitability, and places each item into a visual quadrant (stars, puzzles, plowhorses, dogs) with suggested operational actions.

Workbook structure (tabs)

• Raw Sales — Raw POS export (one row per item sale or daily aggregate per item).
• Recipe Costs — Cost-per-portion data pulled from recipe/recipe-card or procurement prices.
• Linked Items — Mapping that joins POS item codes to recipe cost records and price.
• Contribution — Calculations: price, food cost, contribution margin (price − food cost), contribution margin %.
• Popularity — Quantity sold, share of total item volume, and popularity buckets.
• Matrix & Actions — Popularity vs profitability quadrant chart and suggested interventions for each quadrant.
• Settings — Configurable thresholds (e.g., what counts as “high” popularity or “high” profitability) and filter controls (date range, location, category).

Required inputs / CSV headers

To use the workbook, export these fields from your POS and your recipe/cost system. Example headers:

ItemCode,ItemName,Date,QtySold,NetSales,Price

ItemCode,ItemName,FoodCostPerPortion

If your POS export uses modifiers or comps, include separate columns or a normalized row per modifier so you can map costs correctly.

Key calculations (what they mean)

• Contribution margin (absolute) = Price − Food cost per portion. This is the dollars an item contributes toward covering labor, rent, and profit.
• Contribution margin % = (Contribution margin / Price) × 100. Useful for comparing items priced very differently.
• Popularity = QtySold / TotalQtySold (over chosen period). Shows how strongly guests choose an item.
• Contribution per period = Contribution margin × QtySold. Shows total contribution delivered by an item over the period.

Quadrant logic (popularity vs profitability)

The matrix splits items on two axes: popularity and profitability. Thresholds are configurable (default: median or configurable percentiles). Typical quadrant meanings and suggested actions:

• Stars (high popularity, high profitability): Promote and protect. Keep recipes stable, ensure consistent supply, test high-margin variants, and feature these on specials or combos.
• Puzzles (low popularity, high profitability): Test presentation, placement, and pricing. Consider repositioning on the menu, pairing with popular items, limited-time offers, or improving description/photography.
• Plowhorses (high popularity, low profitability): Reduce cost or increase price carefully. Look for waste, portion creep, ingredient substitutions, or supplier negotiation; consider price-testing small increases or bundling to improve margin.
• Dogs (low popularity, low profitability): Clean-up candidates. Before removing, test a promotion; if performance doesn’t improve, consider removing to simplify operations and reduce volatility.

How to use — step by step

1. Choose a representative date range (e.g., last 30 or 90 days) and export POS sales by item.
2. Export recipe-level or portion cost data for the same period (food cost per portion). If you track yield or waste, adjust cost accordingly.
3. Import the POS CSV into the Raw Sales tab and the cost CSV into Recipe Costs. Use the Linked Items tab to join by ItemCode.
4. Open the Settings tab and set thresholds (for example, top 30% popularity = “high” popularity; contribution margin % > 30% = “high” profitability). Defaults are provided but should be tuned to your business and price points.
5. Review the Contribution and Popularity tabs for outliers, large comps, or seasonal effects that may skew results.
6. Use the Matrix & Actions tab to see quadrant placement and suggested next actions. Add notes and assign owners for experiments (menu placement, price test, recipe change).

Interpreting results — practical advice

• Don’t remove items solely because they are “dogs” in one short period—check seasonality, one-off drops, or supply issues first.
• For plowhorses, quantify whether small price changes or cost reductions will materially improve profitability without losing sales. Use A/B tests where practical.
• Look beyond margin % — an item with modest margin but very high volume can deliver large total contribution.
• Use contribution per period to prioritize workload: items delivering the most dollars deserve operational attention first.

Quick A/B testing checklist (menu or price changes)

• Define the hypothesis (e.g., increasing price by $1 will not reduce sales more than X%).
• Choose comparable days or locations for the test and a minimum sample size / time window (depends on average daily transactions; typically 2–4 weeks).
• Measure: QtySold, NetSales, Contribution per period, and guest feedback or complaint volume.
• Stop early if guest complaints spike or guest counts fall materially. Prefer incremental changes and rollback plans.
• Record results in the workbook notes and update recipe/price records if the test succeeds.

Common pitfalls & how to avoid them

• Mixing gross sales vs net sales: use net sales after discounts/refunds for better accuracy.
• Ignoring comps and modifiers: include them or separate them out so costs map correctly.
• Using a too-short period: short periods amplify noise; use 30–90 days unless seasonality demands otherwise.
• Letting guesses override data: use the workbook to prioritize experiments rather than take irreversible actions immediately.

Example CSV header (POS export)

ItemCode,ItemName,Date,QtySold,NetSales,Price

Next steps & recommended workflow

1. Run this analysis weekly or monthly and track changes over time (add a timestamped snapshot tab or export of the matrix).
2. Assign owners for each proposed action (promote, price-test, rework, remove) and schedule follow-ups to measure impact.
3. Combine with inventory and waste logs to validate food cost assumptions and identify portion drift or waste sources.
4. Use the results to inform menu design, specials, and training for consistent recipe execution.

Where this spreadsheet fits in the bigger picture

Menu engineering is one lever among many. Use this tool alongside inventory accuracy checks, labor analysis, guest feedback, and supplier negotiations to improve profitability without harming guest experience.

Download & customization

The workbook is intended as a starting template: adapt the popularity/profitability thresholds, category filters, and reporting cadence to your business. If you have multi-location POS data, add a Location column and use the Settings tab to filter or pivot by location.

Image search phrase

menu engineering spreadsheet