THE NO-SHOW PROBLEM: SIMULATION AND REVENUE MANAGEMENT

  

Zbigniew H. Przasnyski

 Loyola Marymount University

 

Department of Finance and

Computer Information Systems

 

One LMU Drive

Los Angeles, CA 90045

 

Phone: (310) 338-2900

E-mail: zprzasny@lmu.edu

 

　

ABSTRACT

This paper illustrates a standard problem in revenue management, the no-show problem, through an explanatory example case scenario. A simulation model is built to address the problem and then an optimizing component is introduced to fully solve the problem. The basic model is readily extendable to address modeling, statistical and management issues. Teaching experience with the model and its impact is discussed.

 

INTRODUCTION AND BACKGROUND

Revenue management has been, and is, a hot area in management science/operations research (MS/OR) since its benefits have clearly been demonstrated by many organizations. For example, Ford’s Lloyd Hansen, controller for global marketing and sales, estimated that between 1995-2000, about $3 billion growth came from a series of revenue management initiatives (Leibs, S., 2000). Initially, airlines, car rental agencies and hotel chains adopted revenue management models and as a result of their success the manufacturing, retail and service industries are adopting and adapting revenue management models into their own specific operations (Bell, 2004; Kimes and Thompson, 2004; Carroll and Grimes, 1995; Hanks et al., 1992; Smith et al., 1992).

Revenue management models are based on understanding (and predicting) customer demand and market segmentation sufficiently to be able to dynamically respond to changes in demand in order to maximize revenue. Specific approaches include: dynamic pricing (e.g., an airline adjusting the price of seats based on the remaining inventory of available seats); discount allocation (e.g., a hotel chain deciding on competitive discounts for daily, weekly, weekend and special rates in anticipation of demand); and over booking (e.g., airlines routinely overbooking flights based on anticipated no-show rates). Algorithms are developed to dynamically respond to market changes by carefully investigating and analyzing models of these kinds of decision problems.

This paper will address the last of the above three areas, namely the over booking problem. The problem will be described as a case that shows how simulation can be used to understand the various issues and their interactions. One of the benefits of this case is that the basic underlying problem is easy to model, so that students can quickly grasp the essential problem, but then the model can be extended in a variety of increasingly complex ways. The more involved extensions challenge the students’ modeling and simulation skills and hopefully provide them with an appreciation of the power of simulation to provide insights into complex problems. The case has been used in a graduate Operations Management course for several semesters and experiences and insights gained will be shared.

In the MS/OR community the spreadsheet (which nowadays is almost exclusively synonymous with Microsoft Excel) is accepted as a powerful and useful modeling medium for both practitioners and researchers far beyond the standard accounting and finance applications. There are numerous accounts of real life decision problems being solved quickly and efficiently by using management science methodology and techniques in an Excel environment. The application areas have been very diverse, stretching from mainstream business problems to healthcare, aerospace and many other miscellaneous industries (Seal et al., 2000). The documented benefits of spreadsheets for decision making include the fact that they are readily available on a manager’s desktop; in turn desktop models facilitate what-if analyses; spreadsheets are no longer the purview of “white coated technical experts” as managers become more and more adept at model building, and as a result have more confidence in their decisions based on their models; the end result being that the quality of decisions is improved.

Over time, Excel has provided increasingly sophisticated MS/OR features (for example, by licensing Solver, a mathematical programming package from Frontline Systems, which is now seamlessly integrated into every copy of Excel sold) and integrates well with many third party packages that increase its modeling power, (for example, Crystal Ball sold by Decisioneering Inc., provides the infrastructure for solving stochastic decision problems by simulation simply by highlighting a few input and output cells and then aggregates the results and provides statistical summaries).

Since the central approach to the MS/OR methodology is to solve or gain insights into decision problems through model building the MS/OR community is always on the lookout for new approaches or methods in model development and solution. The MS/OR literature routinely describes the increasingly complex and sophisticated scenarios that have been modeled and implemented in a spreadsheet environment (some recent examples can be found in Elisha et al., 2004; Olphert and Wilson, 2004; LeBlanc et al., 2000). The approach taken in this paper is a spreadsheet simulation approach, with optimization extensions.

　

ALPHA AIR: THE NO-SHOW PROBLEM

Alpha Air, a small upstart airline, prides itself on customer service and efficiency and passenger loyalty. Over the last 18 months it has established a lucrative route from Burbank to La Guardia aimed at the business traveler, in spite of aggressive competition from the major carriers. Part of Alpha Air's success can be attributed to being highly responsive to its customers, allowing last minute bookings at reasonable prices and flying an identical fleet of modern mid-size aircraft (each with a capacity of 220 seats) to minimize maintenance costs.

Due to a significant number of passenger no-shows recently on this route it is re-evaluating its booking strategy. To compensate for the no-shows the airline routinely overbooks its seats and has been accepting up to 250 seat reservations for this flight. However, the number of ticketed passengers showing up at the gate is variable and difficult to predict accurately.

When more passengers than expected show up the airline incurs increased costs (by offering various incentives for passengers to wait for a later flight and/or by placing passengers on competing carriers). The airline estimates that the cost of “bumping” a passenger is $225.

When fewer passengers than expected show up the airline also incurs costs (in unearned revenue and various administrative costs by flying with empty seats). The airline estimates that the cost of flying with an empty seat is $180.

[Assume that overall demand for the route is sufficiently strong to bring in up to 270 seat reservations per flight and that the airline does not currently operate a standby list for this route].

Currently, Alpha Air is allowing up to 250 reservations per flight. Determine (i) number of passengers denied boarding, (ii) the number of empty seats flown and (iii) the overall total costs under each of the assumptions below. Hence determine a “good” overbooking strategy (i.e., the number of seats to book per flight) for Alpha Air given each of the assumptions.

A.        the percentage of no-shows will be 10% of the number of passengers booked

B.         the percentage of no-shows will be 11% of the number of passengers booked

C.         the percentage of no-shows will be 12% of the number of passengers booked

D.        the percentage of no-shows will be 13% of the number of passengers booked

E.         the percentage of no-shows will be 14% of the number of passengers booked

F.         the percentage of no-shows will be Normally distributed with a mean of 12% and a standard deviation of 2%

G.        the percentage of no-shows will be distributed as follows:

 

DISCUSSION

A-F above are used to develop the basic model – see Figure 1. Given the assumptions for the no-show rate, simple relationships are established for the number of no-shows, the total number of passengers showing up, the number boarding, the number denied boarding, and the number of empty seats. The obvious measures of performance are the cost of bumping, the cost of empty seats and the total cost. The no-show rate is deterministic and so there is no uncertainty in the decision.

Figure 1: Basic model – daily

 

Thereafter, uncertainty in the no-show rate is built in as in for example, F and G, to show examples of continuous and discrete distributions respectively. Many alternative distributions can be used at this stage and students are introduced to concepts such as truncated distributions as necessary.

The importance of obtaining long-term or steady state solutions is introduced and the trade off between daily and, say, weekly totals is discussed. Figure 2 shows the equivalent weekly model where we calculate the Total, Average, Minimum, Maximum and Standard Deviation of each of the three main measures of performance (the cost of bumping, the cost of empty seats and the total cost).

The Excel formulas define the relationships and hence the model. These are presented in Appendix 1.

Figure 2: Basic model – weekly

 

Once the measures of performance, or outputs are identified, the actual simulation begins by running the model multiple times and noting the output measures. It is important to repeat the runs a large number of times to obtain the steady-state or the long-term average behavior of the system. The multiple runs and collection of the output measures for each run can be done in various ways. The simplest way is to use an add-in package such as Crystal Ball from Decisioneering Inc., @Risk from Pallisades Corporation, or the free PopTools developed by Greg Hood at Pest Animal Control Co-operative Research Centre in Australia. Alternatively, one can manually execute the runs by simply using the recalculate feature (F9 function key) of the spreadsheet and keeping track of the outputs systematically in an Excel table for eventual calculation of the statistical parameters of the outputs. However, manual runs can be tedious. We used Crystal Ball which runs the simulation a default of 1000 times with a few clicks of the mouse, produces various summary statistics, and allows one to see the behavior of the system in multiple formats. The software is easy to use with a point-and-click interface and works seamlessly in a spreadsheet. An illustrative example shows the Crystal Ball results for the weekly situation model in Appendix 2.

Further uncertainty can be built into the model by having the costs of both bumping and flying with empty seats represented by distributions, simply by declaring the appropriate distributions to Crystal Ball as input cells.

Depending upon the level of the course and the students’ sophistication an optimizing element can be introduced in to the problem. This is very efficiently achieved using the Optquest module within Crystal Ball that will optimize a user quantity (i.e., cell). Figure 3 shows the Optquest window for defining the range of values to be investigated for the number of seats to book - here we are investigating the value from 225 to 275. Figure 4 shows the Optquest window where the measure to be optimized is defined – here we are trying to minimize the average of the Total Costs. Figure 5 shows the Optquest results – the best number of seats to book is 245 and the minimum daily average costs are $784. Optquest found this after 21 simulations.

Figure 3: Optquest window defining the range of values to be investigated for the number of seats to book.

