Spreadsheet Simulation of Airline Reservation Policy Using Multimedia Software

A stand-alone Excel spreadsheet simulation template is used to illustrate the detailed rationale of reservation overbooking to both students and professionals working in reservation-based operations. Because almost everyone has personally encountered the frustrations of being “bumped”—losing an airline seat or hotel room reservation—the selection of this particular subject is driven by the belief that at least half of the problem of effectively teaching a sophisticated analytical method is in getting the user’s attention. The personal complications resulting from overbooking presents such a passionate circumstance. Several multimedia tools, Wimba and SnagIt, are used to produce web hosted, mp4 video files of a library of short films illustrating the detailed, step-by-step operation of the spreadsheet template.

INTRODUCTION Understanding the nuances of reservation policy design is crucial for people who either seek employment or are already working professionals in industries such as airlines, hotels, conventions, travel, health care, and education. 1 A stand-alone Excel spreadsheet is designed that introduces interested users to the key parameters and the financial impact that each has in setting reservation policy. Although a hypothetical airline industry case is used to illustrate the simulation technique, the template can be easily adapted to any relevant reservation-sensitive business operation.
The analysis of airline reservation policy has typically been limited to various types of optimization models that address issues such as crew assignment, flight scheduling, and aircraft reliability (Chadha and Chadham, 2006;Chatwin, 1996;Feng and Xiao, 1998;Rothstein, 1971; Zhao and Zheng, 1997). Other analyses that address stochastic airline operations are not easily amenable for use as a teaching tool that can introduce the student to simulation methods (Goldsman, et al., 2005;Lambert, Lambert, and Cullen, 1989; Kros, Dellana, and West, 2009; Klophaus, R. and Polt, S, 2007). This paper presents a standalone Excel spreadsheet template that can be used as a presentation to Monte Carlo simulation dealing with a commonly experienced and particularly irritating event faced by anyone who uses air travel: getting bumped from your flight.
Although there are no U.S. federal regulations that require domestic airlines to compensate customers for flights that are cancelled or delayed, there are requirements of restitution for passengers that are involuntarily bumped (Curtis, 2008;Wilson, Enghagen, and Sharma, 1997). Conversely, domestic airlines, while allowed to overbook flights to compensate for no-show passengers, must compensate bumped passengers after making attempts to find volunteers to give up their seats in exchange for compensation that is typically "bartered" or: 1. The airline must pay the bumped passenger up to $400 for alternate transportation to the destination that takes longer than one to two hours beyond the original scheduled arrival (or the same compensation for an international flight alternative that arrive one to four hours after the original scheduled arrival time). No compensation is required if bumped passenger is provided with alternative travel that arrives within one hour or the original scheduled arrival time. 2. If the substitute transportation results in the bumped passenger arriving at their destination more than 2 hours later (4 hours for international flights), or if the airline makes no alternative travel arrangements, the compensation doubles to a maximum of $800.
A hypothetical overbooking illustration is presented next.
II. AIR AMERICA OVERBOOKING POLICY Air America has been experiencing what it perceives to be a significant loss in revenues due to fluctuations in customer demand along with the additional impact of those confirmed passengers that do not show up for their San Francisco (SFO) to New York City (JFK) route. A distribution of the passenger single-fare demand and "noshows" for the route is given in Figure 1 2 . To offset these possible losses, Air America is considering changing its current reservation policy of not selling more confirmed reservations than the capacity of the plane. An alternative policy being considered is to include reservation overbooking. Here's how it works. Air America uses the Air Bus 300 on many of its flights between the San Francisco and New York City. This configuration has 300 seats available. When the demand exceeds the seats available, Air America could confirm, say, 310 or even 320 seats as a hedge against no-shows. Of course, if the number of confirmed reservations, including   the overbooked sales, minus the no-shows exceeds the capacity of the plane, passengers will have to be "bumped." In this situation, Air America will have to offer the bumped passengers some sort of restitution to offset the inconvenience and resulting ill will-traditionally, a ticket price reduction approach is offered on its next available flight. The airline reservation terminology, spreadsheet design, and spreadsheet functions are presented in Table II, Table I and Table III, respectively.
A. Overbooking Model #1. Demand does not exceed capacity, D ≤ C Air America reasons that there are two basic kinds of reservation scenarios: 1. Demand less than or equal to capacity of flight, D ≤ C, and 2. Demand greater than capacity of flight, D > C. Overbooking cannot occur when D ≤ C (O = 0). Therefore, the number of confirmed reservations possible, R, is either R = C when D = C or R = D when D < C. Additionally, since no overbooking is possible, no passengers can be lost due to a reservation policy, be bumped, or lost because of the plane's seating capacity. That is, O=0, L=0, iJAC -Volume 3, Issue 1, February 2010 B=0, S=0. The number of passengers that will ultimately fly, F, is SETTING #2: DEMAND EXCEEDS CAPACITY, D > C When D > C, the airline may or may not decide to employ an overbooking reservation policy. Regardless, the general relationship for the number of confirmed reservations offered, R, is given by Overbooking cannot occur when D ≤ C (O = 0). Therefore, the number of confirmed reservations possible, R, is either R = C when D = C or R = D when D < C. Additionally, since no overbooking is possible, no passengers can be lost due to a reservation policy, be bumped, or lost because of the plane's seating capacity. That is, O=0, L=0, B=0, S=0. The number of passengers that will ultimately fly, F, is B. Overbooking Model #2: Demand exceeds capacity, D > C When D > C, the airline may or may not decide to employ an overbooking reservation policy. Regardless, the general relationship for the number of confirmed reservations offered, R, is given by The exact number of reservations offered, R, will depend upon the ultimate reservation policy adopted by the airlines. If the airline decides not to use overbooking, the number of reservations offered will be R=C [5] when O = 0 (no overbooking allowed). However, if the reservation policy adopted is to include some level of overbooking, the exact number of confirmed reservations possible, R, is when 1≤ O ≤ D-C (overbooking allowed). The number of passengers that will ultimately fly depends on the reservation policy of the airline (overbooking vs. no overbooking) and the plane capacity. More specifically, if the demand, D, is greater than the reservations offered for the flight-including overbooking if it is used, the number of filled seats for the flight, F, will be when D > C + O. When the demand is less than or equal to the number of reservations offered for the flight, the actual number of passengers that will fly is when D ≤ C + O. If the plane's capacity, C, exceeds the actual number of people that could have ultimately flown (D -N), there is no opportunity loss associated to the planes seating limit and the number of passengers lost due to the plane's capacity is S=0 [9] when D-N ≤ C. However, if the actual number of people who wanted to buy tickets on the flight minus the noshows at flight time-the number of seats that would have ultimately been occupied on the flight, D-N-exceeds the capacity of the flight, C, the number of seats lost due to the plane's seating capacity is S = D-N-C [10] when D-N > C. The number of passengers lost due to the reservation policy employed by the airline, L, is given by if D > C + O. If the demand for the flight, D, is less than the C + O confirmed reservations allowed, the number of passengers lost due to the reservation policy is zero. No passengers who receive confirmed reservations can be bumped from a flight when the smaller of both is (1) the difference between the number of confirmed passengers minus the no-shows (C + O -N) and (2) the difference between the demand D and no-shows, N, does not exceed the plane capacity, C. This should make sense since it is impossible to bump a passenger when the seats ultimately flown are less than the capacity. The relationship for the number of passengers bumped is given by However, if the smaller of this same difference is greater than the capacity of the plane, the number bumped will be the lesser of (1) the difference between the number of overbooked reservations and the no-shows, O -N, and (2) the demand minus both the number of no-shows and plane capacity, D -N -C as shown in the following equation A simplified flowchart illustrating the simulation process of this reservation model is shown in Figure 2.

