Excel & Regress

1. Paula Ecklund Excel & Regress Handouts Excel Regress Add-In: Skill Building & Quick Guide Materials on Website http://faculty.fuqua.duke.edu/~pecklund/WEMBA/Regress.htm Rev. 3-26-08

2. Agenda • What is regression? • What is the Regress Excel add-in software? • A brief look at Excel’s built-in regression. • See how the Regress software operates. • Prepare data • Use the interface • Run the regression • Manage the outputs Return to Contents

3. What is a regression analysis? Regression A way to compare variables and make predictions. A regression models the relationship between variables. One set of variables is used to explain the other. These are called the independent variables. The single variable being explained. This is called the dependent variable. Return to Contents

4. For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? Return to Contents

5. For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? The independent variables (possible predictors) Return to Contents

6. For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? The dependent variable (the predicted) Return to Contents

7. The Regress software facilitates exploratory regression analysis • Preliminary results are quickly available. Statistics, summary table, correlation matrix. • Easily add variables to (or drop variables from) the regression. • Save and build on interesting results or discard unpromising ones. • Select from a variety of results reports. Return to Contents

8. This session • Preparing data for Regress • Understanding the Regress interface • Selecting and managing Regress outputs • Installation issues • All available Regress options • The interpretation of Regress outputs We will cover: We will not cover: Return to Contents

9. Have a question about the regression results? Ask Dr. Jill Stowe in your statistics class. Doctor Jill This session Need installation help? Contact Fuqua’s WEMBA computer support group. Want to know about every available Regress option? Download the author’s complete guide. Return to Contents

10. Why use Regress instead of Excel’sbuilt-in regression*? • Regress is integrated into Excel (as an add-in); easy-to-use; no need to learn a new application (like Statgraphics). Note: Regress is intended as a teaching tool.It’s limited to 16 variables and 250 cases. • Regress uses any named data ranges (where Excel requires contiguous data). • Regress produces a wider variety of reports than Excel. • Regress is particularly useful for exploratory regression. * Part of Excel’s Analysis ToolPak: Tools, Data Analysis, Regression Return to Contents

11. A quick look at Excel’s built-in regression Menu command:Tools, Data Analysis Return to Contents

12. A sample of Excel’s regression output. Return to Contents

13. This look at Regress The mechanics of using the Regress software Sample data sets: • SmythPieData.xls (session demonstration) • Calculus.xls (for your own practice) Return to Contents

14. Sample data & complete guide The sample data sets and the author’s complete Regress users guide are available for download from this web address: http://faculty.fuqua.duke.edu/~pecklund/WEMBA/Regress.htm Return to Contents

15. Supporting & practice files • Data files • SmythePieData.xls • Calculus.xls • CalculusComplete.xls • Quick Guide • Excel Regress Add-In: Skill-Building & Quick Guide (you have on paper) • Complete User’s Guide(by the Regress author) • RegressAuthorsGuide.doc Return to Contents

16. Regress Demo Return to Contents

17. Step 1: Plan SmythPieData.xls is an Excel file of pie sales data. • The variables in the data: • Quantity(quantity sold) • Price • Ads(advertisements) • Comp_Price (competitor price) • Income • Population • Time 48 rows & 7 columns. Return to Contents

18. Step 1: Plan The question: Does advertising increase sales? Quantity =Quantity Sold =Sales Return to Contents

19. Step 1: Plan The regression analysisVariables to use:► the dependent variable = quantity(of pie sales)► the independent variables = ads, price, comp_price, income, population, time Return to Contents

20. Step 2: Prepare the data set Make sure that: • all variables have the same number of elements (the data is a rectangular range) Return to Contents

21. Typical requirements. A requirement specific to the Regress software. Step 2: Prepare the data set Make sure that: • all variables have the same number of elements (the data is a rectangular range) • all variables are numeric • The variable data ranges are named Do NOT include range header names as part of the named ranges! (What happens if you do?) Return to Contents

22. This highlighted range includes the column label “Quantity”. If you include this label in a range name you supply to a Regress operation, Regress will choke because the “Quantity” label is text not a number. Return to Contents

23. Another possible range naming trap This highlighted range is the entire column. Regress will choke because it can’t handle that many cells (and many of the cells in the range are probably empty). Return to Contents

24. Two ways to name ranges in Excel • Menu Commands • Highlight the entire range of data, including the headers in the top row*. • From the menus choose Insert, Name, Create. • Excel names each column of data using the name in the header row. Note: The column’s header row cell is not included as part of the named range. • Formula Bar • Highlight a column of the data range not including the column label cell. • In the Formula Bar’s “name box”, enter a name for the range. Hit the Enter key. Repeat for each column of data. * Select any cell in the range. Then hit CTRL+SHIFT+8 to select the entire range. Return to Contents

25. Name the piedata ranges The Insert, Names, Create method is the fastest way to name this data since the data is together and already has headers. Return to Contents

26. Check the names Then start the regression. Return to Contents

27. Step 3: Invoke Regress Note: FuquaNet’s installation of Regress requires a different start method. • Menu command: Tools, Regress A few seconds of the Regress “Splash Screen” will display... Return to Contents

28. Start a regression analysis. Step 3: Invoke Regress • The Regress Toolbar displays Return to Contents

29. While you start Regress… If you encounter this dialog, you must click the “Enable Macros” button for Regress to run. Return to Contents

30. Step 4: Define the Regression Select the Independent and Dependent variables from the drop-down lists of named ranges in the dialog. Return to Contents

31. Step 5: Run the regression (or Choose options first & then run) Return to Contents

32. Step 6: Choose Reports Return to Contents

33. Step 7: Manage Reports Use the plus and minus icons at left just as you would in Windows Explorer to display or hide report elements. Return to Contents

34. Keep results you want to save by providing a new name in the “Regression Results” dialog. Further exploration... At this point, you might want to rerun the regression adding a new variable, dropping a variable, changing a variable value, providing data for a nonlinear regression, etc. Return to Contents

35. Regress helps keep track of versions of your analysis If you’re about to overwrite a previous analysis, Regress displays this warning/options dialog. You also have these choices: 1. Append new results. 2. Overwrite old results with the new results. Return to Contents

36. Continue running exploratory regression analyses... Save as many versions of the regression as you like. Return to Contents

37. Regress Notes & Tips Return to Contents

38. Regress Notes & Tips • Limits on the data set size • 16 variables and 250 records. • Results • Regress results are static. • Regress text results are not dynamically linked to the data. • Regress chart results are not dynamically linked to the data. • If you change data values, you must re-run the regression to see new results. • Formatting • Because the results environment is an Excel worksheet you can reformat the results in any way you like. Return to Contents

39. More notes/tips • Missing values • A record with one or more missing values is ignored in the regression processing. A missing value is a completely empty cell. • A zero in a cell is not considered a missing value. • A space in a cell (“ “) is seen as a text value, and cannot be used in the regression. For a complete discussion, see the author’s user’s guide, p 46. • Data location • Simplify processing by keeping your data on a single sheet in a workbook. Return to Contents

40. Step 8: Outputs to Word • For a Text report • From Excel: SHIFT + Edit, Copy Picture, As Shown on Screen • Into Word XP: Edit, Paste • For a Graphic report • From Excel: Edit, Copy • Into Word: Edit, Paste Return to Contents

41. To Quit Regress Removes Regress from the Excel workspace. Deletes the Regress toolbar. Re-establishes the Regress menu option on Excel’s Tools menu. Return to Contents

42. At the end of the tutorial in the author’s guide, the author writes: Good luck with regression modeling and... “May the High R-Square Be With You.” Return to Contents

43. For Practice with Regress Return to Contents

44. A practice problem • File: Calculus.xls Holds data about student courses, test scores, gender, high school rank, etc. Return to Contents

45. The Calculus practice problem • The question Can high school data predict performance in a college class? • Setup • Name the data ranges. • Dependent variable = “Term 1 Calculus Grade” • Independent variables – all the other variables. • Run • Run the regression. • Choose outputs. • Rerun, stop, analyze, etc. Return to Contents

46. END of Regress Introduction Return to Contents