As you know, the Site Selection Committee is in the process of selecting a location for our new eastern regional office. We have been asked by the Committee to provide input on the travel portion of the proposed site locations. It is expected that there will be considerable commercial air travel between our headquarters in Houston and the location of the regional office.
Total travel between the two cities will probably amount to over 1,000 round trips per year. Approximately one-half of the tickets will be advanced purchase (two plus weeks prior to departure) and one-half will be for immediate travel (typically within a couple of days of purchasing the ticket). All data should be for nonstop flights. It is rare that any of our personnel travel over the weekend. Most trips are completed between Sunday evening and Saturday morning of the same week. The expected volume of travel will allow us to negotiate a fare plan with one, or more, of the air carriers that serve both Houston and the selected city. That negotiation, however, is in the future. Right now we just need to know which city provides the most advantageous travel conditions.
The finalist cities for our new eastern regional office are Charlotte, NC, Philadelphia, PA and Atlanta, GA.
For convenience, these airports are known in the business by their three letter designations;
Charlotte = CLT
Philadelphia = PHL
Atlanta = ATL
Houston Hobby = HOU
Houston Bush Intercontinental = IAH
The criteria we are to use for the travel portion of the site selection are:
- High percentage of on time flights at the selected city’s major commercial airport
- Low cost of tickets, both for advanced purchase tickets and immediate travel tickets.
Using the above criteria, please prepare for my review a table in an Excel spreadsheet showing the relevant data that we can use to make a presentation to the Site Selection Committee. Provide a citation to show your data sources. Do not send the data to me as a Word document. I want to be able to manipulate the data when I receive it. Please note, I want the data only in an Excel table, not a chart.
Flight delay data is available on the following web sites:
http://www.transtats.bts.gov/OT_Delay/OT_DelayCause1.asp
http://airconsumer.ost.dot.gov/reports/index.htm
To obtain a useful set of data you will probably need to take the average for a 12 month period of on time arrival at each airport.
Price information can be obtained from:
http://www.orbitz.com/
http://www.travelocity.com/
Sample Solution
