In: Statistics and Probability
A sample on nine public universities and nine private universities was taken. The total cost for the year (including room and board) and median SAT score (maximum total is 2400) at each school were recorded. It was felt that schools with higher median SAT scores would have a better reputation and would charge more tuition as a result of that. The data is in the table below. Uss regression to help answer the following questions based on this sample data. Do schools with higher SAT scores charge more in tuition and fees? Are private schools more expensive than public school when SAT scores are taken into consideration.
Category Total cost Median SAT
Public 21,700 1990
Public 15,600 1620
Public 16,900 1810
Public 15,400 1540
Public 23,100 1540
Public 21,400 1600
Public 16,500 1560
Public 23,500 1890
Public 20,200 1620
Private 30,400 1630
Private 41,500 1840
Private 36,100 1980
Private 42,100 1930
Private 27,100 2130
Private 34,800 2010
Private 32,100 1590
Private 31,800 1720
Private 32,100 1770
PLEASE READ: PLEASE SHOW STOP BY STEP HOW YOU GOT THE ANSWER AND PLEASE SHOW HOW TO DO IT STEP BY STEP ON EXCEL. (answer will be thumbed down if this isn't included)
Answer to the
question)
The steps are as follows
Enter the data in excel
Click on data tab
Click on data analysis
Select regression from the data analysis window
and click OK , as shown in the image below
.
.
The new window of regression appears on the screen as shown below:
.
.
Enter the Y range as cell addresses for the total cost data
similarly enter the X range as cell addresses for SAT data
[the category data is numbered as 0 for public and 1 for private, so as to convert the data in numerical form]
.
Tick mark the check box of labels if while selecting the x and Y ranage you have mentioned the cell containing the label as well
Then click ok
We get the following output
.
This regression model helps us answer the question if the cost is influenced by the SAT score or not
For this we refer to the test statistic F value and its P value
The F value obtained = 4.53856 with a P value = 0.049 < significance level 0.05
Since the P value < significance level , thsi implies that there is significant evidence that the cost is influence by the SAT score
The coefficient of SAT score is 21.6199
This implies with one unit increase in the SAT score, the increase in the cost is 21.6199 times
it shows a strong positive relatio between the two indicating that higher the SAT score more is the cost
.
Now for the second question are private schools more expensive than the public schools
we can enter the data as follows:
.
In the data analysis window select 2 sample T test assuming unequal variances
click ok
Following window appears on screen:
.
I have entered the private data as variable 1 range
Public cost data as variable 2 range
this will help me to know if Private is more than public or not
tick mark the check box of labels
click ok
The following output appears on screen:
.
The T statistic obtained is positive which implies that Private - public is positive or , in other words, private cost is more than the public cost
The P value of the test is 0.00000156 which is < significance level 0.05
Hence we conclude that yes the cost of private is more than the cost of public and this difference is statistically significant