A B C D E F G H J K L 1 Cost Mortgage Payments 2 Cost Description The upscale hotel's building was acquired for $10 million, leading to monthly mortgage payments of $60,000. Behavior Dollar Amount Frequency Prompt: Fixed s 60.000.00 Month Employee Wages 3 The wages for hotel staff are structured to align with occupancy levels, typically averaging around $300,000 monthly, fluctuating based on the number of guests and required services. Variable S 300,000,00 Month Energy Efficiency Upgrades 4 The hotel invested $200,000 in energy efficiency upgrades to promote sustainability and reduce long- term operational costs. Depreciation on the upgrades is $1,200 per month. Fixed Food and Beverage Costs 5 The expenditure on food and beverages averages $25 per room per day, dependent on dining facility usage and menu selections. Variable S 25.00 Room Fumiture and Decor 6 Luxurious fumiture and decor for the hotel were acquired for $540,000, to be amortized over their expected 20-year lifetime. Fixed S 2,250.00 Month Guest Amenities 7 The cost for maintaining and providing guest amenities like the gym, pool, and spa services averages $15 per room per day, adjusted according usage. Variable Guest Services 8 The cost of providing concierge, room service, and other guest-specific services averages $20 per room occupied, directly related to guest demand and preferences. Fixed S 20.00 Room Health and Safety Compliance Compliance with health and safety standards involves an annual expenditure f $30,000. Fixed S 30,000.00 9 Housekeeping Supplies 10 Costs for essential housekeeping supplies, including cleaning materials, linens, and toiletries, average $10 per hotel room occupied. Mixed S 10.00 Room 11 Insurance Coverage To safeguard against various risks, the hotel has secured an insurance policy costing $42,000 annually. Laundry expenses, for both guest and hotel linens, average $5 per room occupied, varying with the volume of laundry processed. Fixed S 42,000.00 Fixed S 5.00 Room Welcome to the case study of our newly opened upscale hotel. This luxurious property boasts state-of-the-art amenities, elegant design, and a prime location. As part of understanding the financial aspects of hotel management, you are provided with a table listing various costs associated with the operation of this hotel. Instructions: 1. Determine the Cost's Behavior: For each cost listed in the table, use the dropdown menu to identify whether it is a fixed, variable, or mixed cost based on its nature and how it is expected to behave relative to hotel occupancy levels. 2. Enter the Dollar Value: Input the estimated dollar value for each cost. 3. Identify the Cost Frequency: Use the drop down menu to determine whether each cost is incurred per month or per room. 4. Formatting for Financial Analysis: Format the Dollar Amount column in a way that is easy to read and suitable for financial analysis. This could include aligning all figures to the right, ensuring consistent decimal places, and using commas for thousands. Laundry Services 12 Property Taxes 13 Supervisor Salaries 14 Utilities 15 Maintenance and Repairs 16 17 Maintenance and Repairs 18 19 20 21 22 Annually, the hotel is liable for property taxes amounting to $120,000, reflecting the property's premium location. The hotel employs a team of experienced supervisors at an annual total cost of $500,000, ensuring consistent, high-quality management across all hotel operations. The hotel's utility expenses average $50 per room occupied, covering electricity, water, and gas, and varying with actual usage in each room. The hotel maintains a salaried, dedicated maintenance team for routine upkeep, representing a consistent staffing expense. Costs for specific maintenance projects or extensive repairs vary based on their nature and frequency. Variable Fixed Mixed Rubric Hotel Operating Costs Maintenance and Repairs Detail Breakeven Analysis Letter to Management + NOTE: You may hard code numbers and use formulas in the "dollar amount" column. For all columns, if an answer does not apply, leave the cell blank. Ready B C37 X fx A B D E F G H H5 fx =MIN(D3:D22) 1 2 Data Used: A B 1 C D E F G J K L M N P 3 Average hotel room price: $ 450 4 Monthly Fixed Costs: Ticket Maintenance & Repair Rooms 2 Number Cost ($) Serviced 1 3 TKT1000 $ 9,116.56 14 4 TKT1001 $ 9,000.12 2 5 TKT1002 $ 7,378.41 6 TKT1003 $ 8,312.90 7 7 TKT1004 $ 7,933,84 8 8 TKT1005 $ 8,820.00 4 9 TKT1006 $ 8,109.20 3 10 TKT1007 $ 7,476.17 1 2 11 TKT1008 $ 8,659.47 3 12 TKT1009 $ 8,194.94 6 13 TKT1010 $ 8,944.78 2 14 TKT1011 $ 9,645.32 2 3 15 TKT1012 $ 8,407.28 2 16 TKT1013 $ 8,990.66 20 17 TKT1014 $ 7,873.11 7 18 TKT1015 $ 8,893.42 12 Step 2: Find average fixed costs per month 19 TKT1016 $ 7,932.43 2 Using the high point: $ 7,259.10 FC per month 20 TKT1017 $ 7,614,49 6 Using the low point: $ 7,259.10 FC per month 21 TKT1018 $ 8,544.18 5 22 TKT1019 $ 7,712.73 7 23 5 Variable Costs per room: Use the min and max formulas to find the high and low levels of activity in the table. Note: you have to determine whether to search the cost ($) column or the rooms serviced column. NOTE: only formulas and cell referencing should be used to calculate any value. Hardcoding numbers into any cell will result in a reduction of points. 6 Contribution Margin per Unit: NOTE: only formulas and cell referencing should be used to calculate any value. Hardcoding numbers into any cell will result in a reduction of points. 7 Contribution Margin Ratio: 8 1 Low Point 20 H 0 High Point 9 Break-Even Analysis 10 Break-even point in rooms 11 Break-even point in dollars 12 Highlight the 2 rows that have the high and low data points in the table. 13 Sensitivity Analysis: 14 Estimated Monthly Profit if Number of Rooms Rented is: 15 0 500 1,000 1,500 2,000 2,500 3,000 16 Use the high-low method to separate the variable and fixed costs: Step 1: Find variable cost per unit 17 18 $ 119.31 per room serviced 19 Prompt: 20 21 22 23 24 1. Calculate Preliminary Data: Use the sumif formula to determine your total fixed costs and variable costs per room. Use any formula you like to determine CM per unit and CM ratio. The hotel has determined the average sales price per hotel room. Management would now like to know how it is possible to earn profit in the first year of operations. You will prepare a cost-volume-profit analysis for them. Instructions: 25 26 2. Determine the Break-Even Point: Use the contribution margin method to calculate the break even point. 4 Go back to the Hotel Operating Costs tab and fill in the VC per unit and FC per month for Maintenance and repairs. You will need to use the final line on the table. One row for fixed costs. The other row for variable costs. 27 28 24 1 $ 7,378.41 25 20 $ 9,645.32 26 27 28 29 30 31 32 33 34 35 36 29 3. Determine Profit at Mutliple Levels of Occupancy: The hotel has 100 rooms. Therefore, the maximum monthly occupancy is somewhere around 3,000 rooms (depending on month). Show the amount of profit that will be made at various occupancies 30 31 32 $ 2,266.91 $ $ 119.31 $ 2,386.22 $ 7,259.10 119.31 S 7,259.10 33 34 35 36 37 38 Rubric Hotel Operating Costs Maintenance and Repairs Detail Breakeven Analysis Letter to Management + Rubric Hotel Operating Costs Maintenance and Repairs Detail Breakeven Analysis Letter to Manage Ready