Amenica Travels sales company te Travels has been working on

Amenica Travels sales company te Travels has been working on a more automatel cmle Another sales associate compiled tare data by flight ues in fees. This customer\'s fare information has Ibcen set up on separate ic task is to complete the last piece of the projicst. project, which is to usc that all flight numbers are included on the flights t and automatically calculate the 5 have already been created in an Escel workbok w hich contains a sample light number, and corporate-rate fare request form from Convertto, including the name, f based ies on a list of flights sorted by flight number and the ass s a table listing fare categories and airport teticket ich contains arrival city, and base fare associated ax returns pidet ares of less than S200.00 fall into fare catcgory ch es. These airport fees will be added to the price of e is listed and fares from $200.00 to $349.99 also fall into fare cat e into far and have category1 $25 airport fee nts, whic aory and the weekday of the ticket the erroe rr hich contains a two dimensional table of discount categs d down include formulas in the Requests worksheet to provide fare information inustwork when copied down the column to determine the requested orcach travel request. Complete the following: (jen the hon sne act the file as Convertto Travel Quotes.xlsx. orkbook named Convertto.xlsx located in the Chapter 5 folder, and sa of the Requests worksheet, write a formula that retrieves the name of the n cll Es the sare city cty for this flight. Copy the formula to cells EA:E6. 8 E3, write a formula that retrieves the name of the arrival city for this flight. the formula to cells F4:Fo. C3 write a formula that retrieves the base fare for this flight based on the data on the Flights worksheet. Copy the formula to cells G4:G6. H3, write a formula that determines the day of the week of this flight using the lo coll Fa in coll G3,u EEKDAY date) function. Using 1 through 7, the WEEKDAY function returns ir Sunday, 2 for Monday, and so on. (Refer to Excel Help for more details on using he WEEKDAY function.) Copy this formula to cells H4:H6.

Solution

10) Fare Category Ticket Price Airport fees Category of ticket price 1 $0 $15 0-199 1 200 25 200-349 2 350 35 350-499 2 500 40 500-699 2 700 50 700-899 3 900 60 900-1099 3 1100 100 1100-1399 3 1400 125 1400 and above Airport fee is based on the discounted fare We can calculate the airport fee by using the IF Function in excel Discounted fare Airport fee 204 25 IF(I13<199,15,IF(I13<349,25,IF(I13<499,35,IF(I13<699,40,IF(I13<899,50,IF(I13<1099,60,IF(I13<1399,100,IF(I13>=1400,125)))))))) 1139 100 333 25 #N/A #N/A 11 Total Price that America travels can obtain Discounted fare Airport Fee America Travels Total ticket price(Discounted fare + Airport fee) 204 25 229 1139 100 1239 333 25 358 #N/A #N/A #N/A