Enter the data into your graphing calculator. Determine a linear model for the data. The linear model can be the linear regression determined by your calculator, or your own linear model. Record your linear model here.
Overview
The 2-3 day lesson has three related components. The first activity teaches students how to create a spreadsheet that will allow them to calculate the least squared error of a sample data set. The second activity will be to ensure that students understand the effect of the constants in a sinusoidal function, Asin(Bx+C)+D, as well as how to incorporate a general trend line with the sinusoidal function. The third activity will involve downloading data from the Census Bureau website, fitting a curve to the data and analyzing the model by examining the sum of the squared errors.
Standards
Computational Thinking in STEM
- Data Practices
- Analyzing Data
- Collecting Data
- Creating Data
- Manipulating Data
- Visualizing Data
- Modeling and Simulation Practices
- Assessing Computational Models
- Using Computational Models to Find and Test Solutions
- Using Computational Models to Understand a Concept
- Computational Problem Solving Practices
- Developing Modular Computational Solutions
- Troubleshooting and Debugging
- Preparing Problems for Computational Solutions
Activities
- 1. Activity 1: Linear Models and Sum of the Squared Error
- 2. Activity 2: Transformations of Sinusoidal models
- 3. Activity 3: Modeling with Census Data
Student Directions and Resources
- Students will understand how changes in the constant values A, B, C, and D impact the function Asin(Bx+C)+D.
- Students will analyze the strength of their model by minimizing the sum of the squared errors.
- Students will learn how to incorporate a trend line into the sinusoid in the form Asin(Bx+C)+mx+b.
1. Activity 1: Linear Models and Sum of the Squared Error
Housing starts are the number of new residential construction projects that have begun during some period of time. The number of housing starts is considered to be a critical indicator of economic strength.
Table 1 shows the annual housing starts (in thousands) for single-family homes. In this activity, you will enter the data into a spreadsheet along with an adjustable linear model.
Table 1: Annual Housing Starts (in thousands) for single-family homes 1990-1999
|
Year |
‘90 |
‘91 |
‘92 |
‘93 |
‘94 |
‘95 |
‘96 |
‘97 |
‘98 |
‘99 |
|
Housing |
895 |
840 |
1030 |
1126 |
1198 |
1076 |
1161 |
1134 |
1271 |
1302 |
Question 1.1
Question 1.2
1. Open a spreadsheet using either Google Sheets or Excel. Create column headings exactly as shown below:
2. Enter the information from Table 1 into your spreadsheet: Year (in Column A) and Housing Data (in Column B). Make sure to enter the first row of data in Row 2.
3. Enter the slope you calculated for your model into F2. Enter the y-intercept of your model into G2.
4. For the Year A2, Housing Starts B2, slope F2, and y-intercept G2, what is the formula for the linear model? Write your answer below.
Question 1.3
In both Google sheets and Excel, the formula for your model should be entered as:
=F2*A2+G2
Since we are using the same slope and y-intercept, we want to fix the values of F2 and G2, no matter where we use the formula. To do that, write the formula with a $ symbol for the row and column we want to stay fixed. Enter this formula in cell C2:
Enter the formula into cell D2 that you see here:
Describe in words what the squared error measures.
Question 1.4
1. Auto fill the columns for C and D by selecting both cells C2 and D2, then dragging down the “Autofill square” corner highlighted below:
The spreadsheet should automatically calculate the appropriate model values and the squared error for values in the other rows.
2. Finally, we want to calculate the sum of the squared errors. At the bottom of Column D, type in the following sum command:
3. Adjust the values for Slope and Intercept to minimize the sum of the squared errors.
Upload a screenshot of your spreadsheet or the file you created.
Upload files that are less than 5MB in size.
| File | Delete |
|---|---|
Upload files to the space allocated by your teacher.
2. Activity 2: Transformations of Sinusoidal models
The purpose of this activity is to understand how the parameters A, B, C, and D impact the graph of y = Asin(Bx+C)+D and how replacing D with a linear expression in the form mx+b affect the shape of the curve.
Question 2.1
Go to the website https://www.desmos.com/calculator. In the upper left hand corner, type in the equation y = Asin(Bx+C)+D as shown below.
Sliders can be created for all the parameters. Change the value of the parameters to see how they affect the shape of the graph. Record your observations:
1) How does the graph change as you change the value of parameter A?
2) How does the graph change as you change the value of parameter B?
3) How does the graph change as you change the value of parameter C?
4) How does the graph change as you change the value of parameter D?
Question 2.2
Replace parameter “D” with the expression “mx + b” into the Desmos calculator as shown below.
Adjust the values of “m” and “b”. How does modifying the parameters m and b impact the shape of the curve?
3. Activity 3: Modeling with Census Data
In this activity, you will examine data in a spreadsheet and attempt to create an equation that models the data. You will download data on New Residential Construction found on the United States Census Bureau website. You will model the data and analyze the strength of your model by examining the sum of the squared errors.
Question 3.1
Visit this site to gather residential construction data. Examine data from any time period, but you must use a minimum of 5 years worth of data.
Make sure that your data is “Not Seasonally Adjusted” as shown in the check box below.
You can examine an initial graph of the data before downloading it by selecting the line chart option as shown below.
The data can be downloaded into a spreadsheet by selecting the “XLS-V” option. Once the data is downloaded into a spreadsheet, use the spreadsheet software to create a scatter plot of the data.
Save the spreadsheet and upload it.
Upload files that are less than 5MB in size.
| File | Delete |
|---|---|
Upload files to the space allocated by your teacher.
Question 3.2
Develop a sinusoidal model of the data, and incorporate that model into the spreadsheet. Plot the actual data and model predictions on the same scatter plot.
Submit a file of the model and scatter plot, or a screen shot.
Upload files that are less than 5MB in size.
| File | Delete |
|---|---|
Upload files to the space allocated by your teacher.
Question 3.3
What is the sum of squared errors?
Question 3.4
Use your calculation of the sum of the squared errors and use that value to improve your fit. Submit the new file or screen shot.
Upload files that are less than 5MB in size.
| File | Delete |
|---|---|
Upload files to the space allocated by your teacher.
