excel

This assignment is due You should submit an Excel file and a Word document through your Moodle account by this time. The Excel file should contain the data work, chart, regression, and pivot table mentioned below and the Word document should contain your written answers to the questions. The Excel part is worth 60 points and the Word part is worth 40 points.

Name your files after your last name. For example, if I were in the class, my files would be named halcoussis.xlsx and halcoussis.docx.

IMPORTANT: This is an individual assignment. You may discuss the assignment with other students, but you must do the work and answers the questions yourself. You can not copy your spreadsheet or answers from another student.

There are 5 different versions of the assignment. Each version involves the same regression model, but the data provided are different, so the answers will be different.

You must use the correct data set (as listed below) or you will receive a 0 for the assignment:

Students who have a Student ID Number that ends with 0 or 1: Data Set A

Students who have a Student ID Number that ends with 2 or 3: Data Set B

Students who have a Student ID Number that ends with 4 or 5: Data Set C

Students who have a Student ID Number that ends with 6 or 7: Data Set D

Students who have a Student ID Number that ends with 8 or 9: Data Set E

In this assignment, you will be studying state government tax revenues using a cross-sectional data set.

Follow these steps to do the assignment:

1. Download the appropriate data set assigned to you above.

2 On a new sheet or page named “clean data” or “per capita data,” prepare the data so that you have the variables defined below.

TAXREVCAP = state government tax revenues for each state, per capita (per person), in dollars.

UNEMPLOYMENT = the unemployment rate for each state.

(continued on next page)

GSPCAP = gross state product for each state, per capita, in dollars. (Gross state product is similar to Gross Domestic Product, except it is measured for each state separately, rather than for the whole nation.)

SOUTH = 1 if the state is considered part of the South; 0 otherwise (for full credit, use a formula to create SOUTH.)

DEMGOV = 1 if the state’s Governor is a member of the Democratic party;

0 otherwise.

Note the following: (a) “Per capita” means per person. Some of the variables in the regression are per capita. Note that your spreadsheet page named “clean data” or “per capita data” should contain the values you will use for the regression and the formulas you used to convert the data from the original data to the variables you will use in the regression.

(b) Based on the information given, use a formula to create SOUTH.

3. On the same page (“clean data” or “per capita data”), add a chart that displays TAXREVCAP on the Y-axis and GSPCAP on the X-axis.

4. On the “per capita data” or “clean data” page, create 1 (and only 1) pivot table that shows

a. The average TAXREVCAP for southern states with Democratic governors

b. The average TAXREVCAP for southern states that do not have Democratic governors

c. The average TAXREVCAP for non-southern states with Democratic governors

d. The average TAXREVCAP for non-southern states that do not have Democratic governors

5. Estimate the following regression model with your data from the “per capita data” or “clean data” page. The results of this regression should appear on a different spreadsheet page named “results.”

TAXREVCAP = B0 + B1UNEMPLOYMENT + B2GSPCAP + B3SOUTH + B4DEMGOV + e

6. This question may be done on a separate page in your excel workbook called “forecast” or it may be done “by hand” in the Word Document you use to answer the questions below, but either way it will be scored as part of the excel part of the assignment.

a. Using your regression results, calculate a point forecast for a state in the South with a Republican governor, with UNEMPLOYMENT of 5.0 and GSPCAP of 30,000.

b. Construct a 90% Confidence Interval for your point forecast.

(continued on next page)

Answer the following questions, typing the answers in a Microsoft Word document.

7. What conclusions can you make from the pivot table results?

8. For UNEMPLOYMENT, TAXREV, and SOUTH comment on the significance level and what it means. Explain it so that someone outside the class who didn’t know about statistics could understand.

9. For each variable in #8 that is significant at a 5% error level, give a precise interpretation of its B estimate.

10. What political conclusion can you make from the result for DEMGOV?

11. How well does the regression fit the data? How do you know?