Access homework

User Generated

17180182888_

Business Finance

Description

see attachment


please help!

thanks!

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

See attached

25.0%

Menu Engineering

20.0%

Menu Mix %

15.0%

10.0%

0.00

0.50

1.00

1.50

2.00

2.50

3.00
5.0%

Contribution Margin

0.0%

3.50

4.00

4.50

5.00

Enter Your Last Name
Your name
Your PID

Item Code
201
202
203
204
205
206
207
208
209
210

Menu Item
Number Sold Menu Mix % Selling Price ($) Item Cost ($)
FRIED SHRIMP...


Anonymous
Excellent! Definitely coming back for more study materials.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags