ECONOMICS HOMEWORK

Assign2.3Tutorial.pdf

Home >Business & Finance homework help >Economics homework help >ECONOMICS HOMEWORK

Performing Regression in Excel

A Tutorial

Simple Regression using the eye-ball method Before proceeding to using Excel to perform multiple regression, it is useful as a refresher (or, as an introduction) to perform some simple regressions using the eye-‐ ball method. Simple regression – involves one “X” or independent variable Multiple regression – involves two or more “X” or independent variables In simple regression, a “Y” variable (or dependent variable or response variable) is assumed to be caused by an “X” variable (or independent variable). (In multiple regression, the “Y” variable is assumed to be caused by a set of X variables.) For example, if “X” is the amount of rainfall during the day, and “Y” is the number of car accidents, then we might assume that “X” causes “Y.” Assuming the relationship is linear, we would have

Yt = α + βXt + et

• Yt is the number of car accidents on day t, • Xt is the amount of rainfall on day t, • α is the expected number of car accidents when it doesn’t rain, • β is the additional number of car accidents for each unit of rainfall, and • et is the random variation of auto accidents about its expected value.

Question: How do we know which variable is the dependent variable and which is the independent variable?

Answer: We often do not know. We’ll simply call it an assumption. Question: Why is there an error term, et? Answer: Possible reasons include (1) other “X” variables not included in the

regression, (2) the relationship isn't well approximated by a straight line, (3) there are errors in measuring the variables that are included in the regression, and (4) there is true unpredictability in the "Y" variable.

Question: Must the relationship between the “Y” and “X” variables be linear? Answer: No. In fact, we will explore a particular non-‐linear relationship later in this

section. Exercise 1. Age and Price of Ford Taurus Cars. Asking Price and Age of Ford Taurus Cars Age (years) Asking Price

0.5 $14,992.00 0.5 $13,900.00 1.0 $11,875.00 1.0 $9,992.00 1.0 $11,900.00 2.0 $8,990.00 3.0 $10,300.00 3.0 $7,999.00 4.0 $6,990.00 5.0 $4,990.00

In this exercise, the Asking Price of Ford Taurus cars is plotted against the age of the car in years. Notice that the trend is downward. Take a straight-edge (the best would be a tinted, see-through plastic ruler). Orient the edge so that it aligns with the trend of the scatter of points. Then, mark this trend with a line, being sure to project the line northwestward through the vertical axis and southeastward to at least where 5 years is on the horizontal axis. Put a small open circle around the point where the line goes through the vertical axis. This is the predicted value of a new Ford Taurus, i.e., the predicted value of a Ford Taurus with zero years of age. We’ll call this value “A.” Carefully identify the point on the trend line directly above 5 years on the horizontal axis, and mark this point with another small open circle. We’ll call the height of this second point “C.” It is the predicted value of a Ford Taurus that is 5 years old. Compute B = (A- C) / 5. “B” is the slope of the line. It is how much the predicted value of a Ford Taurus will fall per year. We now have a regression equation: Y = A + BX + e, where Y is the value of a Ford Taurus, X is its age in years, A and B are the parameters we just estimated, and e is a random number representing factors we have not taken into account.

Asking Price and Age of Ford Taurus

$2,000

$4,000

$6,000

$8,000

$10,000

$12,000

$14,000

$16,000

0.0 1.0 2.0 3.0 4.0 5.0 6.0

Age (Years)

A sk

in g

P ri

Exercise 2. Price and Square Feet of Homes. Price and Square Feet of Homes

Price Square Feet Price Square Feet $98,000 830 $244,650 2550 $77,000 1050 $269,500 2708 $84,000 1124 $192,500 2800

$104,650 1378 $195,600 2843 $150,500 1518 $202,650 2927

$90,300 1700 $241,500 3100 $142,300 1904 $255,500 3171 $185,500 1969 $301,000 3400 $147,000 2070 $273,000 3400 $192,500 2078 $244,650 3400 $195,650 2350 $269,500 3470 $166,600 2439 $265,650 3600 $179,200 2448 $245,000 3600 $185,500 2467 $262,500 3800 $202,300 2490 $300,300 4200

Following the same procedure in Exercise 1, mark the trend line of the scatterplot above. Notice, this trend line slopes upward. Mark the vertical intercept, “A.” In this case, select 2500 square feet to mark the point “C.” Then, estimate B = (A – C) / 2500.

Price and Square Feet of Homes

$50,000

$100,000

$150,000

$200,000

$250,000

$300,000

$350,000

0 500 1000 1500 2000 2500 3000 3500 4000 4500 5000

Square Feet

P ri

Question: In the above exercise, A is (or should be) approximately zero. Does this make sense?

Answer: The expected price of an infinitely small house might indeed be approximately

