Surcharg Checked e Bags Bag Cost Total Cost Class Base Ticket Check Bag? Yes Yes No 4 1 1. In column F create a lookup function to determine the base price of the ticket based on the location tables below. Note there are different prices for first class, coach, international, and domestic. 4 1 No Yes No 1 1 First First Coach First First Coach Coach Coach First Coach Coach Coach 2. In Column G calculate the booking surcharge. If the departure date is less then days from the date booked and the customer is flying first class then the surcharge is 20% of the base ticket. If the customer flies coach the fee is 15% of the base ticket. No 4 Yes 5 5 Date Departur Internation Booked e Date Arrival Airport al 5/1/2016 5/15/2016 CAN Yes 5/11/2016 5/20/2016 KNA Yes 5/13/2016 6/3/2016 MCI No 5/14/2016 5/18/2016 CYP Yes 5/15/2016 6/16/2016 DEN No 5/20/2016 6/20/2016 DEN No 5/21/2016 6/1/2016 DFW No 5/22/2016 6/10/2016 AUS No 5/23/2016 5/29/2016 ORD No 5/24/2016 6/20/2016 MCI No 5/24/2016 6/15/2016 ORD No 5/27/2016 6/10/2016 AUS No 5/29/2016 6/5/2016 DEN No 5/30/2016 6/11/2016 MCI No 5/31/2016 6/1/2016 DEN No 5/31/2016 6/14/2016 FIL Yes 6/1/2016 6/8/2016 SEA Yes 6/5/2016 6/13/2016 AGI Yes 6/8/2016 6/17/2016 TUL No 6/14/2016 6/18/2016 TUL No 6/14/2016 6/14/2016 OKC No No 4 3. In column I calculate the bag cost. If the customer flies first class domestic the bag fee is waived. If the customer flies coach or any international flight, the bag fee is $20.00 per bag. 4 3 5 1 No Yes Yes Yes No Yes No Yes No Yes No 2 4 First Coach Coach First Coach First Coach Coach if and vlookup 4 1 5 4 First No 1 Domestic Airport DEN TUL OKC MCI DFW AUS Base Ticket Prices City Denver $ Tulsa $ Oklahoma City $ Kansas City $ Dallas/Fort Worth $ Austin $ Chicago O'Hare $ First 500.00 325.00 350.00 375.00 450.00 350.00 Base Ticket Prices Airport City Coach DEN Denver $ 275.00 TUL Tulsa $ 175.00 OKC Oklahoma City $ 200.00 | MCI Kansas City $ 215.00 DFW Dallas/Fort Worth $235.00 AUS Austin $ 205.00 ORD Chicago O'Hare $325.00 International Base Ticket Prices Base Ticket Prices Airport City First Airport City Coach CAN Cairo Internationa $ 1,500.00 CAN Cairo Internationa $ 1,275.00 KNA Kona Internationa $ 1,325.00 | KNA Kona Internationa $ 1,175.00 | OKA Osaka $ 1,350.00 | Osaka $ 1,200.00 CYP Cyprus $ 1,375.00 CYP Cyprus $1,215.00 AGI Athens Internatio $1,450.00 AGI Athens Internatio $1,235.00 FIL Florence $ 1,350.00 FIL Florence $ 1,205.00 SEA Seville Airport $ 1,700.00 SEA Seville Airport $ 1,325.00 ORD 700.00