EXCEL Capstone Assignment- European Excursions
Project Description:
You work for a travel company that specializes in arranging travel accommodations for student tours and vacations in exciting destinations such as Canada, Rome, and the Czech Republic. You created a workbook to store agent names, student IDs, and tour codes. The workbook also contains a work- sheet to store lookup tables. You will complete the workbook for your manager's approval. You will insert formulas and a variety of functions, convert data to a table, sort and filter the table, and prepare a chart.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Instructions
1 Start Excel. Open exploring_ecap_grader_h1_Trips.xlsx and save the workbook as e00a1Trips_LastFirst. Make sure the Lookup Tables sheet is active.
2 Assign the range name rates to the base price and commission range. Assign the range name tours to the data for the package, tour description, departure, and base cost.
3 Click cell D13 on the Data sheet and insert a VLOOKUP function that looks up the tour code, compares it to the tours table, and returns the description. Copy the function down through cell D22.
4 Click cell E13 and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the departure date. Copy the function down through cell E22.
5 Click cell F13 and insert a lookup function that looks up the tour code, compares it to the tours table, and returns the base cost of the trip. Copy the function down through cell F22.
6 In cell G13, insert a formula that adds taxes and fees to the base cost of the trip (in cell F13) by using the percentage value in the input area. Use a mixed reference to the cell containing 20% in the input area above the data. Copy the formula down through cell G22.
7 In cell H13, insert a PMT function to calculate the payments for students who want to pay for their trips in three installments. Use the interest rate and months in the input area above the data. Use appropriate relative, mixed, and/or absolute cell references in the formula. Make sure the result is a positive value. Copy the function down through cell H22.
8 Click cell I13 and calculate the agent commission using the base cost of the trip and a VLOOKUP function that returns the commission rate based on the base cost of the trip using the rates lookup table. The function should then calculate the monetary value of the commission. Copy the function down through cell I22.
9 Merge and center the main title on the first row over all data columns on the Data sheet. Apply bold and 18 pt font size. Merge and center the subtitle on the second row over all data columns.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also include a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also Include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.
Attachment:- Exploring_ecap_grader_h1_Trips1.rar