Shelly Cashman Excel 2016 | Module 6: SAM Project 1b Go Adventure Tours Creating, Sorting, and Querying a Table GETTING STARTED Open the file SC_EX16_6b_FirstLastName_1

Subject:MS ExcelPrice:14.99 Bought3

Share With

Shelly Cashman Excel 2016 | Module 6: SAM Project 1b

Go Adventure Tours

Creating, Sorting, and Querying a Table

GETTING STARTED

• Open the file SC_EX16_6b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX16_6b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX16_6b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEP

1. Kris Hayden works at Go Adventure Tours. She wants to summarize tour information about Go’s tours into tables to help her train a new sales associate.

Switch to the 2018 Tours worksheet. Select the range A1:H12 and format the range as a table (with headers), using the Table Style Medium 24. (Hint: Depending on your version of Office, the Table style may appear as Rose, Table Style Medium 24.)

Use ScheduledTours as the name of the table.

2. Kris decides to add a new tour to the 2018 schedule.

Switch to the Air Info worksheet. Enter a new record into the end of the Air_Info table (in row 12) as shown in Table 1 below:

Table 1: New Air_Info Record

3. Sort the Air_Info table from smallest to largest by the Price field values.
4. On the Air Info worksheet, Kris wants to display only tours that have air included.

Use an advanced filter to copy all the records for the tours that include air from the the Air_Info table into a new range as described below:

4. 1. Enter Yes into cell G16 to set up the value to filter on in the criteria range. 
   2. In the Advanced Filter, use the range A1:H12 as the List range, the range A15:H16 as the Criteria Range, and copy the records to the range A18:H18. (Hint: 4 records will be copied from the Air_Info table data.)
5. Switch to the 7-Day Tours worksheet. Apply Wrap Text formatting to the range E1:G1.
6. Remove the duplicate record in the Seven_Days table.
7. Seven-day tours are the most popular tours sold by Go and Kris wants to highlight these tours in the table.

Filter the table to display only those records with a Number of Days value of 7.

8. Kris wishes to show her sales associate a snap-shot of the number of seats reserved and seats available for Go’s tours. Kris already sorted the table containing this data, she just needs to insert subtotals into the table.

Switch to the Subtotals worksheet. Convert the Subtotals table (in the range A1:H24) into a range.

9. After converting the table to a range, insert subtotals into the range A1:H24 using the following options:
   1. Subtotals should be inserted at each change in the Tour value.
   2. The subtotals should use the Sum function.
   3. Subtotals should be added to the Seats Reserved and Seats Available values only.
   4. The subtotals should replace current subtotals and include a summary below the data.
10. Kris wishes to summarize the number of tours offered for certain locations and the deposits collected for these tours.

Switch to the Tour Info worksheet and complete the following steps to identify how many seats are available for each tour.

10. 1. In cell C2, enter a formula using the COUNTIF function that counts the number of Corfu Sailing Voyage tours offered. Use Corfu Sailing Voyage as the criteria argument, and Tour_Info[Tour] as the range argument in your formula.
    2. In cell C3, enter a formula using the COUNTIF function that counts the number of Nepal Trekking tours offered. Use Nepal Trekking as the criteria argument, and Tour_Info[Tour] as the range argument in your formula.
    3. In cell C4, enter a formula using the COUNTIF function that counts the number of Hiking Patagonia tours offered. Use Hiking Patagonia as the criteria argument, and Tour_Info[Tour] as the range argument in your formula.
11. Complete the following steps to determine the total deposits received for each tour.
    1. In cell D2, enter a formula using the SUMIF function that totals the amounts in the Deposits Recieved column for all Corfu Sailing Voyage tours. Use Corfu Sailing Voyage as the criteria argument, Tour_Info[Tour] as the range argument, and Tour_Info[Deposits Received] as the sum_range argument in your formula.
    2. In cell D3, enter a formula using the SUMIF function that totals the amounts in the Deposits Recieved column for each Nepal Trekking tour. Use Nepal Trekking as the criteria argument, Tour_Info[Tour] as the range argument, and Tour_Info[Deposits Received] as the sum_range argument in your formula.
    3. In cell D4, enter a formula using the SUMIF function that totals the amounts in the Deposits Recieved column for each Hiking Patagonia tour. Use Hiking Patagonia as the criteria argument, Tour_Info[Tour] as the range argument, and Tour_Info[Deposits Received] as the sum_range argument in your formula.
12. In the range D8:D29 (the Seats Reserved column), create a new Icon Set conditional formatting rule as described below:

Use the 3 Traffic lights (Unrimmed) icon set in your conditional formatting rule.

12. 1. Display the green, circular icon in all cells with a Number type value greater than or equal to 12.
    2. Display the yellow, circular icon in all cells with a Number type value less than 12 and greater than or equal to 6.
    3. Display the red, circular icon in all cells with a Number type value less than 6.
13. Apply a custom sort on multiple fields to the Tour_Info table, so that the table is first sorted by the Tour field in ascending (A to Z) order, and then by the Depart Date field in ascending (Oldest to Newest) order.
14. Add a Total Row to the Tour_Info table. (Hint: The total row should appear in row 30 with a total for Deposits Received automatically appearing in cell G30.)

Using the total row, display the SUM of the Seats Reserved in cell D30.

15. Upon review of the Tour_Info table, Kris notices that a column displaying the remaining number of seats needs to be added to the table.

Add a calculated column to the Tour_Info table (in the range A7:G30) as described below:

1. 1. In cell H7, enter the text Seats Available as the calculated column’s heading.
   2. In cell H8, enter a formula without a function using structured references that subtracts the value in the Seats Reserved column from the value in the Seat Capacity column. Use [Seat Capacity] and [Seats Reserved] as the structured references in your formula.

The formula should autofill into the range H8:H29. If it does not, copy the formula into the range.

15. 3. Using the table’s total row (in row 30), SUM the values in the Seats Available column.
    4. Copy the formatting of the Seat Capacity column to the Seats Available column.
16. Internally, Go uses tour codes when tracking tours. Kris wants to set up a simple lookup table to make it easier for her new sales associate to identify a tour by the tour code when assisting customers.

Switch to the Lookup worksheet. In cell H3, create a formula using the VLOOKUP function to determine the tour name associated with the tour code using the following parameters:

The formula should use cell H2 (the Tour Code) as the lookup_value, the range A1:E22 as the table_array, 2 as the col_index_num, and FALSE as the range_lookup value.

17. In cell H4, create a formula using the VLOOKUP function to display the depart date of the tour using the following parameters:

The formula should use cell H2 (the Tour Code) as the lookup_value, the table named Lookup as the table_array, 3 as the col_index_num, and FALSE as the range_lookup value.

18. Kris also wants to highlight the number of tours that are priced under $2,000, as well as the average price of the Amazing Amazon tours.

In cell H8, create a formula using the DCOUNT function to count the number of tours priced under $2,000, using the range A1:E22 as the database, “Price” as the field, and the range G6:G7 as the criteria.

19. In cell H13, create a formula using the DAVERAGE function to determine the average price of Amazing Amazon tours, using the range Lookup[#All] as the database, “Price” as the field, and the range G11:G12 as the criteria.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the document, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: 2018 Tours Worksheet

Final Figure 2: Air Info Worksheet

Final Figure 3: 7-Day Tours Worksheet

Final Figure 4: Subtotals Worksheet

Final Figure 5: Tour Info Worksheet

Final Figure 6: Lookup Worksheet