Multi-chapter Investigation Project: CPI and earnings over time

Modeling data using linear relationships: CPI and earnings over time

Background

In this exercise you will investigate how the CPI in Canada changed over a number of years and if there is a relationship between CPI and time that can be modeled by the data. You will then investigate how this model can help us answer questions relating to change in earnings in different industries and if they kept up with inflation. You will develop and use mathematical tools to analyze trends and make projections about expected earnings in different industries and their relationship to changes in CPI.

Overview

Task 1: Understanding CPI

Task 2: Finding and investigating the Canadian historical CPI data and its trends

Task 3: Modeling the Canadian CPI data using linear regression

Task 4: Adjusting the CPI model

Task 5: Investigating trends in earnings by industry

Task 6: Investigating trends in earnings by industry in relation to CPI as indicator of inflation

TASK 1: Understanding CPI

Visit Statistics Canada Prices and price indexes portal and explore information therein. In particular,

1. Watch An Overview of Canada’s Consumer Price Index (CPI) video (if unable to play it in full, search it on YouTube on the Statistics Canada channel – and make notes!
2. Watch Statistics Canada Training Institute – Producer price indexes video – and make notes!
3. Check out Consumer Price Index Data Visualization Tool and, within it,:
   • Latest Snapshot of the CPI: consider the main item categories that go into the consumer basket – which ones have the highest weight and do their weights compare to percentage of your own spending within those categories?
   • Price trends: 1914 to today: consider the graph and identify what the numbers on the horizontal and vertical axes represent; then change Start Date to 2000, then to 1914 – what do you notice in relation to your historical knowledge of the economy?

TASK 2: Finding and investigating the data

1. Getting the data – create the data spreadsheet
   • Search for Statistics Canada CPI, annual average and open the page Consumer Price Index, annual average, not seasonally adjuste
   • Locate the information in the table where it states which year is considered to be the base year.
   • Click on Add/Remove Reference Period, set the From field to the base year, and click on Apply.
   • What data in the table that opens verifies that that is the base year?
   • Click on Download Options and choose CSV: Download as displayed (excluding accompanying symbols)
   • Save the spreadsheet as an Excel Workbook (.xlsx file); name it CPIAnalysis.
2. Visualizing the data – plot the earnings data on a graph
   • Select the CPI data, including the column and row headings; then click on Insert – Charts – Scatter with Smooth Lines and Markers.
   • Right-click on the chart and select Move Chart – New Sheet and name it by typing CPIChart in the box.
   • Go to the newly created worksheet with the graph of the earnings data you selected and type Canadian CPI by Category into Chart Title box.
   • This should fill in the chart better and spread out the lines for better visualization.Adjust the vertical scale to remove the empty area below the lowest data points:
   • Right-click on vertical axis scale numbers, choose Format Axis and change Bounds Minimum to a value that is below, but relatively close to the lowest data point value.
3. Making sense of the data – write down your observations. For example:
   • What does the vertical axis represent? What does the horizontal axis represent? Add axis titles: Click on Add Chart Element, select Axis Titles and type in the axis description for each of the axes.
   • Is the CPI growing for all basket categories? If not, for which one(s) is it not growing?
   • Which category’s CPI has the rate of lowest growth? The highest rate of growth? The wildest rate of growth?

TASK 3: Modeling the CPI data using linear regression

1. Creating the chart
   • Go to the worksheet with the data and select the data in the All-items row, including the row headings; then click on Insert – Charts – Scatter.
   • Right-click on the chart and select Move Chart – New Sheet and type in CPIAllItems.
   • Go to the newly created worksheet with the graph of the earnings data you selected and type Canadian CPI into Chart Title box.
   • Adjust the vertical scale to remove the empty area below the lowest data points (follow steps in 2) from Task 2.
2. Understanding the graph
   • What does the vertical axis represent? What does the horizontal axis represent? Add axis titles (see steps in 3) for Task 1).
   • Do the points on the chart appear to follow a trend (increasing or decreasing; straight line or a wiggly line)?
3. Modeling the data using linear regression
   • Right-click on one of the data points, then select Add trendline. What was added to the chart?
   • Under Trendline Options, click on Display Equation on Chart (you can move it around and increase the font for greater visibility) and try different types (exponential, linear, …) – what do you notice?
   • Choose Linear and write down the equation in two variables ( and ) that is provided. This equation is called a linear regression model and it provides the line of best fit for the CPI values in terms of year.
   • What does the variable represent and what does the variable  represent? What do the numbers in the equation represent?

TASK 4: Adjusting the CPI model

1. Adjusting the input values for the model to reflect the relevant time period
   • Insert a row underneath the current row with column headings for years, name the row Years since base year and enter years since base year values into cells corresponding to each of the years.
   • In the CPIAllItems worksheet, right-click on the chart and choose Select Data; click on Edit (under Legend Entries (Series)), then change the row reference number to the row number of the adjusted input values and click OK.
   • How has your chart changed? Has the equation modeling your data changed? What does the variable represent and what does the variable  represent? What do the numbers in the equation represent? Edit axis titles where necessary (see steps in 3) for Task 2.
