May you also provide an overview of the formulas used in the excel sheet.

Thank you so much!!

1,203, UT. TULLIITOLL 439.00 14,857.00 4,080.00 53,599.00 930.00 104.001 1,366.00 0.00 446.001 2,678.00 OPERATING EXPENSE Contract/Allocated Labor Replacement Costs Snacks & Mints Laundry/Linen Uniforms Printing Licenses Cash Over/Short Fresh Flowers Equipment Rental 78,499.00 TOTAL OPERATING EXP. 0.00 16,959.00 3,400.00 REPAIR & MAINT. R&M Miscellaneous R&M Building 20,359.00 TOTAL REPAIR & MAINT. 0.00 UC TUIALA TUULL 83 84 OPERATING EXPENSE 85 Contract/Allocated Labor 86 Replacement Costs 87 Snacks & Mints 88 Laundry/Linen 89 Uniforms 90 Printing 91 Licenses 92 Cash Over/Short 93 Fresh Flowers 94 Equipment Rental 95 96 TOTAL OPERATING EXP. 97 98 REPAIR & MAINT. 99 R&M Miscellaneous 100 R&M Building 101 102 TOTAL REPAIR & MAINT. 103 104 OPERATING SUPPLIES 105 Operating Supplies-General 106 Paper Goods 107 Cleaning Supplies 108 109 TOTAL OPERATING SUPPLIES 110 111 OTHER EXPENSES 112 Training & Education 113 Memberships & Dues 114 Advertising-Employment 115 Miscellaneous Expenses 116 Member Relations 117 Employee Meals 118 119 TOTAL OTHER 120 121 MUSIC & DECORATIONS 122 Music & Entertainment 123 Decorations 6,767.00 20,071.00 12,655.00 OPERATING SUPPLIES Operating Supplies-General Paper Goods Cleaning Supplies 39,493.00 TOTAL OPERATING SUPPLIES 789.00 1,299.00 1,378.00 2,687.00 15,620.00 26,949.00 OTHER EXPENSES Training & Education Memberships & Dues Advertising-Employment Miscellaneous Expenses Member Relations Employee Meals 48,722.00 TOTAL OTHER 0.00 MUSIC & DECORATIONS Music & Entertainment 14,983.00 4,238.00 124 19,221.00 TOTAL MUSIC/DECOR 0.00 125 TOTAL MUSIC/DECOR 126 127 TOTAL EXPENSES 1,409,998.00 TOTAL EXPENSES 0.00 128 17,227.00 NET INCOME(LOSS) 000 129 NET INCOME(LOSS) 130 131 132 BUDGET OVERVIEW For this assignment, you are the Food & Beverage Director of a private resort. One of your jobs is to plan the annual budget for the Food & Beverage Department. Last year your resort made $17,227 in profit in food & beverage. Attached with this assignment is a copy of the actual profit/loss (P&L) statement from 2019 and a work sheet for the 2020 season. The general manager has provided you with some instructions for the current budget: 1) Plan for a 3% increase in all food revenue Plan for a 1% increase in all beverage revenue Budget for exactly a 33.0000% food cost: Note - you will have to adjust your cost of food sold numbers to attain this percentage - (food purchases) Budget for exactly a 23.0000% beverage cost: Note - you will have to adjust your cost of beverage sold numbers to attain this percentage - (liquor purchases) Due to the expected increase in business, increase the laundry/linen costs by 3% Plan for a 4% increase in salaries & wages for service payroll for raises Plan for a 4% increase in salaries & wages for support payroll for raises Plan for a 4% increase in salaries & wages for kitchen payroll for raises Plan for a 20% increase in health benefits for service staff Plan for a 20% increase in health benefits for support staff Plan for a 20% increase in health benefits for kitchen staff Plans are underway to remodel part of the kitchen so budget replacement costs to a set $20,000 for the purchase of new kitchen equipment Also, due to the renovation, adjust repair & maintenance (R&M) building costs to a set $7,500 Keep all other numbers exactly as they were from last year Although most budgets round to the nearest dollar, for the purposes of this assignment, do not round the numbers up or down 11) 12) 13) With all of these changes to next year's budget, determine if your department will be at a profit or loss for the 2020 calendar year. Nmno 2019 2020 F&B Statement of Activities FOOD & BEVERAGE DEPARTMENT ACTUAL REVENUE FOOD REVENUE 7 Dining Room-Food 8 Grill Room Lunch-Food 9 Grill Room Dinner-Food 10 Member Parties-Food 11 Outside Parties-Food Proposed F&B Budget FOOD & BEVERAGE DEPARTMENT BUDGET REVENUE FOOD REVENUE Dining Room-Food Grill Room Lunch-Food Grill Room Dinner-Food Member Parties-Food Outside Parties-Food 459,164.00 260,072.00 370,526.001 98,632.00 202,154.00 12 13 TOTAL FOOD REVENUE 1,390,548.00 TOTAL FOOD REVENUE 0.00 14 15 BEVERAGE REVENUE 16 Dining Room-Beverage 17 Grill Room Lunch-Beverage 18 Grill Room Dinner-Beverage 19 Member Parties-Beverage 20 Outside Parties-Beverage 94,671.00 38,111.00 124,621.00 15,325.00 23,512.00 BEVERAGE REVENUE Dining Room-Beverage Grill Room Lunch-Beverage Grill Room Dinner-Beverage Member Parties-Beverage Outside Parties-Beverage 21 296,240.00 TOTAL BEVERAGE REVENUE 0.00 22 TOTAL BEVERAGE REVENUE 23 24 TOTAL REVENUE 1,686,788.00 TOTAL REVENUE 0.00 26 COST OF GOODS SOLD 27 Cost of Food Sold 28 Employee Meal Credit 29 Total Cost of Food Sold 510,121.00 (40,242.00 469,879.00 COST OF GOODS SOLD Cost of Food Sold Employee Meal Credit Total Cost of Food Sold 0.00 30 70,960.00 (2,400.00 68,560.00 Cost of Beverage Sold Employee Meal Credit Total Cost of Beverage Sold 0.00 538,439.00 TOTAL COST OF GOODS SOLD 0.00 31 Cost of Beverage Sold 32 Employee Meal Credit 33 Total Cost of Beverage Sold 34 35 TOTAL COST OF GOODS SOLD 36 37 Food C.O.G.S. % 38 Beverage C.O.G.S. % 39 40 Gross Profit 41 42 OTHER INCOME 43 Unused Food Minimum 44 Service Charge 33.8% 23.1% Food C.O.G.S. % Beverage C.O.G.S. % #DIV/0! #DIV/0! 1.148.349.00 Gross Profit 0.00 3,524.00 275,352.00 OTHER INCOME Unused Food Minimum Service Charge 45 278,876.00 TOTAL OTHER INCOME 0.00 46 TOTAL OTHER INCOME 47 48 Total Gross Profit and Other Income 1,427,225.00 Total Gross Profit and Other Income 0.00 49 50 EXPENSES 51 PAYROLL-SERVICE 52 Salaries & Wages 53 Overtime Pay 54 Vacation Pay 55 Holiday/Bonus Pay 56 Commissions 57 Damll Tayar EXPENSES PAYROLL-SERVICE 228,057.00 Salaries & Wages 3,465.00 Overtime Pay 18,718.00 Vacation Pay 1,040.00 Holiday/Bonus Pay 232,822.00 Commissions SS 269 on Dull Tayar 232,822.00 55,368.00 37,609.00 Commissions Payroll Taxes Health Benefits 577,079.00 TOTAL SERVICE PAYROLL 0.00 56 Commissions 57 Payroll Taxes 58 Health Benefits 59 60 TOTAL SERVICE PAYROLL 61 62 PAYROLL-SUPPORT 63 Salaries & Wages-Support 64 Overtime Pay-Support 65 Vacation Pay-Support 66 Sick Pay-Support 67 Payroll Taxes-Support 68 Health Benefits-Support 69 70 TOTAL SUPPORT PAYROLL 102,826.00 0.00 3,307.00 0.00 12,746.00 14,814.00 PAYROLL-SUPPORT Salaries & Wages-Support Overtime Pay-Support Vacation Pay-Support Sick Pay-Support Payroll Taxes-Support Health Benefits-Support 133,693.00 TOTAL SUPPORT PAYROLL 71 376,806.00 10,449.00 13,261.00 304.00 45,445.00 46,667.00 PAYROLL-KITCHEN Salaries & Wages Kitchen Overtime Pay-Kitchen Vacation Pay-Kitchen Sick Pay-Kitchen Payroll Taxes Kitchen Health Benefits Kitchen 492,932.00 TOTAL KITCHEN PAYROLL 1,203,704.00 TOTAL PAYROLL 0.00 72 PAYROLL-KITCHEN 73 Salaries & Wages Kitchen 74 Overtime Pay-Kitchen 75 Vacation Pay-Kitchen 76 Sick Pay-Kitchen 77 Payroll Taxes Kitchen 78 Health Benefits Kitchen 79 80 TOTAL KITCHEN PAYROLL 81 82 TOTAL PAYROLL 83 84 OPERATING EXPENSE 85 Contract/Allocated Labor 86 Replacement Costs 87 Snacks & Mints 88 Laundry/Linen 89 Uniforms 90 Printing 91 Licenses 92 Cash Over/Short 93 Fresh Flowers 94 Equipment Rental 95 96 TOTAL OPERATING EXP. 97 98 REPAIR & MAINT. 99 R&M Miscellaneous 100 R&M Building 101 102 TOTAL REPAIR & MAINT. 103 104 OPERATING SUPPLIES 105 Operating Supplies-General 106 Paper Goods 107 Cleaning Supplies 108 109 TOTAL OPERATING SUPPLIES 110 111 OTHER EXPENSES 112 Training & Education 439.00 14,857.00 4,080.00 53,599.00 930.00 104.00 1,366.00 0.001 446.00 2,678.00 OPERATING EXPENSE Contract/Allocated Labor Replacement Costs Snacks & Mints Laundry/Linen Uniforms Printing Licenses Cash Over/Short Fresh Flowers Equipment Rental 78,499.00 TOTAL OPERATING EXP. 16,959.00 3,400.00 REPAIR & MAINT. R&M Miscellaneous R&M Building 20,359.00 TOTAL REPAIR & MAINT. 6,767.00 20,071.00 12,655.00 OPERATING SUPPLIES Operating Supplies-General Paper Goods Cleaning Supplies 39,493.00 TOTAL OPERATING SUPPLIES 0.00 OTHER EXPENSES Training & Education 789.00 789.00 1,299.00 1,378.00 2,687.00 15,620.00 26,949.00 OTHER EXPENSES Training & Education Memberships & Dues Advertising-Employment Miscellaneous Expenses Member Relations Employee Meals 48,722.00 TOTAL OTHER 0.00 IIT OTHER EXPENSES 112 Training & Education 113 Memberships & Dues 114 Advertising-Employment 115 Miscellaneous Expenses 116 Member Relations 117 Employee Meals 118 119 TOTAL OTHER 120 121 MUSIC & DECORATIONS 122 Music & Entertainment 123 Decorations 124 125 TOTAL MUSIC/DECOR 126 127 TOTAL EXPENSES 128 129 NET INCOME(LOSS) 130 131 MUSIC & DECORATIONS 14,983.00] Music & Entertainment 4,238.00 Decorations 19,221.00 TOTAL MUSIC/DECOR 0.00 1,409,998.00 TOTAL EXPENSES 0.00 17,227.00 NET INCOME(LOSS) 0.00