Worksheet 7 Consumer Price Index

Salary.xls is an Excel workbook with four worksheets. Each worksheet contains a sample salary history for ten years. In the first worksheet, the person earned a 0% raise in each year. In the second worksheet, the person earned a cost of living raise in each year (their salary increases with inflation). In the third worksheet, the person earned an 8% raise in each year. In the last example, the person earned a mixture of raises (either 0%, increases with inflation or 8%).

To convert data to constant dollars, you need:

• A file containing annual dollar amounts.  For this example, we will use Salary.xls.
• CPI.xls

1.  Click on the 0% increase worksheet. This person has received no raise for the past 10 years.

a.  Convert each salary to constant 2005 dollars and create a graph using the following steps:

i.  Paste the CPI factors for the corresponding years in column C.

ii.  Convert the salary to constant dollars in column D (=$C$14/C4*B4).

• To change C14 (a relative cell reference) to $C$14 (an absolute cell reference), click on C14 then strike the F4 key which is found along the top of your keyboard.
• The F4 key adds a "$" in front of the letter C and a "$" in front of the number 14.  Fill-down the formula.

iii.  Make a graph of the years, salaries and the salaries converted to constant 2005 dollars. (Do not graph the CPI indices in column C.)    

iv.  Paste this graph in your Word document.

b.  Describe the differences between the two lines. What does it mean if the constant dollar line is going down while the actual salary amount remains constant?

2.  Click on the Increases with Inflation worksheet. This person has received a cost-of-living increase in each year.

a.  Follow steps i) though iv) in question 1a) above to convert the salaries to constant 2005 dollars and make a graph.

b.  Describe the differences between the two lines. What does it mean if the constant dollar line is horizontal compared to the salary line which is rising?

3.  Click on the 8% Increase worksheet. This person has received an 8% raise for the past 10 years.

a.  Follow steps i) though iv) in question 1a) above to convert the salaries to constant 2005 dollars and make a graph.

b.  Describe the differences between the two lines. Is this person earning more now than in 1995?

4.  Click on the Various Salary Changes worksheet. This is a mixture of 0% increases for a period of time, cost-of-living increases for a period of time and 8% increases for a period of time.

a.  Follow steps i) though iv) in question 1a) above to convert the salaries to constant 2005 dollars and make a graph.

b.  Using what you learned in questions 1 through 3, identify which 3-4 consecutive years the person received a cost-of-living raise (salary increases with inflation), a 0% raise and an 8% raise.