Excel Help!: Succeeding in Business with Microsoft Excel 2013 (1st Edition) Chapter 5, Problem 2CP

*Please show all excel formuals for ecah question asked.

**Screenshots of each sheet are pasted after instructions below.

Flights Sheet

Fees Sheet

Discounts Sheet

Corporate Request Form

Date

Traveler

Flight No.

Corporate Fare

Departure City

Arrival City

Base Fare

Weekday

Fare Category

Discount

Discounted Fare

Airport Fee

America Travels Total Ticket Price

Less than Corp. Rate?

7/22/2016

Frasier, John

1013

$ 972

8/1/2016

Simpson, Amanda

1079

$ 4,285

8/4/2016

Wexel, Thomas

1068

$ 457

8/4/2016

Wang, Robert

1002

$ 837

Flight No.	Departure City	Arrival City	Base Fare
1000	NY	Denver	579
1001	NY	Seattle	938
1002	NY	Seattle	939
1003	NY	Atlanta	930
1004	NY	Atlanta	948
1005	NY	Atlanta	831
1006	New York	Detroit	511
1007	London	NY	1634
1008	San Francisco	Detroit	770
1009	London	NY	851
1010	NY	LA	820
1011	NY	Seattle	732
1012	NY	DC	267
1013	San Francisco	Seattle	272
1014	NY	Boston	410
1015	NY	Boston	423
1016	NY	Miami	500
1017	NY	Boston	295
1018	NY	Kansas City	770
1019	NY	Boston	379
1020	NY	Boston	289
1021	NY	DC	286
1022	NY	Boston	262
1023	San Francisco	Seattle	339
1024	San Francisco	Kansas City	965
1025	New York	Dallas	755
1026	NY	Miami	480
1027	San Francisco	Atlanta	793
1028	NY	Denver	746
1029	NY	LA	1257
1030	Chicago	Seattle	710
1031	NY	DC	205
1032	NY	LA	850
1033	San Francisco	Denver	368
1034	San Francisco	Denver	449
1035	NY	LA	565
1036	San Francisco	Miami	716
1037	NY	DC	302
1038	NY	Chicago	809
1039	NY	Chicago	676
1040	London	NY	1273
1041	NY	Chicago	666
1042	NY	Chicago	469
1043	NY	Chicago	829
1044	London	Miami	1319
1045	NY	Chicago	689
1046	San Francisco	Boston	1288
1047	NY	DC	310
1048	San Francisco	DC	920
1049	London	Boston	1539
1050	San Francisco	DC	1174
1051	San Francisco	NY	807
1052	NY	Indianapolis	765
1053	New York	Dallas	559
1054	San Francisco	Chicago	934
1055	San Francisco	Chicago	919
1056	San Francisco	Dallas	508
1057	San Francisco	Indianapolis	924
1058	London	Chicago	1623
1059	San Francisco	LA	343
1060	London	Chicago	1303
1061	New York	Detroit	528
1062	NY	Miami	746
1063	London	Atlanta	1434
1064	NY	Cleveland	464
1065	Chicago	DC	379
1066	New York	Houston	689
1067	San Francisco	Cleveland	1135
1068	NY	Miami	665
1069	San Francisco	NY	1210
1070	London	DC	1025
1071	New York	Houston	889
1072	New York	Columbus	755
1073	New York	Columbus	322
1074	San Francisco	NY	663
1075	San Francisco	Houston	549
1076	San Francisco	LA	310
1077	London	Houston	1838
1078	San Francisco	Columbus	949
1079	London	LA	2277
1080	Chicago	NY	504

Fare Category	Ticket Price	Airport Fees
1	$ -	$ 15.00
1	200.00	25.00
2	350.00	35.00
2	500.00	40.00
2	700.00	50.00
3	900.00	60.00
3	1,100.00	100.00
3	1,400.00	125.00

Discount Category
	Fare Category
Weekday	1	2	3
1	AA	AA	X
2	Y	Y	Y
3	Y	Y	Z
4	Y	Z	Z
5	Z	Y	AA
6	X	X	X
7	Y	Y	Z

Show all steps: a ON Chapter 5, Problem 2CP Problem As a new sales associate at America Travels travel agency, you assist travel agents in finding the best fares for corporate customers. As a high-volume travel agency, America Travels has negotiated several premium discounts with airlines, which you pass along to your customers who do a large amount of business with you. Compared to standard business fares, these discounts can amount to a substantial savings and do not have minimum stay requirements. Convertto, Inc. is a significant customer with a travel department that prices its own fares online and then contacts America Travels to see if itcan provide a better rate. Convertto sends an Excel workbook containing a list of proposed trips to America Travels each day by 10 a.m. via email, and requests price quotes for these trips by noon. Convertto has corporate offices in New York, San Francisco, and London, which is where most of its travel originates. When the Convertto workbook arrives, America Travels sales associates must work hard to enter the information that Convertto requests and return the workbook to the company on time For several months, America Travels has been working on a more automated method of replying to these inquiries. Another sales associate compiled fare data by flight number and researched airport fees. This information has been set up on separate worksheets in an Excel workbook. Your task is to complete the last piece of the project, which is to use the information on the customers fare request sheet and automatically calculate the best fares. For this project, assume that all flight numbers are included on the flights list. The following worksheets have already been created in an Excel workbook named Convertto xisx Requests, which contains a sample request form from Convertto, including the date, travelers 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 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