Correlation And Simple Linear Regression

Correlation And Simple Linear RegressionProject Description:
The present study shows data for prices per gallon of paint and the demand for each brand of paint. The sales manager at a home improvement store is interested in knowing if there is a significant relationship between the price a gallon of paint and the demand (number of gallons sold). A sample of 28 different paint brands is collected and analyzed. We will use Excel formula to find the values of the covariance and correlation coefficient and analyze what the values mean. We will use the Excel Data Analysis Add-in to build a regression model and find the values of the errors in the model. We will identify the value of the coefficient of determination and analyze its meaning. We will use the regression model to make predictions. We will test the claim that there is no relationship between the two variables. We will compare the observed level of significance (p-value) with alpha and decide if we can conclude that there is relationship between the two variable.
Grader – Instructions Excel 2016 ProjectCorrelation and Simple Linear Regression V2
Project Description:
The present study shows data for prices per gallon of paint and the demand for each brand of paint. The sales manager at a home improvement store is interested in knowing if there is a significant relationship between the price a gallon of paint and the demand (number of gallons sold). A sample of 28 different paint brands is collected and analyzed. We will use Excel formula to find the values of the covariance and correlation coefficient and analyze what the values mean. We will use the Excel Data Analysis Add-in to build a regression model and find the values of the errors in the model. We will identify the value of the coefficient of determination and analyze its meaning. We will use the regression model to make predictions. We will test the claim that there is no relationship between the two variables. We will compare the observed level of significance (p-value) with alpha and decide if we can conclude that there is relationship between the two variable.Steps to Perform:
StepInstructionsPoints Possible1Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. Start Excel. Download and open the workbook named: Correlation_and_Simple_Linear_Regression_Start02On the Data sheet, go to the Data Tab and click on Data Analysis. Double click on “Regression.” For Input Y Range, select C1:C29 and for Input X Range, select B1:B29. Check the Labels box and select Output Range under Output Option and type E1.43In cell C4, find the covariance between the demand and price for the sample on the Data sheet. Hint: use the COVARIANCE.S function.44In cell C5, find the correlation between the demand and price for the sample on the Data sheet. Hint: use the CORREL function.45By assessing the values in cells C4 and C5, what can you say about the relationship between the Price of the paint and Demand? Choose your answer from the dropdown menu in cell C6.46Explain the answer you chose in cell C6. Choose your answer from the dropdown menu in cell C7.47In cell C8, find the Y-Intercept (B0) of the Regression Equation from the Data Analysis output table on the Data sheet.48In cell C9, find the Price (X variable) coefficient (B1) from the Data Analysis output table on the Data sheet.49In cell C10, find the Coefficient of Determination (R2) from the Data Analysis output table on the Data sheet.410In cell C11, find the Regression Sum of Squares (SSR) from the Data Analysis output table on the Data sheet.411In cell C12, find the Error Sum of Squares (SSE) from the Data Analysis output table on the Data sheet.412In cell C13, find the Total Sum of Squares (SST) from the Data Analysis output table on the Data sheet.413In cell C14, find the value of Coefficient of Determination (R2) using SSR and SST.514Explain what the value of R2 means. Choose your answer from the dropdown menu in cell C15.515In cell C16, find the lowest price for which we can predict demand.516In cell C17, find the highest price for which we can predict demand.517In cell C18, predict the demand for a paint that is priced at $50 per gallon.518Explain the relationship between the demand and price. Choose your answer from the dropdown menu in cell C19.519Is the correlation coefficient related to the Coefficient of Determination? Choose your answer from the dropdown menu in cell C20.520Test the hypothesis that there was no linear relationship between the paint price and demand. In cell C21, find the observed level of significance (p-value) from the Data Analysis output table on the Data sheet.521Do you reject the claim that there is no linear relationship at alpha=0.05? Choose your answer from the dropdown menu in cell C22.522Explain your answer in cell C22. Choose your answer from the dropdown menu in cell C23.523In cell C24, insert a Scatter Plot that displays the relationship between the price and demand. On the Data sheet, highlight the range B1:C29, click on the Insert tab, Scatter charts, then choose the first scatter plot. Right-click on the data point and select “Add Trendline”. In the Format Trendline Window, check “Display R-squared value on chart”. Select the Scatter Plot, cut and paste it in cell C24 and resize it to fit in the cell.624Save your file and submit for grading.0Total Points100Created On: 08/21/2019 1 Correlation and Simple Linear Regression V2