need help and also how to do these in excel please
The table below lists the prices (in thousands) of used cars sold and the corresponding miles (in thousands). Create a Scatter diagram and the trendline (regression line) using Excel. Run Excel's Regression program (to be displayed starting cell H91) and plot the residual graph. Use the Regression equation to get the predicted price. Then fill the remaining columns. Then use this data to calculate the SSE, SST, etc. . If done correctly, these should match the corresponding numbers calculated by Excel's Regression program. Class discussion / slides will be very useful in working on this problem. Predicted Miles Residual- (Actual- (Predicted - Predicted Price Residual (Error) (Actual Price squared Average Price Average) Average) Average) Average) Squared Squared 24.2 15.4 38.8 14.3 45.6 4.5 28.7 15.6 38.2 14.7 36.7 14.4 32.5 15.2 39.1 14.1 15.3 14.2 4.4 14.8 38.4 15.1 32.2 15.0 26.6 15.7 33.5 14.6 41.8 14.5 36.7 14.8 37.5 14.8 25.6 15.7 40.1 15.0 1.0 14.9 42.2 14.4 37.4 14.2 34.4 15.4 30.6 15.6 12.5 14.6 38.4 14.3 10.5 14.3 26.0 15.5 46.3 13.9 34 8 14.51. SSE SSE : Sum of Squared Errors 2. SST SST: Sum of Squared Total 3. SSR SSR: Sum of Squared due to Regression 4. R-Squared R-Squared = SSR / SST = Explained / Total 5. Std Err of estimate Standard Error = Sqrt(SSE/(n-k-1))It is not uncommon to see that alumni often give back to their schools. The question is, what factors influence their gratitude and goodwill and play an important role in them deciding how much to contribute? A sample of some top universities has been analyzed to determine if there is a relationship between the Alumni Giving rate (percentage of alumni who give) and factors like Graduation rate (percentage), % of class Under 20, and Student / Faculty ratio. Run a regression model to determine the relationship. Send the output to Cell H90. Answer the following questions based on the Excel table. School State Graduation Rate % of Classes Student / Alumni Under 20 Faculty Ratio | Giving Rate Boston College MA 85 39 13 25% Brandeis University MA 79 68 8 $3% Brown University RI 93 60 8 40% California Institute of Technology CA 85 65 3 16% Carnegie Mellon University PA 75 67 10 28% Case Western Reserve University OH 72 52 8 31% College of William and Mary VA 89 45 12 27% Columbia University NY 90 69 31% Cornell University NY 91 72 13 35% Dartmouth College NH 94 61 10 53% Duke University NC 92 68 8 45% Emory University GA 84 65 37% Georgetown University DC 91 54 10 29% Harvard University MA 97 73 8 46% John Hopkins University MD 89 64 9 27% Lehigh University PA 81 55 11 40% Massachusetts Inst. of Technology MA 92 65 44% New York University NY 72 63 13 13% Northwestern University IL 90 66 8 $0% Pennsylvania State University PA 80 32 19 21% Princeton University NJ 95 68 5 7% Rice University TX 92 62 8 40% Stanford University CA 92 69 7 $4% Tufts University MA 87 67 9 29% Tulane University LA 72 56 12 17% U. of California-Berleley CA 83 58 17 18% U. of California-Davis CA 74 32 19 7% U. of California-Irvine CA 74 42 20 U. of California-Los Angeles CA 78 18 13% U. of California-San Diego CA 80 48 19 3% J. of California-Santa Barbara CA 70 45 20 12%U. of California-San Diego CA 80 48 19 8% U. of California-Santa Barbara CA 70 45 20 2% U. of Chicago IL 84 65 4 36% U. of Florida FL 67 31 23 9% -Urbana Champaign IL 77 29 15 23% U. of Michigan-Ann Arbor MI 83 51 15 13% U. of North Carolina-Chapel Hill NC 82 40 16 26% U. of Notre Dame IN 94 53 13 19% U. of Pennsylvania PA 90 65 7 41% U. of Rochester NY 76 63 10 23% U. of Southern California CA 70 53 13 22% U. of Texas-Austin TX 56 39 21 13% U. of Virginia VA 92 44 13 28% U. of Washington WA 70 37 12 2% in-Madison WI 73 37 13 13% Vanderbuilt University IN 82 68 9 31% Wake Forest University NC 82 59 11 38% Washington University - St. Louis MC 86 73 7 33% Yale University CT 94 77 7 50% 1. Do you think this model is good? That is, do you see an evidence of relationship? Pick the right option. 2. What proportion of the variation in Alumni giving is explained by the three variables? 3. Suggest 2 variables (reasons) not in the table that can also be affecting the alumni giving rate. 3. The coefficient for student / faculty ratio is negative in Excel output. Give a reason as to why this is the case.3. The coefficient for student / faculty ratio is negative in Excel output. Give a reason as to why this is the case. 4. Carnegie-Mellon (my school) has a graduation rate of 75, has 67% of the class under 20, and has a student / faculty ratio of 10. What percentage of the alumni will give money to university endowment as predicted by this model? 5. Find the alumni giving rate for Carnegie-Mellon from the table. Compare this to your results in Q4. What is the residual (error)? Regression OutputWhen the price of gasoline at the pump increases, oil companies often claim that the increase is caused by an increase in the price of crude oil. To examine this claim, we listed on the right the average price of a gallon of unleaded regular (U.S. average) and the domestic price of a barrel of crude oil for every month from 1999 to 2001. The source of this data is U.S. Department of Energy. All prices are in dollars. Do a regression analysis and send the Regression output to Cell H58. Year Month Gasoline Price Crude Price Let 1999 0.972 3.57 X = Price of a barrel of crude oil 1999 2 0.955 8.6 Y = Price of a gallon of unleaded regular gasoline 1999 3 0.991 10.76 1999 4 1.177 12.82 1999 1.178 13.92 1999 6 1.148 14.39 1999 1.189 16.12 1999 8 1.255 17.58 1999 1.28 20.03 1999 10 1.274 19.71 1999 1 1 1.264 21.35 1999 12 1.298 22.55 2000 1 L.301 23.53 2000 2 1.369 25.48 2000 w L.541 26.19 2000 1.506 23.2 2000 .498 25.58 2000 1.617 27.62 2000 1.593 26.81 2000 8 1.51 27.91 2000 9 .582 29.72 2000 10 1.559 29.65 2000 1 1 1.555 2000 12 1.489 24.4 2001 1 1.472 24.64 2001 IN 1.484 25.27 2001 w 1.447 22.98 2001 1.564 23.39 2001 .729 24.06 2001 6 1.64 23.43 2001 1.482 22.822001 7 1.482 22.82 2001 8 1.427 23.08 2001 9 1.531 22.37 2001 10 1.362 18.73 2001 11 1.263 16.4 2001 12 1.131 15.54 1. What is the the correlation coefficient between X and Y 2. What is the Slope b, of the regression line 3. . What is the Intercept bo of the regression line 4. Compute the gasoline price for a given crude price of $20.45. 5. Comment on the relationship between X and Y (is it strong? Weak?). Regression OutputYou are trying to find a relationship between the productivity of your sales staff and some other factors such as the number of training hours, age, and the current annual salary. You have collected the data of 37 employees. The Output is the percent of actual sales given the sales leads. You want to study the impact of other variables on this Output. For example, does age matter? And if it does, are the younger employees better performers than the older employees? Output Data Employee | Output (O) raining hours(T Age (A) Income (1) 1 58.8 24 60 22. 2 58.7 25 59 24.6 w 58.2 25 59 25.1 57 25 53 28 UT D 56.5 25 52 28. 6 55.2 27 50 29.7 54.7 30 50 32 . 8 54.5 30 19 32.6 52.8 30 49 33.7 10 51.1 31 49 44.1 11 44.8 32 48 42.9 12 44 33 47 40.7 1 3 59.4 39 47 40.2 14 59.8 41 45 40 15 61.7 43 45 39.9 16 62.5 44 43 39.6 17 62.6 45 43 38.5 18 64.6 60 41 38.4 19 64.9 57 40 38 20 65.3 56 40 37.4 21 65.7 56 40 34.5 22 66.1 49 40 34.4 23 58.2 48 44.4 24 71. 48 35 44. 25 71.7 46 35 8'tt 26 71.3 46 35 448 27 70.1 60 36 15.6 28 69.9 63 37 46.5 29 69.8 53 38 47.5 30 69.6 63 39 4930 69.6 63 39 49 31 68.8 64 39 51.4 32 68.5 68 39 52.6 33 72.6 72 34 52.9 34 73.9 75 34 54.8 35 75.3 76 33 55.1 36 81.6 77 31 55.2 37 83.7 79 30 55.6 a) What portion of the variation in the Output is explained by the 3 independent variables? b) What output would you expect for a 40-year old employee who has 50 hours of training and is making $55,450? c) Based on this model, productivity d) Based on this model, productivity e) Comment on the quality of your regression result. That is, how strong or weak it is? Regression Output
