EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:REVENUE MANAGEMENT IN THE TRAVEL INDUSTRY

REVENUE MANAGEMENT IN THE TRAVEL INDUSTRY

As described in Sec. 18.8, one of the most prominent areas for the application of operations research in recent years has been in improving revenue management in the travel industry. Revenue management refers to the various ways of increasing the flow of revenues through such devices as setting up different fare classes for different categories of customers. The objective is to maximize total income by setting fares that are at the upper edge of what the different market segments are willing to pay and then allocating seats appropriately to the various fare classes.

As the example in this section will illustrate, one key area of revenue management is overbooking, that is, accepting a slightly larger number of reservations than the number of seats available. There usually are a small number of no-shows, so overbooking will increase revenue by essentially filling the available seating. However, there also are costs incurred if the number of arriving customers exceeds the number of available seats. Therefore, the amount of overbooking needs to be set carefully so as to achieve an appropriate trade-off between filling seats and avoiding the need to turn away customers who have a reservation.

American Airlines was the pioneer in making extensive use of operations research for improving its revenue management. The guiding motto was “selling the right seats to the right customers at the right time.” This work won the 1991 Franz Edelman Award as that year’s best application of operations research and management science anywhere through- out the world. This application was credited with increasing annual revenues for American Airlines by over $500 million. Nearly half of these increased revenues came from the use of a new overbooking model.

Following this breakthrough at American Airlines, other airlines quickly stepped up their use of operations research in similar ways. These applications to revenue management then spread to other segments of the travel industry (train travel, cruise lines, rental cars, hotels, etc.) around the world. Our example below involves overbooking by an airline company.

The Transcontinental Airlines Overbooking Problem

Transcontinental Airlines has a daily flight (excluding weekends) from San Francisco to Chicago that is mainly used by business travelers. There are 150 seats available in the single cabin. The average fare per seat is $300. This is a nonrefundable fare, so no-shows forfeit the entire fare. The fixed cost for operating the flight is $30,000, so more than 100 reservations are needed to make a profit on any particular day.

For most of these flights, the number of requests for reservations considerably exceeds the number of seats available. The company’s OR group has been compiling data on the number of reservation requests per flight for the past several months. The average number has been 195, but with considerable variation from flight to flight on both sides of this average. Plotting a frequency chart for these data suggests that they roughly follow a bell- shaped curve. Therefore, the group estimates that the number of reservation requests per flight has a normal distribution with a mean of 195. A calculation from the data estimates that the standard deviation is 30.

The company’s policy is to accept 10 percent more reservations than the number of seats available on nearly all its flights, since roughly 10 percent of all its customers making reservations end up being no-shows. However, if its experience with a particular flight is much different from this, then an exception is made and the OR group is called in to analyze what the overbooking policy should be for that particular flight. This is what has just happened regarding the daily flight from San Francisco to Chicago. Even when the full quota of 165 reservations has been reached (which happens for most of the flights), there usually are a significant number of empty seats. While gathering its data, the OR group has discovered the reason why. On the average, only 80 percent of the customers who make reservations for this flight actually show up to take the flight. The other 20 per- cent forfeit the fare (or, in most cases, allow their company to do so) because their plans have changed.

Now that the data have been gathered, the OR group decides to begin its analysis by investigating the option of increasing the number of reservations to accept for this flight to 190, since 80 percent of 190 = 152, which is very close to the number of seats of available (150). If the number of reservation requests for a particular day actually reaches this level, then this number should be large enough to avoid many, if any, empty seats. Furthermore, this number should be small enough that there will not be many occasions when a significant number of customers need to be bumped from the flight because the number of arrivals exceeds the number of seats available. Thus, 190 appears to be a good first guess for an appropriate trade-off between avoiding many empty seats and avoiding bumping many customers.

When a customer is bumped from this flight, Transcontinental Airlines arranges to put the customer on the next available flight to Chicago on another airline. The company’s average cost for doing this is $150. In addition, the company gives the customer a voucher worth $200 for use on a future flight. The company also feels that an additional $100 should be assessed for the intangible cost of a loss of goodwill on the part of the bumped customer. Therefore, the total cost of bumping a customer is estimated to be $450.

The OR group now wants to investigate the option of accepting 190 reservations by using simulation to generate frequency charts for the following three measures of performance for each day’s flight:

1. The profit.

2. The number of filled seats.

3. The number of customers denied boarding.

A Spreadsheet Model for Applying Simulation