zero. Question: What do you get for B, and does this make sense? Answer: B would be the expected change in price for another square foot in the size of a

house. This might be compared to engineering-type estimates of the construction cost per square foot of homes. Unfortunately, the data of this example are quite old, and construction costs have risen a lot since then.

Exercise 3. Income versus Education. Education and Income Educational Attain- ment, Males, 25-34, '97

Earnings, Full- Time Workers

less than 9th grade $17,714 9th-12th grade $24,517 H.S. graduate $28,772 Some College $32,354 Assoc. Degree $34,670 Bach. Degree or more $48,688

Earnings versus Educational Attainment (in years) Males, 25-34 years old, in 1997

$10,000

$20,000

$30,000

$40,000

$50,000

$60,000

0 2 4 6 8 10 12 14 16 18

Educational Attainment (in years)

A nn

ua l E

ar ni

ng s

From the Statistical Abstract of the United States, I obtained the data in the above table. These data show that the annual earnings of Males, 26 to 34 years old, in 1997, who worked full-time during the year, tended to rise with education. Of course, these earnings figures are averages, and therefore average-out differences in earnings among individuals due to the quality of an individual’s education, the kind of education, how many hours the individual worked (other than categorized as “full-time” by the census department), how hard the individual worked, and the many other factors that contribute to an individual’s earnings. In order to conduct a statistical analysis of the relationship between earnings and education, I first had to quantify the categories of “less than 9th grade,” “9th to 12th grade,” etc. Somewhat arbitrarily, I assumed that “less than 9th grade” meant 8 years of schooling (on average), “9th to 12th grade” meant 10 years of schooling (on average), etc. I then constructed the scatterplot shown above. Proceeding, for the moment, in the usual way, mark the trend line through this scatterplot. Question: Notice that the vertical intercept, “A,” is negative. Does this make sense? Answer: A negative intercept makes little sense since people with zero years of schooling

have positive (but low) productivity. Their wage is low, not negative. The strange intercept is a clue that the regression line is not exactly correct. A second clue that the regression line is inaccurate is that the vertical increment from the next-to- the-last point to the last point appears greater than the vertical increment from one point to the next among the first several points. These clues indicate that the relationship between earnings and education is non-linear. In cases like this, it is often useful to convert to the data to logarithms. Indeed, this is so often the case that Excel allows you to convert charts to logarithms by one click. The reason why logarithms are so useful, we believe, is because they preserve equal percentage changes. Therefore, if something is growing at a roughly constant rate, the trend line will appear to be a straight line if the numbers are converted to logarithms. By converting the vertical scale to logarithms, I produced the scatterplot show below. Now, the points appear to lie on an approximately straight line. We can mark the trend line of these points using the “eye-ball” method. The vertical intercept is now a positive number, something like $6,000; meaning, that a person with no years of schooling working full-time will make something like $6,000 per year. With the vertical scale converted to logarithms, the slope of the trend line, i.e., the “B” of the regression equation, is interpreted as the percentage change in earnings per year of schooling. Another way of interpreting this parameter is the rate of return to schooling; i.e., by what percentage will your future earnings tend to increase for each additional year of investment in education. It is unnecessarily difficult to estimate “B” via the “eyeball”

method when you are using logarithms, but it is relatively easy to do once you learn how to estimate regression equations using Excel.

Calculating a regression in Excel In this section, we move from the eyeball method to Excel. With Excel we will be able to calculate the regression, including multiple as well as simple regressions, use samples that can be very, very large, and obtain several descriptive and test statistics that enable us to measure the precision of our model and each of its parts. We will kind of start over again, and approach regression using Excel step by step.

1. Formulate a regression. For example … Yi = α + β1X1t + β2X2t + et Where Yt is the dependent variable, and X1t and X2t and the independent variables. The variables X1t and X2t are the causes, and the variable Yt is the effect.

2. Collect the data. Below, I’ve entered some data from the Statistical Almanac of the United States into Excel. (This exercise is taken from my exercise on “eye-ball regressions.”)

Earnings versus Educational Attainment (in years) Males, 25-34 years old, in 1997

$1,000

$10,000

$100,000

0 2 4 6 8 10 12 14 16 18

Educational Attainment (in years)

A nn

ua l E

ar ni

ng s

Educational Attainment, Males, 25-34, '97

Earnings, Full-time Workers

less than 9th grade $17,714 9th-12th grade $24,517 H.S. graduate $28,772 Some College $32,354 Assoc. Degree $34,670 Bach. Degree or more $48,688

3. Transform the data, as may be necessary.

Here, I’ve transformed the category “Educational Attainment” into “Years of Schooling,” and the Earnings of Full-Time Workers into the Natural Log of Earnings of Full-Time Workers.

Years of Schooling Ln(Earnings)

8 9.7821 10 10.1071 12 10.2672 14 10.3845 12 10.4536 16 10.7932

