Project: NPS Visitation Problem
Project Description: Students will analyze statistics about visitation to National Park Service units in the eastern United States.
Instructions
1 Start Excel. Open the downloaded Excel workbook named NPS_Visitation.xlsx.
2 Format cells A3 through N184 as a table using a style of your choice.
3 We need to add an additional table column to store average visitation statistics on the Visitation sheet.
Insert one new table column to the right of existing column N.
4 For the table, turn on the Total Row option.
5 Enter text in the cell as indicated below:
O3: Average
6 Merge-and-center cells A1 through O1.
7 Apply the Title cell style to cell A1.
8 Format the cells as indicated below:
D4 through O185: number with no decimal places, use 1000 separator
9 In column O, use the AVERAGE() function to determine the average number of visitors to each NPS unit from 2003 to 2013.
10 In the total row, individually find the sums for columns D through N.
11 In the total row, do not display any statistics in column O.
12 AutoFit the widths of columns A through C. Set the widths of columns D through O to 11.
13 Create a 2-D line chart based on the non-contiguous cell range A3, D3 through N3, A185, and D185 through N185 of the Visitation sheet. Move the chart to a new sheet named Total Visitation Chart.
Ensure that the years are shown as labels for the horizontal (category) axis, not plotted as chart data. Specify appropriate chart and axis titles.
14 Add a trendline to the Total Visitation Chart based on the total visitation. Use the trendline type that best fits the data and project the values forward 10 periods (through the year 2023). Display the R-squared value on the chart.
Note: You cannot use the Moving Average type for your trendline.
15 Create a new PivotChart and PivotTable based on the data in cells A3 through O184 of the Visitation sheet. Place the PivotTable on a new sheet named Visitation PivotTable and move the PivotChart to a new sheet named Visitation PivotChart.
16 On the PivotTable, do the following:
- Add the park type as a Rows field.
- Add the average visitation as a Values field.
17 On the PivotTable, summarize the average visitation figures by summing them.
18 Format the PivotTable cells as indicated below:
- Visitation column: number with no decimal places, use 1000 separator
19 We need to perform some formatting on our PivotChart.
- Ensure the chart is a 2-D clustered column chart.
- Specify appropriate chart and axis titles.
- Turn off display of the legend.
Attachment:- NPS_Visitation.xlsx