Develop a least-squares estimated regression line.
- Develop a least-squares estimated regression line. Go to Excel. Create a scatter diagram for the data. Include the regression line (trendline).
- Make sure from Week 1, you know what the regression line is – it is NOT simply connecting the dots. Once the scatter diagram is created, right-click on the trendline itself in the graph. Use “format trendline” from the menu to show the equation of the trendline and the r-squared value on the scatter diagram itself. You will now create a table of values like the ones on pages 536 and 537 for this data. The table will include the following columns:
- pages (x) – these values are given above – At the bottom of this column, find the mean of the x values. You will need this later. Price (y) – these values are given above – At the bottom of this column, find the mean of the y values.
- You will need this later. Predicted value. List the equation of the trendline (regression line) from your scatter plot as the title of the column.
- Plug in each value of x into the equation and solve it. These values are your predicted y values and will be used in the next step. Error – Take the given y value minus the predicted y valve (column 2 minus column 3)Squared error – Square each value in the error column. At the bottom of this column, add up all the squared values.
- This is your SSE. Label it. You will need it later. Deviation.
- Take each y value minus the mean y value. (This is why you put the mean under your second column).Squared deviation – Square each deviation.
- Then add up your squared deviations at the bottom of this column. This is your SST. Label it. You will need it later. Go to page 538 to the formula for the relationship among SST, SSR, and SSE. You calculated SSE and SST. Solve for SSR. Label it. Compute the coefficient of determination and explain its meaning.
- This is found on page 539. Make sure that your calculation matches what the graph says it should be. If it does not, go back and find the error. Compute the correlation coefficient between the price and the number of pages (page 540).Test to see if x and y are related. Use Î± = 0.10. You will be using the t-test formulas around page 546. What are the degrees of freedom for this test? Calculate s (Standard error of the estimate) and label it.
- Go back to the table. Create another column in which you subtract the mean of the x values from each x. This is why you have the mean under the x values. On the table, create your last column. Take the difference between the mean of x and the x values that you just created and square each one. Then add them. Go to page 547 and calculate the estimated standard deviation – sb1. You will need the s you calculated and the sum of the squared difference between the x values and the x mean. The t test involves the slope of the trendline (see your equation on your scatter diagram) divided by the estimated standard deviation (sb1) that you just calculated. Calculate t. Use the yellow box on the bottom of 547 to determine if your t value is significant. State your findings in terms of whether the null hypothesis should be rejected or should fail to be rejected.
The following data represent a company’s yearly sales volume and its advertising expenditure over a period of 8 years.
- Develop a scatter diagram of sales versus advertising and explain what it shows regarding the relationship between sales and advertising.
- Use the method of least squares to compute an estimated regression line between sales and advertising OR just show it on the graph along with the r-squared value.
- If the company’s advertising expenditure is $400,000, what are the predicted sales? Give the answer in dollars. (This is algebra using the equation of the trendline / regression line – make sure to watch your units)What does the slope of the estimated regression line indicate?
For these project assignments throughout the course you will need to reference the data in the ROI Excel spreadsheet. Download it here.
Using the ROI data set:*** The step-by-step directions for the assignment may also be applied here.
For each of the two majors: Draw the scatter diagram of Y = Annual % ROI against X = Cost. Include the trendline (regression line) and the r-squared value (coefficient of determination).Obtain b0 and b1 of the regression equation defined as y Ì‚ = b0 + b1X. (y intercept and slope)Calculate the estimated ˜Annual % ROI when the Cost(X) is $160,000. (Algebra using the equation of the line) Test the hypothesis: You will need to follow and show work for the steps you are taking – there are t tests, F tests… see the end of chapter 12 to make sure you are doing a comprehensive analysis…
H0: Î²1 = 0Ha: Î²1 â‰ 0
Write a paragraph or more on any observations you make about the regression estimates, coefficient of determination, the plots, and the results of your hypothesis tests. Please note the plural nature – paragraph or MORE, hypothesis tests…. One of the ways to help do this is to present the test, tell what it determines (what is the test for?) and then present your work and conclusions for that test. Repeat this for each test.