need help resolving this on excel please

1 The Company WSpectacles is a firm that organizes concerts, festivals and spectacles in Paris. In particular, WSpectacles focuses on events for young people, e.g. rock concerts or heavy-metal festivals. The firm has an agreement with The Concert-Hall, a venue for spectacles that is available for WSpecatcles every Saturday. The Concert-Hall has a capacity for 1,000 attendance and does not have seats, so people have to stand during the spectacles. Such characteristic fits well to the requirements of WSpectacles, because most of the attendance prefers to stand during the shows. Mr. Keulls, the CEO of WSpectacles, implemented two actions in order to maximize the revenues of the firm. First, WSpectacles sells tickets by using only a single channel: the website of the firm. Tickets are posted on the website three months before the show is held (assume 90 days). For security reasons, tickets are not available the day of the concert. Second, there are two tickets category: Magic ticket: Unit price of 55 euros. With this ticket, the client accesses to The Concert Hall through priority queues (expected waiting time 7 minutes). W ticket: Unit price of 35 euros. With this ticket, the client accesses to The Concert-Hall through regular queues (expected waiting time 26 minutes). WSpectacles implemented the Magic and W tickets under the basis of a first-comefirst-served, i.e., tickets are sold according to the arrival time of the clients to the webpage. After implementing those policies, Mr. Keulls is quite dissapointed on the result of his Demand Management. Indeed, he is wondering if having two tickets categories is a good strategy. In order to improve the situation of the firm, Mr. Keulls hired an external consultant to analyze the customer behavior. This external consultant concluded the following about the demand at the beginning of the booking period: The demand for Magic tickets can be assumed uniformly (discrete) distributed, where the extreme values of the distribution are 50 and 350. 2 The demand for W tickets can be assumed as normally distributed, where the expected value is 650 and the standard deviation is 200. Based on this new information about customers, Mr. Keulls aims to increase the revenues of the firm. 2 Discussion 1. Currently, selling two categories of tickets is not showing good results. How could you explain such results? (1 point) 2. After a detailed analysis, the firm has decided to work with the two category of prices, but without making differences about the waiting line. They do not expect major changes for the demand, because it seems customers are just price sensitive. How could you organize the demand arrivals in a natural way? (1 point) 3. For the beginning of the booking horizon, provide a detailed control plan for maximizing the revenues of WSpectacles? (4 points) 4. Mr. Keulls proposes a third type of category: the VIP ticket, whose price is 80 Euros. Mr. Keulls expects that this new ticket category will take a quarter of the customers of the Magic tickets. How can WSpectacles maximize the expected revenues under these new conditions? (5 points) 5. Mr. Keulls would like to estimate the revenues resulting if selling the tickets of the last concert were sold under both, the new pricing condition and the proposed control policy. Afterwards, he would like to compare the results with the revenues obtained under the first-come-first serve basis. What are the gains for WSpectacles? Hint: Use the observed demand during the booking period of the last concert. (4 points) 6. The externals consultant has provided a mores sophisticated demand forecast; the demand for the first 30 days of the booking horizon should be decreased by 20%, while the demand of the last 30 days of the booking horizon should be 3 increased by 20%. Implement a control policy for the data of the last concert and provide expected revenues. (5 points)