Statistics Computer Homework Excel

MGMT 201

Computer Homework #1

Note: Computer homework assignments are individual assignments and must be done without assistance from or collaboration with other members of the class. Collaboration on Computer Homework assignments will result in zero credit for the assignment.

The purpose of this assignment is to help you get started using Excel to perform some of the basic statistical functions we’ve talked about in class. The data set is 100 responses to a survey given last year to MGMT 201 students. The questions in the survey are shown below:

1.	Your age converted to months (e.g., 20 years and 3 months would be 243 months):
2.	Distance in miles from where you lived during high school to UofL:
3.	Your GPA:
4.	Your major or planned major:
5.	Your class year at UofL (F=freshman, S=sophomore, J=junior, R=senior, G=grad, O=other):
6.	Have you taken statistics previously, either in high school or college? (yes or no)
7.	Your level of anxiety about this course on a 1-5 scale with 1 = none and 5 = very high:
8.	Average number of hours per week you work (for pay):
9.	Number of other classes currently enrolled in: Indicate number (indicate 0, 1, 2 etc.)
10.	The average number of hours per week you study during a normal semester.

The spreadsheet with the survey results is posted in Blackboard with this assignment. The answers for the 100 students are arranged in columns in the spreadsheet. The requirements of this assignment are shown below. We will use this data set for at least one other computer homework assignment.

1. The first thing you will have to do create descriptive statistics for the all quantitative variables.

a. To do this, click on the Data tab at the top of the spreadsheet. The far right section of the menu bar should be labeled “Analysis” and should include an item titled “Data Analysis.” If Data Analysis does not appear, click on the File tab at the far left of the tab bar, then click on Options (near the bottom), then click on Add-Ins (next to the bottom of the list at the left of the window), then look at the bottom of the window and you’ll see “Manage:” next to a drop down box. Make sure “Excel Add-ins” appears in the box, then click “Go.” In the Add-Ins window that appears, check “Analysis ToolPak,” then click “OK.” Then restart Excel and “Data Analysis” should appear in the Data toolbar.

b. To create descriptive statistics for the responses to each quantitative question, Click on “Data Analysis” then click on “Descriptive Statistics.” Put the range of the responses you want descriptive statistics for in the “Input Range” filed, click on “Grouped By” Columns, check the “Labels in First Row” box, select “Output Range” and insert a cell address that you want to be the upper left corner of the output range, check the “Summary statistics” box and click “OK.” Make sure you include the variable in your Input Range.

c. Convert the “Class Year” data to numerical data by substituting 1 for F, 2 for S etc. so you can do descriptive statistics on the class year responses.

d. Below the descriptive statistics for each quantitative variable add a line with “CV” and next to it calculate the coefficient of variation for the question. Below that add a line with “RCV” and next to it put the region of concentrated values for the variable. Below RCV add a line “Outliers” and to the right list any outliers in the responses.

e. Make sure the count is 100 for all the quantitative variables.

2. Frequency distributions:

a. Have Excel create grouped frequency distribution for the Age(Months) and work hours variables. Make sure the frequency distributions include frequency, relative frequency and cumulative relative frequency.

b. Create Histograms for both Age(Months) and the work hours variables.

c. Create ungrouped frequency distributions for the qualitative variables -- “Major,” the “Class Year” and the “Stat Class ?”. For the Class Year data, you should have five values of the variable (Freshman,Sophmore, Junior, Senior, Graduate) and for the Major data you should have 11 values and for the “Stat Class?” question you will have two values. Make sure the frequency distributions include frequency, relative frequency and cumulative relative frequency.

d. Create Histograms for all qualitative variables.

Turn in a one-page Excel spreadsheet with descriptive statistics for all of the quantitative variables, the frequency distributions and the histogram. The page must be landscape oriented (one point) with your name in the upper left corner. Everything must be typed – any hand-written numbers, notes, corrections or explanations will be ignored.

Due Thursday, Jan 23th at the beginning of class.