Figure 28.16 shows a spreadsheet model for this problem. Because there are three measures of interest here, the spreadsheet model needs three results cells. These results cells are Profit (F23), NumberOfFilledSeats (C20), and NumberDeniedBoarding (C21). In addition, three statistic cells are defined in cells C23:C25 to measure the mean value of each of the results cells for the simulation run. The decision variable ReservationsToAc- cept (C13) has been set at 190 for investigating this current option. Some basic data have been entered near the top of the spreadsheet in cells C4:C7.

Each trial of the simulation will correspond to one day’s flight. There are two random inputs associated with each flight, namely, the number of customers requesting reserva- tions (abbreviated as Ticket Demand in cell B10) and the number of customers who actu- ally arrive to take the flight (abbreviated as Number That Show in cell B17). Thus, the two uncertain variable cells in this model are SimulatedTicketDemand (C10) and Num- berThatShow (C17).

Since the OR group has estimated that the number of customers requesting reserva- tions has a normal distribution with a mean of 195 and a standard deviation of 30, this information has been entered into cells D10:F10. The Normal Distribution dialog box (shown earlier in Fig. 28.14) then has been used to enter this distribution with these parameters into SimulatedTicketDemand (C10). Because the normal distribution is a continu- ous distribution, whereas the number of reservations must have an integer value, Demand (C11) uses Excel’s ROUND function to round the number in SimulatedTicketDemand (C10) to the nearest integer.

The random input for the second uncertain variable cell NumberThatShow (C17) depends on two key quantities. One is TicketsPurchased (E17), which is the minimum of Demand (C11) and ReservationsToAccept (C13). The other key quantity is the probability that an individual making a reservation actually will show up to take the flight. This prob- ability has been set at 80 percent in ProbabilityToShowUp (F17) since this is the average percentage of those who have shown up for the flight in recent months.

However, the actual percentage of those who show up on any particular day may vary somewhat on either side of this average percentage. Therefore, even though NumberThat- Show (C17) would be expected to be fairly close to the product of cells E17 and F17, there will be some variation according to some probability distribution. What is the appropriate distribution for this uncertain variable cell? Section 28.6 will describe the characteristics of various distributions. The one that has the characteristics to fit this uncertain variable cell turns out to be the binomial distribution.

As indicated in Sec. 28.6, the binomial distribution gives the distribution of the num- ber of times a particular event occurs out of a certain number of opportunities. In this case, the event of interest is a passenger showing up to take the flight. The opportunity for this event to occur arises when a customer makes a reservation for the flight. These opportunities are conventionally referred to as trials (not to be confused with a trial of a simulation). The binomial distribution assumes that the trials are statistically independent and that, on each trial, there is a fixed probability (80 percent in this case) that the event will occur. The parameters of the distribution are this fixed probability and the number of trials.

Figure 28.17 displays the Binomial Distribution dialog box that enters this distribution into NumberThatShow (C17) by referencing the parameters TicketsPurchased (E17) and ProbabilityToShowUp (F17). The actual value in Trials for the binomial distribution will vary from simulation trial to simulation trial because it depends on the number of tickets purchased which in turn depends on the ticket demand which is random. ASPE therefore

The Simulation Results

Figure 28.18 shows the frequency chart obtained for each of the three results cells after applying simulation for 1,000 trials to the spreadsheet model in Fig. 28.16, with Reservations To Accept (C13) set at 190.

The profit results estimate that the mean profit per flight would be $11,775. However, this mean is a little less than the profits that had the highest frequencies. The reason is that a small number of trials had profits far below the mean, including even a few that incurred losses, which dragged the mean down somewhat. By entering 0 into the Lower Cutoff box, the Likelihood box reports that 98.6 percent of the trials resulted in a profit for that day’s flight.

The frequency chart for Number Of Filled Seats (C20) indicates that almost half of the 1,000 trials resulted in all 150 seats being filled. Furthermore, most of the remaining trials had at least 130 seats filled. The fact that the mean of 142.273 is so close to 150 shows that a policy of accepting 190 reservations would do an excellent job of filling seats.

The price that would be paid for filling seats so well is that a few customers would need to be bumped from some of the flights. The frequency chart for NumberDenied- Boarding (C21) indicates that this occurred about 40 percent of the time. On nearly all of

for the Transcontinental Airlines overbooking problem. The Likelihood box in the first statistics table reveals that 98.6 percent of the trials resulted in a positive profit.

these trials, the number ranged between 1 and 10. Considering that no customers were denied boarding for 60 percent of the trials, the mean number is only 2.015.

Although these results suggest that a policy of accepting 190 reservations would be an attractive option for the most part, they do not demonstrate that this is necessarily the best option. Additional simulation runs are needed with other numbers entered in Reservations To Accept (C13) to pin down the optimal value of this decision variable. This can be done fairly easily with trial-and-error. We also will demonstrate how to do this efficiently with the help of a parameter analysis table in Sec. 28.7.