Business decision model/probability question, please answer with detailed procedures, excel and calculations. Need explanations. Will rate helpful.

Question 18 (2 points) The Nabaco Nation Singles Resort ("The Singles") offers outdoor activities, nightlife, and a gambling casino. The Singles has 400 rooms, all outfitted for single occupancy, usually renting for $120 per night. Due to high demand and proximity to a major interstate highway, The Singles does not accept advance reservations. Each day, vacant rooms are allocated to new guests, who either arrive or call on that day, on a First-Come-First-Served (FCFS) basis. The daily demand for rooms by new guests can be approximated by a Poisson random variable with a mean of 225. In addition to the room rent, each guest spends an average of $60, with standard deviation $40, on outdoor and entertainment activities during each day's stay in the resort. Every morning, each guest has a 60% chance of "checking out" and leaving the resort, independently of all other guests and how many days he/she has already spent in the resort. The Nabaco tribal council wants to evaluate a proposal to offer a special one-day "casino guest" package: each morning, The Singles will set aside n of its vacant rooms (or all vacant rooms, if the number of vacant rooms is less than n). These set- aside casino package rooms will then be offered at a reduced nightly rent of $75 to newly arriving/calling persons on a FCFS basis, until either all the set-aside rooms are taken, or the reception desk closes for the day, whichever occurs first. Guests accepting the casino package must commit to gamble for at least 3 hours during their first day at the resort. The council estimates that each guest has a 45% probability of being willing to accept the casino package. They also believe that each guest committing to the casino package will spend an average of $160, with a standard deviation of $100, on outdoor and entertainment activities during their first day at the resort (an amount larger than for regular guests, due to the required time in the casino). After their first night, casino package guests have checkout probabilities and spending characteristics identical to other guests, and pay full room rent for additional days they stay. New arrivals unwilling to accept the casino package are placed in the rooms that have not been set aside for the package, as long as any are available, and pay the full, usual $120 rent for the first night. Non-package guests are never placed in the set-aside rooms. Using the Excel/YASAI model printed below, the council has decided to evaluate the possibilities of trying to set aside either n = 40, 60, 80, 100, 120, 140, or 160 rooms for the casino package. The spreadsheet simulates a 100-day period (the rows for days 6-94 are hidden). Since The Singles is usually full or nearly full, the simulation starts by assuming that all rooms are occupied. The council is trying to determine which scenario will maximize average daily total revenues, and also wants to know the average number of unoccupied set-aside rooms. A B C D E F G H J Probability of leaving each day 60% Total number of rooms 400 2 Probability of accepting casino package 45% Mean demand 225 3 4 Casino Regular 5 Room rate per night $ 75 $ 120 6 Non-room mean spending $ 160 $ 60 7 SD of non-room mean spending $ 100 $ 40 8 9 Target number n of set-aside rooms 10 100 40 60 80 100 120 140 160 11 12 Guests Staying: Revenue From: 13 Number Not Set-aside Interested Casino Other Set-aside Casino Other 14 Day Leaving Rooms Demand in Casino Package Guests Vacant Guests Guests 15 0 100 300 16 161 100 242 116 100 300 $ 23,027 53,705 17 2 181 100 250 100 100 300 $ 21,707 54,036 18 3 151 100 229 99 99 281 $ 22,947 49,429 19 .A 146 100 237 103 100 283 0 $ 23,856 51, 106 20 5 157 100 215 97 97 275 3 $ 22,880 50,279 110 95 148 100 216 100 100 264 0 $ 23,715 48,011 111 96 145 100 208 89 89 264 11 $ 20,935 47,355 112 97 135 100 204 97 97 242 3 $ 22,779 44, 140 113 98 138 100 242 113 100 280 O $ 23,365 52, 167 114 99 152 100 251 119 100 300 0 $ 23,260 53,824 115 100 158 100 230 92 92 296 8 $ 21,085 $ 52, 190 116 Average Daily Revenue $ 22,665 $ 50,461 117 Average Daily Total Revenue |$ 73,126 118 Average Number of vacant set-aside rooms 3.67