Activity 8.2

Consumer Indexes

10 points

Due at the beginning of class, Friday, March 20, 2009

In this activity, you will look at how the Consumer Price Index can be used to understand trends involving changes in costs and salaries over time.  Several “Consumer Indexes” are used to measure items related to consumers.  In particular, the Consumer Price Index (CPI) is used as a measure of inflation by considering how the price of commonly purchased commodities changes; it measures the price of a market basket of a large number of goods and services purchased by consumers.

1.  The following table gives the median weekly earnings, in dollars, of fulltime wage and salary workers, 25 years and older, by educational attainment from the years 1980 to 2005 along with the CPI for the same years. (Source: U.S. Bureau of Labor Statistics.)

1. a. By what percentage did the CPI increase from 1980 to 2000?

1. b. By what percentage did the median weekly salary for workers with four years of high school only increase from 1980 to 2000?

1. c. By what percentage did the median weekly salary for workers with one to three years of college increase from 1980 to 2000?

1. d. By what percentage did the median weekly salary for workers with four or more years of college increase from 1980 to 2000?

1. e. Explain what these percentages show about salaries for these groups of workers from 1980 to 2000.

2.  Retrieve the file EA8.2 Median Salaries and CPI.xls.  This file contains the information given in the previous table. You will first create one graph showing median weekly earnings of the three groups of workers. To do this without graphing the CPI column, follow these instructions. Note that there are more years of data in the file than mentioned below, so you should highlight beyond cell 15 in each column.
.

3.  Explain what your graph shows about the salaries of the three groups of workers.

4.  You will now add columns that will contain these salary figures converted into constant 2000 dollars. To the right of each of the salary columns, create one additional column.
.

5. Next, you’ll work on converting each of the salary values to dollars in year 2000, starting with cell 2 of the “High School, 4 Years Only” column, and when you are finished (in #7 below), you should paste this table into your Word document.

To convert this value to 2000 dollars (interpreted to be dollars at time O), use the ratio:

Because you want to convert values to time O dollars, when solving for dollars at time O, you get:

With time A as 1980 and time O as 2000 (because you’re converting to 2000 dollars), you will calculate for the first year in the “High School, 4 Years Only” column of the table,

Compute this value, and enter it in your Word document.

Similarly, for the next year in the “High School, 4 Years Only” column, you’ll have:

Compute this value, and enter it in your Word document.

6. In column D (labeled something along the lines of “High School, 4 Years Only in 2000 $”) of your spreadsheet, use the following Excel instructions to compute the “High School” salaries from 1980 to 2000 in 2000 constant dollars.

7. Enter the appropriate formulas and convert the “College, 1 to 3 Years” and “College, 4 or More Years” salary values to 2000 dollars in the columns you added for this purpose. Paste your updated table into your Word document.

8. Now create a scatterplot using the “Year” column and the three columns containing the salaries converted to 2000 dollars. (Remember to use the Ctrl key to highlight noncontiguous columns, as you did when making the graph in #2.) Explain what this graph shows about the salaries of the three groups of workers: “High School, 4 Years Only,” “College, 1 to 3 Years”; and “College, 4 or More Years.” Paste this scatterplot into your Word document.

Summary
By investigating the Consumer Price Index over a 20-year period and using it to compare salaries, in constant dollars, during that period of time, you experienced the effects of looking at values over time in constant dollars. You also learned how to construct a scatterplot using noncontiguous columns, how to add a column, and how to name a cell in Excel.