1. Jerry Dickson has been approached by the franchise sales representative of a major hotel chain. The sales representative is trying to interest Jerry in building one of the franchise brand's full-service hotels. The hotel will cost $8,000,000 to build and will consist of 200 rooms. Mortgage payments on the hotel will be $750,000 per year and other nonoperating expenses will be $250,000 per year. At an assumed 60% occupancy level, the rooma manager has calculated that payroll and related expenses and other expenses for rooms is $45 per room, and undistributed operating expenses related to operating the hotel are $1,000,000. The hotel is projected to make an operating income of $125,000 per year from the F&B department and $50,000 from all other non-rooms departments. Jerry is interested in the project if he can achieve a 12% return on the investment, assuming a 40% tax rate. a. Utilizing the Hubbart room rate formula, what is the room rate required for Jerry to meet his 12% investment target? b. Assume Jerry decided that, due to the potentially risky nature of this ventura, he required a 15% (not 12%) retum on his investment. What is the room rate required for Jerry to meet this more aggressive investment target? C. Assume the oucupancy rate estimated to be achieved by the franchise sales representative was 70% and this number was utilized to compute the Hubbart rate (rather than Jerry's more conservative 60%). What would the sales representative tel Jerry his ADR would have to be to achieve Jerry's 15% investment goals? d. Assume instead, that the food and beveraga operating income is astimated at $75,000 per year rather than $125,000. Using the 15% desired investment return and a 70% ocupancy rate, what new room rate would allow Jery to meet his investment goals? (Spreadsheet hint: Calculate question a. above first and type the answer for ADR in the cell provided at the bottom of the spreadsheet. For items b., c., and d., change only the numbers that are required in each question, and all the other numbers in the spreadsheet will change accordingly. Then, type the answers for ADR (as you go) in the cells provided at the bottom of the spreadsheet.) step 1. Calculate the hotel's target before-tax net income. Owner's investment ROI After-Tax Net Income After-Tax Net Income Tax Before-Tax Net Income Step 2. Calculate estimated nonoperating expenses. Mortgage Other Nonoperating Expenses Total Nonoperating Expenses Step 3. Calculate estimated undistributed operating expenses. Undistributed Operating Expenses Step 4. Calculate estimated operated departments income excluding rooms. F&B Operating Income Other Non-Rooms Income Total Non-Rocms Operating Income Step 5. Calculate the operated department income for rooms. Before-Tax Net Income Nonoperating Expenses Undistributed Operating Expenses Total Non-Roorms Operating Income Rooms Operated Department Income Step 6. Calculate the estimated rooms department revenues based on estimated occupancy. Rooms Days in Pericd Occupancy 45 Total Rocms