use solver in excel and show formulas please!!
The manager of an opera theater is concerned about overbooking one of his upcoming productions. The theater has 300 general admission seats. General admission seats are not reserved, but ticket holders can fill the seats as they arrive. Sometimes there are empty seats at a performance either due to insufficient sales or ticket holders who do not show up for the performance. Tickets cost $50 per seat. Historical records demonstrate that demand for tickets is normally distributed with a mean of 325 tickets and a standard deviation of 25 tickets. Historical records also demonstrate that between 6% and 12% (uniformly distributed) of ticket holders do not show up. (In ycy' model use integers for all demands and for the number of ticket holders who do not show ung) There are no refunds for those who do not show up. The number of tickets sold for each performance will be the smaller of the number of tickets made avaifable for sale or the demand for tickets. If the manager overbooks and more than 300 individuals show up with general admission tickets, those unable to be accommodated with a general admission seat for that performance will be given guaranteed reservations and VIP seats to an upcoming show of their choice. A VIP seat is simply a regular general admission seat within the theater that the manager will mark as "reserved" for a previously bumped customer. Bumped ticket holders do not receive a refund on their ticket, but they do effectively decrease the number of tickets that are available for sale for upcoming shows. For simplicity, assume bumped ticket holders will be accommodated at the next evening's show. For example, if the policy is to make 310 tickets available for each show and there were 20 ticket holders bumped from 'Tuesday night's performance, then there are only 31020=290 tickets available for sale for Wednesday night's performance. For purposes of calculating no-shows for Wednesday night's performance, the number of ticket holders would be 20 + the number of tickets sold. I.e., if only 285 of the 290 tickets were sold there may be 285+20=305 ticket holders show up for seats on Wednesday night (if there were no no-shows Wednesday). Moreover, to keep his customers happy, the manager gives each individual who is bumped $20 worth of gift certificates. Part A) (25 points) Refer to the information above, The manager wishes to explore the impact on profitability if 310 tickets are made available for each show. Simulate this ticket policy for a month ( 30 days). Use Excel with N=200 replications. What is the average monthly ticket income (less any $20 gift certificates given to bumped customers). Part B) (25 points) Refer to the information above. Suppose that the manager wishes to examine the effect of different ticket availability levels on the average monthly total ticket income (less gift certificates). In specific, the manager wishes to consider ticket availability levels of 300 , 310,320,330, and 340. Use Excel and construct a Data Table to find the reservation level that maximizes average monthly profit, Use N200 replications. What is your recommendation? Why
