[COMPLETED] Tables for a Tourism Company

Tables for a Tourism Company

 

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.

When saving files for submission, include your last name in the file name.

1. Open the data file PR5 Data from the Data Files and save the file to your computer as PR5 Canyon Trails.
2. In Column J, calculate Total Cost (Number of Guests *Per Person Cost). Copy the formula down the column.
3. Format Columns I and J with Currency and no decimal places.
4. Center all headings in Row 3.
5. Click in cell A3. Insert a table with headers for the range A3:J53.
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).
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 one more copy of the Current Tours sheet and rename it Tours by State. Subtotal the sheet by State, summing the Total Cost column.
11. Change the name of the Tours by State sheet to 5-7 Day Tours by State. Filter out 3-day tours in the table.
12. On each worksheet, make the following print setup changes:
    1. Change to Landscape Orientation
    2. Set the scaling to Fit All Columns on One Page
13. 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.
14. Save the PR5 Canyon Trails workbook.
15. Make sure your sheets are in the following order from left to right: Current Tours, Tours by Canyon, US Guests, and 5-7 Day Tours by State.
16. Submit the PR5 Canyon Trails workbook as directed by your instructor.