In order to understand how your restaurant menu can improve, you need to first have a clear sense of how your restaurant menu is currently performing. How much are you spending on inventory? What does your restaurant menu pricing look like? How popular are your menu items? What is your total profit from these items, and how do they compare to each other? These are all questions that restaurant owners need to be able to answer if they hope to optimize their menu and improve bottom line profits.
A restaurant menu engineering worksheet is an Excel spreadsheet that helps restaurant owners understand their menu performance during a given period of time and, more importantly, the items that are contributing to or hurting the business.
This article will introduce the key metrics you need to know for advanced menu engineering analysis and show you how to gain valuable and actionable insights from them.
Create a Menu Engineering Worksheet
1. Select a time period.
The first step in creating a restaurant menu engineering worksheet is to select the timeframe to analyze. You can analyze your menu weekly, monthly, quarterly, or annually - and even all of the above. Chef Brian Duffy recommends updating your menu four times a year so you can have a clear sense of how your menu items are affecting costs and sales.
In our template, each sheet is broken down by quarter: Q1, Q2, Q3, and Q4. If you’re starting now, you can start by analyzing last year: Q1 2015, Q2 2015, Q3 2015, and Q4 2015. Don’t forget to mark the date prepared and period covered fields to stay on track.
2. Record menu items and number sold within that time period.
The first thing you’ll need to do is record the items on your menu. If you just want to optimize your drinks menu, focus there. If you just want to optimize your breakfast menu, focus there. However, if you’re a novice to menu engineering, I suggest doing a total menu analysis, recording every menu item you have.
Next, you’ll need to record the number of items sold within that time period. If you have a restaurant point of sale system that offers menu analytics tracking and reporting, you should be able to easily access detailed sales information for your selected timeframe. If you do not have a POS system, use whatever method is in place for tracking menu item sales .
3. Record the item food cost, or exactly what you paid for that item.
What is item food cost? The short answer is: how much you paid for that menu item. The long answer is: cost of each ingredient + cost of purchasing. “Cost of purchasing” not only includes the price you paid on the item, but any delivery fees, interest, return charges, or other expenses related to purchasing inventory (excluding labor costs). “Cost of each ingredient” also means you may need to do some portioning, depending on how granular you want this analysis to be.
Here’s an example: an onion costs 25 cents + $1 for delivery, so $1.25. Each onion yields eight slices, so the onion cost for a dish that includes two slices would be 30 cents. If you’re making tomato soup, your menu item food cost might be one stick of butter ($1) + 2 slices white onion ($0.30) + 3 tomatoes ($2) = $3.30. If you’re using the menu template, this will be the only field in which you actually need to do your own calculations, if that’s the route you choose to go.
4. Record the item sell price as it appears on your menu.
Finally, you need to record the amount you’re selling this menu item for as the “item sell price” in the menu engineering spreadsheet. You can view this information in your point of sale system or on the menu itself. Hopefully, you’re selling menu items for more than you’re paying for them! If not, that’s the first red flag. But don’t worry; we’ll dig deep into menu analysis to see where you can cut costs in the next section.
Menu Engineering Analysis
With just these three metrics for each menu item - number sold, item food cost, and item sell price - you can create dozens of reports. Here are the most important ones.
Food Cost Percentage
If you're taking advantage of the free menu engineering worksheet, you'll see food cost percentage per menu item in Column F calculated automatically. Food cost percentage is item food cost / item sell price. The percentages listed here are an important indication of how your business is performing, but should be taken with a grain of salt. It’s most often better to have a low food cost percentage, but it’s not the sole indicator of success.
You need to know exactly how much profit each menu item is contributing to your revenue. This field calculates item sell price - item food cost. It’s a simple indicator of which items are contributing positive profit, and which ones are not. This resource recommends that restaurants should have menu items with food costs that are less than 30% of the menu price in order to be profitable, so keep an eye on this metric.
Total Food Cost and Total Menu Sales
Next, the menu engineering worksheet calculates total food cost and total menu sales. Rather than viewing food cost for one item, Total Food Cost consolidates all of your items into one metric. It multiplies item food cost by number sold. Total Menu Sales, similarly, multiplies item sell price by number sold. These numbers show the overall food cost and menu sales for each item within the time frame you chose.
Finally, the menu engineering worksheet calculates contribution margin for each menu item. This is arguably an even more actionable metric than food cost percentage. In order to calculate contribution margin, we used the menu engineering formula: Total Menu Sales - Total Food Cost. In that way, contribution margin is basically “Total Item Profit.”
Menu Engineering Dashboard
Overall Menu Analysis
During this entire time, you’ll see the menu engineering worksheet and graph change as you add more items and metrics. The graph and gray bar next to the graph are what I refer to as your “Menu Engineering Dashboard,” where you can see some quick statistics about your overall menu performance. Here are some important stats:
- All Items Sold = The Sum of Number Sold column
- Overall Food Cost = The Sum of Total Food Cost
- Overall Menu Sales = The Sum of Total Menu Sales
- Overall Contribution Margin = The Sum of Contribution Margin
- Average Profit Per Item = Overall Contribution Margin / All Items Sold
- Average Number Sold = The Average of Number Sold column
- Average Contribution Margin = The Average of the Contribution Margin column
- Overall Food Cost Percentage = Overall Food Cost / Overall Menu Sales
Some of these menu engineering formulas are simply interesting to know - average profit per item, for instance - while others are immediately actionable, and form the basis of the menu engineering graph to the right.
Menu Engineering Graph
The menu engineering graph in our free menu engineering template organizes your menu items into stars, puzzles, plowhorses, and dogs, and plots them based on number sold and contribution margin - basically, popularity and profit. In order to do that, we needed to define a few things:
If the item’s contribution margin was higher than the average contribution margin, it was marked as “high.” If not, it was marked as “low.”
If the item’s number sold was higher than the average number sold, it was marked as “high.” If not, it was marked as “low.”
Finally, we marked item category based on the profit and popularity meters. If profit and popularity were high, the item was a Star. If profit was high and popularity was low, we marked it as a Puzzle. If profit was low and popularity was high, we marked it as a Plowhorse. If both profit and popularity were low, we marked it as a dog.
All of these categories are baked into the menu engineering graph, so you can see menu item name, contribution margin, number sold, and whether those items were stars, dogs, plowhorses, or puzzles right in the same place.
Get Those Menu Engineering Formulas Cranking!
According to the Restaurant Technology Industry Report, inventory management, which includes menu engineering, is the No. 1 feature restaurateurs are asking for in 2016. With this data, you can make more data-backed decisions about your restaurant; and the great thing about data is, nobody can argue with it. For example, Aaron Allen recommends restaurants remove the dollar sign on their menu in order to improve item popularity. If you run that test on your menu for one quarter, you may be able to see the differences in your menu engineering template!
If you ever want to supercharge your efforts even more, making some of this menu reporting even easier, check out Toast Restaurant POS and our sophisticated restaurant software.