file attached.
Building a Baseball Statistics Data Dashboard. The Springfield Spiders, a baseball team in the All American Baseball Association, wants to create a data dashboard for its fans. Spiders management would like the fans to be able to review the runs scored and allowed by game, and review the number of wins and losses and the average per game attendance by opponent and by day of the week. They would also like for the fans to be able to filter each of these displays by home and away games.
The Spiders have collected data on the date, opponent, whether the game was played at home or away, how many runs the Spiders scored, how many runs the Spiders allowed their opponent to score, whether the Spiders won or lost, and the attendance for each game of the previous season. They also added a field for the day of the week, and have created the following charts for inclusion in its data dashboard.
A line chart of runs scored and runs allowed by game (in the Chart1 worksheet)
A clustered column chart of number of wins and losses by month (in the Chart2 worksheet)
A clustered bar chart of average per game attendance across months for home and away games (in the Chart3 worksheet)
A clustered bar chart of average per game attendance across days of the week for home and away games (in the Chart4 worksheet)
1. Create a data dashboard for the Spiders by creating a new worksheet and naming it Dashboard; moving the charts from the Chart1, Chart2, Chart3, and Chart4 worksheets to the Dashboard worksheet; and repositioning the charts on the Dashboard worksheet, adding a title to the dashboard, and doing whatever formatting and editing is necessary to make the dashboard functional and visually appealing.
2. Amend the data dashboard you created in part b in the following ways.
i. Create a slicer to filter day of the week for the charts created in the Chart1, Chart2, Chart3, and Chart4 worksheets
ii. Create a slicer to filter opponent for the charts created in the Chart1, Chart2, Chart3, and Chart4 worksheets
iii. Create a slicer to filter month for the charts created in the Chart1, Chart2, Chart3, and Chart4 worksheets
iv. Create a slicer to filter home and away games for the charts created in the Chart1, Chart2, Chart3, and Chart4 worksheets
Once you have amended the data dashboard, rearrange the charts and slicers to create an effective and visually appealing dashboard. Test each slicer to ensure it works on the appropriate charts
3. Create an alternative display of the Runs Scored and Runs Allowed by game through the following steps.
STEP 1
Create the new field Run Differential in Column I of the SpidersData table by subtracting Runs Allowed from Runs Scored for each game
Refresh all PivotTables and PivotCharts
STEP 2
Create a new worksheet and rename the worksheet Chart1A
Create a new pivot table and pivot chart in the Chart1A worksheet
Drag the Date field to the Rows area and the Run Differential field to the Values area in the PivotFields Table pane to create a column chart
Right click the horizontal axis and click Format Axis… to open the Format Axis pane, click the Axis Options button and click Labels, then select Low from the Label Position dropdown menu to position the horizontal axis at the bottom of the chart
While still in the Format Axis task pane, click the Axis Options button and click Fill & Line, then in the Line area change the color of the axis to black and increase its width to 2 pt
Click any bar in the chart and click Format Data Series, click the Fill & Line button, and in the Fill area click the Invert if Negative box and select the colors to be applied to the positive and negative bars
STEP 3
Replace the line chart on the dashboard that was originally created in the Chart1 worksheet with the chart you created in the Chart1A worksheet.
Comment on differences between the resulting dashboard and the dashboard from part b.
4. Protect the data dashboard in part c from being revised by users. Ensure that the slicers cannot be resized or moved, password protect the Dashboard worksheet (use the password Problem823), and hide all worksheets except the Dashboard worksheet.
