Welcome to Highline BI
348 class video number 44. Hey, if you want to download
this workbook BI 348 chapter 4, or you want to download the
PowerPoint slides, click on the link below the video. Hey, in this chapter--
and I'm going to hit the F5 key to start
that PowerPoint-- we're going to talk about linear regression. Now regression just
means we're going to have a method
to take sample data and create a model that
will help us predict, and the model will be
represented by a straight line. Next slide, wow, we have a lot
of awesome topics associated with linear regression. There they all are. Now, next slide, we want to
talk about making decisions based on a relationship
between two or more variables. So managerial
decisions are often based not just on
a single variable, but on a relationship between
two or more variables. Hey, we might want to be able
to predict or estimate sales based on advertising expense. Now notice we have a y
there and an x there. So the y, that's the predicted
variable, will be the sales. And the x, the
independent variable that will help us predict, will
be advertising expenditures. Another example,
what if we wanted to make a prediction about the
annual amount spent on a credit card? Why? Based on, whoa,
one, two variables, household annual
income and education. How about making a prediction
about the price of a bike based on the bike weight? Another example, how
about predicting a stroke based on age, blood
pressure, and smoking. Next slide, hey x-y
data, that's what we're going to be doing
with this whole chapter. x is the independent variable.
y is the dependent variable. Hey, x is called a
predictor variable because we'll have an equation,
and we'll throw an x-value in and it will calculate
the y-value. So that makes x the
predictor variable. The dependent variable,
y, then is the variable that is predicted or
estimated, sometimes called the response variable. Now here's two data
sets that we'll be dealing with a lot
throughout this chapter. Hey, here we have weekly
ad expense, an x-variable. That's the one that will
try and get to predict the y-variable, weekly sales. Now this is the raw data. We collected this
sample data and we'll use this to create a model. Another example, here's the y,
annual credit card charges y. And then we have an x, annual
income, and number of years post high school education. So hopefully we can build a
model based on these two x's. That means we plug-in
an annual income amount and number of
years, and the model will predict annual
credit card charges. Now again, this is
the sample data. From this sample data
we'll create the model. Next slide, regression
analysis, hey it's just a statistical
procedure used to develop an equation showing
how two more variables are related. It will allow us to build a
model slash equation to help us estimate and predict. Now the entire
process will start with taking an initial
look at the data-- that will be like a scatter
plot-- and then creating the equation, which
has a bunch of steps, and then assessing whether the
equation fits the sample data. We'll use a measure
called r square using statistical
inference to see if there's a significant relationship. We'll use hypothesis
testing, and then we can predict with the equation. Now one that you want to
be careful with regression analysis, it will not prove a
cause and effect relationship. All it will do will
help us to create a model equation that can
help us estimate or make predictions. Now, simple regression,
when you hear simple in front of regression
it just means we only have one independent variable. Linear regression,
when you see linear that just means
the model's going to be represented
by a straight line. So when we study simple
linear regression, that means one independent
variable with a straight line. When you hear multiple
linear regression, multiple just means two or
more independent variables. Linear of course
means straight line. There are definitely
curvilinear relationships. We will not cover
those in this class. Next slide, this
is where we start. Anytime we get a data
set, we'll plot it, and look or see if it looks
like they're a relationship. Now this is the end result of
the charting engine in Excel. The actual first step is just
getting the blue markers. All of the process that gets
us our model or equation will create that line
and that equation. Now in the charting
engine, we'll see that there's a fast and
easy way to visually show the line and the equation. So now we want to
jump over to Excel and do step number one, which
is to plot and look or see if it looks like there
is a relationship. And then we can proceed with
the rest of the process. Now we have our x and our y. And if we're going to use the
charting engine to plot it, you want to have the x first and
then the y, Control Shift Down Arrow to highlight all that. Now, one thing is,
if I make a chart, it will show up down here. Now you can either use your
scroll bar to scroll up, or you can use a little tick
Control Backspace, to jump back to the active cell. And now we go to Insert. And there it is,
the scatterplot. We click on that, and for
sample data you use the markers. If you have a model like
we did earlier in the class when we did our fixed
cost variable cost, then you use the line. You can see that is
ridiculous for sample data. So now we click on
that, and there it is. Now this is a
completely bad chart, because there's some numbers,
there's some more numbers, but I have no idea
what they are. So we immediately--
the first step is you click on the green
plus and say, please show me the axis titles. There it is. It's highlighted. So I type in equal sign. That shoots me up to the
formula bar, and this is y. The y is always plotted
on the vertical axis here. I click on that
cell and hit Enter. There it is. Now I click down here. When I see that
solid line I can type an equal sign that shoots
me up to the formula bar. I click on my x and Enter. Man, that's already
looking much better. Now I want to give this a good
chart title, so solid line all the way around. Equal sign, and I
happened to already have my chart title in cell E1. So I click on the E1 and Enter. And there it is, step one
of linear regression when you have two variables
is to plot and see if there's a relationship. Now clearly, it looks as
x increases, y increases. That means as we increase
weekly ad expense, it looks like weekly
sales are increasing. As we go out the x,
it tends to go up. Now with the charting
engine, there's a fast and easy way to actually
show the line, the equation, and a measure called r square. Simply right click
any one of the dots and point to Add Trendline. And instantly. there's kind of a slight
line in the background, but this pane opens up. There's linear. I come down here and
display equation on chart. And I want to show r square. Now I'm going to click and
drag this over to the side. I want to click on the
horizontal grid lines. I see those translucent
circles, and I hit Delete. Now I'm going to click
on the vertical lines-- I see those translucent
circles-- I hit Delete. I'm going to try
and select the line. Now if you accidentally
click like right there and you can't quite get to the
line, all of the chart elements are easily accessed
by going up to Format. And over to the
current selection you click the Dropdown. And notice we always
name everything smartly like our field names at the top. We know there's
the weekly sales, there's the weekly
sales trendline, there's the weekly
trendline equation. I'm going to select
the trendline one, and you can see, sure
enough, it's selected. Otherwise, if you
very carefully click, you can kind of
see a little marker right there and right there. Now we can go over to
our bucket of paint, and I want to say
the line is solid. And I'm going to change the
color, because right now it's the same color. I'm going to use red. That's looking much better. Now I want to select the box
with the equation and r square. Now that's font,
so I can come up. There's a few ways, go
up to the home ribbon, change the font color. Now we'll talk about
what the slope means, what the y-intercept
means, and what the r-square, the goodness of
fit, means later in the class, but that's a quick
and easy way to do it. Now if you're going to use
these numbers in formulas and equations and
things like that, you want to do it in the
cells not from the chart. Now what kind of
relationship is this? Well we kind of already
hinted what the relationship-- as x increases, y increases. That means it's direct. Up, up, any time the x
goes up, the y goes up. Now let's go over and
look at scatter two. We want to create a
second scatter chart. Here it is. We have from the
internet, I went out and searched for BMX bikes. And here's a bunch of entry
level BMX racing bikes. Here's the weight of each in
pounds, and here's the price. And we want to see, is there a
relationship between the weight of the bike and the price? Now in racing, the lighter
the bike, in general the better the
bike is, so I would think there would be
some relationship. I'm going to highlight, and
notice we have our field names at the top. Those will show up as names
for the series of numbers. So you always want
to have field names. Insert, Scatter, and
this is sample data so we click on the markers. Now look at that. That's got a lot
area with no markers. We'll deal with that
in just a second. But always, the first thing,
click on the green plus, Axis Titles. There is the y. I type in equal sign
price, and enter. Click on the x-axis,
equal sign, I'm going to click on bike weight, enter. Chart Title, I'm selected, I see
that square line, equal sign, and I'm going to
click in A1 and enter. Now here, in this
case, I would like to change the minimum value
on this horizontal axis. So I'm going to select
the access, Control 1 and change the task pane,
and there it is, min and max. I'm going to change the
min to say 15 pounds. Now I think I'm going to
change min into 19, and enter. Now one thing that's very
important we'll learn later, we see that there's
about sum min and about sum max for our sample data,
that'll be very important. That'll be call the
experimental region. And when we make
predictions, we'll always want to
limit our x-values that we put into our equation
to the min and the max. Now I'm going to right click
and point to Add Trendline. There it is, linear, display
equation, and r square. Drag this off to
the side, and you have to see that solid line
with your move cursor, click and drag. I don't think I want either one
of the vertical or horizontal grid lines. Notice when I click there,
that's not what I want. If I delete, it will
delete everything. So I'm going to try and
click the lines again when I see those translucent
circles, delete. Now this is a completely
different relationship than the one we saw just a
moment ago with our ad expense and sales data. Here it looks as bike
weight increases, the price of the bike decreases. And that makes sense, right? The heavier the bike,
the lower the price. Or if we do it backwards,
as we decrease the price and it becomes a better
and better race spike, the price goes up. Now this one is called
a negative relationship, or indirect, or inverse. If we go back over
to scatter one, this is called a direct
or positive relationship. Positive, negative, and we'll
have measures to measure those relationships later. Now I want to go over to
the sheet scatter three. Now here, we have a
couple of examples. This is a team
baseball data set. Things like team name, the
season batting average-- that's where the whole team--
runs scored for the whole team, and there's one, two,
three, four plots. We're wondering if there's
a relationship between team batting average and runs score. Well it sort of looks like
direct or positive as batting average increases,
runs scored increased. Over here, how about team
at bats and team home runs, it looks like more and more
less of a relationship, right? When the dots start to
be all over the place, doesn't look like there's
much of a relationship. Here's walks as the x and
strike out says the y. Again the dots seem to
be all over the place. How about team batting average
and team on base percentage? Looks like there's somewhat
of a direct relationship. Now let's scroll
over to the side. And one thing you
want to be careful of, here it's a data set,
and it doesn't really look like there is
much of any direct or indirect linear relationship. But it looks like there
may be some other type of relationship. Now again, we're not going
to cover a curvilinear relationship, but for
our linear example it doesn't look like there's
a linear relationship, but there might be some
other relationship. Now I want to go to one summary
slide over in our PowerPoints, types of relationship, looks
like direct or positive, inverse indirect or negative,
and no relationship. So here we have hours
studied as our x and test score for a particular
class as our y. So as hour studies
increased, test score looks like it's increased. That's direct or positive. How about number of absence in
the class in the final grade? It looks like as number of
absences increases, final grade decreases, inverse
indirect or negative. And then here, we
plot a particular data set that had customer
age and amount spent, and it doesn't look like
there's much of a relationship. Now when we come back
in our next video, we'll talk about
not a graphical way to determine if
there's a relationship, but a numerical measure. And we'll talk about
covariance and correlation. All right we'll
see you next video.
