Pages 2-4 of this module are basic: getting the data and graphing it. The remaining pages are optional and mostly independent. Pages 5 and 6 concern the exponential trend curve and the logarithm of the data. Page 7 is about inflation rates, usually measured by the growth rate of the CPI instead of the CPI itself. Page 8 shows how to adjust numbers for inflation (constant dollars). Page 9 is about components of the CPI. Sometimes one component, such as college tuition, has high inflation, while another component, say clothing, has low inflation or even deflation. The final page is a list of exercises by which students can practice the skills learned in the module. To download an Excel file that contains all the data for this module, click here.
In addition to the Internet and spreadsheet skills presented in this module, there are many opportunities to present or review functional relationships, graphing skills, logarithmic and exponential calculations, and regression concepts.
Here is a quotation from a recent article by Allan Rossman, one of the principals in the Workshop Precalculus Project based at Dickinson College:
"... Presenting precalculus students with real data to analyze can increase their motivation (or at least soften their disinterest) by addressing the 'where am I ever going t o use this' question head-on. Moreover, data analysis can also help students to investigate and understand the crucial concept of function that undergirds all precalculus topics. Finally, examples that use real data can help to teach students some lessons about issues that arise in everyday life.
"All Americans have a stake in data that are collected and analyzed by the U.S. government. The most important of all the government statistics is the Consumer Price Index (CPI) ..."
Method 3a is faster and permits more options than method 3b. But method 3a requires a Java-enabled browser.
The downloaded data (Figure 2) has a column of years, from 1913 to 2002, with corresponding values of the CPI. I suggest making a new column, Years Since 1913, and using those numbers instead of the calendar years to make the graph. That step is not necessary -- either way, you can fit an exponential curve to the data. But if you use calendar years, the equation of the exponential curve is -- the coefficient of the exponential function may look a lot like 0 to students. If you use years since 1913, the equation is .
Figures 13 through 16 were made on the Web, using components of the index with monthly data for the last 10 years. You may wish to show students how to graph several components of the index on the same grid so that the components can be compared. You can make several columns of annual data for any desired component. If you wish to focus on, say, the last decade, then annual data makes a jerky graph. Monthly data makes a smoother graph.
Each downloaded component with monthly data has the data in rows, a pair of rows for each year. Such a table is easy to read, but difficult to graph in Excel. One solution is to use CPI-All Urban Consumers (Current Series), Flat Files, at the Bureau's Web site. Clicking on this option gives a list of files. If the names are not displayed, click View, Details. Select each desired file, and choose File and Copy to Folder. Use a file name and folder as desired. Then in Excel, use the commands, Data, Get External Data, and Import Text File. Ask to see all documents, not just text documents. Select the first file you just made, say Medical. Click Import. The defaults will import the text in columns. Replace the word "Value" as a heading with "Medical." Now go to a vacant cell, say G1, in your worksheet, and repeat the procedure to import the next file, and so on.
Say you want data since 1993. Delete all rows above the row for January 1993. Delete all columns except the ones with index values. You now have several columns of data, one column for each component. Insert at the left a column for time values. Start with Jan/93, then Feb/93. Highlight these cells and drag down to make a column of dates, a sequence in the same format. Select all the data, including the headings, and follow the Chart Wizard as on Page 3. The result is the CPI graphed for several components. If you wish to show 12-months percent changes, calculate these values in the spreadsheet and graph them, as on Page 7.
The CPI compares prices to a base of 100 in 1982-84. For example, in 2002 the CPI was 179.9. The purchasing power in 2002, compared with the base year, is the ratio
Thus the dollar in 2002 was worth only 56% of its value in 1982-84. In general, to find the purchasing power, calculate the ratio (in percent form) of 100 to the CPI. Purchasing power may be more dramatic than the CPI. Excel can be used to make a table and graph of it. Or this information can be extracted from the Web, as with the components described on Page 9.
If students know a little statistics, you may wish to explain regression. It involves paired numerical observations, an independent variable x and a dependent variable y. One calculates the regression line and the mean of the values of y. For each value of x, one calculates the value on the regression line, . The Sum of Squares Due to Error is found by calculating each deviation of y from the corresponding value on the regression line, squaring it, and adding:
The Sum of Squares Due to Regression involves each deviation of the value on the regression line from the mean of all the y's:
The Total Sum of Squares involves each deviation of y from the mean:
It can be shown that SST = SSR + SSE.
The coefficient of determination is defined by
This statistic indicates how well the regression line determines the value of y. The square root of this statistic is r, the correlation coefficient -- positive if the line slopes up, negative if the line slopes down.
Students may some day want to publish technical work or display it, and this information may be useful. They may enjoy an attractive graphic even if it is not shown to others.
You may wish to show students how to change the font in Excel. The default is often Arial (Helvetica). Another font might be more appealing. Arial is a sans serif font (no tails on the open ends of characters). Other fonts are serif (with tails). In print and transparencies, serif is more legible, but on the computer monitor sans serif may be better. Among the common fonts, I suggest
Times New Roman for print on paper (serif)
Verdana for the monitor (sans serif designed for the Web)
Georgia for the monitor (serif designed for the Web)
These fonts are suggested by Lynch and Horton. In Excel you can change the font one worksheet at a time, but it is easier to change the default font. To do so, go to Tools, Options, General, Standard font. In the drop-down box, select a font. Click OK. Close Excel. When you open it again, the chosen font will be the default for all new workbooks.
You might want to use Verdana or Georgia while working on a document at the computer. Then change the font for the entire document to Times New Roman to print it.
If a graphic is printed adjacent to text, then you might want text in the graphic in sans-serif to distinguish it readily from the rest of the text (serif). In that case, Arial might be a good choice.
You may wish to ask students to save their data and graphs and show them to you, either as a computer file or in print. Most of the exercises depend on where and when you assign them, but here are answers for a few: