Problems: There are two budget problems to complete. Ther first is to calculate amounts to use in budgeting based on 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 600 room Flagstaff hotel for the first six months of the year. Room Revenue 10,420,000 Transient Rooms Sold Group Rooms Sold Total Rooms Sold 77,360 42.640 120,000 Operating Expenses Linen 1 Cleaning Supplies Guest Supplies 23 Outside Services Laundry Concierge Expense Office Supplies $37.770.00 $28,640.00 $112,500.00 $28,590.00 $94,900.00 $70,000.00 $22,200.00 Reservation Expense Reservation Dept. $150,000.00 Fixed Reservation Center Cost $180,000.00 Variable Reservation Center Cost $232,080.00 1 Total Reservation Expense $562,080.00 All Other Expense $120,000.00 3 Total Operating Expenses $1,076,680.00 5 6 37 Problem 1 38 39 Calculate each one of the line items. They will be used to prepare the annual budget for next year. H0 Round the average cost per month to two decimals, round the cost per occupied room to two decimals 1 (cents), and round the percentage of sales to two decimals (change the cell to percentage format). 12 Answer: 43 1. Linen-Calculate the average cost per month 44 2. Cleaning Supplies--Calculate the average cost per occupied room. 45 3. Guest Supplies--Calculate the average cost per occupied room. 46 4. Outside Services Calculate the average cost per month. 47 5. Laundry--Calculate at the average cost per month 48 6. Concierge Level Expense-Calculate as a percentage of total room sales. 49 7. Office Supplies - Calculate as a percentage of total room sales. 50 B. Reservation Cost $25,000.00 per month 51 a. Reservation department cost at $25,000 per month. $30,000.00 per month 52 b. Fixed expense per month at $50 per total number of rooms in hotel, (Hint: Number of transient rooms sold 50x56] c. Variable expense at $6 per reservation. Calculate the reservations as 50% for 4 transient rooms sold. 59. All other expense at $20,000 per month. 6 $20,000.00 per month 58 Problem 59 60 Prepare the annual operating expense budget for the next year using the formulas 61 from problem 1 and the following assumptions. Remeber there are 12 months in a 62 fiscal year. Round dollar amounts to two decimals. 63 24,540,000 224,000 36.000 260,000 65 66 Rooms Revenue 67 08 Transient Rooms Sold 69 Group Rooms Sold 70 Total Rooms Sold 71 72 73 Operating Expenses 74 Linen 75 Cleaning Supplies 76 Guest Supplies Outside Services 70 Laundry Room Revenge 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%*56) Total Operating Expenses Sheet1