Fernando Hernández, a Senior Risk Consultant and Trainer at Palisade, was tasked with developing a model for predicting demand and price elasticity for a domestic airline catering to the tourism industry in Costa Rica. This predicting model uses neural networks technology powered by Palisade’s NeuralTools application. NeuralTools is a sophisticated data mining application that uses neural networks in Microsoft Excel. New predictions are based on the patterns of the known data. It imitates brain functions in order to “learn” the structure of your data. Once the trained network understands the data, it can take new inputs and make intelligent predictions.

Collecting the Data

The airline conducted 74 daily flights over 17 routes, utilizing eight planes, and with access to a historical dataset of 143,000 ticket records, Hernández was able to set a baseline of information to develop an accurate prediction set on prices and occupancies. This database contained information on flights, routes, customer categories, dates, fares and aircraft capacity.

The dataset consisted of 21 different fields divided into two categories: route data and date data. Route data fields included information such as route origin and destination, mean fare, and departure block (early morning, mid-morning, or afternoon). The dataset also drilled down to include more detailed route information, such as competitors’ prices and passenger loads, distance traveled, alternative driving time, road conditions, car rental availability, bus availability, and inbound/outbound category (whether the flight leaves or comes back to the major operating airport for this airline).

Since this particular airline depends heavily on American tourism, it was also important to consider the nature of the dates of flights, based on when Americans might be more likely to fly with the airline. Such categories included day of the week, month, holidays, typical vacation periods, etc.

Finding the Price Point

The objective of the trained network created by NeuralTools was to adequately predict how many passengers would purchase tickets on a particular flight and the optimum price point based on a wide range of condition combinations. Once the dataset was created, the neural network was set to be trained and tested. In total, 80 percent of the records were used to train the network and 20 percent were designated to test the validity of the findings.

After training and testing, a sensitivity analysis on 21 variables was performed, and the network was ready to begin calculating predictions for passenger demand for a particular flight. The sensitivity analysis showed which factors held the greatest weight in determining the number of passengers per flight:

"While demand and price elasticity is not an exact science, NeuralTools helped the airline by utilizing data it already had to determine the most profitable price point and adjust it based on the numerous factors that impact air travel."

Fernando Hernández
Senior Risk Consultant and Trainer, Palisade Corporation

Once the factors contributing to flight occupancy were determined, Hernández could put that data to work in the prediction phase. A 30-day model for a single route was created that calculated all 21 independent variables for this time frame, some of which were route-related and date-dependent.

Another view of the factors determining the number of passengers per flight.

Depending on the calendar, the model would “know” whether this time frame included holidays, certain seasons and other date-related features. With all independent variables calculated, the neural network predicted passenger levels based on fares, creating a price sensitivity prediction of the flight.

For example, see the following chart that shows both a prediction for passengers and revenue for a specific flight:

A data entry table was created utilizing fare increments of $5.17, starting at $50, and all the way up to $200. The model predicted that occupancy would remain more or less steady—between 17 to 23 passengers— regardless of the fare, until the $175 threshold is reached. Once this mean fare was surpassed, passenger demand abruptly decreased driving down total expected revenue for the route. At a mean fare of above $185—a mere increase of $10 per ticket—occupancy dipped to less than five passengers.

Based on the model, it was clear that the mean fare that maximizes revenue edges around $174, pushing flight revenue to $3,848 with an average of 22 passengers, under the specific conditions of that flight’s route and date. NeuralTools was also helpful in sensitizing passenger predictions under certain marketing strategies around dates and routes.

While demand and price elasticity is not an exact science, NeuralTools helped the airline by utilizing data it already had to determine the most profitable price point and adjust it based on the numerous factors that impact air travel.

Pace University’s Lubin School of Business combined their MBA-level Statistics and Operations Research courses, using Data Analysis and Decision Making with Microsoft Excel from Duxbury Press (an imprint of Thomson Brooks/Cole), and StatTools and the DecisionTools Suite by Palisade Corporation.

When Pace University’s Lubin School of Business decided to combine their MBA level Statistics and Operations Research courses, they needed a new textbook and new software. For the book they chose Data Analysis and Decision Making with Microsoft Excel from Duxbury Press (an imprint of Thomson Brooks/Cole). For software they selected StatTools and the DecisionTools Suite by Palisade Corporation.

According to Dr. Jack Yurkiewicz, Professor of Management Science at Lubin, the selection was easy. He says, “We standardized on Microsoft Excel as our analysis platform for all MBA courses, so we wanted a statistical analysis package that worked in Excel. StatTools fits the bill and provides robust and accurate analysis.”

The new combined course provides a foundation for business analysis and decision making. It starts by introducing students to statistics and their role in business decision making. From there it moves on to decision modeling and simulation with applications in finance, production, and marketing.

"We standardized on Microsoft Excel as our analysis platform for all MBA courses, so we wanted a statistical analysis package that worked in Excel. StatTools fits the bill and provides robust and accurate analysis."Dr. Jack Yurkiewicz
Lubin School of Business, Pace University

Textbook and Software in One Package

As a bonus, Data Analysis and Decision Making with Microsoft Excel comes with a special student edition of StatTools and the DecisionTools Suite. So students get a two-year license of the software for free when they purchase a new textbook. School administrators are very pleased with the convenience and cost effectiveness. According to Dr Yurkiewicz, “My students are delighted with StatTools, they find it easy to learn and easy to use.”

StatTools is Palisade’s statistical analysis add-in for Microsoft Excel. StatTools replaces Excel’s statistics functions with 36 robust and accurate statistical procedures and 5 built-in data utilities. StatTools capabilities include descriptive statistics, normality tests, group comparisons, correlation, regression analysis, quality control, forecasts and more.

The Lubin School on the Rise

Lubin’s part-time MBA program ranks 17th nationwide in the US News & World Reports “America’s Best Graduate Schools 2006,” moving up from 19th in 2005. Additionally, Pace’s Lubin School has been ranked as one of the Top 100 Entrepreneurial Colleges in Entrepreneur magazine for all years of the survey.

magnifierarrow-right
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram