(jazzy music)
- Hello, I'm professor Rick Jerz
and I welcome you back to my week eight reinforcement session
of Business Analytics.
In this session I would like to explore
some additional correlation and regression problems
and see how we can use our Excel model
to explore what we can conclude
with some of these data scenarios.
I also plan to explain some of my Excel modeling techniques
that I have used.
Let's get started.
My objectives for this session
are to review a few administrative items,
to take you through an example of sampling
and test of hypothesis which is meant to illustrate
some of the things that we have learned
in our previous sessions.
Then we will do some correlation
and linear regression problems
which is the topic for this week
and say a few words about our next session.
Relative to administrative items,
at this point I have nothing new to share with you.
Let's go on to an example of sampling
and test of hypothesis.
I love doing this example in class.
We are online and we lose some of the flavor
of this particular exercise.
But I will do my best to share it with you.
You will have to use your imagination a little bit.
In class I open a container
of 12 freshly baked chocolate chip cookies.
The scenario is that we are a chocolate chip cookie company
and we're trying to decide
how much we should sell these cookies for.
The question is what should be the selling price
of an individual chocolate chip cookie?
We could debate this issue
and maybe the owner of the company would just simply decide.
But why not use our knowledge of statistics
to set this cookie price.
In my class of approximately 50 students
I use our sampling theory to randomly select 12 students
and I ask them to write down on a slip of paper
the price that they would pay for one of these cookies.
I collect this data
and enter it into my descriptive statistics model.
The best estimate for a single price of these cookies
is the mean of the sample
but I also know that samples can vary,
so I want to provide oh let's say a 95% confidence interval
for the selling price of these cookies.
Since we do not know what the population variance is
I'm going to use my t-distribution model
and predict the 95% confidence interval
for the selling price.
Here are the results.
I would be able to say to the owner that I am 95% confident
that the selling price of cookies
should be between $.75 cents and $1.35.
This is the result from our sample of 12 cookies.
The owner's reaction to this is,
"Hey, I've just paid a lot of money
"for you to take a course in business analytics.
"Is this the best you can do?
"This is a range of $.60.
"Can't you be any more accurate than this?"
I know that I can if I had a bigger sample.
So in class I open up another container of 12 cookies
and randomly select 12 more students
to provide me their price that they'd be willing to pay
for one cookie.
I add this information to my Excel model
and then look at the results on my t-distribution model.
I can now say to the owner of the company
that I am 95% confident that the price should be
between $.66 and $1.02.
This is an improvement over what I had previous stated.
This is a spread of about $.37 instead of $.60.
More data has allowed me to make a more accurate prediction.
The owner is still now pleased and says,
"Can't you be within plus or minus a nickel?"
The real question being asked is
what should our sample size be
to allow us to make a 95% prediction
within plus or minus a nickel?
To do this on our t-distribution model
I need to use Goal Seek.
We can do this very quickly.
I pull up Goal Seek and I want this plus or minus value
to be .05 by changing sample size.
The result is that we would need a sample size
of 290 cookies.
Now we have a business decision to make.
Is it worth our while, meaning time and cost,
to produce a sample of 290 cookies to find 290 students
in order to get this level of precision?
This is a business decision that we need to make.
Since I am the real baker of these cookies
and I would need to make 13 batches
of chocolate chip cookies, well I don't think so.
We can also use our model to do a test of hypothesis.
If the owner said,
"I think we should sell these cookies for $1.25."
We could put this into our model and reject the claim.
We would say to the owner
that the data does not support the claim
that the cookies would sell for $1.25.
The calculated P value is very small.
In class I present another data scenario.
I open up a bag of store purchased chocolate chip cookies
and the statement from the owner
is that all chocolate chip cookies
should sell for the same price.
It doesn't matter
if they were from a purchased bag of cookies
or made fresh at home.
This allows me to use
my two-sample test of hypothesis Excel model.
I go through the process of randomly picking some students
to sample the store purchased cookies
and provide me the data.
Here's the data represented in my two-sample model.
Remember that the hypothesis from our owner is
the means are the same,
meaning that cookies will sell for the same price.
Using our model I answer the question
are you using raw data?
Well, quite honestly the cookies really are not raw
but the data is raw.
So I enter a yes.
Do you know the population variances?
No, the hypothesis is an equal to hypothesis.
I cannot use test four.
In test five we test the variances and we do see
that we reject the null that the variances are not equal.
To test the means we scroll down to test 6b.
At the 5% level of significance we reject the null
that they are equal.
This means that they are different.
The P value is very small so we can confidently reject
the owner's claim.
I can also look at the graph of these two data sets
and the graph seems to confirm our final decision.
Well, I'm sorry to say
that I'm not able to share this experiment
and the real cookies with you.
I am going to share with you a video that I made
about a small piece of this experiment.
You will see this video show up
within our lecture videos, enjoy.
Let's now turn our attention
to correlation and regression analysis
and we can do some problems from the textbook.
I do want to remind you
that in this correlation and regression analysis
all we are able to do is to see if there was a relationship
between the independent and dependent variables.
If there is a relationship this is not to say
that the independent variable is causing something to happen
with the dependent variable.
Cause needs to be explored further.
We will see this as we do some of these problems.
Let's start with this problem.
Bi-lo Appliance Super-Store has outlets
in several large metropolitan areas in New England.
The general sales manager aired a commercial
for digital cameras on selected local TV stations
prior to a sale starting on Saturday and ending on Sunday.
She obtained the information for Saturday-Sunday
digital camera sales at the various outlets
and paired it with the number of times the advertisement
was shown on the local TV stations.
The purpose is to find out if there was a relationship
between the number of times the advertisement was aired
and digital camera sales.
The pairings are shown as follows.
And we have a couple questions shown down below.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
It is important to recognize
the independent and dependent variables in these problems.
In this case we are wondering if sales
depends upon the number of airings.
So the dependent variable is sales.
Let's put this data into my Excel model
and see what we can conclude.
I enter the number of airings into the x column
and the sales into the y column.
I make sure to delete data that is leftover
from the previous problem.
The interesting thing
about correlation and regression analysis
is that after entering this data
our work is nearly complete.
Now all we need to do is sit back and interpret the results
and maybe use the model to do some predicting.
Question b asks us to draw a scatter diagram
and this is already done for us over on the right.
The scatter diaphraph seems to suggest
that there is a relationship and it is positive.
This suggests that if we air the commercial more frequently
sales do go up.
To the left
we see the coefficient of correlation being .9295.
This value also suggests a strong positive correlation.
My model also allows us to ask the question
how confident are we that the coefficient of correlation
is not zero, meaning that there is a relationship?
At the 5% level of significance we reject the null,
meaning that there is a relationship.
Well, this Excel model of mine made it pretty easy
to address the scenario and answer these questions.
Let's do another problem.
Bradford Electric Illuminating Company
is studying the relationship
between kilowatt hours in thousands used
and the number of rooms in a private single residence.
A random sample of 10 homes
yielded the following information
and we are asked some questions about this scenario.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
In this question we are asking whether the energy usage
depends upon the number of rooms in the house.
So the kilowatt hours used is the dependent variable
and rooms are the independent variable or the x variable.
I enter this data into my model,
making sure to delete any data that is leftover
from the previous problem and we are basically done.
The scatter diagram
seems to suggest a strong positive relationship.
If we look at the coefficient of correlation
we see that it is .9038, which is also suggesting
a strong positive correlation.
We were asked to determine the regressed line
and here it is.
Y-hat equals 1.33 plus .667x.
Question b asks us to determine
the number of kilowatt hours in thousands
for a six-room house.
I enter six into this x value in the model
and the forecast is 5.335 kilowatt hours in thousands.
This problem does illustrate
how we can use our linear regressed line
to make predictions.
Let's do another problem.
An air travel service samples domestic airline flights
to explore the relationship between airfare and distance.
The service would like to know if there is a correlation
between airfare and flight distance.
If there is a correlation
what percent of the variation in airfare
is accounted for by distance?
How much does each additional mile add to the fare?
The data is as follows.
And down below we are asked some questions.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
In this problem the distance is the independent variable
and we're trying to see if fare
depends upon distance traveled.
So the fare is the dependent variable.
We are also provided a data set for this problem.
One problem that we immediately run into
is that our data file contains 30 values
and my model only allows for 25.
I need to insert some more rows.
I can do this somewhere in the middle of my model
by swiping through a couple rows, oh maybe 10 or so,
and then right-clicking and inserting some blank rows.
Now I can copy the distance data into the x column
and the fare data into the y column.
I am again done with the analysis
and I can sit back and interpret the results
and use the model to make some predictions.
The scatter diagram seems to show
that there might be some relationship but it is weak.
When I look at the coefficient of correlation
I see that it is .65 which also suggests
that it is not a strong relationship.
The coefficient of determination is .43
which says that 43% of the variation in fare
is accounted for by the distance of a flight.
At the 5% level of significance
we do reject the null, meaning that there is a relationship
but once again it is a weaker relationship.
The equation for the regressed line is shown below
and the slope of this line calculates
that each additional mile adds .0527 dollars to the fare.
To predict what a 1,500 mile flight would cost
I enter 1,500 miles into x and I see
that it would cost approximately $226.
Since we don't have a particularly strong relationship
trying to predict what a flight of 4,218 miles would cost
is outside the range of our data.
So with this regressed equation
our accuracy would not be very good.
Wow, we are able to do a lot with the Excel model
and we don't have to worry about memorizing equations
or doing the calculations with a calculator.
This is pretty nice.
Let's do another problem.
The following data show the retail price
for 12 randomly selected laptop computers
along with their corresponding processor speeds
in gigahertz.
The data is shown below and we are asked some questions
about the scenario.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
I have entered the data into my model
using price as the dependent variable
and CPU speed the independent variable.
We can scroll down
to see the equation for the regressed line.
We are asked if there seems to be one machine
that is particularly over or under priced.
When I look at the data in the scatter diagram
the point that is the furthest away from the regressed line
seems to be this second data point.
The model computes that the correlation coefficient
is .835 and at a 5% level of significance
we do reject the null.
This suggests that there is a relationship.
Let's do another problem.
A dog trainer is exploring the relationship
between the size of a dog, meaning weight in pounds,
and its daily food consumption measured in standard cups.
Below is the result of a sample of 18 observations
and the data is shown below.
Then we are asked some questions about the scenario.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
We are provided a data set for this problem.
The first thing that we need to decide
is what is the dependent variable
and what is the independent variable?
In question b we are asked how much does each additional cup
change the estimated weight of the dog?
Based upon this question
the food consumption appears to be the independent variable
and the weight is the dependent variable.
I have copied the data from the data set into my model
making sure that the independent variable x is consumption.
Once we have copied this data into the model
we are basically done.
When I look at the scatter diagram
it looks like there is a very strong relationship
because all of the data points
are nearly on the regressed line.
The coefficient of correlation is also very high, .987.
At a 5% level of significance we reject the null
that the data is not correlated, meaning that it is.
So there seems to be a relationship.
The slope of the regressed line is 22.93.
So each additional cup of food
increases the dog's weight by 22.93 pounds.
To answer the question
which dog appears to be an over or under eater,
I try to find the point
that is the furthest from the regressed line.
To me it looks like this point, which is for the fourth dog.
I have always found this question to be interesting.
As we approached it we were wondering whether or not
the dog's food consumption is related to the dog's weight
but we could have asked the question differently.
Maybe we want to explore whether or not the dog's weight
makes a dog eat more food.
If I reverse the x and y variables in my model
the correlation analysis is unaffected.
The only thing that does get affected
by having the x and y variables reversed
is the results for the linear regressed line.
Remember that with correlation analysis
all we are able to conclude is that two variables
seem to be related.
We cannot assign cause.
Cause will take deeper analysis.
So in this problem
is the dog's weight causing the dog to eat more
or is the eating of more food
causing the dog's weight to go up?
One debated contemporary issue
is whether or not higher carbon dioxide emissions
is causing the earth's temperature to increase.
Some people say it does and some people say it doesn't
and some people say
we have the data to support this conclusion
and some people say we don't.
I have provided this link to Wikipedia
that lists the countries by carbon dioxide emissions.
If it is true that higher carbon emissions
is causing the temperatures to rise
then one might say that the countries
that are emitting the most carbon dioxide
should be of most concern.
Remember, with correlation analysis we cannot assign cause.
We can only say whether or not two variables are related.
I am sharing with you an interesting letter to the editor
from my honor's engineering society journal publication
Tau Beta Pi.
Frank Barton writes,
"Just because there is a statistical correlation
"between temperature and CO2 concentration
"does not mean that CO2 is driving the temperature upward.
"In fact, a close examination of temperature
"versus CO2 concentration reveals that temperature
"is driving CO2 concentration, not the other way around.
"Changes in temperature have led CO2 concentrations
"by approximately 800 years,
"according to 'Temperature-to-CO2 Proved'
"by John L Daly.
"The normal sense says CO2 concentration
"is not the major contributor or cause of global warming."
So, do higher carbon dioxide emissions
cause the temperature to rise
or does the rising temperature
cause CO2 carbon dioxide concentrations to rise?
Or maybe temperatures are rising for some other reason.
Maybe there are some mid to long-term cycles
relating to temperatures on our planet Earth
or maybe some different solar patterns
or maybe some different or unusual geological patterns.
At this point
we have less than 175 years of temperature data
to base future predictions on.
I don't know, I'll leave this up to you to decide.
Let's do one more problem.
Refer to the real estate data,
which reports information on homes
sold in Good Year, Arizona last year.
We aren't going to address all of these questions,
let's just look at question a.
Let the selling price be the dependent variable
and size of the home the independent variable.
Determine the regression equation.
Estimate the selling price for a home
with an area of 2,200 square feet.
Determine the 95% confidence interval
and the 95% prediction interval
for the selling price of a home with 2,200 square feet.
I encourage you to pause this video
and try to solve this problem on your own
before seeing how I solve this problem.
For this problem we are provided the data in an Excel file.
When I look at this data I immediately recognize
that we have 105 data points
and my model only accommodates 25.
So once again what I will have to do
is to insert more rows to accommodate the 105 values.
I don't have to have exactly 105 rows in my model,
it is fine to have more.
You might also be wondering
if I have the ability to use defined names in this model.
I haven't built this into this particular model
but I do leave it up to you to decide
if you would like to do this on your own.
In the problem we are told that selling price
is the dependent variable
and size of home is the independent variable.
So I copy and paste this data from the data file
into my model.
I make sure to delete any leftover data
from a previous problem and I am basically done
with most of the calculations.
When I look at the scatter diagram
I do detect some trend but the data seems to be dispersed
around this regressed line
so this makes me wonder if there is a strong relationship
between size of home and selling price.
The coefficient of correlation is .37
which suggests that there is not a strong correlation.
I scroll down and look at the equation
for the regressed linear line.
Below this I can enter 2,200 square feet
and a 95% interval
and my model calculates the prediction interval
to be between 131,000 and $307,000
and the confidence interval between 210,000 and 227,000.
The prediction interval is for one individual home
that happens to be 2,200 square feet in size,
the confidence interval is for a group of homes
that would happen to have their average square footage
at 2,200 square feet.
When we have a group of data points
with some values being high, some values being low
we can make more accurate group predictions
compared with that of an individual home.
This is the difference between the confidence interval
and the prediction interval.
These intervals would also be smaller
if there was a stronger correlation
between these two variables.
I think that what we have seen
is that this particular Excel model
for correlation and regression analysis
makes the calculations and the interpretation of the results
relatively easy.
We just need to practice a little bit using this model.
Next week we are going to extend what we have learned
with correlation and regression analysis
and look at data scenarios
where we might have multiple independent variables
driving a dependent variable.
This is called multiple linear regression.
Of course since we have multiple independent variables,
the analysis gets to be more complex.
(jazzy music)
Thanks for spending some of your time with me.
I hope you have enjoyed what I have shared with you.
If you ever have any questions or need my help
please let me know.
(jazzy music)