A C E J M N 0 P R S 5 T V w Problems There are two bucket problers to complete. Ther first is to calculate amounts to use in budgetine besed en actual information and formulas. The second is to prepare an annual budget based on that Information. Following is information for the Rooms Department for the cou room Flagstaff hotel for the first six months of the year 9 10,420.000 77,360 42,540 120,000 10 11 12 Room Revenue 13 14 Transient Rooms Sald 15 Group Rooms Sold 16 Total Rooms Seld 17 12 19 Operating Lxpenses 20 21 Cleaning Supplies 22 Guest Supplies 23 Outside Services 24 Laurdry 25 Concierge Expers 26 Orice Supplies 27 22 Reservation Dept. 29 Fixed Reservation Center Ons 30 Variable Reservation Center Cost Total Reservation Expense 32 All Other Expense 33 34 Total Operating Expenses 35 36 $37,770.00 $28,643.00 $212,500.00 $2,599.00 $94,900.00 $70,000.00 $27,200.00 Reservation Expense $150,000.00 $180,000.00 $232,080.00 $562,080.00 $120,000.00 $1,076,680.00 Problem 1 Calculate each one of the line items. They will be used to prepare the annual budget for next year. Round the average cost per month to two decimals, round the cost per occupied room to two decimals (cents), and round the percentage of sales to two decimals (change the cell to percentage format). Answer: 1. Linen-Calculate the average cost per month. 2. Cleaning Supplies-Calculate the average cost per occupied room. 3. Guest Supplies-Calculate the average cost per occupied room. 4. Outside Services Calculate the average cost per month. 5. Laundry-Calculate at the average cost per month. 6. Concierge Level Expense-Calculate as a percentage of total room sales. 7. Office SuppliesCalculate as a percentage of total room sales. 8. Reservation Cost a. Reservation department cost at $25,000 per month. b. Fixed expense per month at $50 per total number of rooms in hotel. c. Variable expense at $6 per reservation. Calculate the reservations as 50% for transient rooms sold. 9. All other expense at $20,000 per month. $25,000.00 per month $30,000.00 per month (Hint: Number of transient rooms sold * 50%*$6) $20,000.00 per month Problem 2 Prepare the annual operating expense budget for the next year using the formulas from problem 1 and the following assumptions. Remebmer there are 12 months in a fiscal year. Round dollar amounts to two decimals. Room Revenue 24,540,000 Transient Rooms Sold Group Rooms Sold Total Rooms Sold 224,000 36,000 260,000 Operating Expenses Linen Cleaning Supplies Guest Supplies Outside Services Laundry Concierge Expense Office Supplies Reservation Expense Reservation Dept. Fixed Reservation Center Cost Variable Reservation Center Cost Total Reservation Expense All Other Expense (Hint: Number of transient rooms sold * 50%*$6) Total Operating Expenses A C E J M N 0 P R S 5 T V w Problems There are two bucket problers to complete. Ther first is to calculate amounts to use in budgetine besed en actual information and formulas. The second is to prepare an annual budget based on that Information. Following is information for the Rooms Department for the cou room Flagstaff hotel for the first six months of the year 9 10,420.000 77,360 42,540 120,000 10 11 12 Room Revenue 13 14 Transient Rooms Sald 15 Group Rooms Sold 16 Total Rooms Seld 17 12 19 Operating Lxpenses 20 21 Cleaning Supplies 22 Guest Supplies 23 Outside Services 24 Laurdry 25 Concierge Expers 26 Orice Supplies 27 22 Reservation Dept. 29 Fixed Reservation Center Ons 30 Variable Reservation Center Cost Total Reservation Expense 32 All Other Expense 33 34 Total Operating Expenses 35 36 $37,770.00 $28,643.00 $212,500.00 $2,599.00 $94,900.00 $70,000.00 $27,200.00 Reservation Expense $150,000.00 $180,000.00 $232,080.00 $562,080.00 $120,000.00 $1,076,680.00 Problem 1 Calculate each one of the line items. They will be used to prepare the annual budget for next year. Round the average cost per month to two decimals, round the cost per occupied room to two decimals (cents), and round the percentage of sales to two decimals (change the cell to percentage format). Answer: 1. Linen-Calculate the average cost per month. 2. Cleaning Supplies-Calculate the average cost per occupied room. 3. Guest Supplies-Calculate the average cost per occupied room. 4. Outside Services Calculate the average cost per month. 5. Laundry-Calculate at the average cost per month. 6. Concierge Level Expense-Calculate as a percentage of total room sales. 7. Office SuppliesCalculate as a percentage of total room sales. 8. Reservation Cost a. Reservation department cost at $25,000 per month. b. Fixed expense per month at $50 per total number of rooms in hotel. c. Variable expense at $6 per reservation. Calculate the reservations as 50% for transient rooms sold. 9. All other expense at $20,000 per month. $25,000.00 per month $30,000.00 per month (Hint: Number of transient rooms sold * 50%*$6) $20,000.00 per month Problem 2 Prepare the annual operating expense budget for the next year using the formulas from problem 1 and the following assumptions. Remebmer there are 12 months in a fiscal year. Round dollar amounts to two decimals. Room Revenue 24,540,000 Transient Rooms Sold Group Rooms Sold Total Rooms Sold 224,000 36,000 260,000 Operating Expenses Linen Cleaning Supplies Guest Supplies Outside Services Laundry Concierge Expense Office Supplies Reservation Expense Reservation Dept. Fixed Reservation Center Cost Variable Reservation Center Cost Total Reservation Expense All Other Expense (Hint: Number of transient rooms sold * 50%*$6) Total Operating Expenses