In this project you will retrieve and manipulate data on the Consumer Price

Index (or CPI) for 3 cities – Boston, Atlanta, and San Francisco – over a period of a number of years. You will present your results using Excel and charts, and those objects will be embedded in a short document (written in WORD).

The CPI measures what it costs a family to live. It includes expenses such as rent, heat, car maintenance, health care, and food. The Bureau of Labor Statistics (BLS) (of the Dept. of Commerce) does surveys each month to measure this. The BLS publishes this both for selected cities and for the whole country.

The CPI is measured relative to a base year (either 1967 or 1982-4---- your choice). That is, a CPI of 165, relative to the base year of 1967 means that it now costs $165 to buy what would have been bought in 1967 for $100.

You should use for this project CPI-U, which is the CPI for all urban wage earners and consumers.

Using the search engine of your choice, find the CPI-U data at the BLS for the cities of

Boston, Atlanta and San Francisco. You may use the annual data for the years 1956-1998. The per cent change in CPI, or rate of inflation, during 1997 is

CPI for 1997

formatted as a per cent. The fraction (for a 5% inflation rate) will be 1.05. Subtracting the 1 gives you the change as a decimal.

The formula for the annual percent change in CPI for the period from 1988-1998 is

CPI for 1988

formatted as a per cent. The exponent of 0.1 = 1/10 is because we are looking at a 10 year time period.

After you retrieve your data you are to compute the rate of inflation in each of the cities for each year 1958 through 1998. You are also to compute the rates of inflation for each city for the 10-year periods 1958-68, 1968-78, 1978-88 and 1988-98.

You are to examine the results of your computations and present them in a meaningful way. Your report should include a line graph of the annual inflation rates for each of three cities (a different color for each city). You should also have bar graphs showing the inflation rate for each decade (with cities in a different color) and for each city (with the decades in a different color). Your report should discuss and interpret the results intelligently. You may find other things that you wish to examine – such as cumulative changes in inflation or a time plot of the CPI for various cities or projections for the next two years. You will need to have at least one page of text (aside from graphs) describing what you see.

 In addition to the charts you should include part or all of the spreadsheet in your report.

At least one of the objects that you have included in the report should be linked and at least one should be embedded. TELL ME on the cover sheet which objects in the report are linked and which are embedded.

The spreadsheet should be formatted nicely (for legibility), but you should not spend huge amounts of time "fine-tuning" the appearance.

Finally, on a new sheet in the workbook, you should take the CPI data for Boston and project it out for the next 10 years using (a)a 3% rate of inflation (b) a 5% rate of inflation and (c) a rate of inflation which is 3% for 1999, 4% for 2000, 5% for 2001, etc..

Do (a) and (b) in columns where the rate of inflation is a constant (absolute or mixed reference) and do (c) in a column which uses a formula. Draw a line graph showing the three possible future CPI's and include it on that sheet.