Data Used: Average hotel room price: $ 450 Monthly Fixed Costs: Variable Costs per room: Contribution...

Transcribed Image Text:

Data Used: Average hotel room price: $ 450 Monthly Fixed Costs: Variable Costs per room: Contribution Margin per Unit: Contribution Margin Ratio: Break-Even Analysis Break-even point in rooms Break-even point in dollars Sensitivity Analysis: Estimated Monthly Profit if Number of Rooms Rented is: Prompt: 0 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. 500 1,000 1,500 2,000 2,500 3,000 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: 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. 2. Determine the Break-Even Point: Use the contribution margin method to calculate the break even point. 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 Data Used: Average hotel room price: $ 450 Monthly Fixed Costs: Variable Costs per room: Contribution Margin per Unit: Contribution Margin Ratio: Break-Even Analysis Break-even point in rooms Break-even point in dollars Sensitivity Analysis: Estimated Monthly Profit if Number of Rooms Rented is: Prompt: 0 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. 500 1,000 1,500 2,000 2,500 3,000 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: 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. 2. Determine the Break-Even Point: Use the contribution margin method to calculate the break even point. 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

Answer rating: 100% (QA)

Preliminary Data Average hotel room priceB1B1 BreakEven Analysis Brea... View the full answer