III. AIRLINE RESERVATION POLICY SIMULATION
The Excel spreadsheet simulation template replicates eleven (11) different overbooking policy settings at once. For our model, a 500-flight cycle is replicated 200 times for eleven reservation policies of between 0 and 10 overbooked seats-a sample of 1,100,000 flights that executes in less than 10 seconds. The simplicity of the template  allows users to experiment with the effects of changing many of the original input variables, such as plane capacity, fixed costs, ticket price, "bumping" costs, seats overbooked, etc., and to rapidly see the impact of these changes on output parameters that include profit per flight, seats lost due to current plane configuration (capacity), seats lost due to reservation policy, and number of passenger bumped. Learning is simplified when more challenging steps in running the template are explained with accompanying voice-over video clips created with SnagIt. Figure 5 and Figure 4 suggest that a reservation policy of overbooking 5 seats yields the highest average flight profit of about $16,750 ($16,764).This policy will also result in an average of 0.86 passengers lost due to its booking strategy, L, 10.34 passengers lost due to plane capacity, S, and an average of 1.17 passengers bumped per flight, B. The student also sees that there is a degree of flexibility in establishing the reservation policy, i.e., overbooking of between 4 to 8 seats creates no more than a 1.5% variation on the per flight profit (Figure 4). During the user introduction to the spreadsheet operations, SnagIt software is employed to record the steps of opening each specific tab in the workbook as well as provide a "voiceover" explaining the visual part of an mp4 video generated and saved for later use. Employing this software provides a memorialized tutorial that demonstrates every mouse-click, keystroke, and each pull-down menu needed to accomplish the correct use of this spreadsheet simulation. When the student has resources such as a carefully organized library of short, well-illustrated mp4 tutorial   videos available to practice, the time in getting "up to speed" with the appropriate level of competency in using the simulation is minimized. Additionally, the opportunity for going beyond the standard variations of the original overbooking problem "on-the-fly" provides users with an additional layer of realism when they are asked to respond to proposed policy changes. A few live "situations" presented include: 1. What changes, if any, occur to the overbooking policy if fluctuations in fuel cost result in the over-all fixed cost of the flight to increase or decrease by 10 percent? 2. Management splits into two factions that strongly disagree with the financial accommodations made to the bumped passengers. What happens to the current overbooking policy if one group feels that the $1,000 expense allocated for each bumped pas-senger is too much because of strong brand loyalty and wants it reduced to $100 while the other fac-tion feels that it should be closer to $2,000 due to increasing route competition from Virgin America, Southwest, and Jet Blue Airlines and the fear that a bumped passenger will be lost for subsequent air-line purchases. 3. Management is considering creating more comfortable seating by increasing legroom from 39 to 44 inches. This seating arrangement will cause the ticket price to increase to $700 and reduce the ca-pacity of the plane to 280 seats. Use the new esti-mated demand and no-show distributions you will be given as influenced by the new higher average fair of $700 per seat and determine if it would pro-vide an improvement over our current policy, i.e., compare the mean profit per flight with both ar-rangements as well as contrast the current and new overbooking policies. 4. Management, due to a recent economic downturn, is going to increase seating capacity by decreasing legroom from 39 to 34 inches per seat. This will increase the plane's capacity to 320 seats but, of course, decrease the comfort index considerably. However, Air America feels that it can draw a slightly more fare-minded market segment if they price their average ticket at $550. Consider the new demand and no-show distributions that you will be given and determine if this is a good idea. Of course, establish the new reservation policy and contrast it with the original. 5. The current model assumes that all revenues are lost for no-shows. Management feels that this is not a reasonable assumption. Determine what changes in the reservation policy occur, if any, if Air Amer-ica recoups 50% of the ticket price from each no-show reservation.
IV. CONCLUSIONS The use of multimedia software in conjunction with a well-designed spreadsheet simulation template makes it possible to facilitate understanding of an airline's complex reservation overbooking policy. The spreadsheet can allows for the use of actual plane configurations and, if available, particular airline policy that might become available, to see the impact of specific operating assumptions. The near instantaneous results that are generated embrace the use of real-time software in either synchro- nous or asynchronous learning settings. In particular, using Wimba whiteboard allows the instructor to share a live desktop view of each step of the simulation template operation with students-either in a classroom setting or at an agreed upon "office hour" when they can view the demonstration from any location that has internet access ( Figure 6). These "live" meetings, can be memorialized for asynchronous, website-stored, short videos and voiceover directions using the SnagIt screen audio and video capture features. Each SnagIt video is converted to a cross-platform compatible mp4 video file format that is edited into short 2-3 minute segments. This latter step is particularly valuable when students want to practice running the simulation at any time. 3 Lastly, the opportunity of assigning believable variations to the original problem setting also provides the students with a sense of realism and an appreciation of the ability to compress time as the template easily replicates these different scenarios variants within a few seconds.