8. Bonus: Big Tex is a true Texan. When he opened his hotel on the plains of West Texas, he named it the Screaming Saloon Inn. He now provides you with the following information about his property.

Screaming Saloon Inn

Number of rooms 200

Days open per year 365

Average number of rooms available per day 75%

Average daily rate $70.00

Variable costs as % of sales 40%

Annual fixed costs $604,800

Desired aftertax profit $800,000

Tax rate 36%

Proposed new Assistant Manager cost $40,000.00

(Spreadsheet hint: Use the ROUNDUP function in the cells indicated throughout the problem to round up rooms.)

a. Big Tex has asked you to help him calculate the following.

Per Unit (Room) Percentage

SP

VC

CM

Total fixed costs

Desired aftertax profit

Tax rate

Beforetax profit

Rooms available for sale per year

(Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP Percentage in order for the grid to calculate properly.)

b. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to breakeven.

Breakeven point in rooms sold

Occupancy %

Breakeven point in sales dollars

c. Calculate the rooms sold, occupancy percentage , and sales dollars he will need to achieve his desired profit.

Rooms sold to achieve desired aftertax profit Rounded up =

Occupancy %

Sales dollars to achieve desired aftertax profit

Big Tex decides to hire a new assistant manager to help him out around the hotel, and he will pay a total of $40,000 per year for the assistant manager.

d. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to breakeven after he hires the new assistant manager.

Total fixed costs

Breakeven point in rooms sold Rounded up =

Occupancy %

Breakeven point in sales dollars

e. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to achieve his desired profit after he hires the new assistant manager.

Rooms sold to achieve desired aftertax profit Rounded up =

Occupancy %

Sales dollars to achieve

desired aftertax profit

f. If occupancy percentage averages 70% after he hires the new assistant manager, what is his margin of safety?

Sales ($) Rooms

Projected Sales

Breakeven Sales

Margin of Safety

Big Tex wants his new assistant manager (the same one mentioned in part (d) above) to oversee a proposed hotel gift shop. The small gift shop will increase his ADR by 1%, his variable costs by 5%, and his fixed costs by $24,000.

g. Calculate the following to reflect the addition of the gift shop.

Per Unit (Room) Percentage

SP

VC

CM

Total fixed costs

(Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP Percentage in order for the grid to calculate properly.)

h. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to breakeven if he opens the gift shop.

Breakeven point in rooms sold Rounded up =

Occupancy %

Breakeven point in sales dollars

i. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to achieve his desired profit if he opens the gift shop.

Rooms sold to achieve desired aftertax profit Rounded up =

Occupancy %

Sales dollars to achieve desired aftertax profit

j. If Big Tex has an average occupancy of 55%, will he be able to hire his new assistant manager and still achieve his desired profit? Why or why not? Base your answer on occupancy percentage.

k.If Big Tex has an average occupancy of 55%, will he be able to hire his new assistant manager, open his proposed gift shop, and still achieve his desired profit? Why or why not? Base your answer on occupancy percentage

SHOW ALL WORKING !!