Reference no: EM133602907
Case Scenario
You are planning on spending the Winter Break as the Assistant Office Manager for an Internship (receiving credit for this as EXPL-202) at a travel agency that specializes in arranging trips to common tourist destinations:
- Netherlands Antilles (Aruba, Bonaire and Curacao)
- Western Caribbean (Bahamas, Cozumel and Grand Cayman)
- Alaska (Hubbard Glacier)
- Hawaii (Big Island, Maui and Oahu)
You know the manager will not allow you to implement industry standard software during the Winter Break, but also know based on your knowledge of quantitative methods that you can implement an efficient system in Excel that will:
- Automatically calculate the cost (pre-tax and destination fees) of each mode of touring to each destination based on the season (peak, shoulder, trough).
- Graphically present the cost of each mode of touring across seasons to the customer.
- Graphically present the daily cost of each destination, including the opportunity cost of the extra days associate with a cruise.
To work on your system, you have collected the information in the associated data file.
Project One Data
Important Notes
- Cruise and Airplane Quotes are per passenger.
- The All-Inclusive Quote is per two individuals.
- Final Quotes and Comparison should be based on two individuals.
- Individuals expect to spend five days across physical locations.
- Individuals are leaving from the Quad Cities.
- The All-Inclusive Quote includes flights/all transportation, accommodations, meals, and activities, and the trip is assumed to allow for five days at physical locations.
Alaska (Hubbard Glacier)
The only feasible way to see the Hubbard Glacier that does not involve dangerous overland or short-hop flights is via a cruise. Five-day excursions are round-trip from Vancouver, British Columbia. The cost of a high-quality cruise is:
Activity |
Peak |
Shoulder |
Trough |
Air Round-trip |
$650 |
$600 |
$525 |
Cruise |
$2,100 |
$1,750 |
$1400 |
Hawaii
Any high-quality cruise departs from Oahu and returns to Vancouver, British Columbia.
Due to sailing time, the cruise takes 11 days to allow for five days at physical locations.
Activity |
Peak |
Shoulder |
Trough |
Air - QCA to Oahu |
$900 |
$600 |
$400 |
Air - Vancouver to QCA |
$400 |
$350 |
$325 |
Cruise |
$3,100 |
$2,250 |
$1,700 |
All-Inclusive |
$8,700 |
$7,200 |
$6,500 |
Netherland Antilles
Any high-quality cruise will have a round-trip from Fort Lauderdale.
Due to sailing time, the cruise takes nine days to allow for five days at physical locations.
Activity |
Peak |
Shoulder |
Trough |
Air Round-trip |
$550 |
$405 |
$350 |
Cruise |
$2,170 |
$1,500 |
$1,060 |
All-Inclusive |
$7,200 |
$6,200 |
$4,600 |
Western Caribbean
Any high-quality cruise will have a round-trip from Fort Lauderdale.
Due to sailing time, the cruise takes nine days to allow for five days at physical locations.
Activity |
Peak |
Shoulder |
Trough |
Air Round-trip |
$550 |
$405 |
$350 |
Cruise |
$2,300 |
$1,780 |
$950 |
All-Inclusive |
$6,900 |
$5,900 |
$4,000 |
Opportunity Cost
Each individual has a daily opportunity cost of $200 for every day beyond the planned five-day excursion.