Menu Engineering Template User Guide & FAQ
For many of our corporate clients, your Account Manager may have supplied you with a Menu Engineering spreadsheet template to run the Menu Engineering Initiative at select locations. This guide is to help you use the template effectively and troubleshoot any issues you encounter. You can also view the sister article How to Use an Item Sales Report for Menu Engineering.
In this guide:
- What is Menu Engineering?
- What is the Menu Engineering template for?
- How to Use the Menu Engineering Template
- Common Problems & FAQ
What is Menu Engineering?
Menu engineering is using the popularity and profitability of individual menu items to make strategic decisions about pricing, menu content, sales strategies, and more. Using the data already at your fingertips in Avero you can quantify customer preferences to drive revenue while protecting profit.
What is the Menu Engineering template for?
Avero's Menu Engineering Template creates a place where you can analyze your menus in more depth and forecast changes you may be considering. First, you'll use the initial calculations to understand where your menu is now. Then, you can make changes to the data to see how increasing a price, lowering the cost of an item, or fluctuations in popularity could change how an item contributes to your profitability.
How to Use the Menu Engineering Template
Don't have the template? Request one from your account manager!
Viewing the Instructions Tab
The first tab on the left will contain the instructions for using the ME Template. These include instructions to make sure excel is allowing the template to function properly, as well as importing the necessary information.
Enable All Macros
- Upon opening the file, click the security warning at the top of your excel application
- Note: If no security warning appears, change security settings to "Disable all macros with warning", close file using the instructions below, and reopen the template
- Select "Enable content" to allow macros to function properly.
Set Up Your Category for Analysis
- On the Setup tab, enter Item Name, Qty. Sold, Gross Sales, Avg. Item Cost and COGS$ from Sales: Item Sales report in Avero for up to 38 items per category in designated input cells.
- Note: If your currency is not USD, be sure to type in your currency symbol in cell L1
- On Category tab, enter Start Date, End Date, Meal Period, Revenue Center, Check Count, Cover Count and Average Check in yellow input cells at top.
- On the category tab, reformat the chart by changing horizontal and vertical axis values. Right-click on horizonal or vertical axis values and select format axis. Alter maximum value so items are more evenly spaced. Look at the Axis Adjustments guidelines under the graph for guidance.
- Hide unused rows at the bottom of the category tab.
Alter Item Price, Quantity, and Cost
- Using yellow input cells on each category sheet, enter new proposed values for Item Price, Item/Cover %, and Item Cost.
- Notice changes to graph with new trend line and item position.
- Notice changes to table on each "Proposed" item row and total row compared to "Baseline" values.
- Restore original item price, quanity and cost by clicking the "Restore Values" button on the bottom of each sheet.
- Note: Values will restore to baseline values
Save and Close File
- Save as macro-enabled file by selecting "Save-As" and "Macro-Enabled Workbook".
- Close file.
Running the Reports
Log in to Avero and navigate to Item Sales to run your report:
- Select the business/outlet in the Business box
- Select the dates - for menu engineering longer time frames of the same menu items is better. Choose at least one full month, 3 months if your menu items and prices have been the same for that long.
- Filter out revenue centers, meal periods, and order types that don't matter. You'll want to compare like to like, so avoid comparing the bar menu to the dining room or breakfast to dinner. There are lots of tabs so you can individually evaluate each category you want to.
- Select the Category you want to analyze, or run for all categories.
- Make sure to include the following metrics: Item Qty, Gross Sales, Avg Item Cost, Cost of Goods $
- Export the report to excel to make it easy to copy and paste.
See the process for running the report here:
Inputting the Necessary Data
Now you'll take the data from your Avero report and input it into the Menu Engineering template.
- Setup Tab
- Highlight & Copy the data from your excel report, starting with the Item Name tab and making sure you have all six columns: Item Name, Item Number, Quantity Sold, Gross Sales, Average Item Cost, COGS$
- Right-click on the first field in the top left of the yellow sales, Paste Values
- Once the yellow fields are filled, you'll see the same data populate on the left table - do not edit this table, it powers the ME calculations.
- If your currency is not USD - make sure to input the currency symbol at the top or your currency symbol may break the formulas!
- Category Tab
- At the top of the Category tab, add the outlet name
- In the yellow cells, enter the context of your report so you know the parameters of the menu engineering you are running. These fields must be filled.
See the process:
Viewing the Results
You can now view the results - your chart will populate automatically for an easy visualization of the data. If the chart is not easy to view, you may want to adjust the plot area to reflect the values included in your report. This is easy to do!
At the bottom of the chart, you'll see the minimum and maximum values included to adjust your axes:
- Right-Click the chart and select Format Plot Area
- Use the excel drop-down menu to select the horizontal axis, input the listed minimum and maximum values.
- Repeat with vertical axis
Your chart should now be easier to view and read.
Avero's Menu Engineering template allows you to forecast the effects of changes you may decide to make. For each item there are two data areas: Baseline (white cells, on top) - this is your actual sales data and should not be adjusted; Proposed (yellow cells, below baseline) - this is where you can try out different solutions.
Proposed cells allow you to input different prices, costs, or popularity to see how that may change an item's performance. If you run a server sales incentive or better the menu position to increase popularity can you make a Puzzle into a Star? If you change the portion size or garnish to decrease cost could a Dog become a Plowhorse?
Menu Engineering usually divides your graph into four categories indicating which items should be kept, which should go, and which can be adjusted to improve. First, we will describe the categories then provide examples of actions that could be taken. Get creative!
The four quadrants are named some variation of:
Top Right - The STAR
These are items that are to the right-hand side of the graph in the upper half. These are items that are very profitable and very popular. Guests order them often and they make you money. Keep these!
Bottom Right - The PLOWHORSE
On the right-hand half of the graph are items that are very popular - your restaurant might be known for them - but they are in the lower half of the graph meaning they aren't contributing much profit. These are menu items that might need adjustment in portion size or pricing to increase their contribution.
I don't want to mess with my popular items: take a look at what makes your item popular and don't change that. Do people love it because it is tasty? Keep the recipe the same but raise the price a dollar, it will still be very popular but now add more to the bottom line. Do people love it because it is inexpensive? Don't touch the price, but perhaps adjust the portion size so the item cost is lower. Do people order it because it is in the center of the menu, at the top of the list or in a 'Specials' section? Consider moving it mid-list and giving the prominent placement to a Puzzle item instead.
Top Left - The PUZZLE
These items are in the upper half of the graph, meaning they are good at contributing profit like the STARs but they aren't as popular with your guests. These are menu items you should look at adjusting!
Why make changes to profitable items?
Profitability is great, but items that are infrequently or rarely ordered lead to wasted product, inefficient prepping, and wasted menu space. Try to figure out why these items aren't more popular.
What kinds of changes can be made to increase popularity?
Forget everything you know about running a restaurant and look at your printed and online menu as someone trying to order dinner. Sometimes it is as simple as these items are on a lost place in the menu - hidden in the middle of a list or at the bottom right, consider moving the item to a more prominent spot, changing the name, or adding a menu call out such as a frame or a box around the item.
Of course, there is also he good old-fashioned sales contest for staff. Ask them about the item and why guests don't order it more often, maybe the portion size is too small or too large for most customers, or servers don't sell it because it comes on the heaviest plate. All of these are real reasons we have seen for items lagging in popularity!
Bottom Left - The DOG
These items are not very popular and don't contribute much to profit. Products in this section are chief contributors to wasted time and product. Ordering supplies for items that aren't ordered, wasted time prepping the mise en place for these dishes, and a busier menu for including them.
Should I just cut all the Dogs from my menu?
Not necessarily, though removing these items is an option. Can they be adjusted to make them more profitable or more popular? Try adjusting portion sizes, switching out the garnish, or adding something your customers love.
Common Problems & FAQ
International Customers - Currency
The letters used to indicate different currencies need to be removed for the formulas to process the pricing and sales correctly. The template is built to filter these out by inputting the currency code in this box to remove them from the fields: