Excel WOrk
PowerPoint Presentation ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Run a wage regression with the union dummy variable included. Then re-run the union wage regression with the male, black, and Hispanic dummy variables also included. How does the effect of being in a union on wages change when controlling for basic demographic characteristics? ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 A regression shows the effects of independent variables on a dependent variable. In this assignment, we will estimate the effect of being in a union on wages. Wages will be the dependent variable and union status will be (one of) the independent variable. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 A coefficient shows the effect of an independent variable on a dependent variable. If the coefficient is positive, it means the independent variable increases the dependent variable. If the coefficient is negative, it means the independent variable decreases the dependent variable. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 For example, the regression equation could be represented by Y = β1X1 + β2X2 + β3X3 + ε, where Y is the dependent variable, the Xs are the independent variables, the βs are the coefficients, and ε is the error term. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 In this assignment, the regression equation could be represented by Wage = β1Union + β2Male + β3Black + β4Hispanic + ε. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 A standard error shows whether an independent variable’s effect is statistically different from zero. Using traditional 95 percent confidence levels, if a coefficient is 1.96 times the size of its standard error or larger, then we consider the effect of the independent variable to be statistically significant. Using traditional 95 percent confidence levels, if a coefficient is less than 1.96 times the size of its standard error, then we consider the effect of the independent variable to not be statistically different than zero. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 A t-statistic gives you the ratio of the coefficient to its standard error. If the t-statistic is greater than or equal to 1.96, then the effect of the independent variable is considered to be statistically significant. If the t-statistic is less than 1.96, then the effect of the independent variable is not considered to be statistically different than zero. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 A p-value also indicates whether the effect of an independent variable is considered statistically significant. Using 95 percent confidence levels, if a p-value is less than or equal to 0.05, then the effect of the independent variable is considered to be statistically significant. Using 95 percent confidence levels, if a p-value is greater than 0.05, then the effect of the independent variable is considered to not be statistically different than zero. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Essentially, the t-statistic and the p-value tell us the same thing. They don’t contradict each other. If the t-statistic is greater than 1.96, then the p-value must be smaller than 0.05 (and vice verse). If the t-statistic is less than 1.96, then the p-value must be larger than 0.05 (and vice versa). ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 To run a regression in excel, you will need to have installed the “Data Analysis” tools. In excel, click on the “Data” tab to see whether you have the “Data Analysis” tools. If you do not see “Data Analysis” under the “Data” tab, then you’ll have to install those tools. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Here is how you install the “Data Analysis” tools. Once in excel, Click on the “File” tab, and then select “Options.” Under “Options,” select “Add Ins.” Under “Add Ins,” highlight “Analysis TookPak” and click “Go.” Next, check the box to the left of “Analysis TookPak” and click “Ok.” At this point, the requisite tools should be installed. You should only need to install these tools the first time that you use them. Under the “Data” tab, “Data Analysis” should appear to the far right. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Assuming you now have the “Data Analysis” tools, you must next rearrange your data columns in preparation to run the regression. First, include variable labels in the first row. Next, place the column representing the regression’s dependent variable in the left-most column. Finally, place the columns representing the independent variables to be included in the regression in consecutive columns to the right of the column with the dependent variable. The independent variables must be in adjacent columns. No spaces (or columns with other variables) can be between them. Arranging the data appropriately for the regression may require you to cut and paste columns or even delete columns. So, you may wish to arrange your data in a new spreadsheet. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Assuming the data is arranged appropriately, next select “Data Analysis.” Under “Data Analysis,” select “Regression” (highlight “Regression” and click “Ok”). With the cursor in the “Input Y Range,” highlight the column with the dependent variable, including the label. Then click in the “Input X Range.” Highlight in one block the independent variables, including the labels. Also check the box beside “Labels” so that excel knows you’re using labels. Then click “Ok,” and the regression results should appear. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 If you are including only one independent variable, then you’ll only highlight one column when your cursor is in the “Input X Range.” If you are including multiple independent variables, then you’ll highlight multiple columns (and these columns should be adjacent to each other). ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 You also have the option of checking “Constant is Zero,” if you desire. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Here are the answers when using a constant and one independent variable. The dependent variable is the wage rate. The independent variable is whether the worker is in a union. CoefficientsStandard Errort StatP-value Intercept21.0150.25682.1060.000 union2.3210.5893.9390.000 ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 In the next regression, we control for gender and race/ethnicity. That is, we essentially compare wages for union and nonunion workers of the same gender and same race/ethnicity. ECON 4510/5510 Unions and Collective Bargaining: Data Assignment 7 Here are the answers when using a constant and 4 independent variables. The independent variables are whether the worker is in a union, gender, and race/ethnicity. CoefficientsStandard Errort StatP-value Intercept20.6930.45245.7890.000 union1.2700.7501.6930.091 male6.4460.57111.2820.000 black-7.1760.737-9.7320.000 hispanic-5.1041.006-5.0750.000