Fill everything out in the excel sheet, the other documents are supporting documents to help you fill it out. Pick any restaurant or business in NAPA for information. Rubric is attached, please follow it.
Part 1 Check Average Student Name Date Restaurant Food Average PricePercent OrdredQuantity orderedTotal Appetizer Entrée Dessert Food Totals Beverage Average PricePercent OrdredQuantity orderedTotal Wine Beer Spirits Beverage Totals Total Check Average To receive full credit, you must complete all cells, use formulas, and link worksheets together whenever possible. All cells must be formatted corectly. Part 2 Cover Count Original P&LModel AModel BModel C Day of the WeekCoversDay of the WeekCoversDay of the WeekCoversDay of the WeekCovers SundaySundaySundaySunday MondayMondayMondayMonday TuesdayTuesdayTuesdayTuesday WednesdayWednesdayWednesdayWednesday ThursdayThursdayThursdayThursday FridayFridayFridayFriday SaturdaySaturdaySaturdaySaturday Weekly CoversWeekly CoversWeekly CoversWeekly Covers 28 day (4 weeks) covers28 day (4 weeks) covers28 day (4 weeks) covers28 day (4 weeks) covers Food Check AverageFood Check AverageFood Check AverageFood Check Average Wine Check AverageWine Check AverageWine Check AverageWine Check Average Beer Check AverageBeer Check AverageBeer Check AverageBeer Check Average Spirit Check AverageSpirit Check AverageSpirit Check AverageSpirit Check Average 28 day food sales28 day food sales28 day food sales28 day food sales 28 day wine sales28 day wine sales28 day wine sales28 day wine sales 28 day beer sales28 day beer sales28 day beer sales28 day beer sales 28 day spirits sales28 day spirits sales28 day spirits sales28 day spirits sales Total sales per periodTotal sales per periodTotal sales per periodTotal sales per period Annual SalesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagoriesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagoriesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: sum all sales catagories Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagories Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Part 3 P&L Sales$% Food Wine Liquor Beer Total Sales Cost of Sales Food Wine Liquor Beer Total Cost of Sales Gross Profit Labor Salaries and Wages Employee Benefits Total Labor Costs Prime Cost Other Controllable Expenses Direct Operating Expenses Music & Entertainment Repairs & Maintenance Marketing Administrative and General Utilities Total Other Controllable Expenses Income before Fixed Costs Fixed Costs Lease Other fixed costs Total Fixed Costs: Net Profit Before Income Tax (loss): Part 4 Model A Copy the completed P&L to this worksheet and increase your wine check average by 10% Write a narrative describing the financial impact of this change. Please write this in a text box on this sheet. Part 4 Model B Copy the completed P&L to this worksheet and increase your cover count by 5 people each day Write a narrative describing the financial impact of this change. Part 4 Model C Copy the completed P&L to this worksheet but close for service on Mondays Write a narrative describing the financial impact of this change. Possible Points Student Points Part 1: Average Check (20pts) Accurate data 10 Accurate formulas 7 Correct formatting 3 Part 2: Revenue Projections (20 pts) Accurate data 10 Accurate formulas 5 Correct formatting 2 Worksheets linked 3 Part 3: Income Statement (30 pts) Accurate data 10 Accurate formulas 10 Correct formatting 5 Worksheets linked 5 Part 4: What if Models (30 pts) A: Check average Accurate data 5 Accurate formulas 3 Description 2 B: Increase cover count Accurate data 5 Accurate formulas 3 Description 2 C: Close Monday Accurate data 5 Accurate formulas 3 Description 2 Total score 100 0 Comments BUSM 250 Assignment # 1 Select a restaurant in Napa or Sonoma County. The business must be a full-service, a la carte restaurant with a full bar that is open for dinner. To receive full credit, you must use formulas in excel and link worksheets together whenever possible Part 1: Average Check: 1. Obtain an online or hardcopy of the restaurant’s current menu, and forecast the: Food Sales per person 1. Average Appetizer PricePredict the percentage of guests that order one (45% to 75%) 2. Average Entrée Price Predict the percentage of guests that order one (75% to 95%) 3. Average Dessert PricePredict the percentage of guests that order one (15% to 65%) Beverage Sales per person 4. Avg Beer Bottle PricePredict the guest % that order thisHow many p/person? (1% to 5%)(1 to 2) 5. Avg Cocktail PricePredict the guest % that order thisHow many p/person? (5% to 15%)(1 to 2) 6. Avg Wine-B-T-Glass $Predict the guest % that order thisHow many p/person? (28% to 57%)(1 to 3) 2. Based on your assumptions, what would you expect the average check to be? Part 2: Revenue Projections Assume the following covers served for every: Sunday – 100Monday – 65Tuesday – 65 Wednesday –75Thursdays – 90 Fridays – 150 Saturdays – 175 a) Calculate the average sales per week. b) Calculate Food Sales and Beverage sales for a 4 week period based on above projections c) Calculate the projected sales per year Part 3: Create an Income statement for a 28-day period incorporating your revenue assumptions (above) and the costs listed below. a) Assume your food cost is 28% b) Assume your wine cost is 30% c) Assume your spirit cost is 18% d) Assume your beer cost is 22% e) Assume payroll is 34% of sales f) Assume benefits are 7% of sales g) Assume your direct operating expenses are 7% of sales h) Admin and general are 8% of sales i) Music is $150 a month j) Repairs and maintenance are 2% of sales k) Marketing is 4% of sales l) Utilities are 3% of sales m) Assume rent is $7,500 per period n) Other fixed costs are $3000 per period Part 4: What if Scenario: Using the model created in Part 4, what happens to revenue and net profit before income taxes when a) You increase your wine check average by 10% i. (keep the same labor cost-in dollars-as Part 3) b) You increase your cover count by 5 people daily i. (keep the same labor cost-in dollars-as Part 3) c) You close on Mondays Complete separate worksheets for each of the models above. Write a narrative describing the financial impact of each change. Part 1 Check Average Student Name Date Restaurant Food Average PricePercent OrdredQuantity orderedTotal Appetizer Entrée Dessert Food Totals Beverage Average PricePercent OrdredQuantity orderedTotal Wine Beer Spirits Beverage Totals Total Check Average To receive full credit, you must complete all cells, use formulas, and link worksheets together whenever possible. All cells must be formatted corectly. Part 2 Cover Count Original P&LModel AModel BModel C Day of the WeekCoversDay of the WeekCoversDay of the WeekCoversDay of the WeekCovers SundaySundaySundaySunday MondayMondayMondayMonday TuesdayTuesdayTuesdayTuesday WednesdayWednesdayWednesdayWednesday ThursdayThursdayThursdayThursday FridayFridayFridayFriday SaturdaySaturdaySaturdaySaturday Weekly CoversWeekly CoversWeekly CoversWeekly Covers 28 day (4 weeks) covers28 day (4 weeks) covers28 day (4 weeks) covers28 day (4 weeks) covers Food Check AverageFood Check AverageFood Check AverageFood Check Average Wine Check AverageWine Check AverageWine Check AverageWine Check Average Beer Check AverageBeer Check AverageBeer Check AverageBeer Check Average Spirit Check AverageSpirit Check AverageSpirit Check AverageSpirit Check Average 28 day food sales28 day food sales28 day food sales28 day food sales 28 day wine sales28 day wine sales28 day wine sales28 day wine sales 28 day beer sales28 day beer sales28 day beer sales28 day beer sales 28 day spirits sales28 day spirits sales28 day spirits sales28 day spirits sales Total sales per periodTotal sales per periodTotal sales per periodTotal sales per period Annual SalesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagoriesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagoriesAnnual Sales Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: Get these numbers from previous worksheet Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: sum all sales catagories Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Dustin Rogge: Dustin Rogge: Remember we are using 13, 4 week financial periods Dustin Rogge: Dustin Rogge: sum all sales catagories Dustin Rogge: Dustin Rogge: Multiply 28 day cover count by each catagories' check average Part 3 P&L Sales$% Food Wine Liquor Beer Total Sales Cost of Sales Food Wine Liquor Beer Total Cost of Sales Gross Profit Labor Salaries and Wages Employee Benefits Total Labor Costs Prime Cost Other Controllable Expenses Direct Operating Expenses Music & Entertainment Repairs & Maintenance Marketing Administrative and General Utilities Total Other Controllable Expenses Income before Fixed Costs Fixed Costs Lease Other fixed costs Total Fixed Costs: Net Profit Before Income Tax (loss): Part 4 Model A Copy the completed P&L to this worksheet and increase your wine check average by 10% Write a narrative describing the financial impact of this change. Please write this in a text box on this sheet. Part 4 Model B Copy the completed P&L to this worksheet and increase your cover count by 5 people each day Write a narrative describing the financial impact of this change. Part 4 Model C Copy the completed P&L to this worksheet but close for service on Mondays Write a narrative describing the financial impact of this change.