• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

Excel Regression Assignment

Questions

Use your assigned sample from the Excel file: Dataset.xlsx. These data are random samples of 200 private industry workers in the New England region for 2011. There are 10 questions in the assignment. Each question is for 10 points. Explain your answers clearly and precisely. Provide complete explanations/interpretations. Complete all the tests and run regressions in the Excel file and show all your work (including Excel functions used, if any). Please write legibly.

The data set provides information on the following variables:

Variable

Description

age

Individual's age in years

f

Binary variable indicating the individual is female (=1) or male (=0).

wage

Individual's wage in dollars per hour ($/hour).

LTHS

Binary variable indicating the individual did not complete high school.

HS

Binary variable indicating the individual's highest level of education is a high school degree

BS

Binary variable indicating the individual's highest level of education is a bachelor's degree (BS, BA, BBA, BFA, etc.)

grad

Binary variable indicating the individual's highest level of education is a graduate degree (MS, MA, PhD, JD, MD, etc.)

Note: Make sure that the variables you are using to run a regression are contiguous. Excel will show an error if the variables are not contiguously placed. Carefully place the dependent and independent variables in the worksheet before running a regression.

Q1. Estimate the following model:

  • 𝑤𝑎𝑔𝑒𝑖 = 𝛽0 + 𝛽1𝑒𝑥𝑝𝑖 + 𝛽2𝑓𝑖 + 𝛽3𝐵𝑆𝑖 + 𝛽4𝑔𝑟𝑎𝑑𝑖 + 𝜀𝑖

You might assume you should estimate the following model to capture all levels of education:

  • 𝑤𝑎𝑔𝑒𝑖 = 𝛽0 + 𝛽1𝑒𝑥𝑝𝑖 + 𝛽2𝑓𝑖 + 𝛽3𝐻𝑆𝑖 + 𝛽4𝐵𝑆𝑖 + 𝛽5𝑔𝑟𝑎𝑑𝑖 + 𝜀𝑖

Review your data – if you create a column sum for the column LTHS, what do you find? There were no individuals without a HS degree, so the HS, BS and grad columns will always sum to one. One of these three needs to be dropped because we are including an intercept. In the model above, the estimate for β0 is the wage for an individual with a HS degree and no experience.

You could also estimate the following model without an intercept. You will get exactly the same results as you would estimating model (1):

  • 𝑤𝑎𝑔𝑒𝑖 = 𝛽0 + 𝛽1𝑒𝑥𝑝𝑖 + 𝛽2𝑓𝑖 + 𝛽3𝐻𝑆𝑖 + 𝛽4𝐵𝑆𝑖 + 𝛽5𝑔𝑟𝑎𝑑𝑖 + 𝜀𝑖

Q2. Interpret the effect of the variable exp on wage, your estimate of β1. Provide a complete sentence that explains the effect.

Q3. We would expect that greater levels of experience lead to greater/higher wages. Do you find statistical evidence to support this hypothesis? Provide a complete hypothesis test to support your conclusion from the model results.

Q4. You are working on completing a bachelor’s degree. Use your model results to estimate:

  1. How much more you will earn compared to a person with a high school diploma
  1. How much more you would earn if you continued after your BS degree to complete a graduate degree?

Q5. Does the completion of a bachelor’s degree significantly increase individual’s wages? Again, provide a complete hypothesis test to support your conclusion. Write the hypothesis statement (null and alternative). Determine and conduct the test. Write the decision rule and statement clearly.

Q6. In Excel, create two additional variables (new columns):

  1. An interaction variable fexp by multiplying exp by f. (fexp = exp * f)
  2. expsq = exp*exp (the squared of exp).

Q7. Estimate the following model.

  • 𝑤𝑎𝑔𝑒𝑖 = 𝛽0 + 𝛽1𝑒𝑥𝑝𝑖 + 𝛽2𝑓𝑒𝑥𝑝𝑖 + 𝛽3𝑓𝑖 + 𝛽4𝐵𝑆𝑖 + 𝛽5𝑔𝑟𝑎𝑑𝑖 + 𝜀𝑖

Q8. What are the effects of an additional year of experience (exp) on wage for:

  1. Men?
  1. Women?

(Remember, fexpi = fi*expi ; what happens if the individual is male? Female?) Q9. Estimate the following model with a quadratic effect of experience

  • 𝑤𝑎𝑔𝑒𝑖 = 𝛽0 + 𝛽1𝑒𝑥𝑝𝑖 + 𝛽2𝑒𝑥𝑝𝑠𝑞𝑖 + 𝛽3𝑓𝑖 + 𝛽4𝐵𝑆𝑖 + 𝛽5𝑔𝑟𝑎𝑑𝑖 + 𝜀𝑖

Q10. The model above in quadratic in experience (exp). For this model, the effect of education is a partial derivative:

𝜕𝑤𝑎𝑔𝑒̂ 𝑖/𝜕̂𝑒𝑥𝑝𝑖 = 𝑏1 + 2𝑏2 ∙ 𝑒𝑥𝑝𝑖

where, b1 or b2 are your estimates of β1 and β2

  1. Provide the equation that will estimate the effect of an additional year of experience for an individual (complete the equation above using coefficients from your estimation of the model in question 9).
  2. Assume an individual has 10 years of experience. What is the estimated effect of an additional year of experience when exp = 10? (Use your equation in part a and substitute exp = 10.) Write a complete sentence interpreting the value.