Please help to answer the yellow highlighted areas and be sure to use excel formulas for the answer $($ex: $=$B$3/$B$2)$

Data Used:

Average hotel room price:

Monthly Fixed Costs:

Variable Costs per room:

Contribution Margin per Unit:

Contribution Margin Ratio:

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.

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:

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:

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.

Determine the Break$-$Even Point: Use the contribution margin method to calculate the break even point.

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