Part 1 |
Four states have been randomly assigned from the college data set
(blue worksheet/tab
) to each student. These state assignments are found on Sakai under Resources in the same subfolder as this file.
- Copy paste all the college data (rows and columns) from the Source Data Set into this spreadsheet, along with the column headings.
- There should be no spaces between states, which would be four tables; it is one data file.
- Please remember to address the Formatting of Excel results in Part 9.
This is your personal data set for this assignment. (Please see the Tutorial PPTs in Sakai under Resources under College Data Analysis.)
Backup this dataset.
|
1 |
|
|
|
|
|
|
Part 2 |
Charting - Identify each state # with its 2-digit abbreviation.
Use the summary data (in yellow) for each of the charts.
Label appropriate axes.
|
Points |
Points Lost |
1. Display only the relevant column(s) of data, with column headings. |
1 |
|
2. Produce one column chart on the number of application accepted by state, including title and axes' labels. |
2 |
|
3. Produce one pie chart on the number of applications received by state. |
2 |
|
4A. Calculate 4 state acceptance rates (%); formulae in Excel need to be visible on-line for the calculation of the acceptance rate (%). |
2 |
|
4B. Produce a chart (other than a pie, bar chart, or column chart) on the calculated acceptance rate (%) by state, including title and axes' labels. |
2 |
|
|
|
|
|
|
|
|
|
|
Part 3 |
Calculate Descriptive Statistics, with a 99% confidence interval from Data Analysis, for the out-of-state tuition for your combined data. For your four states combined, display the full set of Data/Data Analysis/Descriptive Statistics below, with one 99% confidence level value for your four states combined. |
Points |
Points Lost |
1. Display only the relevant column(s) of data, with column headings. |
1 |
|
2. For your four states combined, display the full set of Data/Data Analysis/Descriptive Statistics below, with the 99% confidence level value for your four states in the aggregate. |
3 |
|
3A. For all states combined, calculate one 99% confidence level, the mean and the upper and lower confidence interval in the template provided to the right.
|
4 |
|
3B. Summarize the confidence interval in words here: |
2 |
|
4. Calculate the Coefficient of Variation for your data. |
2 |
|
5. Formulae must be displayed in Excel. |
1 |
|
|
|
|
|
|
Part 4 |
Pivot Table: With any three variables in the data set, create either a table or a graphic using Pivot Tables.
See the two YouTube videos on Sakai under Resources in the folder College Data Analysis. |
Points |
Points Lost |
1. Copy all your data for your four states into the Pivot space (or new spreadsheet). |
1 |
|
2. The pivot table report/chart needs to reflect at least 3 of these variables. |
1 |
|
3. Metric (counts, averages, etc.) are appropriate. |
2 |
|
4. The chart must make sense. |
2 |
|
5. Include appropriate title/labels. |
1 |
|
|
|
|
|
|
Part 5 |
Calculate all correlations for all data, excluding college name, state, and public/private indicators. |
Points |
Points Lost |
1. Display only the 10 relevant column(s) of data, with column headings. |
2 |
|
2. Please include your Excel full correlation matrix over the 10 variables. |
3 |
|
3. Apply conditional formatting over the correlation matrix. |
1 |
|
4A. Select and copy 2 of the 10 variables; plot the data for these 2 variables as one combined scatter diagram reflecting the data for your four states. |
2 |
|
4B. Include the title, and label both axes. |
2 |
|
4C. Describe the scatterplot. |
1 |
|
5. Quantify the size of the correlation here: |
1 |
|
|
|
|
|
|
Part 6 |
Statistically, is the mean SAT Verbal score for public colleges/universities the same than for private colleges/universities? α = 0.10. |
Points |
Points Lost |
1A. Display only the relevant column(s) of data, with column headings. |
1 |
|
1B. Sort the data by public/private. |
1 |
|
2A1. For this study, invoke the one appropriate function in Excel.
Please bold and underline your selection here.
t-Test: Paired Two Sample for Means
t-Test: Two Sample Assuming Equal Variances
t-Test: Two Sample Assuming Unequal Variances
z-Test: Two Sample for Means
|
4 |
|
2A2 - Justify your selection for 2A1 here. |
2 |
|
2A3. Display your Excel output. |
2 |
|
3. Place your responses as follows: |
|
|
3A. Null Hypothesis: |
2 |
|
3B. Alternative Hypothesis: |
2 |
|
3C. Alpha Level: |
1 |
|
3D. Critical Value(s): |
1 |
|
3E. Test Statistic(s): |
1 |
|
3F. Decision to Reject/Fail to Reject and justify with regard to the critical value(s): |
2 |
|
3G. Justify the answer to 3F with regard to the critical value(s): |
2 |
|
3G. Conclusion in words. |
2 |
|
|
|
|
|
|
Part 7 |
Regress one quantitative variable as a function of another quantitative variable in your data set,
excluding Math and Verbal SAT scores
.
Check the Normal Probability Plot Box. α = 0.10.
|
Points |
Points Lost |
1. Display only the relevant column(s) of data, with column headings. |
2 |
|
2. Name your dependent variable here: |
1 |
|
3. Name your independent variable here: |
1 |
|
4. Display your Excel output |
1 |
|
5. Based on your specific data set, write out what the regression equation is here: |
3 |
|
6. State the R
2
here:
|
1 |
|
7. Interpret the R
2
value here:
|
2 |
|
8. State the alpha value here: |
1 |
|
9A. State the Significance F (p-value) here: |
1 |
|
9B. Interpret the Significance F (p-value) here: |
2 |
|
10A. Display the Normal Probability Box below. |
1 |
|
10B. Is the data normal or not? Bold Yes or No. |
1 |
|
10C. Justify the decision in 10B here: |
2 |
|
|
|
|
|
|
Part 8 |
Statistically is there a difference in average out-of-state tuition for your states 1, 2, 3 and 4? α = 0.10. |
Points |
Points Lost |
1. Display only the relevant column(s) of data, with column headings. |
1 |
|
2. Display your Excel output. |
1 |
|
3. State the null hypothesis here: |
2 |
|
4. State the alternative hypothesis here: |
2 |
|
5. State the alpha value here: |
1 |
|
6. State the F-critical value here: |
1 |
|
7. Decision to Reject/Fail to Reject: |
1 |
|
8. Conclusion in words and justify with regard to the F critical value: |
2 |
|
|
|
|
|
|
Part 9 |
Format appropriately as follows. |
Points |
Points Lost |
1. Use this template, including your name, course #, section, etc. |
1 |
|
2. Miscellaneous: spelling, labeling, addressing Excel comments in the first worksheet; label all new tabs/worksheets. |
2 |
|
3. Include commas/two decimal places in all Excel output. |
2 |
|