The purpose of this Assignment is to practice creating and interpreting an expected payoff table, which will help a hotel determine how many of its rooms to offer at a discount, and how many to offer at full price. By forecasting the expected revenue at different levels, you can help the hotel maximize its revenue by minimizing its discount rate while maximizing its occupancy rate.
Assignment Instructions
Scenario: You have been approached by a hotel owner who would like you to forecast expected payoffs for him. The hotel has 100 rooms, and they would like to maximize their revenue by selling as many as possible at full price, and selling the rest to travel brokers, such as Travelocity and Expedia, at a discounted price. The market rate for hotel rooms in their area is $279 per night. They feel that they can sell rooms for as little as $229 per night and still profit. Selling rooms for less than that would cause a financial loss. Every room left empty for a night reduces revenue, so they would rather discount some rooms than have them empty, but their preference is to sell as many rooms as they can at full price. Your task is to help the hotel owner find the best possible combination of full and discounted rooms in order to maximize hotel revenue. You will do this in Excel. Download the ExpectedPayoff.xlsx file. Tables for Expected Payoff and Opportunity Loss have been prepared for your use in that file.
Complete the following steps:
Assignment Requirements
For this Assignment, you are not required to cite sources or format according to APA style. Your only submission will be the completed Excel spreadsheet. If you use sources in your written conclusion, you should cite them. Be sure that you have saved the spreadsheet with all of your formulas, color coding, and written explanation before uploading it to the Unit 4 Assignment dropbox.
Pricing Assumptions Table | |||||||||||||||||
Number of Hotel Rooms to Sell | |||||||||||||||||
Full Price for a Room | |||||||||||||||||
Discount Price for a Room | |||||||||||||||||
Expected Payoff Table | |||||||||||||||||
Conclusion | |||||||||||||||||
Likelihood → | 1% | 4% | 7% | 9% | 11% | 15% | 20% | 15% | 11% | 5% | 2% | Expected Payoff Calculations | |||||
Rooms Sold | At Full Price → At a Discount ↓ | 50 | 55 | 60 | 65 | 70 | 75 | 80 | 85 | 90 | 95 | 100 | |||||
0 | |||||||||||||||||
5 | |||||||||||||||||
10 | |||||||||||||||||
15 | |||||||||||||||||
20 | |||||||||||||||||
25 | |||||||||||||||||
30 | |||||||||||||||||
35 | |||||||||||||||||
40 | |||||||||||||||||
45 | |||||||||||||||||
50 | |||||||||||||||||
Price of Perfect Information | |||||||||||||||||
Oppportunity Loss Table | |||||||||||||||||
Rooms Sold | At Full Price → At a Discount ↓ | 50 | 55 | 60 | 65 | 70 | 75 | 80 | 85 | 90 | 95 | 100 | Expected Opportunity Loss Calculations | ||||
0 | |||||||||||||||||
5 | |||||||||||||||||
10 | |||||||||||||||||
15 | |||||||||||||||||
20 | |||||||||||||||||
25 | |||||||||||||||||
30 | |||||||||||||||||
35 | |||||||||||||||||
40 | |||||||||||||||||
45 | |||||||||||||||||
50 |
IT528: Quantitative Risk Analysis Expected Payoff Worksheet &P