Homework #4
Menu Engineering
Menu Engineering is a marketing approach to the evaluation of current and future menu pricing,
design, and content decisions. A unique feature of menu engineering is its ability to classify
menu items; not only based on menu selling price, but also upon the menu (sales) mix. The
purpose of the menu mix analysis is to distinguish which items on the menu are sold and in what
relative proportions. The menu mix, also referred to as the sales mix, identifies the percentage of
customer demand attributable to each competing menu item. Menu Engineering focuses on three
essential elements in the pricing decision: customer demand, menu mix analysis, and item
contribution margin. Customer demand refers to the number of patrons dining at a particular
establishment at some point of time. Contribution margin analysis is perhaps the most critical
component to pricing because it deals specifically with each item’s profitability. The
mathematical difference between an item’s menu price and its direct cost is defined as its
contribution margin. The use of the word contribution emphasizes the number of dollars
remaining after paying an item’s direct cost, to cover other expenses and profit. Hence, the higher
an item’s contribution margin, the more desirable it is to sell.
Introduction to the Vlookup Function
a. What is a ‘lookup’ function?
It is a data search performed within a predefined table of values (array, matrix, etc.) or within a
data file.
b. Anatomy of the Vlookup function.
First understand that this function always takes three arguments in this order:
1) The value to be looked up
2) The location of the vertical or columnar lookup table in which the first argument is
looked up
3) The number of the column in the lookup table from which a value is returned as the
result of a function.
The V in Vlookup () means that this function uses a vertical table and searches the first vertical
column in the table. There is also the Hlookup () function that searches the first or top “horizontal
row” in a table for a matching value.
Important: Both of these function require that the values in the first lookup row or column of the
table be in sorted, ascending order.
Page 1
Homework #4
c. The Lookup Table.
It is the table (range or cell range) that contains information needed for the Vlookup () function.
We can define a name for this table using the formula Define Name…command in order to make
it easier for referring to a cell or range of cells (lookup table). Once the name is defined we can
search the table using the F3 key.
Explanation of VLOOKUP Formula
VLOOKUP is applied in order to find data from a defined table and display the data in the cell
where VLOOKUP is used.
=VLOOKUP ($A$31, Table, 2) is divided into three parts: Lookup Value, Table_array, and
Col_index. In this case,
Lookup Value: $A$31
Table_array:
Table
Col_index:
2
Table_array has to be defined prior to implementing VLOOKUP formula. In this case, cell range
A8:N20 has been defined as “table.” VLOOKUP will find information from Table_array and
display it depending on Lookup Value. In this case, a certain data will be searched and displayed
in the cell B32 based on inputting in the cell $A$31. What is a certain data here? It is what
Col_index defines. In this case, it is the data in the second column of the Table_array, or the cell
range A8:N20 (Col_index:2 means the second column, Col_index:3 the third, and so on).
Therefore, Col_index should be changed according to the data which we want to display in a cell.
Explanation and Flowchart of IF Formula
Explanation of IF formula
=IF(L9="HIGH",IF(M9="HIGH","STAR","PUZZLE"),IF(M9="HIGH","PLOWHORSE","DOG") can
be divided into three parts: Logical Test, Value_if_true, and Value_if_false. In this case,
Logical Test:
If L9=“HIGH”
Value_if_true: If (M9=“HIGH”, “STAR”, “PUZZLE”)
Value_if_false: If (M9=“HIGH”, “PLOWHORSE”, “DOG”)
Page 2
Homework #4
When Logical Test is true (if cell L9 is HIGH), cell M9 is compared: is cell M9 HIGH or NOT?
If cell M9 is HIGH, STAR will be displayed.
If cell M9 is NOT HIGH (or LOW), PUZZLE will be displayed.
When Logical Test is false (if cell L9 is NOT HIGH, or LOW), cell M9 is compared again: is cell
M9 HIGH or NOT?
If cell M9 is HIGH, PLOWHORSE will be displayed.
If cell M9 is NOT HIGH (or LOW), DOG will be displayed.
Flowchart of IF formula
No
Is C.M Category “HIGH”?
Yes
Is M.M Category “HIGH”?
Is M.M Category “HIGH”?
No
Yes
“STAR”
No
Yes
“PUZZLE”
“PLOWHORSE”
“DOG”
Page 3
Homework #4
Menu Engineering Lab Exercise
Enter in cell A1…Chef [Enter Your Last Name] (e.g. Chef Zhang)
Enter in cell A3…Your name [e.g. Lu Zhang]
Enter in cell A4…Your PID [e.g. A12345678]
Enter this chart in cell range A8:E18 and format them appropriately (two decimal
places for column D & E and adjust column width) (Make sure all the column
titles are in the same row – A8-E8).
Item Code
Menu Item
Number
Sold
Selling Price Item Cost
($)
($)
202
FRIED CHICKEN
420
4.95
2.21
210
TENDERLOIN TIPS
360
6.45
4.00
206
NEW YORK STRIP STEAK
360
8.50
4.50
208
RED SNAPPER
240
6.95
3.95
203
CHOPPED SIRLOIN
90
4.50
1.95
201
FRIED SHRIMP
210
7.95
4.90
204
PRIME RIB 12 OZ.
600
7.95
4.95
207
TOP SIRLOIN STEAK
510
7.95
4.30
209
LOBSTER TAIL
150
9.50
4.95
205
PRIME RIB 20 OZ.
60
9.95
5.65
Enter in cell F8…Item Cost (%)
Calculate Item Cost (%) in F9
Item Cost (%) = Item Cost ($) ÷ Selling Price ($) (=E9/D9)
Click and drag to copy formula in cell range F10:F18
Format cell range F9:F18 with one decimal percentage (Make sure you are on the
HOME tab on top… click on the down arrow in the NUMBER box and select
MORE NUMBER FORMATS…click PERCENTAGE… click DECIMAL
PLACES: 1) and adjust column width
Page 4
Homework #4
Insert a column between columns C and D by placing the cursor in any place of
column D and right click…Insert
Enter in cell D8…Menu Mix %
Enter in cell B19…Column Totals
Page 5
Homework #4
Calculate the total numbers sold in column C
Go to cell C19 and make “autosum” (Σ icon in the Home Toolbar) in cell
range C9:C18
Go to cell D9 and calculate Menu Mix %
Menu Mix% = No. sold of each item ÷ total numbers sold (= C9/$C$19)
(Make sure you have the $ signs in the formula)
Copy formula in D9 to cell range D10:D18
Format column Menu Mix % (cell range D9:D18) with one decimal percentage
and adjust column width
Page 6
Homework #4
Enter in cell H8…Item CM
CM is abbreviation of Contribution Margin
Calculate Item CM in H9
Item CM = Selling Price - Item Cost ($) (=E9-F9)
Copy formula in cell range H10:H18
Format column Item CM (cell range H9:H18) with two decimals (adjust column
width:
Click Increase Decimal
to display more digits after the decimal point.
Click Decrease Decimal
to display fewer digits after the decimal point.
Enter in cell I8…Menu Costs
Calculate Menu Costs in I9
Menu Costs = Number Sold Item Cost ($) (= C9*F9)
Copy formula in cell range I10:I18
Page 7
Homework #4
Format column Menu Costs (cell range I9:I19) with two decimals & 1000
separator (right click…select “format cells”…check “Use 1000 Separator”) and
adjust column width
Enter in cell J8…Menu Revenues
Calculate Menu Revenues in J9
Menu Revenues = Number Sold Selling Price (=C9*E9)
Copy formula in cell range J10:J18
Format column Menu Revenues (cell range J10:J19)with two decimals & 1000
separator and adjust column width
Enter in cell K8…Menu CM
Calculate Menu CM in K9
Menu CM = Number Sold Item CM (=C9*H9)
Copy formula in cell range K10:K18
Format column Menu CM (cell range K9:K19) with two decimals & 1000
separator and adjust column width
Go to cell I19 and make “autosum” in order to have the Total Menu Cost
Go to cell J19 and make “autosum” in order to have the Total Menu Revenues
Page 8
Homework #4
Go to cell K19 and make “autosum” in order to have the Total Menu Contribution
Margin
Enter in cell I20…Menu FC %
Enter in cell J20…Average CM
Enter in cell K20…MM Rule
Calculate Menu FC % in cell I21
Menu FC% = Total Menu Costs ÷ Total Menu Revenues (=I19/J19)
Calculate Average CM in cell J21
Average CM = Total Menu CM ÷ Total Number Sold (=K19/C19)
Remember the value of the Average CM in cell J21
Go to cell K21 and calculate the MM Rule
MM Rule = (1 ÷ Number of Menu Items) 0.7
Number of Menu Items is 10
Remember the value of MM Rule in cell K21
Format column J21 with two decimal number and I21 & K21 with two decimal
percentage
Enter in cell L8…CM Category
Enter in cell M8…MM Category
Page 9
Homework #4
Calculate C.M Category in cell L9 using IF function
If Item CM is greater than or equal than J21 (Average CM), it is
categorized as HIGH. If not, it is categorized as LOW
The formula is =IF (H9>=$J$21, “HIGH”, “LOW”)
Copy formula in cell range L10:L18 and adjust column width
Calculate MM Category in cell M9 using IF function
If Item Menu Mix % is greater than or equal than K21 (MM Rule), it is
categorized as HIGH. If not, it is categorized as LOW
The formula is =IF (D9>=$K$21, “HIGH”, “LOW”)
Copy formula in cell range M10:18 and adjust column width
Enter in cell N8…Menu Item Classification
Enter formula in cell N9 as following and be careful with “ ”, ( ) and commas.
(For more details, please read page 2 on the instruction)
=IF(L9="HIGH",IF(M9="HIGH","STAR","PUZZLE"),IF(M9="HIGH",
"PLOWHORSE","DOG"))
Copy formula in cell range N10:N18 and adjust column width
Page 10
Homework #4
Select…cell A8:N18…Sort A-Z (ascending order – smallest to largest) (icon in
toolbar)
We need to sort the table information before using Vlookup Function
Select…cell range A9:N18…Formulas…Define Name (Down Arrow)…Define
Name and type...table…OK
Format column range A:N
Font type: Arial
Style: regular
Size: 10
Format row 8 & row 20 with Bold style
Select columns from B to N and click…Format…AutoFit Column Width
This task will arrange the correct size to each column of the worksheet
Go to cell A8 and Format…AutoFit Column Width
Select cell range A8:N8 and copy to cell range A25:N25
Page 11
Homework #4
Go to cell B26
Click…Formulas… Insert Function…search for “vlookup”…OK
Lookup value: $A$26
Table_array: table (able to select it by pressing F3 key)
Col_index: 2 (“#N/A” will be showed up)…OK
For more details, please read the page 1 on the instruction
Copy Formula to cell range C26:N26
Be sure to change the data in formula copied: the Lookup_value should be
A26 for all cases and Col_index_num should be changed in every formula
accordingly (3 for cell C26,4 for cell D26…14 for cell N26… you have to
go through and do this manually to each cell) (example:
=vlookup($A$26,table, 3)
Once you finish, enter different item codes (from 201 to 210) in cell A26 and look
how the vlookup function works
It is important to understand how this Menu Engineering Worksheet works
and how the formulas and functions are linked each other as well.
Select cell D9:D18 and click…Insert…Scatter (Chart)… Scatter with only
markers (top left chart)…A chart will appear on your screen…
Page 12
Homework #4
C
Under the Chart section… Click “Design Tab”… Select “Select Data”
tab…Select “Series 1”… Click “Edit” and enter in X-value:
=Sheet1!$H$9:$H$18…OK… Click OK again
Page 13
Homework #4
Execute the following:
Select Layout tab and enter
Chart title (Above Chart): Menu Engineering
Axis Titles: Primary Horizontal (X) axis: Title Below Axis:
Contribution Margin
Axis Titles: Primary Vertical (Y) axis: Rotated Title: Menu Mix %
Select Gridlines tab… Primary Horizontal… None
Select Gridlines tab… Primary Vertical… None
Select Legend tab… None…
Select…Design Tab… Select “Move Chart icon” (far right corner)…As new
sheet: and enter ME Chart...OK
Page 14
Homework #4
Place the cursor on one of the X-axis value [e.g. 0.50 or 1.00] and “Right-Click”
Page 15
Homework #4
Select…Format Axis
Select Axis Options…Vertical axis crosses: axis value: 3.22…Close
The value entered here is the same value as the average CM (cell J21) in
Menu Engineering worksheet.
Be sure Y axis is moved to right after this process
Page 16
Homework #4
Place the cursor on one of the Y-axis value [e.g. 5.0% or 10.0%] and R-Click
Page 17
Homework #4
Page 18
Homework #4
Select…Format Axis
Select Axis Options… Horizontal axis crosses: axis value 0.07…Close
The value entered here is the same value as MM Rule (cell K21) in Menu
Engineering worksheet.
Be sure X axis is moved to right (up) after this process
Page 19
Homework #4
Click…Insert …WordArt…Select the first one in the first row (it is ok if yours
does not look exactly the same as shown here)…OK
Type: Star…and place it in the first quadrant
Click…Insert …WordArt…Select the first one in the first row…OK
Type: Plowhorse…and place it in the second quadrant
Click…Insert …WordArt…Select the first one in the first row…OK
Type: Dog…and place it in the third quadrant
Click…Insert …WordArt…Select the first one in the first row…OK
Type: Puzzle…and place it in the fourth quadrant
Page 20
Homework #4
Name the Menu Engineering spreadsheet and submit it electronically
(via D2L Dropbox) before 11/22.
Thanks. Good Luck!
Page 21
Purchase answer to see full
attachment