4. Invoke …. Tools, Data Analysis, Regression

5. Invoke … Input Y range and then highlight the range of numbers giving

ln(earnings).

6. Invoke … Input X range and then highlight the range of numbers giving years of schooling.

7. Invoke … New Worksheet Ply and give the ply a recognizable name, e.g.,

RegressionHumanCapital.

8. Hit … OK and a new ply will show with the regression output.

9. I suggest annotating this ply with the names of the dependent and independent variables (or, Y and X variables).

ALTERNATE INSTRUCTIONS 4.Highlight a matrix that is 1+ the number of X variables wide in terms of columns; and, 5 rows high. 5.Type =linest(“. Some helps should appear. 6.Highlight the array of the Y variable, from the first to the last observation (when you observe both the Y and the X variable or the set of X variables). Then, type a comma. 7.Highlight the array of the X variable or the matrix of the set of X variables (if you have more than one X variable, they all have to adjacent), also from the first to the last observation. Then, type a comma. 8.Now type “1,1)”. 9.Now use two fingers of your left hand to hold down the “shift” and the “control” buttons;” and, while holding down these two buttons, use a finger of your right hand to depress the “Return” button. 10.A matrix of number should appear. 11.In the cell above the right-most column of the matrix, input “Constant.” 12.In the cell or cells to the left of the one just marked “Constant,” input the name of the X variable or the names of the X variables (putting the name of the X1 variable in the first cell to the left of the cell marked “Constant,” the name of the X2 variable in the next cell to the left, and so on). 13.In the cell to the left of the top row of the matrix, input “Coefficient.” 14.In the cell below the cell just marked “Coefficient,” input “Std. Error.” 15.In the cell below the cell just marked “St. Error,” input “R-square.” 16.In the cell three rows below the cell just marked “R-square,” input “t-statistic.” 17.In the cell to the left of the cell just marked “t-statistic,” input as a formula the amount in the cell in the top cell of the first column of the matrix divided by the amount in the cell in the second to the top cell of the first column of the matrix. 18.Copy and paste this formula across all the other cells in the row below the matrix.

“X variable” or, as I have renamed it, “Yrs of Sch,” refers to the one X variable in this regression. Its coefficient, 0.11, is the regression’s estimate of the parameter β. In general, it is interpreted as the expected change in the Y variable for a 1 unit change in the X variable. In this case, since the Y variable is the natural logarithm of earnings, the coefficient gives the percentage change for a 1 unit change in the X variable. Thus, an additional year of schooling is predicted to increase earnings by 11 percent. “Intercept” is the regression’s estimate of the parameter α. In general, it is interpreted as the expected value of the Y variable when the X variable is equal to zero. In this case, it is the expected value of the natural logarithm of earnings for full-time workers with zero years of schooling. To convert this figure, 8.92, into units, take its exponential, viz.,

7,497 = e8.92 [ or, in Excel, = exp(8.92) ]

Regression Output: R2 (“R-squared”) R2, pronounced “R Square,” is the percent of the variation in the Y variable explained by the X variable (or, by the set of X variables in the case of a multiple regression). In the above case, we have explained 91 percent of the variation in the average earnings of men grouped by educational attainment. In the following chart, I show three hypothetical regressions. In the bottom regression, there is perfect positive correlation between the X and Y variables. In this regression, R2 is 100 percent. In the middle regression, there is a strong but less than perfect positive correlation. The Y values are close to their predicted values. In this case, R2 is shown to be 78 percent, indicating that percent of the variation in the Y variable is explained by the X variable. In the top regression, there is only a weak positive correlation. R2 is shown to be 12 percent.

As pleasing as R2 is, it is merely a descriptive statistic. One regression might have an R2 of 78 percent and be considered unacceptable. Another regression might have an R2 of 12 percent and be considered acceptable. For example, in the above case, which deals with data grouped by educational attainment, 91 percent is obtained for R2. But, if I were to calculate the regression with the original data – the earnings and educational attainment of individual men aged 25 to 34, who worked full-time – it is likely R2 would be something like 12 percent. At this time, all that can be said about how big R2 should be is that it depends on the situation.

Regression output: the t-statistic The estimates of α and β are merely statistics, and are subject – among other things – to sampling error. This error is given by the numbers in the column marked “Standard Error” in the Excel output. The “t-Stat.” indicates the significance of the X variable. The t-statistic is calculated as the estimated parameter divided by its standard error. With a large sample, if the absolute value of t > 1.96, we can say, with 95 confidence, that the Y variable is correlated with the X variable.1 (If we had more than one X variable, we would say that the Y variable is correlated with that particular X variable, holding the other X variables in the regression constant.)

1 In the first eye-ball regression (the one involving Ford Taurus cars), we see a negative relationship and should expect to obtain a negative t statistic in Excel.

