ISOM 201 Chapter 4

There are only one question in this homework. Have to finish in excel sheet. Homework 2 chapter 4

Instruction:

Generate three excel sheet and name them Model 1, Model 2, and response sheet

Solve the following problem in the excel and answer all the section in response sheet and highlight in model 1, model 2 sheet if necessary

Specify each section by the related letter in excel cell you are responding

Use only 2 decimal digit in responses

Save the file with your name and Submit only one file (10 Points)

A sample of nine public universities and nine private universities was taken. The total cost for the year (including room and board) and the 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 are in the attached excel file. Run the regression one time with Total cost as dependent variable and Median SAT as independent variable and name it Regression model 1 and Run regression for the second time with Total cost as dependent variable and Median SAT and Dummy variable as independent variables and name it Regression model 2.

a)Write the predicted regression equation and highlight it for both models

b)High light the r2 and explain it for both models

c) Which model is a better model based on Adjusted R²? Why Adjusted R² and not R² ?

d)Highlight Significance F and explain it for both models

e)Highlight the p-value and discuss if independent variables are significant or not for both models

f)Discuss the sign of the co-efficient for both models and compare them.

g)Generate Residual vs fitted value for the regression model 1 and interpret it

h) Generate Normal Probability Plot for regression model 1 and interpret it

Hint: Dummy variable gets the value of 0 for public universities and1 for private universities.

	Total Cost ($)	Median SAT	Dummy
University 1	21700	1990	Public
University 2	15600	1620	Public
University 3	16900	1810	Public
University 4	15400	1540	Public
University 5	23100	1540	Public
University 6	21400	1600	Public
University 7	16500	1560	Public
University 8	23500	1890	Public
University 9	20200	1620	Public
University 10	30400	1630	Private
University 11	41500	1840	Private
University 12	36100	1980	Private
University 13	42100	1930	Private
University 14	27100	2130	Private
University 15	34800	2010	Private
University 16	32100	1590	Private
University 17	31800	1720	Private
University 18	32100	1770	Private