2. Making sense of the data – investigate the trends and make predictions
   • If you substituted the value of 6 for [latex]x[/latex] into the equation and found the corresponding value of [latex]y[/latex], what would the value of [latex]x[/latex] and [latex]y[/latex] tell you? How does that compare to the CPI value for the corresponding year in the data table from Statistics Canada?
   • Can you use the linear regression model to forecast the CPI in year 2023? In 2050? Write down the expected CPI values for those two years, with 2002 as the base year. What do these results mean?
   • Why do you think CPI forecasts may be valuable to you, to the businesses and the economists, and to the policy makers in the government? Write down your thoughts.

TASK 5: Changes in earnings per industry

1. Getting the data – create the data spreadsheet
   • Search for Statistics Canada weekly earnings by industry, annual and open the page Average weekly earnings by industry, annual
   • Click on Add/Remove Reference Period, set the From field to the base year from Task 1, and click on Apply.
   • What are the two main industry classifications (groupings) in the data table?
   • Click on Download Options and choose CSV: Download as displayed (excluding accompanying symbols)
   • Save the spreadsheet as an Excel Workbook (.xlsx file); name it EarningsAnalysis.
2. Visualizing the data – plot the earnings data on a graph
   • Adjust the input values for the model to reflect the relevant time period by adding a row with years since the base year (follow steps in 1) from Task 4).
   • Select the row with years since base year and the rows of any two (2) goods producing industries and any two (2) service producing industries; then click on Insert – Charts – Scatter with Smooth Lines and Markers.
   • Right-click on the chart and select Move Chart – New Sheet and name it by typing EarningsChart in the box.
   • Go to the newly created worksheet with the graph of the earnings data you selected and type Weekly Earnings into Chart Title box.
   • Adjust the vertical scale to remove the empty area below the lowest data points (follow steps in 3) from Task 2).
3. Making sense of the data – write down your observations. For example:
   • What does the vertical axis represent? What does the horizontal axis represent? Add axis titles (see steps in 3) for Task 2).
   • What trends do you notice? Are the earnings increasing or are they decreasing? Are they doing so at a consistent rate or are they fluctuating?
   • Which industry’s earnings have the rate of lowest growth? The highest rate of growth?

Task 6: Weekly earnings and inflation

1. Creating earnings index data – calculate weekly earnings indexes
   • Open a new worksheet within the document and name it EarningsIndex.
   • Copy into the worksheet the column headings row with years since base year.
   • In the next row, copy the All-Items CPI data from the spreadsheet created in Task 1. Name the row CPI Index.
   • Name the rows below for each of the industries selected in Task 5 with a reference to index and the industry category. For example, if you selected the manufacturing industry, name the row manufacturing earnings index.
   • For each of the selected industries, enter 100 for the index under the base year column. For all other years, enter the formula that calculates the earnings index for that specific year and industry (use index ratios discussed in class to create the appropriate formulas and enter the formulas into corresponding cells.)
2. Visualizing the data – plot the earnings index data on a graph
   • Select the rows with index information, including the row and column headings; then click on Insert – Charts – Scatter.
   • Right-click on the chart and select Move Chart – New Sheet and name it by typing IndexChart in the box.
   • Go to the newly created worksheet with the graph of the earnings data you selected and type Weekly Earnings Index into Chart Title box.
   • Adjust the vertical scale to remove the empty area below the lowest data points (follow steps in 2) from Task 2).
3. Making sense of the data – write down your observations. For example:
   • What does the vertical axis represent? What does the horizontal axis represent? Add axis titles (see steps in 3) for Task 2).
   • What trends do you notice? Are the earnings indexes increasing or are they decreasing? Are they doing so at a consistent rate or are they fluctuating?
   • Would it be reasonable to try to model these index trends using a linear model (a straight line of best fit) and why?
4. Modeling the data using linear regression
   • For each of the data point sets, right-click on one of the data points, then select Add trendline and, under Trendline Options, choose Linear, click on Display Equation on Chart
   • Write down the equations in two variables ( and ) that are provided for each of the selected industry earnings index linear models and for the CPI.
5. Making sense of the models – write down your observations. For example:
   • What does the variable represent and what does the variable  represent for each of the equations? What do the numbers in the equations represent?
   • How does the earnings index for each of the selected industries compare to CPI over time?
   • Can you use the linear regression models to forecast the CPI and the earnings indexes in year 2023? In 2050? Write down the expected index values for those two years, with 2002 as the base year. What does that mean to the change in earnings in real terms?
   • Why do you think forecasting earnings index and the CPI and the relationship between them may be valuable to you, to the businesses and the economists, and to the policy makers in the government? Write down your thoughts.

 THE END

Attribution: Created by Ana Duff (Ontario Tech University), published under Creative Commons Attribution-NonCommercial-ShareAlike licence (CC-BY-NC-SA)

Any modifications to this case study may affect its solvability and caution is advised.