You are working in the National Headquarters of the National Parks Department. One of your jobs this week is to develop a template for individual park use data, along with a Summary of the parks visitation data. To do this you will need to create a template with summary and individual park sheets, and then use the template to enter park data for five national parks. 1. Open a blank spreadsheet. 2. Save the file as a template called SC7 National Parks Template.xitx. 3. Design a professional quality sheet to display individual park data for the most recent calendar year. Include areas to enter the name of a park and the park statistics. Name the sheet Park Data. Figure 6.17 below is an example of how you could set this up. Figure 6.17 Sample setup 4. The statistics available for parks may have changed. Skip to step 14 below to do a quick search for data on any national park to see what is provided. As of September 2022, the data provided is: Recreation Visitors, Non-Recreation Visitors, Concession Lodging, Tent Campers RV Campers, Concession Camping, Backcountry Campers, Misc Campers, Total Overnight Stays. 5. Make a copy of the Park Data sheet making it the first sheet in the workbook, and rename it Summary Park Data. Change any text needed for the Summary sheet. Make sure both sheets are laid out exactly the same. 6. Insert the provided park image in an open area of the Summary Park Data worksheet at the top. Alternatively- research how to insert a background image into a worksheet! Adjust column widths so that all data will be displayed over lighter areas of the image. This may be challenging and may require you to add additional columns or rows to adjust data placement. 7. Make sure Park Data is the last sheet in the workbook, then make a copy naming it Park Data N, placing it last in the workbook. 7. Make sure Park Data is the last sheet in the workbook, then make a copy naming it Park Data N, placing it last in the workbook. 8. In the Summary Park Data sheet, create formulas to sum all of the data fields: For each statistic on the Summary Park Data sheet, create a 3D cell reference that sums the values in the corresponding cells of the Park Data through the Park Data N worksheets so that the totals for all the parks are accumulated for each statistic. 9. Protect the Summary Park Data sheet. Do not specify a password. All cells should remain locked. 10. On both the Park Data and Park Data N worksheets, select the cells where the park statistics will be entered, and unlock those cells. Individually protect both sheets. Do not specify a password. Under Allow all users of this worksheet to: deselect all check boxes except for Select unlocked cells. 11. Preview the entire workbook in Print Preview to ensure that it is printing professionally. Make any changes needed. 12. Save your template SC7 National Parks Template.xltx. Close the template once it has been saved. 13. Make a new file using the template and save it as SC7 Five National Parks.xlsx. 14. Make three copies of the Park Data sheet being careful to place the new sheets between the two original Park Data sheets. Visit the National Park Service website ⊟ to see a park reports page similar to the following: 15. Choose any of the five national parks, and obtain statistics for each park by generating a "Summary of Visitor Use By Month and Year" report for each park. 16. Rename the five Park Data sheets after the five parks you chose. 17. Enter the park data for each of the parks in each of the five sheets. 18. Save the SC7 Five National Parks workbook. \begin{tabular}l \hline &
Blue Ridge
Pkwy
&
Crater
Lake NP
&
Yellowstone
NP
&
Yosemite
NP
\\ \hline
Recreation
Visits
& 14,862,422 & 696,337 & 4,104,444 & 3,896,657 \\ \hline
Non-recreation
Visits
& 1,942,260 & 49,600 & 1,223,591 & 144,420 \\ \hline
Concessioner
Lodging
Overnights
& 46,001 & 34,614 & 663,150 & 660,362 \\ \hline
Concessioner
Camping
Overnights
& 0 & 42,039 & 616,194 & 0 \\ \hline Tent Overnights & 59,056 & 6,814 & 88,304 & 378,201 \\ \hline
Recreation
Vehicle
Overnights
& 34,158 & 0 & 73,872 & 270,740 \\ \hline
Backcountry
Overnights
& 787 & 2,641 & 42,215 & 124,725 \\ \hline
Misc.
Overnights
& 1,294 & 0 & 11,834 & 18,345 \\ \hline \end{tabular}