The t-statistic deals with the problem of a small sample. A sample is “small” first because it has a limited size and second because a bit of information is used in estimating each parameter that is estimated. So, if we are simply estimating the unconditional mean, it is like we are using up the information in one observation. If we are estimating two parameters, e.g., the constant and x-coefficient of a straight line, then we are using up the information in two observations. The sample size minus the number of parameters being estimated gives the “degrees of freedom” (or, “df” in the associated chart). As the degrees of freedom gets large, the t-statistic approximates the normal curve. But, even with small degrees of freedom, the t-statistic is approximately normal. It is the t-statistic (and not the R2) that tells us whether the relation between an X variable and the Y variable is statistically significant. Meaning, probably not due to chance. What is more, each X variable and the constant term gets its own t-statistic. Exercise 4. Income and Expenditure – simple regression. From the 1934-1935 study of the budgets of 14,469 families, the U.S. Bureau of Labor Statistics developed the table below.

Average Income

Average Expenditure

552 651 777 851

1,065 1,110 1,352 1,371 1,641 1,624 1,937 1,869 2,252 2,160 2,529 2,414 2,881 2,704 3,468 3,251

Question: Do these data support or contradict the hypothesis that as income rises,

expenditure tends to rise? Answer: The estimated β coefficient is 0.89, meaning, that for each additional dollar of

income, expenditure rises by 89 cents. Question: Is the relationship between income and expenditure statistically significant? Answer: The t-statistic of the β coefficient is 275.81, which is larger than 1.96; so, the

relationship is statistically significant. Question: What is the 95 percent confidence interval for the true parameter b? Answer: Adding and subtracting 1.96 standard errors to the estimated coefficient of

income, it can be said that the true amount of each additional dollar of income that is expended is between 88.1 and 89.3 cents

Exercise 5. Income and Expenditure – multiple regression. Also, from the 1934-1935 study of the budgets of 14,469 families, the U.S. Bureau of Labor Statistics developed the data of the table below.

Average Income

Dummy Variable = 0 for white families and 1 for black

families

Average Charitable

Contributions 555 0 5 781 0 6

1,068 0 13 1,351 0 17 1,642 0 26 1,935 0 35 2,253 0 46 2,530 0 52 2,880 0 63 3,466 0 91

549 1 6 758 1 10

1,031 1 15 1,333 1 31 1,592 1 32 2,315 1 83

Question: Do these data support or contradict the hypothesis that charitable contributions

rise as income rises: Answer: In a multiple regression, Yi = α + β1X1i + β2X2i + ei, where Yi is charitable

contributions, X1i is income, and X2i is a dummy variable identifying black families, the estimated coefficient β1 is 0.0318, indicating that for each additional dollar of income, 3.18 cents tends to be contributed to charity.

Question: Is the relationship between income and charitable contributions statistically

significant when you hold the other independent variables of the regression constant?

Answer: The t-statistic of the X1 variable is 13.12, which is greater than 1.96. Therefore,

yes, the relationship is statistically significant holding the other independent variables of the regression constant.

Question: Do these data support or contradict the hypothesis that, during the 1930s,

blacks were “exaggerated Americans”2 because, for the income that they had, they tended to make larger charitable contributions than whites?

2 The expression “exaggerated American” was developed by the Swedish economist and politician Gunner Myrdal (An American Dilemma: The Negro Problem and Modern Democracy, 1944). The expression was designed to capture the greater dedication of African Americans to work, family and charity, which he considered to be strong traits among all Americans. (Things may have changed since then.) The “dilemma” facing America, Myrdal said, was the high ideals on which this country was founded, of freedom and equality, which were not being realized by all of its people.

Answer: The estimated coefficient β2 is 12.66, indicating that a black family tended to give an additional $12.66 to charity when compared to a white family with the same income.

Question: Is the relationship between race and charitable contributions statistically

significant when you hold the other independent variables of the regression constant?

Answer: The t-statistic of the X2 variable is 3.00, which is greater than 1.96. Therefore,

yes, the relationship is statistically significant holding the other independent variables of the regression constant.

The chart below illustrates the multiple regression, compressing three dimensions ("Y" and two "X" variables) into two dimensions using color. You can see that the dots representing black households tend to lie above those representing white households, and that both sets of dots have an upward slope.

!"#$

"#$

%#$

&#$

'#$

(##$

("#$

(%#$

#$ )##$ (*###$ (*)##$ "*###$ "*)##$ +*###$ +*)##$ %*###$

! " #$ %& #'

() *! + , &$ %' - . + , /*

0,1+2)*

!"#$%&#'()*!+,&$%'-.+,/*+3*4"%&)*#,5*6(#17*8+-/)"+(5/*%,*9:;<*

,-./0$12340-2564$

7589:$12340-2564$