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

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 should include the graphs and charts listed in the locations described. The dashboard should be a separate tab in Excel that only includes the five items below. A sample layout is provided below the dashboard description.

1. Top Left: Bar graph showing the average number of ER visits for each of the five age groups. Show the actual average values above each bar.
2. Middle Left: Bar graph showing the average number of procedures for each of the five age groups. Show the actual average values 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 the total sum of all claim costs for each of the five age groups. Show the actual percent values of each slice.
5. Bottom Right: Line graph showing the percent of each age group that has one or more ER visits. Show the actual percent values of each group. To create this chart, first create a new calculated column, named “Has ER Visit,” that is equal to 1 when the patient has had one or more ER visits; otherwise 0. HINT: The average of a 0-1 column is a percent. Refer to the example in the resource “Visual Representation of Data Screenshot: Preview of the Excel Dashboard.”

Part 2:

Interpret the dashboard and the story it is attempting to tell users by writing a 250-word summary that clearly describes the merits of each of the charts used on the dashboard. For example, discuss why a pie chart might be more appropriate than a bar graph for highlighting the information you want key stakeholders to obtain by studying that content on the dashboard. Include specific discussion about why each specific chart 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 prior to beginning the assignment to become familiar with the expectations for successful completion.

You are not required to submit this assignment to Turnitin.

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

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.