Tables for a Tourism Company

Download Data File: PR5 Data

Travel and tour companies need to keep track of client data, as well as, travel/tour options and tour guides. Keeping up-to-date, accurate records is essential to their bottom line. To run a tour company, employees must be able to manipulate their data quickly and easily. This exercise illustrates how to use the skills presented in this chapter to generate the data needed on a daily basis by a tourism company. See Figure 5.30 above.

1. Open the data file PR5 Data and save the file to your computer as PR5 Canyon Trails.
2. Insert a table based on A3:J53 (Excel will automatically select the range, you just need to confirm it).
3. In Column J, calculate Total Cost (number of Guests *Per Person Cost).
4. Format Columns I and J with Currency number format and no decimal places.
5. Center all headings in Row 3.
6. Adjust column widths within the table so that all the headings are completely visible.
7. Rename Sheet 1 Current Tours. Sort this sheet alphabetically (A to Z) by Last Name.
8. Make a copy of the Current Tours sheet and rename it Tours by Canyon.  Place the Tours by Canyon sheet to the right of the Current Tours sheet. Sort this sheet by Tour Canyon (A to Z), then Home Country (A to Z), and then Last Name (A to Z). Insert subtotals by Tour Canyon for numbers of Guests.
9. Make another copy of the Current Tours sheet and rename it US Guests. Place the US Guests sheet to the right of the Tours by Canyon sheet.  Filter this sheet so that only guests with a Home Country of the United States show. Sort the filtered data alphabetically (A to Z) by Tour State. Add a Total Row that sums the Guests and Total Cost columns.
10. Make another copy of the Current Tours sheet and rename it European Guests.  Place the European Guests sheet to the right of the US Guests sheet. Hide the Average Age column.
11. Insert a slicer in the European Guests sheet for Home Country. Move the top left corner of the slicer to the top left-hand corner of cell K3. Change the width of the entire slicer to 1.65”.
12. Select both Germany and the United Kingdom on the slicer. Sort the filtered sheet by Home Country (A to Z) and then Last Name (A to Z).
13. Make one more copy of the Current Tours sheet and rename it Tours by State. Place the Tours by State sheet to the right of the European Guests sheet. Convert to a range. Subtotal the sheet by State, summing the Total Cost column.
14. Change the name of the Tours by State sheet to 5-7 Day Tours by State. Filter out 3-day tours in the table.
15. On each worksheet, make the following print setup changes:
    1. Add a footer with the worksheet name in the center.
    2. Change to Landscape Orientation
    3. Set the scaling to Fit All Columns on One Page
16. For any worksheets that print on more than one page, add Print Titles to repeat the first three rows at the top of each page.
17. Save the PR5 Canyon Trails workbook.
18. Make sure your sheets are in the following order from left to right: Current Tours, Tours by Canyon, US Guests, European Guests, and 5-7 Day Tours by State.
19. Use your file to answer the practice assessment questions.
20. Save your work for your records.

Attribution

“5.4 Chapter Practice” by Diane Shingledecker, Portland Community College is licensed under CC BY 4.0

“Canyon Trails Data File” by Matt Goff is licensed under CC BY 3.0