Please help! VERY OVERWHELMED AND STRUGGLING!

Page Layout Formulas View Help File Home Insert Data Review X Cut Calibri * 11 ~ A A = = ab Wrap Text General [B Copy Paste Merge & Center $ ~ % Format Painter Alignment Numbe Clipboard Font F9 X V M C D E G K A F H N 1 Single DOE Menu price: 4.25 2 Quantity Item EP/Cost Extended 4 1 Hot Dog 5 1 Hot Dog Bun Total Recipe Cost 7 8 Food cost % ? 9 10 Coney Dog Menu price 4.95 11 12 Quantity Item EP/Cost Ext 13 Hot Dog 14 1 Hot Dog Bun 15 2 fluid oz Coney Sauce 16 Total 17 18 Food Cost % 19 20 The Works Menu price 5.95 21 22 Quantity Item EP/Cost Ext. 23 Hot Dog 24 1 Hot Dog Bun 25 2 fluid oz Coney Sauce 26 5 oz Onion 27 1 oz Cheese, Shred Ched 28 .5 fluid oz Mustard 29 Total 30 31 Food Cost % 32 Double Dog Menu price 6.95 34 35 Quantity Item EP/Cost Ext. 36 2 Hot Dog 37 1 Hot Dog Bun 38 2 fluid oz Coney Sauce 39 5 oz Onion 40 1 oz Cheese, Shred Ched 41 .5 fluid oz Mustard 42 Total 43 44 Food cost % June recap Profit and Loss Instructions Inventory Recipe Costs Menu Analysis-EngFile Home Insert Page Layout Formulas Data Review View Help & Cut Arial 10 ~ A A ap Wrap Text General TH Paste LG Copy * BIU BLA Conditional Format as Format Painter HE E Merge & Center Formatting * Table Clipboard Font Alignment Number L16 X V A B C D E F G H K M [E-D) (D*B (E*B) (F*B) popularity profit MENU ITEM MENU MENU ITEM # SOLD MIX % FOOD SALES ITEM MENU MENU MENU $ #sold MENU CM ITEM Co (MM%) COST $ PRICE $ CM $ COSTS $ EVENUES CM CATEGORY CATEGORY CLASS Hot Dog 300 0.00 0.00 0.00 0.00 10 Coney Dog 800 0.00 0.00 0.00 0.00 11 The works 600 0.00 0.00 D.00 0.00 12 Double Dog 300 0.00 0.00 0.00 0.00 13 COLUMN TOTALS 0.00% $0.00 0.00 0.00 0.00 14 Average 15 Number of Menu Items 4 CM 16 Benchmark (70% 17 18 19 20 21 22 23 24 26 32 35 36 37 38 39 40 41 Instructions Inventory Recipe Costs Menu Analysis-Eng June recap Profit and Loss +AutoSave O Off) Final Project HRM 110 Online SP21 (1).xIsx - Excel File Home Insert Page Layout Formulas Data Review View Help X Cut Calibri 11 YAA ab Wrap Text [ Copy Paste BIU A = Merge & Center Format Painter Font Alignment Clipboard D6 X F G A B C D F H June 30th 2019 Recap 2 Sales Recap Payroll Recap W Food Sales Wages $1,800.00 payroll 4 No Beverage Sales Employee Bene. $ 700.00 benefit 5 Sales tax 628.87 Employee meals $ 200.00 benefit 7 8 General Ledger Dir. Oper. $1,600.00 10 Music and Enter. $ 200.00 11 Rep. and Maint. $ 200.00 12 Admin. And Gen. $ 300.00 13 Adv. And Promo $ 100.00 This does not include promo under COGS 14 Utilities $ 400.00 15 Property Tax 300.00 16 Rent and Misc. $1,500.00 17 Insurance $ 300.00 18 Lease Amort. $ 150.00 19 Interest LT $ 150.00 20 Depreciation $ 200.00 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Instructions Inventory Recipe Costs Menu Analysis-Eng June recap Profit ReadyX Cut LO Calibri 11 ~ A" A" aB Wrap Text General [B Copy Paste Format Painter BIU Merge & Center * Clipboard Font Alignment Number G17 X v f B C D E F G H 1 Scott's World Famous Hot Dogs W N Profit and Loss Statement (or income statement) for the month of June 4 Sales 5 Food Sales 6 COGS 7 d Cost 00 9 Gross Profit on Sales 10 11 Operating Expenses 12 Payroll 13 Employee Benefits 14 Direct Operating 15 Music and Entertainment 16 Repair and Maintenance 17 Administrative and Genera 18 Advertising and Promotional 400 This is Adv and Promo + COGS Promo $400.00 total 19 Utilities 20 Total Operating Expenses 96 21 22 Profit Before Occupation Expenses 196 23 24 Occupation Expenses 25 Property Tax 26 Rentals and Miscellaneous 27 Insurance 28 Lease Amortization 29 Interest- long term 30 Depreciation 31 Total occupation expenses 96 32 33 Profit before income tax 34 35 36 Module 6 Prime Cost Calculation 37 Prime Costs 38 Prime Cost Percent 9% 39 Instructions Inventory Recipe Costs Menu Analysis-Eng June recap Profit and Loss (+ Ready& Cut = = = Currency 2 Norma Calibri * 11 -A A Be Wrap Text Genera Paste CE Copy E Merge & Center $ ~ % 9 00 20 Conditional Format as Good Neutr Format Painter Formatting * Table Clipboard Font Alignment Number Styles Q2 X A B D E G H K M N O P Q 1 2 Final Project for Food and Beverage Cost Control This is the final project for food and beverage cost control. The project is worth 80 points! Each table below is part of the progression w to eventually get to the profit and loss statement. Be sure to follow the progression listed below and double check all forms are filled out completely. 6 Before you begin one note on something you may not know. Marketing and promotions are handled carefully in food service. 7 Initially you account for the full sale, but at the end of the accounting period you only value the cost of the sale in calculating COGS and then putting in 8 as an expense in marketing and promotions on the income statement. I put notes in the project to direct you. 10 1) Calculate EP unit costs per ingredient. Next find total value of the inventory on hand for the end of the month of June. 11 2) Calculate cost of goods sold (sales) 12 3) Cost out each individual recipe costs using what you calculated for EP on the inventory page. 13 4) Calculate food cost % for each recipe 14 5) Complete the Menu Analysis worksheet 15 6) Complete the profit and loss statement 16 7) Calculate the Prime Costs and Prime Cost Percent 17 8. Evaluation: Put this evaluation in a separate word document. 18 1. Did your company make money? Do you think this is enough money to sustain your business? Why or Why Not? 19 A. If the owner of the company is also the only employee, does this change your answer? 20 2. How do you interpret problems with the company and sustaining profits based on possible problems that could occur. 21 Base your answer on our discussion board on companies that went out of business. Example change in markets, economy, health concerns, 22 theft, etc... 23 3. What information did you pickup from prime costs? How relative to this example is prime costs? What factor is prime cost missing in the equation? 24 4. When going through the menu analysis, what details stand out as concerns about the different menu options? What changed if any would you make to the 25 different menu options, pricing and recipes? 26 5. Lastly, review the profit and loss statement. If you needed to make changes, what area of the statement stands 27 out as a possible concern based on current figures? 28 29 30 31 32 33 34 35 36 37 38 Instructions Inventory Recipe Costs Menu Analysis-Eng June recap Profit and Loss +M17 X V fc A C D E F G H J K L M N 1 case = 4 bags of Hot Dog Bea hot Buns each 100% dog buns $ 16.00 5 cases Cheese, Cheddar, 7 Shred 100% 10 # CS S 32.00 2 Cases Coney 8 Sauce fl oz 75% 10# S 15.00 8 Cases 9 Onions OZ 70% 50# 22.00 2 Cases 1 case =4 10 Mustard fl oz 100% gallons 17.00 1 Case 11 End of the month inventory 12 13 Inventory value as of May 31, 2019: $600 14 15 Food Cost Report June 30th 16 Opening Inventory 17 Purchases $1,800.00 18 Transfers in $ 100.00 19 Closing inventory 20 Transfers out 100.00 21 Employee Meals 200.00 22 Promos $ 300.00 also added to marketing and promo on income statement 23 Cost of sales (Cogs) 24 25 26 27 28 29 30 31 32 33