Download data for this exercise as Excel spreadsheet.

Create an XY-SCATTERPLOT. 1. Find the worksheet named "TWINS." Look over the column headings and remind yourself of what each one means:

AGE of twins
EDUC_TWIN1 years of education first twin
MALE_TWIN1 "dummy" variable that is 1 if twin 1 is a male, 0 if female
WAGE_TWIN1 wage earned by first twin
EDUC_TWIN2 years of education first twin
MALE_TWIN2 "dummy" variable that is 1 if twin 1 is a male, 0 if female
WAGE_TWIN2 wage earned by first twin
EDUC_DIFF the difference (twin 2 minus twin 1) in education
TENURE_DIFF the difference (twin 2 minus twin 1) in tenure, or number of years at current job.
WAGE_DIFF the difference (twin 2 minus twin 1) in wages
WAGE_DIFF_LOGN logarithm of WAGE_DIFF

2. Make a scatterplot of twins' wage differences vs. education differences: select the data range in columns I-K (we don't need the J column - we'll forget it in a moment). 3. On the ribbon, click tab for charts and then OTHER>SCATTER>MARKED SCATTER. You should see something like this:

4. Right click on the chart and choose SELECT DATA. You should see a dialog box like this:

Select Series 1 the box on the left and then make some edits to the boxes on the right as shown below. After you edit Series 1 you can delete the other two series.

Click OK and you should have a chart that looks like this:

Prettify the chart by adjusting size, deleting legend, editing title, inserting vertical grid lines, ending up something like this:

5. Do a right click on one of the points in the chart and select ADD TRENDLINE.

In the trendline dialog select OPTIONS:

.

PORTFOLIO: Copy and paste your chart into a Google doc that you title lname-fname-lab-20160308. Add commentary about the chart. What do you make of it? How much of the variation in wages seems to be explained by variation in years of education?

Make a scatterplot of California health care expenditures by year; add regression line.

6. Recode expenditures taking account for inflation. We will use the consumer price index (CPI) which tells us how much to adjust an amount in a given year's dollars to express it in terms of a given base year.

Suppose the CPI for a given year is 1.34 and the data amount is $2000. 7. Look up inflation rate from another Excel worksheet (called, surprise, "Inflation"). This sheet has year to year inflation rates since 1913 in the column CPI-U. In the column CumCPI we see a "multiplier" based on 1913 being "1." The number in column CumCPI was computed by multiplying each year's number by 1+inflationrate. To express our data in "constant dollars" we take the value in current dollars and multiply by this number. Thus, if we have$1000 in 1945, it would be 1800 in "1913 dollars." (1) \begin{align} 1000 \times 1.8 = 1800 \end{align} To adjust for inflation we will need to LOOK UP the cumulative CPI index for each year in our data. To do this we will use one of the most useful functions in Excel: VLOOKUP. VLOOKUP takes 3 required arguments and one optional one: VLOOKUP (lookUpValue, lookUpTableRange, columnToReturn, exactOrNot) We'll add the inflation index to our data table. On sheet HEALTH type "Inflation" in cell C1. Then in cell C2 type this formula: =vlookup(A2,inflation!A2:C\$98,3,false)

Then double click in the lower right corner of the cell to autofill down.

Now create a new column with the heading MedExpendCorrected and in the cell D2 write the formula

=B2*C2

and autofill down.

8. Make a chart with MedExpendCorrected on the Y-axis and Year on the X-axis. Label and tidy up. Add trend line, etc. as above.

PORTFOLIO: Copy and paste your chart into your Google doc. Add commentary about the chart. What do you make of it? What's the change over time look like?

page revision: 26, last edited: 08 Mar 2016 23:39