Figure 4: Optquest window defining the measure to be optimized

Figure 5: Optquest results

　

IMPACTS

The case clearly shows the power of simulation to address a reasonably complex scenario.  The model is used to illustrate the behavior of the system in a given situation, in this case, for example the total costs of bumping and flying with empty seats for a strategy of booking up to 250 seats. The impacts of other booking strategies can now be investigated. To fully address the no-show problem all meaningful booking strategies would have to be systematically simulated, for example booking 220, 221, … 270 seats to find the booking strategy that minimized the total costs. This would be somewhat tedious to do manually. While the process could be automated by writing some VBA macros this approach would be beyond the scope of most typical MBA students. The OptQuest module does exactly this with a friendly user interface.

Once the basic model and simulation process have been established there are many extensions that can be discussed in class based on the goals of the class. In a management science oriented class the model and good modeling practices can be the focus, in a more statistics oriented class the simulation process can be used to illustrate and/or verify the analytical solution of the problem, while in a management oriented class the managerial implications and behavioral trade-offs can be the focus.

Example extensions that can be discussed in class, could be to more than one type of plane and/or different no-show distributions on different routes. The case provides an excellent backdrop for discussion of good practices in model building, e.g., keeping parameters external to the main model, self documenting the spreadsheet model using meaningful headings, comments and auditing features, specifically:

·         Requires careful model creation.

·         Requires interpretation of the basic simulation results provided by Crystal Ball and the variability inherent in processes.

·         Requires interpretation of the optimization results provided by Optquest based on the simulation model – what are the implications.

·         Requires an understanding of assumptions made implicitly or explicitly.

After a class introduction to the principles and mechanics of simulation students work on the case, develop their models and make recommendations in written report. The model is also verified by students online in the classroom on the day that the report is due – sample instructions to students are provided in Appendix 3. An interactive verification tests if the models the students have created are robust enough to cope with changes and tests whether they understand the implications and meaning of the results obtained. Some examples of typical changes would be:

·         Changing the costs of bumping and/or flying with empty seats

·         Changing the capacity of the plane

·         Changing the no-show distribution

None of these changes require any structural modifications of the model, simply changes to the various input parameters. Students who have developed robust models themselves (as opposed to obtained them from their classmates!) generally have no difficulty with evaluating the consequences of these changes.

　

CONCLUSION

The Alpha Air model is relatively straightforward application area that introduces students to the ideas and concepts of Revenue Management and shows the power of simulation to investigate system performance. The basic scenario can readily be adapted or extended in various ways to reflect teaching philosophies or goals.

　

REFERENCES

Bell, P. C., (2004) “Revenue Management for MBA’s,” OR/MS Today, Volume 31, Number 4,  22-27.

Carroll, W. J., and Grimes, R. C., (1995) “Evolutionary change in product management: Experiences in the car rental industry,” Interfaces, Volume 25, Number 5,  84-104.

Elisha, D., Levinson, D., and Grinshpoon, A., (2004) “A Need-based Model for Determining Staffing Needs for the Public Sector Outpatient Mental Health Service System,” The Journal of Behavioral Health Services and Research. Volume 31, Number 3,  324-333.

Hanks, R. B., Noland, R. P., and Cross, R. G., (1992) “Discounting in the hotel industry: A new approach,” Cornell Hotel and Restaurant Administration Quarterly, Volume 33, Number 3,  40-45.

Kimes, S. E., and Thompson, G. M., (2004) “Restaurant Revenue Management at Chevys: Determining the Best Table Mix,” Decision Sciences, Volume 35, Number 3,  371-392.

LeBlanc, L. J., Randels Jr. D., and Swann, T. K., (2000) “Heery International's spreadsheet optimization model for assigning managers to construction projects,” Interfaces, Volume 30, Number 6,   95-104.

Leibs, S., (2000) “Aided by New Software the Automaker is Using Revenue Management to Boost the Bottom Line,” CFO Magazine, (http://www.cfo.com/article.cfm/2990968).

Olphert, C. W., and Wilson J. M., (2004) “Validation of decision-aiding spreadsheets: the influence of contingency factors,” The Journal of the Operational Research Society, Volume 55, Number 1,   12-17.

Seal, K. C., Przasnyski, Z. H.  and Leon, L., (2000) “A Literature Survey of Spreadsheet Based MS/OR Applications: 1985-1999", OR Insight, 2000, Volume 13. Number 4,  21-31.

Smith, B. A., Leimkuhler, J. F., and Darrow, R. M., (1992) “Yield management at American Airlines,” Interfaces, Volume 22, Number 1,  8-31.

APPENDIX 1: EXCEL FORMULAS FOR THE BASIC MODEL