[CUSTOM SOLUTION] The Concentration Variable

This exercise follows very closely to what we did in class in terms of analyzing a claimant database.First, you will need to download an Excel file that contain information on the claimants. This is “claimant_hw.xlsx.”Even if you don’t get the answers correct, I can still give you credit for trying. Don’t give up!1. [ 0 points – but please do it for the next step]. First, I’d like for you identify the state for each claimant. As we did in class, please take the left 2 digits of the claim_id variable for the first 3 states and the left 1 digit of the claim_id variable for observations in San Diego. This will identify the state “fip”. State fip 26 = Michigan, State fip 24 = “New Jersey”, State fip 25 = “Massachusetts”, State fip 6 = “California”. This will create a new “state” variable.Next, I’d like you to create a summary table of the Concentration Variable. To do so create a new tab in Excel (you can create a new tab by pressing the “+” button down where it says “claimant”.2. [4 points]. Create a summary table that has as columns, “State”, “Minimum Concentration”, “Mean Concentration”, “Maximum Concentration”, and “Number of Observations”. Use the appropriate Excel Functions to create this table by state. Once you create this table, format it so that it looks presentable and paste into a Word document with a description of the table. You get 1 point for presentation of this table and 3 points for the correct answers.3. [4 points] In this next step, I’d like for you to run a regression of sicknesses (dependent variable) on concentration (independent variable). Present the key coefficient results in a table in Word. Describe the main slope parameter of interest in terms of its magnitude and significance. 3 points for the correct answer, 1 point for presentation and description of your results.4. [2 points] Add a scatter plot of sickness versus concentration to your Word document. Make it presentable and briefly describe what it shows. Please include a linear fit of the data on the graph (i.e., a line that runs through your data). 1 point each for correct answer and presentation.

Assignment posted by client #4327***