Chapter 9, Question 8 (Spreadsheet hint: Use the ROUNDUP function in the cells indicated 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: 175 Screaming Saloon Inn Number of rooms Days open per year Average number of rooms available per day Average daily rate Variable costs as % of sales Annual fixed costs Desired after-tax profit Tax rate Proposed new assistant manager cost 365 94% $92.00 43% $625,000 $875,000 34% $50,000.00 a. Big Tex has asked you to help him calculate the following: a. (Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP Percentage in order for the grid to calculate properly.) Per Unit (Room) Percentage SP VC CM Total fixed costs Desired after-tax profit Tax rate Before-tax profit Rooms available for sale per year b. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to b. 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. c. Rooms sold to achieve desired after-tax profit Rounded up = Occupancy % Sales dollars to achieve desired after-tax profit Big Tex deci to hire a new assistant manager to help him out around the hotel, and he will pay a total of $50,000 per year for the assistant manager. d. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to d. 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. e. Rooms sold to achieve desired after-tax profit Rounded up Occupancy % Sales dollars to achieve desired after-tax profit f. If occupancy percentage averages 70% after he hires the new assistant manager, what is his margin of safety? f. manager 70% 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. (Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP g. Percentage in order for the grid to calculate properly.) With proposed giftshop opening ADR increase Variable cost increase Fixed cost increase $ 1% 5% 24,000 Per Unit (Room) Percentage SP VC CM Total fixed costs h. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to breakeven if he opens the gift shop. h. 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. i. Rooms sold to achieve desired after-tax profit Rounded up = Occupancy % Sales dollars to achieve desired after-tax profit Chapter 9, Question 8 (Spreadsheet hint: Use the ROUNDUP function in the cells indicated 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: 175 Screaming Saloon Inn Number of rooms Days open per year Average number of rooms available per day Average daily rate Variable costs as % of sales Annual fixed costs Desired after-tax profit Tax rate Proposed new assistant manager cost 365 94% $92.00 43% $625,000 $875,000 34% $50,000.00 a. Big Tex has asked you to help him calculate the following: a. (Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP Percentage in order for the grid to calculate properly.) Per Unit (Room) Percentage SP VC CM Total fixed costs Desired after-tax profit Tax rate Before-tax profit Rooms available for sale per year b. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to b. 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. c. Rooms sold to achieve desired after-tax profit Rounded up = Occupancy % Sales dollars to achieve desired after-tax profit Big Tex deci to hire a new assistant manager to help him out around the hotel, and he will pay a total of $50,000 per year for the assistant manager. d. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to d. 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. e. Rooms sold to achieve desired after-tax profit Rounded up Occupancy % Sales dollars to achieve desired after-tax profit f. If occupancy percentage averages 70% after he hires the new assistant manager, what is his margin of safety? f. manager 70% 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. (Spreadsheet hint: Do not calculate SP Percentage. You must type in 100 for SP g. Percentage in order for the grid to calculate properly.) With proposed giftshop opening ADR increase Variable cost increase Fixed cost increase $ 1% 5% 24,000 Per Unit (Room) Percentage SP VC CM Total fixed costs h. Calculate the rooms sold, occupancy percentage, and sales dollars he will need to breakeven if he opens the gift shop. h. 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. i. Rooms sold to achieve desired after-tax profit Rounded up = Occupancy % Sales dollars to achieve desired after-tax profit