Base Price	Commission		Package	Tour Description	Departure	Base Cost
$1,699	2.5%		10	Adventures in Canada	2016/6/11	$1,699.00
$1,749	3.0%		20	Check Out the Czech Republic!	2016/6/4	$1,799.00
$1,799	3.5%		30	Croatian Wonders	2016/6/11	$1,899.00
$1,849	4.0%		40	Live it Up London	2016/6/18	$1,699.00
$1,899	4.5%		50	Parisian Pleasures	2016/6/25	$1,749.00
			60	Roaming Rome	2016/6/11	$1,849.00
			70	Viva Espaa	2016/6/25	$1,799.00

1

Start Excel. Open exploring_ecap_grader_h1_Trips_start.xlsx. 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. Hint: When defining the ranges do not include the headings.

3

On the Data sheet, in cell D13, 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. Hint: D13 should display Check Out the Czech Republic!

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. Refer to the cell containing 20% in the input area above the data. Use relative and absolute cell references appropriately. Copy the formula down through cell G22. Hint: G13 should be about 2160 dollars.

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 relative and absolute cell references appropriately. Make sure the result is a positive value. Copy the function down through cell H22. Hint: The Student Payment Plan amount will be about 1/3 of the Total Cost with Taxes.

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. Hint: The commission in cell I13 should be about 63 dollars.

9

On the Data sheet, merge and center the main title on the first row over columns A through I. Format using the Title cell style. Merge and center the subtitle on the second row over columns A through I. Format using the Heading 2 cell style.

10

Apply Currency number format to the monetary values in columns F, G, H, and I. Hide the Tour Code column.

11

On the Data sheet, wrap text in the range F12:I12. Set the column widths for these columns to 13 (which equals 96 pixels or 1.14 inches). Freeze the panes so that rows 1 through 12 do not scroll offscreen.

12

Apply 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. Hint: Top/Bottom Rules

13

Insert 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.

14

Insert 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.

15

Select the Data (2) sheet. On the Data (2) sheet, convert the data range to a table. Apply the Table Style Medium 21 style to the table.

16

Sort the table by departure date from oldest to newest. Apply a filter to display trips arranged by agents Avery and Ross only.

17

Display a total row. Select a function that calculates totals for all monetary columns.

18

On the Summary sheet, create Line sparklines in column H to display six-month trends for each agent. Show the high point in each sparkline. Apply the SparkLine Style Accent 2, Darker 50% style.

19

Insert an IF function in column I that displays a $500 bonus if an agents average sales are greater than $4000. Use the fill handle to fill the formula down. Hint 1: You'll need to nest an AVERAGE function inside an IF function. Hint 2: Don't type 500 into your formula. Instead, refer to cell I10.

20

Create a clustered column chart of the agents and their six-month sales. The months (January, February, etc.) should be visible on the category (X) axis. Move the chart to a new chart sheet named Sales Chart.

21

Apply the Layout 1 chart layout. Type January-June 2016 Sales by Agent for the chart title. Apply the Style 14 chart style.

22

On the Summary sheet, create a footer with the sheet name code in the center, and the file name code on the right side. Hint: You may need to change to Normal view after this step.

23

On the Data (2) sheet, apply 0.2" left and right margins and scale to one page (height and width). Select Landscape orientation.

24

Save 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.