Transcript Linear Models and Trendlines

Welcome to LSP 120
Dr. Curt M. White
What is LSP 120?
First course in QRTL (Quantitative
Reasoning and Technological Literacy)
Also known as Quantitative Reasoning
First Year Program requirement
Prereq to course is ISP 110 or Math 101
or placement through advising
Know this stuff already? Take the exam
this week.
What is LSP 120?
We will examine data – look at it, talk
about it, graph it, manipulate it
To graph and manipulate we will use
mathematics and technology
Why do we want to do this?
What is LSP 120?
Because the person that can use data
(information) gains knowledge and thus
has power!
In college – writing research papers and
taking advanced courses
In your job – career advancements
In government – getting things done (?)
In life – more intelligent life decisions
What is LSP 120?
“Information is a beacon, a cudgel, an
olive branch, a deterrent – all depending
on who wields it and how. Information is
so powerful that the assumption of
information, even if the information does
not actually exist, can have a sobering
effect.”
What are the topics?
Linear and near-linear models (with
trendlines and forecasting)
Exponential models
Useful percentages
Graphing
Consumer Price Index
Absolute versus relative values
Financial models
What is LSP 120?
Let’s take a closer look at the syllabus
LSP 120
Linear Models
and Trendlines
Linear Relationships
What makes a graph or function or table of
values linear? (You have already seen this!)
For a fixed change in x, there is a fixed
change in y, or
The change in y per unit change in x is
constant, or
There is a constant rate of change
Linear Relationships
Why do we care if a set of data is linear?
If a data set is linear (or near-linear), then
we can better predict where the data will
be in the future
We can also go back in time and see
where the data has been!
Linear Relationships
Look at a set of values. Is it a linear
relationship? Apply (B3-B2)/(A3-A2)
Column A Column B
(X values) (Y values)
2
3
4
5
6
7
4
5
6
7
8
9
25
50
75
100
125
150
No entry in this first row
=(B3-B2)/(A3-A2) = (50-25)/(5-4) = 25
=(B4-B3)/(A4-A3) = (75-50)/(6-5) = 25
=(B5-B4)/(A5-A4) = (100-75)/(7-6) = 25
=(B6-B5)/(A6-A5) = (125-100)/(8-7) = 25
=(B7-B6)/(A7-A6) = (150-125)/(9-8) = 25
All the results are the same (25), so this is a linear set of values.
Linear Relationships
If it is linear, what is the function?
Recall: y = mx + b
m is the slope, or the (change in y) / (change in
x)
b is the y intercept
So calculate the slope
Then plug in slope and first x and y values
into y=mx+b and solve for b
Linear Relationships
2
3
4
5
6
7
A(or X)
4
5
6
7
8
9
B(or Y)
25
50
75
100
125
150
No calculation in this row
(B3-B2)/(A3-A2) = (50-25)/(5-4) = 25
(B4-B3)/(A4-A3) = (75-50)/(6-5) = 25
(B5-B4)/(A5-A4) = (100-75)/(7-6) = 25
(B6-B5)/(A6-A5) = (125-100)/(8-7) = 25
(B7-B6)/(A7-A6) = (150-125)/(9-8) = 25
y = mx + b
m = change in y / change in x = 25/1 = 25
25 = 25 * 4 + b
b = -75 (this is the y-intercept)
y = 25x – 75
This is the equation of the line
Examples
x
5
10
15
20
y
-4
-1
2
5
x
0
2
4
6
y
1
4
16
36
m=change y / change x = 3/5=0.6
y = mx+b -4=0.6*5+b b=-7
y = 0.6x - 7
x
3
6
9
12
y
5
9
13
17
Rate of Change
=(B3-B2)/(A3-A2)
x
3
4
6
9
y
5
9
11
17
Linear Relationships
Linear growth – occurs when a quantity
grows by the same absolute amount
Exponential growth – occurs when a
quantity grows by the same relative
amount – that is by the same percentage –
in each unit of time
There is also linear decay and exponential
decay
Examples
 The number of students at Wilson High School
has increased by 50 in each of the past four
years.
 The price of milk has been rising with inflation at
3% per year.
 Tax law allows you to depreciate the value of
your equipment by $200 per year.
 The memory capacity of computer hard drives is
doubling approximately every two years.
 The price of DVDs has been falling by about
25% per year.
Trendlines
Real data is seldom perfectly linear
Suppose the data is reasonably linear or
near-linear?
How does one make a linear model of the
data?
The standard approach is to use a “bestfit” line
In Excel – note the equation for the trendline and the R2 value.
Trendlines
If R2 = 1, then 100% of the variance in y is
explained by the line, so we have a perfect
fit (the data is linear)
If R2 = 0, then we have a terrible fit.
(Better not make a prediction)
What if R2 value = 0.5?
Trendlines
Do we have to do this calculation?
No, Excel can do it for you
Let’s say you have made a graph of some
data using Excel
After you make
a graph,
right-click on
any datapoint.
Select
Add Trendline…
Then check the
two bottom boxes
Trendlines
Let’s take a look at the dataset
Motorcycles_By_Year2005.xls
Graph the data using an XY Scatter. After
the graph is done, right click on a data
point, select Add Trendline
Trendlines
How do you make a prediction?
Two possible techniques:
 1. Extend the trendline using Excel
 2. Use the slope / intercept model (next slide)
Be careful! One’s confidence in
predictions made far from the data must
be tempered!!
Trendlines
You can use the equation for the trendline,
but the terms in the trendline equation are
significantly rounded.
Can remove some of this rounding
Right-click on the formula in the graph and
then click on Format Trendline Label
Select Number on left and right
Let’s give it 6 decimal places
Trendlines
You can also have Excel calculate the
Slope and Intercept individually, and then
use them in an equation
Somewhere in Excel, enter label Slope
and in next cell to right, enter =Slope(
Do the same thing for Intercept
Let’s Go!
Let’s head to the lab and start our first
Activity
But first, let’s break up into groups