Using the attached "Summary Statement of Income" as your format, use Excel to create a one-year profit projection for a restaurant of your choosing. It must be done as an Excel Worksheet; when you hand it in. I will be checking your formulas using a built-in auditing tool, so please ensure you're doing the work in Excel using the formulas we've discussed. You must: a. Create a food and beverage sales projection for the year b. Project what each of your expenses will be in both dollars and percentages c. Determine the Cost of Sales in both dollars and percentages d. Determine the Gross Profit in both dollars and percentages e. Determine the total of Operating Expenses in both dollars and percentages f. Determine the Operating Income in both dollars and percentages g. Determine the Net Income in both dollars and percentages h. Show all percentages to two decimal places Your guidelines are: 1. The yearly sales will be $2,300,675 2. Food and Beverage Cost should be between 33.00% and 35.00% 3. Staff Wages should be between 20.00% and 21.00 % 4. Management Salaries should be between 4.00 and 5.00% 5. Employee Benefits should be between 2.00% and 3.00% 6. Operating supplies should be between .80% and 1.00% 7. Music and Entertainment should be below .50% 8. Advertising and Marketing should be between 2.00% and 3.00% 9. Utilities should be between 2.00% and 3.00% 10. Professional fees should be approximately $30,000 per year 11. Repairs and Maintenance should be between .80% and 1.00% 12. Occupancy Cost should be $120,000 per year 13. Depreciation should be $85,000 per year 14. Interest Expense should be $55,000 per year Keep in mind: Sales are always 100.00% Always calculate your percentages to two decimal places, rounding up or down as needed As we practiced in class, when you know the dollar figure, you get the percentage by dividing the dollar expense by the sales dollar total. As we practiced in class, when you know the percentage, you get the dollar total by multiplying the percentage by the sales dollar total. Summary Statement of Income Name of Restaurant: Period Covered by Statement: Amounts Percentages SALES: $ % Food and Beverage _____________ ____________ COST OF SALES: Food and Beverage _____________ ____________ Gross Profit _____________ ____________ OPERATING EXPENSES: Staff Wages _____________ ____________ Management Salaries _____________ ____________ Employee Benefits _____________ ____________ Operating Supplies _____________ ____________ Music and Entertainment _____________ ____________ Advertising and Marketing _____________ ____________ Utility Services _____________ ____________ Professional Fees _____________ ____________ Repairs and Maintenance _____________ ____________ Occupancy Costs (Rent/Mortgage) _____________ ____________ Depreciation _____________ ____________ Interest Expense _____________ ____________ Total Operating Expenses _____________ ____________ NET INCOME (Gross Profit minus Total Operating Expenses) _____________ _