Suppose a clinic is interested in the number of visits per person per year.  After selecting 25 individuals from the…

Suppose a clinic is interested in the number of visits per person per year.  After selecting 25 individuals from the insured population, the following data were obtained:

 

1, 2, 5, 3, 7, 8, 0, 4, 3, 7, 10, 2, 2, 4, 2, 6, 7, 8, 3, 2, 1, 1, 6, 5, 0

 

Use Excel to construct a frequency distribution, a relative frequency distribution, and a cumulative frequency distribution.  Also, construct a pie chart from the relative frequency distribution (%) and a bar chart and column chart from your frequency distribution data.  Create a summary descriptive statistics table in Excel using the Descriptive Statistics Data Analysis tool  (see slides from Units 2 & 3) and describe this dataset using measurements of central tendency and variability (range, mean, mode, median, standard deviation).    

 

·        

Leave all of your original data (the numbers used to calculate everything) on the sheet

·         Use bin values of 3, 7, and 11.  This should be the first column in your newly created table.

·         Use the Excel “Histogram” tool to create your frequency table.  Leave this table in your Excel sheet to “show your work.”

·         Create a new table into which you will paste data from the histogram table Excel creates for you.

·         Use bin values of 3, 7, and 11.  This should be the first column in your newly created table.

·         To the right of your BIN values column, put another descriptive column denoting the frequency distributions: 0 to 3, 4 to 7, and 8 to 11.

·         Paste the data from your histogram table into the new table you have created.

·         Add a new descriptive column in your table to identify your cumulative frequencies (i.e., 0 – 3, 0 – 8, 0 – 11).  This column will be just to the left of the cumulative frequencies created by your data analysis toolpak table.

·         Add a new title above your descriptive statistics table identifying your data.

·         Use the absolute cell function ($) to create your relative frequency column (see PP slides).

·         Delete “Series 1” from your charts or replace it with the appropriate verbiage.

·         Add data labels in each chart.

·         Use Exhibits 4.1 and 4.6 as guides. 

·         LABEL each axis (x and y) and put an appropriate title on your chart. 

·         In approximately three – five sentences, tell me a little about your data (groups, min/max, frequencies, central tendency, variability, etc.) in “words your grandmother would understand.” 

 

Question 2:

 

The following data depict the occupancy rate, expressed as a percentage, during the past 12 months (Jan – Dec):

 

97, 98, 95, 94, 93, 91, 89, 90, 87, 88, 86, 85

 

Use Excel to construct a line chart depicting monthly changes in the occupancy rate.  Describe the implications of the chart and the general trend.