ISOM 130

This workbook will be used to assist travel agents in finding the best fares for corporate customers.  You will use the information on the fare request sheet and automatically calculate the best fares.

The following worksheets have already been created:

• Requests, which contains a sample request form, including the date, traveler’s name, flight number, and corporate-rate fare.
• Flights, which contains a list of flights sorted by flight number and the associated departure city, arrival city, and base fare. . For this project, assume that all fight numbers are included on the fights list.
• Fees, which contains a table listing fare categories and airport fees associated with ticket prices. These airport fees will be added to the price of each ticket. For example, fares of less than $200.00 fall into fare category 1 and have a $15 airport fee, and fares from $200.00 to $349.99 also fall into fare category 1 but have a $25 airport fee.
• Discounts, which contains a two-dimensional table of discount categories based on the fare category and the weekday of the ticket.

• Open ISOM 130
• In cell E3 of the Requests worksheet, write a formula that retrieves the name of the departure city for this flight. Autofill down.
• In cell F3, write a formula that retrieves the name of the arrival city for this flight. Autofill down.
• In cell G3, write a formula that retrieves the base fare for this flight based on the data given on the Flights worksheet. Autofill down.
• In cell H3, write a formula that determines the day of the week of this flight using the WEEKDAY function. Using 1 through 7, the WEEKDAY function returns 1 for Sunday, 2 for Monday, and so on. Autofill down.
• Airline tickets are assigned a fare category based on the base fare ticket price (column G) and the categories listed on the Fees worksheet. In cell I3, use the LOOKUP function to determine the fare category for this ticket. Autofill down.
• The Discounts worksheet contains a two-dimensional table that has been set up to find the discount category of a ticket based on the weekday of travel and the fare category. In cell J3, use the INDEX function to determine the discount category for this ticket. Autofill down.
• In cell K3 of the Requests worksheet, write a formula that retrieves the discount % and then multiplies that by the Base Fare to determine the amount of the discount. Autofill down.
• In cell L3 write a formula to calculate the Discounted Fare price of the flight. Round the result to the nearest dollar. Autofill down.
• In cell M3, calculate the airport fee based on the fee schedule in the Fees worksheet. Note that the airport fee is based on the discounted fare. Autofill down.
• In cell N3, calculate the total ticket price that America Travels can obtain (discounted fare plus airport fees). Autofill down.
• In cell O3, compare the America Travels total ticket price to the corporate fare that Convertto found and display a message, Use America Travels" or "Use Corporate” based on the result. Copy this formula to cells O4:O6.
• Create a chart show the Corporate Fare and America Travels Total Ticket Price.  Your chart must include a chart title, legend, axis titles and data labels.
• Create a new sheet and name it “Message to Employee”
• Copy and paste the Traveler names into cell A1.
• Use an Excel feature to get the last name and first name in different cells.  In cell A6 document the process that you used to accomplish this.
• In cell C1, write a formula/function to compose a text or email message to each traveler to let them know who to use when booking their trip. For example: Dear Jake Smith, you should use America Travels when booking your trip to Miami.  Autofill the formula/function.
• Save your work.

• 1493969680-ISOM 130.xlsx

ComputerScienceExpert

(11)

Status NEW Posted 05 May 2017 07:05 AM My Price 9.00

ISO-----------M 1-----------30 -----------  -----------

Attachments

file 1508875241-ISOM 130.xlsx preview (34 words )

Fl-----------igh-----------t N-----------o.D-----------epa-----------rtu-----------re -----------Cit-----------yAr-----------riv-----------al -----------Cit-----------yBa-----------se -----------Far-----------eNY-----------Lon-----------don-----------San----------- Fr-----------anc-----------isc-----------oCo-----------lum-----------bus-----------Cle-----------vel-----------and-----------New----------- Yo-----------rkI-----------ndi-----------ana-----------pol-----------isD-----------etr-----------oit-----------Hou-----------sto-----------nCh-----------ica-----------goA-----------tla-----------nta-----------Bos-----------ton-----------Dal-----------las-----------Den-----------ver-----------Kan-----------sas----------- Ci-----------tyL-----------AMi-----------ami-----------Sea-----------ttl-----------eDC-----------Cor-----------por-----------ate----------- Re-----------que-----------st -----------For-----------mDa-----------teT-----------ick-----------et -----------Pri-----------ceA-----------irp-----------ort----------- Fe-----------esT-----------rav-----------ele-----------rWe-----------ekd-----------ayD-----------isc-----------oun-----------tDi-----------sco-----------unt-----------ed -----------Far-----------eAi-----------rpo-----------rt -----------Fee-----------Les-----------s t-----------han----------- Co-----------rp.----------- Ra-----------te?-----------Dis-----------cou-----------nt -----------Cat-----------ego-----------ryF-----------are----------- Ca-----------teg-----------ory-----------XYZ-----------Ame-----------ric-----------a T-----------rav-----------els----------- To-----------tal----------- Ti-----------cke-----------t P-----------ric-----------eCo-----------rpo-----------rat-----------e F-----------are-----------Fra-----------sie-----------r, -----------Joh-----------nSi-----------mps-----------on,----------- Am-----------and-----------aWe-----------xel-----------, T-----------hom-----------asW-----------ang-----------, R-----------obe-----------rtA-----------AFl-----------igh-----------t

Not Rated(0)

• Buy Answer