Excel project due Friday please help EXP E Capstone 1 - European Excursions 2.5 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: StepInstructionsPoints Possible 1Start Excel. Open exploring_ecap_grader_h1_Trips.xlsx and save the workbook as e00a1Trips_LastFirst. Make sure the Lookup Tables sheet is active.2Assign 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. 3Click 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.4Click 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.5Click 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.6In 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.7In 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.8Click 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.9Merge 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.10Apply Currency number format to the monetary values in columns F, G, H, and I. Hide the Tour Code column.11Wrap text in the range F12:I12. Set the column widths for these columns to 11, if necessary. Adjust the row height, if necessary. Freeze the panes so that the row of column labels does not scroll offscreen.12Apply the Light Red Fill with Dark Red Text conditional formatting to values in the Total Cost with Taxes column when the values are above average.13Insert a function to calculate the total for all trips (including taxes) in cell G5, the average trip cost in cell G6, and the median trip cost in cell G7.14Insert a function to calculate the lowest trip cost in cell G8 and the highest trip cost in cell G9. Click cell G10 and enter a function to display today's date.15Copy the Data sheet and place the copied sheet before the Summary sheet. Remove the conditional formatting rule on the Data (2) sheet.16Convert the data range in the Data (2) sheet to a table. Apply the Table Style Medium 21 style to the table.17Sort the table by departure date from oldest to newest and then alphabetically by trip description. Apply a filter to display trips arranged by agents Avery and Ross only.18Display a total row. Select a function that calculates totals for all monetary columns.19Create Line sparklines in column H in the Summary sheet to display six-month trends for each agent. Show the high point in each sparkline. Apply the SparkLine Style Accent 1, Darker 50% style. If needed, apply the Blue, Accent 1 high point marker color. 20 Insert an IF function in column I that displays a $500 bonus if an agent?s average sales are greater than the average of all sales for the six months. Use two nested AVERAGE functions in the logical_test argument of the IF function to make the comparison.21Create a clustered column chart of the agents and their six-month sales, using the chart type that displays the months on the category axis. Move the chart to a new chart sheet named Sales Chart. 22Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agent for the chart title. Apply the Style 14 chart style.23Create a footer with the sheet tab code in the center, and the file name code on the right side of each sheet.24Apply 0.2" left and right margins and scale to one page (height and width) for the Data and Data (2) sheets. Select Landscape orientation for the Data (2) sheet.25Save the workbook. Ensure that the worksheets are named correctly and in the following order: Lookup Tables, Data, Data (2), Sales Chart, and then Summary. Close the workbook and exit Excel. Submit the workbook as directed.

Base Price $1,699 $1,749 $1,799 $1,849 $1,899 Commission 2.5% 3.0% 3.5% 4.0% 4.5% Package 10 20 30 40 50 60 70 Tour Description Adventures in Canada Check Out the Czech Republic! Croatian Wonders Live it Up London Parisian Pleasures Roaming Rome Viva Espaa Departure 6/11/2016 6/4/2016 6/11/2016 6/18/2016 6/25/2016 6/11/2016 6/25/2016 Base Cost $1,699.00 $1,799.00 $1,899.00 $1,699.00 $1,749.00 $1,849.00 $1,799.00 European Excursions Agent Sales and Commissions Input Area Taxes and Fees 20% Loan APR 7% Months Per Year 12 Total Payments 3 Agent Name Avery Collins Avery McDonnough Ross Shannon Collins Ross McDonnough Ross Student ID 10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 Summary Statistics Total for all trips: Average trip cost: Median trip cost: Lowest cost: Highest cost: Today's date: Tour Code 20 30 10 50 60 70 10 40 50 60 Trip Description Departure Date Base Cost of Total Cost with Taxes Payment Plan Commission Trip Student Agent 2016 Total Sales by Agent January - June Avery Collins Kinsley McDonnough Ross Shannon January $ 3,671 $ 1,549 $ 2,996 $ 2,003 $ 4,038 $ 1,172 February $ 2,973 $ 992 $ 6,948 $ 4,190 $ 2,064 $ 866 March $ 4,017 $ 2,267 $ 4,205 $ 3,496 $ 7,015 $ 2,109 $ $ $ $ $ $ April 5,040 3,201 5,523 1,975 6,832 3,385 $ $ $ $ $ $ May 4,963 4,008 6,597 2,936 5,971 4,078 $ $ $ $ $ $ June 5,497 3,798 5,147 3,498 5,497 3,598 Trends Bonus Bonus $ 500