CSUN Simple Linear Regression Exercises
Excel Lab 7 – Simple Linear Regression In the first module of this lab, you will be examining data both visually and numerically. The main goal is to illustrate the important role that data visualizations play in conducting quantitative research. Numerical descriptions of data can sometimes be misleading and, as you will see, plotting your data in a scatterplot is always a good idea before you start crunching numbers. The moral is “look before you leap”. In the second module of the lab, you will use Excel to generate simulated data for use in a linear regression model. After generating data for the independent variable (x) and error term (e), you will then choose values for 𝛼 and đť›˝ to calculate the dependent variable (y). Using formulas in Excel you will proceed to estimate the regression model and calculate Ě‚ 𝑦̂, and 𝑢. 𝛼̂ , đť›˝, Module 1 1. Open the Excel file and navigate to the worksheet titled “quartet”. You will see four datasets at the top of the page. For each of the four datasets calculate the following statistics: a. The average value of x b. The average value of y c. The standard deviation of x d. The standard deviation of y e. The correlation coefficient between x and y 2. Answer Question 0. 3. Next, create a scatterplot for each of the four datasets. Resize and position the scatterplots so they line up below their respective data. You should be able to see all of them at once. 4. Answer Questions 1-5. 5. Now add trendlines to each scatterplot. You may have to refer to last week’s lab if you forgot how to do this step. After you add the trendlines, make sure to add the estimated regression equation to each of the charts. 6. Answer Questions 6 and 7. Module 2 7. Navigate to the worksheet titled “Simulation”. Your first step will be to generate some data. Begin by choosing the “true” values of alpha and beta. The choice of values is yours, but I would suggest choosing a value of alpha between -10 and 10. Similarly, I would suggest choosing a value of beta between -5 and 5. For alpha, enter your chosen number into cells C3 through C32. For beta enter your chosen number into cells D3 through D32. Later you will adjust these values to see what happens to the estimated regression equation. For example: 8. Now generate the values for the independent variable (x) data in column E. Open the random number generator in the data analysis toolkit. Generate 30 random numbers between 1 and 10 from the uniform distribution. For the “Random seed” field, enter your favorite three-digit number. 9. Now generate the data for the random errors (e) in column F. Reopen the random number generator and generate 30 random numbers from the normal distribution with mean = 0 and standard deviation = 5. For the “Random seed” field, enter your favorite three-digit number. If you are unable to enter a three-digit number just leave it blank. 10. Your spreadsheet should look something like this. Remember, your random numbers will be different from mine. 11. Next, use the appropriate formula to calculate the values for the dependent variable (y) in cells B3 through B32. You will find the formula below the data you just created. 12. Note that the values of x and y you just created also show up in columns S and T, and will update automatically when you generate the x data and calculate y in steps 8 and 10. Create a scatterplot for x and y using the data in cells S2:S32 and T2:T32. The look of your scatterplot will be based on the values of alpha and beta that you chose. My chart (alpha=5, beta=3) looks like this: Chart Title 40.000 35.000 30.000 25.000 20.000 15.000 10.000 5.000 0.000 0 2 4 6 8 10 12 13. Add a trendline and equation to the chart. Chart Title 40.000 y = 3.2857x + 3.5083 35.000 30.000 25.000 20.000 15.000 10.000 5.000 0.000 0 2 4 6 8 10 12 My estimates of alpha and beta (3.5083 and 3.2857) are reasonably close to the true values (5 and 3). 14. Choose new values for alpha and beta in columns C and D. Your scatterplot and estimates should automatically update. Experiment and change the values of alpha and beta a few times to see how the scatterplot reacts. 15. Set the value of alpha equal to 4 and the value of beta equal to 2. Now see how the estimates and scatterplot behave when you adjust the size of the error term (e). Reopen the random number generator and generate 30 new values for (e) using a larger or smaller standard deviation. (See step 9) Before we used a standard deviation of 5. Try out standard deviations of 1 and 10 to see how the model behaves. Your estimates of alpha and beta should typically be worse with a larger standard deviation because there is more randomness in the data. 16. Move your scatterplot to the right of column T and calculate the average value of y and x (y_bar and x_bar) in cells I3 and J3. Now calculate beta_hat in cell M3 using the provided formula. You can either choose the easy formula or the challenging formula. Remember that rxy is the correlation coefficient, sy is the standard deviation of y, and sx is the standard deviation of x. Likewise, calculate alpha_hat in cell L3 using the formula given. 17. Verify that you calculated the estimates correctly. They should match the estimates from the equation in the scatterplot. 18. Calculate y_hat in cells O3 through O32 using the formula provided. You will need to use the values for X in cells E3 through E32 and the values you computed in cells L3 and M3. Finally, compute the residuals (prediction errors) in cells P3 through P32. Once again, use the formula provided. 19. Upload your Excel file to Canvas to receive credit.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
