Question

Follow all the instructions and guidance for this assignment. Use the formulas we reviewed in class, in addition to the absolute value ($) function.

Question 1:

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 Module 5, 5.1, 5.2. 5.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.

Question 3:

Name the four ways that distributions (shapes) can be different from one another. Describe and differentiate between these four types of distributions.

Question 4:

What types of charts have you seen presented? What type of data are displayed in these different charts? For example, why might some want to use a bar chart? What about a line chart? How about a pie chart? When do you think these different ways to display data would be used?

Answer 1

1.

Constructing a frequency distribution, a relative frequency distribution, a cumulative less distribution, and a cumulative more distribution