DIRECTIONS
Your task is to complete the Requests worksheet to provide fare information. All formulas must work when copied down the column to determine the requested information for each travel request. In cell E3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the departure city for this flight. Copy the formula down to cell E6. In cell F3 of the Requests worksheet, use the VLOOKUP function to retrieve the name of the arrival city for this flight. Copy the formula down to cell F6. 4 . In cell G3 of the Requests worksheet, use a combination of the INDEX and MATCH functions to retrieve the base fare for this flight. Copy the formula down to cell G6. In cell H3 of the Requests worksheet, use the WEEKDAY function to determine the day of the week of this flight (Sunday = 1, Saturday = 7). Copy the formula down to cell H6. 6. Airline tickets are assigned a fare category based on the base fare ticket price (column G of the Requests worksheet) and the categories listed on the Fees worksheet. In cell 13 of the Requests worksheet, use the XLOOKUP function (or the LOOKUP function if you don't have the XLOOKUP function) to retrieve the fare category for this flight. Copy the formula down to cell 16. 7. 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 of the Requests worksheet, use the INDEX function to retrieve the discount category for this ticket. Copy the formula down to cell J6.A B D G H Discount Category Flight Discounts 2 Fare Category 3 Weekday 1 2 B 4 1 AA AA X 2 Y Y Y 6 3 Y Y Z 7 4 Y Z Z 8 5 Z Y AA LO 6 X X X 10 7 Y Y Z8. In cells G2:J3 of the Discounts worksheet, create a horizontal lookup table (without row headings) that can be used with the HLOOKUP function based on the following discount information: Fare discount category AA: 0% discount on the published base fare. . Fare discount category X: 25% discount on the published base fare. . Fare discount category Y: 50% discount on the published base fare. . Fare discount category Z: 65% discount on the published base fare. 9. In cell K3 of the Requests worksheet, write a formula that includes the HLOOKUP function that determines the discounted fare price (base fare minus discount) of this flight using the table that you created in the Discounts worksheet. Use the ROUND function to round the fares to the nearest dollar. Copy the formula down to cell K6. Check Figure: Cell K5 = $333.00 10. In cell L3 of the Requests worksheet, use the VLOOKUP function to retrieve the airport fee based on the fee schedule in the Fees worksheet. Note that the airport fee is based on the discounted fare. Copy the formula down to cell L6. Check Figure: Cell L4 = $100.00 11. In cell M3 of the Requests worksheet, calculate the total ticket price that Jayhawks in Flight can obtain (discounted fare plus airport fee). Copy the formula down to cell M6. Check Figure: Cell M3 = $229.00 12. In cell N3 of the Requests worksheet, use a formula to compare the Jayhawks in Flight total ticket price to the corporate fare that Rock Chalk, Inc. found. Return TRUE if the Jayhawks in Flight price is less than the corporate fare that Rock Chalk, Inc. was offered. Return FALSE if the Jayhawks in Flight price is not less than the corporate fare that Rock Chalk, Inc. was offered. Do not write an IF statement! Copy the formula down to cell N6. 13. Type your first and last name into the center section of the custom header of the Requests worksheet. 14. Make the Requests tab the active worksheet, save and close your file, and use the Excel 8 link on Blackboard to submit your work.Clipboard IS Font Alignment Number Styles Cells Editing Ideas Sensitivity M4 X V A B E F G H K Corporate Request Form Date Traveler Flight No. Corporate Fare | Departure City | Arrival City| Base Fare Weekday Fare Category |Discount Discounted Fare Airport Fee IN 3 7/22/2016 Frasier, John 1013 $ 972.00 San Francisco Seattle 6 4 8/1/2016 Simpson, Amanda 1079 4,285.00 London Los Angeles 2 UI 8/4/2016 Wexel, Thomas 1068 $ 457.00 New York Miami 5 OY 8/4/2016 Wang, Robert 1002 $ 837.00 New York Seattle 5M N Jayhawks in Airport Fe Flight Total Less than Ticket Price Corporate Rate?A B C D 1 Flight No. Departure City Arrival City Base Fare 1000 New York Denver $ 579.00 1001 New York Seattle $ 938.00 1002 New York Seattle $ 939.00 1003 New York Atlanta 6 930.00 6 1004 New York Atlanta S 948.00
