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:

1. Netherlands Antilles (Aruba, Bonaire and Curacao)
2. Western Caribbean (Bahamas, Cozumel and Grand Cayman)
3. Alaska (Hubbard Glacier)
4. 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:

1. Automatically calculate the cost (pre-tax and destination fees) of each mode of touring to each destination based on the season (peak, shoulder, trough).
2. Graphically present the cost of each mode of touring across seasons to the customer.
3. 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

1. Cruise and Airplane Quotes are per passenger.
2. The All-Inclusive Quote is per two individuals.
3. Final Quotes and Comparison should be based on two individuals.
4. Individuals expect to spend five days across physical locations.
5. Individuals are leaving from the Quad Cities.
6. 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.