Similar to week 2, this discussion has several elements. Excel has a couple of ways to calculate the regression equation and you are going to attempt each of them in your initial post this week. We do this in the discussion environment so that students can learn the fundamentals of regression using Excel. If you have questions, check out the posts of other students who have been reviewed by Dr. Letsch and where the subject line states something about that post being Excellent or Accurate. Try to compare what they have done with what you have attempted. This work will help you succeed with the project in week 7. 1. Journalize the following terms from your textbook: Dependent Variable, Independent Variable, Regression, Regression Equation, Slope, Intercept, Pearson correlation coefficient or Rsquared, 2. In your own words, please describe and explain regression to someone with no financial background. Why would we need to calculate regression and provide someone with the regression equation? What is the Pearson Correlation Coefficient and what does it tell you about your data and the regression equation provided? 3. Determine which data set to use from one of the approved data tables (based on the first initial of your last name) in the attachment below. Copy and begin with Row 6 and the two columns of data selected for you ending with Row 32. Paste your data set to a new Excel Worksheet beginning in column A1. The data should now be on cells A1-B27. 4. With only your approved data set, starting in cell A30-A32 enter in your textual descriptions for the following functions to be placed in cells B30-B32. Determine which column is your Dependent or for Excel the Y variable and which column is your Independent or for Excel the X variable (you will need to remember these designations as you will use them a few times in this discussion. Use Excel formulas(functions) to calculate slope (=Slope), Intercept (=Intercept), and R.Squared (=RSQ) in cells B30, B31,& B32). 5. In Cell A33 enter text, “Regression Equation” and in cell B33, using the information provided in cells B30 and B31, type your regression equation. NOTE: begin with a single quote mark (‘) to tell Excel you are providing a textual formula. The cell should begin with ‘y= 6. In Excel, under the Data Tab there is a program called “Data Analysis” (same program used in week 2 for standard deviation); open the Regression input box, enter in your x and y input Range data cells, including the label row in cells A2 and B2, and check the Labels and Line Fit Blots boxes. In the Outbox Options Selection area select Output Range and Enter E2. Now copy the Excel screen (by holding down the alt key while pressing the prt sc key)then click on OK. In your worksheet, paste a copy of the Regression input box on Cell J21. Verify your calculations of Slope, Intercept, and Rsquared with your calculations in cells B30, B31, and B32. 7. Review the graph that was placed on your worksheet. Expand the size of the graph filling cells O2 to X17. Click on the data points in the middle of the graph. Right click your mouse and Add the trendline. The Format Treadline box will appear to the right of the screen. Click Display Equation on Chart. Compare this equation with the equation you drafted in #5 above. If the equation matches and your formulas in #6 above all match your Regression Summary Output beginning on cell E2, then you are finished – move to step 8 below. If not, please try to correct your work. 8. Copy your worksheet to your PC. Attach the worksheet to your discussion original answer for #2 above. 9. All items 1-7 must be completed on or before Wednesday at midnight and posted to the discussion board. Late posts will have points deducted for each day late. Do not do #1 I have the definitions from my text book. Attached is the Data Table. You will be using data table “Last Name begins with F-J” SP500/Walmart
