Please help me with this excel problem!

Level 2 Calculating Travel Costs at America Travels

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.

Texto, Inc. is a significant customer with a travel department that prices its own fares online, and then contacts America Travels to see if it can provide a better rate. Texto e-mails an Excel workbook containing a list of proposed trips to America Travels each day by 10 a.m., and requests price quotes for these trips by noon. Texto has corporate offices in New York, San Francisco, and London, which is where most of its travel originates. When the Texto workbook arrives, America Travels sales associates must work hard to enter the information that Texto 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 Texto.xlsx:

RequestsThis worksheet contains a sample request form from Texto, including the date, travelers name, flight number, and corporate-rate fare.

FlightsThis worksheet contains a list of flights sorted by flight number and the associated departure city, arrival city, and base fare.

FeesThis worksheet contains a table listing fare categories and airport fees associated with specific ticket price categories. 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 $299.99 also fall into fare category 1 but have a $25 airport fee.

DiscountsThis worksheet contains a two-dimensional table of discount categories based on the fare category and the weekday of the ticket. Your task is to include formulas in the Requests worksheet to provide fare information. All formulas must work when copied down the column to determine the requested informa- tion for each travel request. Complete the following:

1. Open the workbook named Texto.xlsx located in the Chapter 5 folder, and then save it as Texto Travel Quotes.xlsx.

2. In cell E3 of the Requests worksheet, write a formula that retrieves the name of the departure city for this flight. Copy the formula to cells E4:E6.

3. In cell F3, write a formula that retrieves the name of the arrival city for this flight. Copy the formula to cells F4:F6.

4. In cell G3, write a formula that retrieves the base fare for this flight based on the data given on the Flights worksheet. Copy the formula to cells G4:G6.

5. In cell H3, write a formula that determines the day of the week (1 through 7) of this flight using the WEEKDAY(date) function. The WEEKDAY function returns 1 for Sunday, 2 for Monday, and so on. (Refer to the Microsoft Excel Help feature for more details on using the WEEKDAY function.) Copy this formula to cells H4:H6.

6. Airline tickets are assigned a fare category based on the base fare ticket price (column G) and the categories listed on the Fees worksheet. In cell I3, write a formula that determines the fare category for this ticket. Copy the formula to cells I4:I6.

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, write a formula that determines the discount category for this ticket. Copy the formula to cells J4:J6.

8. In a separate area of the Discounts worksheet, create a horizontal lookup table based on the following discount information: Fare discount category AA: 0% discount of the published base fare Fare discount category X: 25% discount of the published base fare Fare discount category Y: 50% discount of the published base fare Fare discount category Z: 65% discount of the published base fare

9. In cell K3 of the Requests worksheet, write a formula that determines the discounted fare price (base fare minus discount) of this flight using the table you created in the Discounts worksheet. Fares should be rounded to the nearest dollar. (Hint: Do not use an IF function.) Copy this formula to cells K4:K6.

10. In cell L3, calculate the airport fee based on the fee schedule in the Fees worksheet. Note that the airport fee is based on the discounted fare. Copy this formula to cells L4:L6.

11. In cell M3, calculate the total ticket price that America Travels can obtain (discounted fare plus airport fees). Copy this formula to cells M4:M6.

12. In cell N3, compare the America Travels total ticket price to the corporate fare that Texto found. Return a TRUE value if the America Travels price is less than the corporate fare Texto was offered. Copy this formula to cells N4:N6.

13. Format your worksheet so that it is easy to read and understand.

14. Save and close the Texto Travel Quotes.xlsx workbook.

AutoSave On Texto Travel Quotes - Insert Page Layout FormulasData Review View Help wrap Text Merge & Center , $ % , .00 8 C File Home Tell me what you want to do 14A A General Normal @ Copy- Paste B I U onditional Format as Formatting Table Check Cell Format Painter Clipboard Font Alignment Number C1 Corporate Request Form America Flight Corporate Base Fare Discounted Airport Travels Tota Less than Fare Departure City Arrival CityFare Weekday CategoryDiscount Fare Fee Ticket Price Corp. Rate? 3 1049$ 1,875 499 175 6 1007$ 1,199 41016 $ 10 12 13 15 16 17 18 19 20 21 23 24 25 26 28 29 30 31 32 34 35 36 37 38 39 40 Requests Flights FeesDiscounts AutoSave On Texto Travel Quotes - Insert Page Layout FormulasData Review View Help wrap Text Merge & Center , $ % , .00 8 C File Home Tell me what you want to do 14A A General Normal @ Copy- Paste B I U onditional Format as Formatting Table Check Cell Format Painter Clipboard Font Alignment Number C1 Corporate Request Form America Flight Corporate Base Fare Discounted Airport Travels Tota Less than Fare Departure City Arrival CityFare Weekday CategoryDiscount Fare Fee Ticket Price Corp. Rate? 3 1049$ 1,875 499 175 6 1007$ 1,199 41016 $ 10 12 13 15 16 17 18 19 20 21 23 24 25 26 28 29 30 31 32 34 35 36 37 38 39 40 Requests Flights FeesDiscounts