excel assignment
emily12345
assignment.docxObjective:
The goal of this assignment is to further your ability within excel while at the same time allowing you to explore the concept of costing out a recipe and setting up a master inventory. To complete this assignment open up an excel document and follow the procedures listed below.
1. Think of 3 Menu items that you might want to serve at a restaurant. Open an Excel Work Book and set up sheets in the following order
· Master Inventory
· Menu Item 1
· Menu Item 2
· Menu Item 3
· PMIX and Ideal Food Cost
2. Set up a Master Inventory Sheet to resemble something like what is pictured below.
3. Your menu items should have 3-4 ingredients at a minimum thus your inventory should be 10 or more items.
4. For each of your menu items set up recipe cards that look like either or a combination of the following:
With the one above you would want to add the process and steps to produce the recipe!
This one is nice as well but you would then want to add total cost of menu item and the sales price so you could determine your ideal food cost percentage. This one directly above is an example of what I call a sub recipe something that would go into other menu items. For example people do not order tartar sauce on its own but it is served with fish and chips thus is an ingredient in that menu item as well as spread on the fish sandwich etc.
5. Once you have created your Menu cards for your three items you then need to link all the cost on those cards to the master inventory sheet. The master inventory items can be determined by going to the South Fork Public House FSA Order Guide that will be posted on black board, and using the ingredients listed here to create your recipes.
6. On the top of all your excel sheet please place a “header” with your restaurants name!
7. Finally the Menu mix and Ideal food cost sheet. The assumptions for this section is as follows:
· The menu items sold as follows:
i. Menu item on sheet 1 = 100
ii. Menu item on sheet 2 = 150
iii. Menu item on sheet 3 = 50
· You need to link the PMIX (listed above) with the appropriate food cost percentage for each item listed on their menu cards and come up with the Ideal Food Cost percentage for the establishment. Remember Weighted Average!!!
|
|
PMIX |
FC % |
|
|
Chix |
100 |
31.50% |
0.105 |
|
Beef |
150 |
27.90% |
0.1395 |
|
Fish |
50 |
45.00% |
0.075 |
|
|
300 |
|
31.95% |
Will look like this!!!! Within the last cell make the equation all-encompassing by taking the PMIX and dividing it in to the total items sold (300 in this case) and then multiplying it by the FC% of that item. You will want to use $ signs to hold that totals cell constant. Good Luck!
8. Make sure that whatever format and layout you use that you are consistent throughout the assignment/workbook. Place this file in the drop box on angel.
