Use the information needed for week 6 and answer the questions
1 Create a pie chart for the variable insurance in excel:
·
Review this video to learn how to create a pie chart in excel.Links to an external site.
· First, you have to create a frequency distribution table using the data from insurance column from the database.
Example Table
Here is an example of what your table should look like:
|
A |
B |
|
Insurance |
Frequency |
|
Blue Cross Blue Shield |
{Type the number of those that have this insurance} |
|
Health Select |
{Type the number of those that have this insurance} |
NOTE: This is only an example of what your table should “look” like and how it should be organized. You should have many more insurance companies and the frequencies should be based on what you count (for each insurance company) from the main database.
· Once completed, highlight all of the cells of your completed frequency table, by selecting and dragging your pointer through all of the cells.
· Select on Insert, and then Recommended Charts.
· Select to select the Pie Chart.
· Select on the pie slices, right-click Add Data Labels, and select Add Data Callouts. Add an appropriate title.
· Make sure to highlight Category Name and Percentages on each sector.
· Make sure to highlight percentages on each sector.
· For some of you, the options to add titles and format your pie chart are located in your toolbar(s); so, explore your own version of Excel
· Save the worksheet to your computer and upload your file by selecting the “Choose a File” button below.
2 What is the most utilized insurance company among those that are surveyed in this database? Include the percentage to support your statement.
Question 3
Create a bar graph for the variable satisfaction in excel:
·
Review this video to learn how to create a bar graph in excel.Links to an external site.
· Use the strategies in the video to create a frequency table of the satisfaction levels using the categories: Completely Dissatisfied, Dissatisfied, Average, Satisfied, and Completely Satisfied.
· Tip: It may be helpful to sort the data based on the satisfaction variable first.
· Don’t forget that you should have a column for “Satisfaction Levels” and a column for “frequencies”.
· Fill in the frequencies of each category.
· After the frequency table is complete, highlight the frequency table, and select Insert, then Recommended Charts, and choose the column chart shown and select OK.
· Add an appropriate title and axis label.
· Save the file to your computer again and upload the file by selecting the “Choose a File” button below.
·
Question 4
· Make a statement based on the bar graph created. Research ideas on how satisfaction levels can be improved. Make sure to cite at least one source.
5 Create a Scatter Plot with Trendline for the variables wait time and satisfaction in Excel:
·
Review this video to learn how to create a Scatter Plot in ExcelLinks to an external site.
· Use the strategies in the video to create a Scatter Plot of the wait time and satisfaction.
· Select the two columns
· Click Insert – Charts – Scatter Plot
· Click on the graph – Click the + sign in the top right – Axis Titles & Trendline
· Add an appropriate title and axis labels.
· Save the graph to the appropriate location
Question 6
Review the Trend of the data in the Scatter Plot graph created in question 5.
A. Describe using a complete sentence the slope of the trendline (Increasing, Decreasing, Constant).
B. Make a statement on the slope of the trendline and what it tells us about the data.
C. Describe how you would explain this data to a medical office manager?
Question 7
Looking at “Out of Pocket Pay”, what would be the best way to display this data? Why? What key points would you expect the viewer to extract from the graph?
