Lynn University Graphing Lines Linear Equations Excel Mini Project 2

Excel Mini-Project 2: Graphing LinesName:
Overview
Directions
Instructor:
We are going to learn how to graph linear data on Excel. We will also learn about b
We will see how to turn data into a graph a
Step 1: Recognizing that our data is linear:
Go to Sheet2 and view the data and answer question 1 on Sheet4.
Create a scatterplot to view the data graphed as a linear equation.
Turn on the “trendline” and turn on the “show equation on chart”(not on
If you are on a MAC or iPad you will need to get the equation by hand.
Answer questions 2, 3, and 4 on Sheet4.
Step 2: Creating a linear equation to model a problem:
Go to Sheet3. Calculate the simple interest. (answer Q. 5)
Calculate the next 5 values in the table. (answer Q. 6)
Identify the independent and dependent variables. (answer Q. 7)
Come up with an equation to model the data. (answer Q. 8)
Test the equation by plugging in some values. (answer Q. 9)
Step 3: Make sure you answer all the questions on Sheet4.
We are done! (unless you want a challenge … then go to Sheet5)
Tips!
Look for color-coded tips on each step if you are stuck on how to use Excel. They w
If you venture outside these thick blue bars you have gone too far! All of the mater
But feel free to use this area for notes or calculations or experimentation!
Date:
will also learn about bonds and simple interest as an example of a linear equation.
n data into a graph and into an equation.
1 on Sheet4.
ar equation.
on on chart”(not on MAC).
quation by hand.
nswer Q. 7)
to Sheet5)
to use Excel. They will be highlighted in green.
far! All of the material is inside of these bars.
mentation!
Below is data showing the value of Lin Financio’s
bond as the months go by.
Month
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Bond Amount
$500.00
$525.00
$550.00
$575.00
$600.00
$625.00
$650.00
$675.00
$700.00
$725.00
$750.00
$775.00
$800.00
$825.00
$850.00
$875.00
$900.00
$925.00
$950.00
$975.00
$1,000.00
$1,025.00
$1,050.00
$1,075.00
$1,100.00
Step 1:
Recognizing that our d
Go to Sheet2 and view
Create a scatterplot to
Turn on the “trendline
If you are on a MAC or
Answer questions 2, 3
Place your graph in this box:
Tips for Step 1:
first:
To create the scatterp
Highlight your data; al
and y on the right, (yo
second: Click “insert”, then “ch
are all correct here). A
You should now have
third:
Click on the graph (an
“add chart elements”
Here is where you can
fourth: In that menu select “t
and here you can sele
for iPad: If you are on an iPad y
equation. In that case
First get the slope usin
Then find the y-interce
Now write the y = mx
Now Lin wants to model how interest accrues in a bond under a different
circumstance. We will help them create an equation they can use to predict the
bond’s value for any given time length.
Financial concept:
Bonds are sometimes set up to accrue simple interest as we saw in the previous example.
This means that the interest is only calculated once, on the principal amount. (simple interest = rate*pr
Then that calculated interest is added at each step but it stays constant.
This is much simpler than other financial systems like stocks or savings accounts that use compound inte
Lin’s new bond:
Principal =
Rate =
Simple Interest =
$1,100.00
6%
Lin’s table representing the growth of the bond:
Month
Bond Amount
0
1100
1
2
3
4
5
Math concept:
In linear equations the slope, m, and the y-intercept, b, have real-world applications and qualities.
The y-intercept represents the initial value, or the starting point.
The slope represents a constant change in the y-value for every constant change in the x-value.
i.e. if the independent variable, x, changes by a constant amount, then the dependent variable, y, will
also change by a constant amount equal to the slope. (note these two constant changes are usually diffe
from each other; the x-change is usually 1 unit)
Building the linear equation:
independent variable:
dependent variable:
slope: m =
(words)
(words)
(a number)
y-intercept: b =
The equation is:
(You can test the equation on Sheet4 next to Q. 9)
(a number)
er a different
se to predict the
Step 2:
Creating a linear equation to model a problem:
Calculate the simple interest. (answer Q. 5)
Calculate the next 5 values in the table. (answer Q. 6)
Identify the independent and dependent variables. (answer Q. 7)
Come up with an equation to model the data. (answer Q. 8)
Test the equation by plugging in some values. (answer Q. 9)
(note you only need to fill in the orange-shaded boxes)
vious example.
nt. (simple interest = rate*principal)
unts that use compound interest.
Tips for Step 2:
Creating a linear equation to model a problem.
(note you only need to fill in the orange-shaded boxes on this sheet
and this sheet is mostly here to help you answer questions 5 through 9 on Sheet4).
first:
calculate the simple interest by multiplying the principal times the rate.
second:
fill out the table by adding the simple interest to the value above it.
(i.e. next to month 1 would be 1100 + “value for simple interest”)
reminder:
Try to use excel as a calculator for many of the steps.
To do so always start the empty cell with “=”
pro-tip:
You can use excel shortcuts and functions to speed up repetitive operations.
Google using the “fill down shortcut” or ask your professor.
You can also use cell locations rather than the number amount.
i.e. you can type “=E15*E16” to multiply the number amounts in those cells.
plications and qualities.
hange in the x-value.
dependent variable, y, will
tant changes are usually different
Questions for you to answer. Write out your answer in the box below and make sure to explain yo
reasoning if prompted.
Q. 1
What was the initial value of Lin’s bond? For how long did the bond gain interest?
What was the final amount of the bond?
Answer:
Q. 2
Write the linear equation for the data. (For iPad you need to do this by hand, there
is a scratchwork area to right with hints).
Answer:
Q. 3
What are the slope and y-intercept of this line? What do these numbers represent
in the context of the data?
Answer:
Q. 4
Use this equation to calculate what the bond value will be after 5 years. (Try using
excel as your calculator; see the box to the right for hints).
Answer:
Q. 5
What was the simple interest that you calculated on Sheet3? How did you arrive
at this answer?
Answer:
Q. 6
What were the next 5 values in the table on Sheet3?
Answer:
Q. 7
What is the independent variable in this model? What is the dependent variable in
this model? What are the slope and y-intercept in this model? Your answer to
each of these should be word, not a number.
Answer:
Q. 8
What is the equation that you came up with to model the data? How did you
arrive at this answer?
Answer:
Q. 9
What does it mean to “test the equation” and what values did you use to test it?
How do you know it is correct or “passes the test”?
Answer:
Q. 10
Describe at least one new thing you learned about linear equations in
mathematics. If you did not learn a new thing, describe something that you now
understand better.
Answer:
Q. 11
Describe at least one new thing that learned about in Excel.
Answer:
make sure to explain your
nd gain interest?
his by hand, there
Slope formula:
𝑚=
𝑦2 − 𝑦1
𝑥2 − 𝑥1
choose your points (rows on table)
x1 =
y1=
x2=
y2=
m=
b=
mbers represent
years. (Try using
w did you arrive
Perform the calculations here:
x=
y = mx + b so y =
(try using excel as a calculator)
endent variable in
our answer to
How did you
u use to test it?
ns in
g that you now
Perform calculations here
to test your equation:
Test point
Result
You do not need to complete this task to receive full credit on this assignment.
This is a harder task to deepen your understanding of mathematics if you are up
for the challenge.
Directions:
first:
second:
third:
fourth:
hint:
fifth:
Read the above first!
You may need to use a pen and paper to perform some scratch work (Excel does no
Explain why this equation models simple interest.
Change this linear equation into slope-intercept form.
It may be easier for you to do step four before step 3.
What are the slope and y-intercept? Does that make sense?
The equation:
𝑦 = 𝑃 𝑥𝑟 + 1
where:
P = prinicipal amount
r = interest rate
x = the time elapsed
y = the amount after x time has elapsed
Answer to step 3, your explanation:
Answer to step 4, the equation in slope-intercept form:
Answer to step 5:
this assignment.
atics if you are up
me scratch work (Excel does not excel at this).
pal amount
mount after x time has elapsed