Problem $1$: You have just been hired as the travel coordinator for Intrepid Corporate Solutions. Part of

your job is to purchase airline tickets for company executives. Intrepid receives corporate$-$rate fares

directly from the airlines, but also $($from time to time$)$ purchases tickets through a ticket consolidator,

America Travels. Use the worksheet for Problem $1$ to complete the travel requests currently in process.

$$ On the Flights worksheet you will find:

o A table, named Flights that shows each possible flight number, the departure and arrival

cities of the flight and the base fare before discounts.

o A table that shows the fare category associated with each ticket price. This table is

named Fare$\_$Category.

$$ On the Discounts worksheet you will find:

o A table, named Discount$\_$Categories, which shows the discount category for each flight.

The discount category depends on both the weekday on which the trip is made and the

fare category of the flight.

o A table of discount amounts, named Discount$\_$Rates, that shows the percentage

discount offered for each discount category.

$$ On the Airport Fees worksheet, you will find tables showing airport fees, which are based on

fare type and departure city for each flight. There are three different tables showing the three

different levels of fees: Category$\_1\_$Fees, Category$\_2\_$Fees, Category$\_3\_$Fees.

a$.$ Begin by using the Flight number to retrieve the correct Departure and Arrival cities from the

table of Flights. Include MATCH in your formula.

b$.$ Next, fill in the correct Base Fare for each flight, making sure you use the Account Type to retrieve

the correct fare. You do not have to include MATCH on this part. If you want to include MATCH, the

syntax will use a $$wildcard$.$ For example: MATCH$($D$3$ &$"*",$ Flights!$A$$1$:$E$$1,0)$ will return the

position of any entry that starts with $$National$$

c$.$ In the Weekday column, display the name of the weekday that corresponds to each travel date.

Use the Weekday function with a return$\_$type of $1.$ Thus numbers the weekdays with: $1$

$($Sunday$)$ through $7($Saturday$).$ If you are not sure about this function, check the help.

d$.$ In the Fare Category column, show the category associated with the Base Fare for that flight.

e$.$ Using the Weekday of the flight, retrieve the Discount designation from the Discount

Categories table.

f$.$ Use the Discount designation to display the discounted fare for each flight in the Discounted

Fare column. The discounted fare is the Base Fare less the percent discount offered.

g$.$ The Airport Fee for each flight is a function of the Fare Category and the Departure City. Enter

the correct fee for each flight in the Airport Fee column.

h$.$ Enter the total price for each ticket in the Total Ticket Price column.