The purpose of this assignment is to use pivot tables and pivotcharts to aggregate data and to explain the process used for dataaggregation.

For this assignment, you will use the "Claims 2" dataset. Use Excel pivot tables and pivot charts for this exercise.

Part 1:

Create a dashboard describing the data by age group (e.g., 21-30 yrs,31-40 yrs, 41-50 yrs, 51-60 yrs, and 61-70 yrs). The dashboard shouldinclude the graphs and charts listed in the locations described. Thedashboard should be a separate tab in Excel that only includes the fiveitems below. A sample layout is provided below the dashboarddescription.

1. Top Left: Bar graph showing the average number ofER visits for each of the five age groups. Show the actual averagevalues above each bar.
2. Middle Left: Bar graph showing the average numberof procedures for each of the five age groups. Show the actual averagevalues above each bar.
3. Bottom Left: Bar graph showing the average claim cost for each of the five age groups. Show the actual average values above each bar.
4. Top Right: Pie chart showing the percent of thetotal sum of all claim costs for each of the five age groups. Show theactual percent values of each slice.
5. Bottom Right: Line graph showing the percent ofeach age group that has one or more ER visits. Show the actual percentvalues of each group. To create this chart, first create a newcalculated column, named "Has ER Visit," that is equal to 1 when thepatient has had one or more ER visits; otherwise 0. HINT: The average ofa 0-1 column is a percent. Refer to the example in the resource "VisualRepresentation of Data Screenshot: Preview of the Excel Dashboard."

Part 2:

Interpret the dashboard and the story it is attempting to tell usersby writing a 250-word summary that clearly describes the merits of eachof the charts used on the dashboard. For example, discuss why a piechart might be more appropriate than a bar graph for highlighting theinformation you want key stakeholders to obtain by studying that contenton the dashboard. Include specific discussion about why each specificchart is used to illustrate the data it represents.

APA format is not required, but solid academic writing is expected.

This assignment uses a grading rubric. Please review the rubric priorto beginning the assignment to become familiar with the expectationsfor successful completion.

You are not required to submit this assignment to Turnitin.

New Question 1:Data summarization is usually not enough when performing analysis. Mostof the time, adding context by telling a story about the data isnecessary to describe the analysis to others, especially those who arenot data-savvy. What are some general guidelines to follow to tell agood data story? What story elements or structure should be used toorganize the presentation? New Question 2: Consider your organization, or an organization you are most familiarwith. Explain the general process of data aggregation for a typicalmetric (e.g., sales revenue, cost per unit, etc.) used in theorganization. What specific charts are commonly used to visually depictthe data? What might be some areas for improvement regarding how thedata is visually presented?