6.6 Scored Assessment

A Multiple Sheet workbook for National Parks Data

Data file: none

You are working at the Headquarters of the National Parks Department. One of your jobs this week is to develop a workbook for individual park visitor data, along with a Summary sheet of four of the parks visitation data. To do this, you will need to design and create a workbook with summary and individual park worsheets, and then enter park data for four national parks: Blue Ridge Parkway, Crater Lake, Yellowstone, and Yosemite.

When designing your worksheets, be intentional about your formatting choices. Think about font sizes, fill colors, bold and number formatting to make your worksheet aesthetically pleasing. Make sure that all of your worksheets are formatted consistently. In addition, search for and insert  a relevant park image into every worksheet. You will enter the visitor data later in the assignment after you have created the individual park and summary worksheets.

 

  1. Open a blank Excel workbook. Save the file as SC6 National Parks.
  2. Design a professionally formatted worksheet that will be used to display the individual park visitor data (this worksheet will be copied in later steps). Include an area to enter the name of the individual park and the park visitor categories listed in Figure 6.16.  Name the worksheet Park Data. 
  3. Create four copies of the Park Data worksheet, one for each of the national parks listed in Table 4 below.   Be sure to rename each worksheet!
  4. Enter the data from Table 4 below into the correct park worksheet.  Insert a picture of the park and place it appropriately on the worksheet.

Table 4: 2018 National Park Visitors

Blue Ridge Pkwy Crater Lake NP Yellowstone NP Yosemite NP
Recreation
Visits
14,862,422 696,337 4,104,444 3,896,657
Non-recreation Visits 1,942,260 49,600 1,223,591 144,420
Concessioner
Lodging
Overnights
46,001 34,614 663,150 660,362
Concessioner
Camping Overnights
0 42,039 616,194 0
Tent Overnights 59,056 6,814 88,304 378,201
Recreation
Vehicle Overnights
34,158 0 73,872 270,740
Backcountry Overnights 787 2,641 42,215 124,725
Misc. Overnights 1,294 0 11,834 18,345

 

  1. Rename the Park Data worksheet to Summary Park Data. Change the Park Name text so that it is appropriate  for the Summary sheet.
  2. In the Summary Park Data sheet, create 3D formulas for each category that add up the visitors in the four park sheets.
  3. Insert a picture that represents National Parks.
  4. Group the worksheets and add a Custom Footer that shows the Sheet Name in the center of the footer.
  5. Preview the entire workbook in Print Preview to ensure that it is printing professionally. Make any changes needed.
  6. Check the spelling on all of the worksheets and make any necessary changes. Save the SC6 National Parks workbook.
  7. Submit the SC6 National Parks file as directed by your instructor.

 

National Park 2018 Fiscal Year Visitation Report Data from: https://irma.nps.gov/Stats/SSRSReports

Attribution

“6.6 Scored Assessment” by Mary Schatz and Diane Shingledecker, Portland Community College is licensed under CC BY 4.

License

Icon for the Creative Commons Attribution 4.0 International License

Beginning Excel 2019 by Noreen Brown, Barbara Lave, Hallie Puncochar, Julie Romey, Mary Schatz, Art Schneider, and Diane Shingledecker is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.