Fluent Essays

W7: Regression and Correlation Subscribe Look back to the raw data you collected in week 1. There are 7 variables listed: Vehicle type/class Year Make Model Price MPG (city) MPG (highway) Choose TWO variables that you feel are correlated and explain why you feel that they are correlated. Do you suspect the relation is positive or negative? Why? Which would be considered the independent variable, which the dependent variable? Why? Run a regression analysis in Excel and provide the results in your post along with your raw data. Looking at the R2 value, explain what this indicates about the strength of the relation.  Then write out your Regression Equation, state if your p-value and conclusion. I encourage you to review the Week 7 Regression PDF  at the bottom of the discussions.  This will give you a step by step example on how to calculate a correlation and run a Regression using Excel. I DO NOT recommend doing this by hand.  Let Excel do the heavy lifting for you.  You can also use this PDF in Quizzes section. There are additional PDFs that were created to help you with the Homework, Lessons and Tests in Quizzes section.  I encourage you to review these ASAP!  These PDFs are also located at the bottom of the discussion. Once you have posted your initial discussion, you must reply to at least two other learners post. Each post must be a different topic. So, you will have your initial post from one topic, your first follow-up post from a different topic, and your second follow-up post from one of the other topics. Of course, you are more than welcome to respond to more than two learners.” Instructions: Make sure you include your data set in your initial post as well.  You must also respond to at least 2 other students. Responses may include direct questions. Peer response #1 - Looking at your peers Excel output, and the Regression Equation they wrote out, interpret the slope of their Regression Equation.  Use their Regression Equation to make a prediction and show the work for your predicted value based on your expression.   For Example, if your peer used Year to predict Price, plug in a Year value into the regression equation and use it to predict the Price of a vehicle.  Does this predicted Price value make sense with their data? Peer response #2 - It is important to remember that typically a two-factor regression model cannot accurately describe the entire situation. Look at the dependent variable that your peer chose. Name at least 2 independent factors you would use to run a Multiple Linear Regression (MLR) and explain why you feel they are related.  Then use those factors to run a Multiple Linear Regression (MLR) on your peers data and see if the variables you chose are related to the dependent variable they chose.  What is your MLR equation?  Is your MLR significant?  Are any of the Independent factors significant?  What is the R2 value?  Explain and interpret this value and how it relates to the MLR.  Make sure you include your MLR Excel output as an attachment in your response post.Recall, our Car Price data Car Price: Year Years Old Observation 1 $ 20,000 2015 4 Observation 2 $ 25,000 2016 3 Observation 3 $ 30,000 2018 1 Observation 4 $ 31,000 2018 1 Observation 5 $ 22,500 2016 3 Observation 6 $ 25,000 2016 3 Observation 7 $ 29,500 2018 1 Observation 8 $ 24,000 2015 4 Observation 9 $ 24,500 2017 2 Observation 10 $ 25,000 2017 2 With the Regression output, Lastly, I want to use my Regression Equation to predict prices. And then we want to find a 95\% prediction interval for that predicted price. What would I expect to pay for a car that was manufactured in 2014? Remember 2019 – 2014 = 5. This means the car is 5 Years Old. This is the value you want to substitute into the Regression Equation. DO NOT put 2019 into the equation. 𝑃𝑟𝑖𝑐�̂� = −2,629.03 (𝑌𝑒𝑎𝑟𝑠 𝑂𝑙𝑑) + 31,959.68 𝑃𝑟𝑖𝑐�̂� = −2,629.03 (5) + 31,959.68 𝑃𝑟𝑖𝑐�̂� = −13,145.16 + 31,959.68 SUMMARY OUTPUT Regression Statistics Multiple R 0.884606501 R Square 0.782528661 Adjusted R Square 0.755344744 Standard Error 1725.490814 Observations 10 ANOVA df SS MS F Significance F Regression 1 85706451.61 85706451.61 28.78646 0.000673381 Residual 8 23818548.39 2977318.548 Total 9 109525000 Coefficients Standard Error t Stat P-value Lower 95\% Upper 95\% Lower 95.0\% Upper 95.0\% Intercept 31959.67742 1296.435244 24.65196589 7.83E-09 28970.09239 34949.26245 28970.09239 34949.26245 Years Old -2629.032258 490.0064638 -5.365301179 0.000673 -3758.98919 -1499.075326 -3758.98919 -1499.075326 𝑃𝑟𝑖𝑐�̂� = $18,814.52 In the Year 2014, when the car is 5 Years Old, we will expect to pay $18,814.52 for a car. Now that we know what are expected to pay for a 5-Year-Old car, lets calculate a 95\% prediction interval for 5 Years. We need to use this equation: �̂� ± 𝑡 ∗(𝑆𝐸)√1 + 1 𝑛 + (𝑥0 − �̅�) 2 (𝑛 − 1)𝑆𝐷𝑥 2 We will use the =T.INV.2T function to find the T-Critical Value. This value should look familiar. DF = n – 2 = 10 – 2 = 8. Which is the same DF for the Residual in the Regression output. =T.INV.2T(0.05,8) 2.306004135 Next, we will need to calculate the mean and SD for the x-variable. You should recall how to calculate descriptive statistics from Week 2. Mean = 2.4 SD = 1.1737878 SE is the Standard Error from the Regression Output which is 1725.4908 Now we can plug in what we know 18814.52 ± 2.306(1725.4908)√1 + 1 10 + (5 − 2.4)2 (10 − 1)1.17378782 18814.52 ± 3978.9817√1 + .1 + 6.76 12.4 18814.52 ± 3978.9817√1.64516129 18814.52 ± 3978.9817(1.28263841) 18814.52 ± 5103.59476 ($13,710.93, $23,918.11) The 95\% prediction interval for a 5-Year-Old car will go from $13,710.93 to $23,918.11.Recall, our Car Price data Car Price: Year Years Old Observation 1 $ 20,000 2015 4 Observation 2 $ 25,000 2016 3 Observation 3 $ 30,000 2018 1 Observation 4 $ 31,000 2018 1 Observation 5 $ 22,500 2016 3 Observation 6 $ 25,000 2016 3 Observation 7 $ 29,500 2018 1 Observation 8 $ 24,000 2015 4 Observation 9 $ 24,500 2017 2 Observation 10 $ 25,000 2017 2 With the Regression output, Next, we want to test the hypothesis and see if the results are significant. The hypothesis scenario looks like: Ho: 𝜌 = 0 Ha: 𝜌 ≠ 0 If we look at the p-value or the Significance F we see the p-value = .000673. .000673 < .05, Yes this is significant. This means Years Old is a significant predictor of the Price of a Car. SUMMARY OUTPUT Regression Statistics Multiple R 0.884606501 R Square 0.782528661 Adjusted R Square 0.755344744 Standard Error 1725.490814 Observations 10 ANOVA df SS MS F Significance F Regression 1 85706451.61 85706451.61 28.78646 0.000673381 Residual 8 23818548.39 2977318.548 Total 9 109525000 Coefficients Standard Error t Stat P-value Lower 95\% Upper 95\% Lower 95.0\% Upper 95.0\% Intercept 31959.67742 1296.435244 24.65196589 7.83E-09 28970.09239 34949.26245 28970.09239 34949.26245 Years Old -2629.032258 490.0064638 -5.365301179 0.000673 -3758.98919 -1499.075326 -3758.98919 -1499.075326 We can also compare r, the correlation to a critical value. If r < negative critical value or r > positive critical value, then r is significant. If r is significant and the line may be used for prediction. We know r = -.8846. There is a correlation critical value table in RealizeIT under Testing the Hypothesis in Week 7 but here is a link to a more detailed table. Correlation CV Table We know alpha = .05, this is two tailed test from the hypothesis scenario and n = 10. The critical value that corresponds to this in the table is r CV = 0.632. We know our correlation is negative, so we will use the negative value of this. -.8846 < -0.632, this tells us that r is significant and you can use the line for prediction. This is the same conclusion we got with the p-value from above. Lastly, we can run a t-test to see if the data is significant. From the regression output the t-Stat for the slope is -5.3653. But if we didn’t have the regression output we can calculate this value using this equation. t = 𝑟√𝑛−2 √1−𝑟2 Plugging in our correlation and sample size we get: t = −.8846√10−2 √1−(−.8846)2 = − 2.50202 .4663505 = −5.3651 t – Test Stat we calculated by hand is very close to the t-stat in the output. It is a little off because I did round some of my values. Then we can use the =T.DIST.2T function to find the p-value. This Excel function should look familiar. =T.DIST.2T(ABS(-5.3651),8) Remember if you have a negative value you will need to use the ABS fuMLR Recall: Linear Regression is a data analysis technique that tries to find a linear pattern in the data. We use all the data to calculate a straight line which may be used to predict the values. The equation of line for a Simple Linear Regression (SLR) is: �̂� = 𝛽1𝑥 + 𝛽0 Where 𝛽1 is the slope coefficient or the coefficient, 𝛽0 is the y-intercept and �̂� is the predicted y value. For Multiple Linear Regression (MLR) the equation of a line will look like: �̂� = 𝛽1𝑥1 + 𝛽2𝑥2 + 𝛽3𝑥3 + 𝛽4𝑥4+ 𝛽5𝑥5 + … + 𝛽0 Where 𝛽1, 𝛽2, 𝛽3, 𝛽4, 𝛽5, … are the slopes coefficient or the coefficients, 𝛽0 is the y-intercept and �̂� is the predicted y value. For Multiple Linear Regression you will have more than 1 slope, but you will still only have 1 y-intercept. The number of slopes will depend on the number of x- variables you have. In the example equation above, I only wrote out 5 slopes but know you can have any amount as along as it is 2 and above. Because if there is only 1-slope then this is a SLR NOT a MLR. Example: Let’s move away some our car price data and look at home prices. Is the MLR model a good predictor of home prices? If so, what variables help predict the price of a home? Data: Price Area (Sq Ft) Floor Bedrooms Bathrooms $ 69,000 600 1 2 1 $ 118,500 1000 2 2 2 $ 125,000 1100 1 3 2 $ 139,300 1300 2 3 2 $ 147,900 1700 2 3 2 $ 169,900 1800 1 3 2.5 $ 134,900 1300 1 4 2.5 $ 169,900 1700 2 4 3 $ 194,500 2000 2 5 3.5 $ 209,900 2100 3 5 4 Looking at our example, we want to use Area, Floors, Bedrooms and Bathrooms to try and predict home prices. This means the x-variables are Area, Floors, Bedrooms, and Bathrooms and the y-variable is Price. Because there are 4 x-variables, this means there are 4 slopes in the MLR model. Next, we will run a Regression using Excel. We will use the Data Analysis ToolPak to run the Regression. Go to Data - > Data Analysis When the new window pops ups, scroll to where it says “Regression”, highlight it and Click “OK” Then it will say “Input” Input Y Range: Click in the box and highlight the y values or the price column. Input x Range: Click in the box and highlight the x values or the area, floors, bedrooms, and bathrooms columns. Check the box that say “Labels” this will tell you that the first row has labels in it. Output Options Make sure the second bubble is highlighted. “New Worksheet Ply” Make sure you check the box for Residuals and Standardized Results Then Click “OK” (Remember, the x-values predicts the y-value. Area, Floors, Bedrooms and Bathrooms will predict what the Price of a Home. This is very important to understand and remember) It should look like this: Once you click OK, here is the Regression Output: Looking at the output we see the estimated regression line is: 𝑃𝑟𝑖𝑐�̂� = .053181(𝐴𝑟𝑒𝑎) − .111766(𝐹𝑙𝑜𝑜𝑟) − 5.3826The correlation is the direction and strength of association between 2 variables is often expressed in a single number called the correlation coefficient. This is denoted by the variable r. • r can only be between -1 and 1, -1 ≤ r ≤ 1. • If r = 0, then there is no linear relationship at all. • If r = -1, then there is a perfect linear relationship that slopes down. • If r = 1, then there is a perfect linear relationship that slopes up. The Coefficient of Determination refers to how much percent Variation is around the model. This is denoted by R². Note: If you have one you can find the other. Simple Linear Regression is a data analysis technique that tries to find a linear pattern in the data. In linear regression, we use all the data to calculate a straight line which may be used to predict the values. We will also discuss if the linear regression is significant and if the independent variable (x) is a significant predictor of the dependent variable (y). The equation of line for a Simple Linear Regression (SLR) is: �̂� = 𝛽1𝑥 + 𝛽0 Where 𝛽1 is the slope coefficient or the coefficient, 𝛽0 is the y-intercept and �̂� is the predicted y value. Let review our car price example. From the car price data, we also found out what year these cars where manufactured in. Car Price: Year Observation 1 $ 20,000 2015 Observation 2 $ 25,000 2016 Observation 3 $ 30,000 2018 Observation 4 $ 31,000 2018 Observation 5 $ 22,500 2016 Observation 6 $ 25,000 2016 Observation 7 $ 29,500 2018 Observation 8 $ 24,000 2015 Observation 9 $ 24,500 2017 Observation 10 $ 25,000 2017 Having this information, we first want to see if there is a correlation between Year and Car Price. Usually the older the car, cheaper the car is. As the age goes up, the price will go down. The Price of the car depends on what Year it was manufactured. This describes a negative correlation, but I want to see if my assumption is correct and what the actual correlation value is. Before we can do any calculations on the data, we will need to convert the Year to a numeric value. Keeping the physical Year is going to skew the data and it doesn’t make sense when we will get into analyzing and interpreting the data. If the car was made in 2018, then this means the car will be 1 year old. 2019 – 2018 = 1. I am rounding all these to full years for ease of the example. Converting all these Years will look like: Car Price: Year Years Old Observation 1 $ 20,000 2015 4 Observation 2 $ 25,000 2016 3 Observation 3 $ 30,000 2018 1 Observation 4 $ 31,000 2018 1 Observation 5 $ 22,500 2016 3 Observation 6 $ 25,000 2016 3 Observation 7 $ 29,500 2018 1 Observation 8 $ 24,000 2015 4 Observation 9 $ 24,500 2017 2 Sheet1 Vehicle type/class Year Make Model Price MPG (City) MPG (hwy) Max Seating SUV 2020 BMW X3 $42,945 25 29 5 Sedan 2019 Chevrolet Cruze $18,870 28 38 5 SUV 2021 Chevrolet Tahoe $55,095 16 20 9 Coupe 2020 Chevrolet Camaro $26,495 20 30 4 SUV 2020 Ford Edge $32,345 21 29 5 SUV 2020 Ford Explorer $34,010 21 28 7 Luxury 2020 Porsche 911 $111,550 18 24 4 Van/Minivan 2020 Toyota Sienna $32,760 19 26 7 Sedan 2020 Dodge Charger $31,390 19 30 5 Van/Minivan 2019 Dodge Grand Caravan $27,595 17 25 7 Qualitative Qualitative Qualitative Qualitative Quantitative Quantitative Quantitative Quantitativedescriptivestats sample size 10 Descri Mean 32069.5 Standard Error 4961.3723151967 Median 28920 Mode ERROR:#N/A Standard Deviation 15689.2368361243 Sample Variance 246152152.5 Kurtosis 8.2076699903 Skewness 2.7509552142 Range 55505 Minimum 19650 Maximum 75155 Sum 320695 Count 10 Confidence Level(95.0\%) 11223.4039201309 T Critical Value 2.2621571628 SE 4961.3723151967 ME 11223.4039201309 descriptivestats sample size 11 Price Mean 192790.454545455 Standard Error 160783.596649566 Median 28940 Mode ERROR:#N/A Standard Deviation 533258.862530453 Sample Variance 284365014467.273 Kurtosis 10.9770526255 Skewness 3.3119504866 Range 1780350 Minimum 19650 Maximum 1800000 Sum 2120695 Count 11 Confidence Level(95.0\%) 358248.178456988 Data Vehicle type/class Year Make Model Price MPG (city) MPG (highway) # of cylinders WEEK 3 WEEK 4 SUV 2021 GMC Yukon $ 75,155.00 15 22 8 P 0.7 less than 500$ Compact SUV 2021 Hyundai Santa Fe $ 26,850.00 25 28 4 Q 0.3 New SD 7844.6184180622 Compact SUV 2021 Ford Escape $ 24,885.00 44 37 4 P(X=4) 3.68\% p(x<31569.50) 47\% SUV 2021 Kia Sorento $ 29,390.00 24 29 4 P(X<5)/ P(X<=4) 4.73\% Pickup Truck 2021 Ford F150 $ 28,940.00 25 26 8 P(X>6)/ 1-P(X<=6) 64.96\% 1000$ above Pickup Truck 2021 Chevy Silverado 1500 $ 28,900.00 22 28 8 P(X>=4)/ 1-P(X<=3) 98.94\% New SD 7844.6184180622 Compact Car 2021 Hyundai Elantra $ 19,650.00 33 43 4 p(x>33069.50) 100\% Compact Car 2021 Chevy Malibu $ 22,140.00 29 36 4 Pickup Truck 2021 Dodge Ram 1500 $ 32,595.00 22 32 8 equal to mean $32069.50 SUV 2021 KIA Telluride $ 32,190.00 20 26 6 New SD 7844.6184180622 p(x=32069.50) 0\% Mean: $ 32,069.50 Median: $ 28,920.00 within $1500 SD: 15689.2368361243 New SD 7844.6184180622 Sample Size: 10 p(30569.50<x<33569.50) 15\% Week 2 Outlier Vehicle type/class Year Make Model Price MPG (city) MPG (highway) # of cylinders WEEK 5 Classmate 1- Week 5 SUV 2021 GMC Yukon $ 75,155.00 15 22 8 T Critical Value 2.2621571628 T-Critcal Value 1.8331129327 Compact SUV 2021 Hyundai Santa Fe $ 26,850.00 25 28 4 SE 4961.3723151967 SE 5206.771494 Compact SUV 2021 Ford Escape $ 24,885.00 44 37 4 ME 11223.4039201309 ME 10724.2086330932 SUV 2021 Kia Sorento $ 29,390.00 24 29 4 Mean $ 32,069.50 Mean $ 55,882.20 Pickup Truck 2021 Ford F150 $ 28,940.00 25 26 8 equation from pdf 11223.4039201309 Pickup Truck 2021 Chevy Silverado 1500 $ 28,900.00 22 28 8 55882.20-10724.2086 $ 45,157.99 Compact Car 2021 Hyundai Elantra $ 19,650.00 33 43 4 32069.50-11223.40 $ 20,846.10 $ 22,446.81 55882.20+10724.2086 $ 66,606.41 $ 21,448.42 Compact Car 2021 Chevy Malibu fail to reject null hypothesis because p-value is greater than alpha 29 36 4 32069.50+11223.40 $ 43,292.90 Sheet2 SUMMARY OUTPUT Regression Statistics Multiple R 0.865508314 R Square 0.7491046416 Adjusted R Square 0.7177427218 Standard Error 2.2547210913 Observations 10 ANOVA df SS MS F Significance F Regression 1 121.4298624024 121.4298624024 23.8858031126 0.0012130798 (P-value) < alpha - Yes, this is a significant predictor Residual 8 40.6701375976 5.0837671997 Total 9 162.1 Coefficients Standard Error t Stat P-value Lower 95\% Upper 95\% Lower 95.0\% Upper 95.0\% Intercept 1.607288955 4.1725703027 0.3852035648 0.7101262342 -8.0146754175 11.2292533275 -8.0146754175 11.2292533275 MPG Highway 0.6719970249 0.1374983309 4.887310417 0.0012130798 0.3549253053 0.9890687445 0.3549253053 0.9890687445 RESIDUAL OUTPUT Observation Predicted MPG (city) Residuals Standard Residuals 1 20.4232056527 4.5767943473 2.1530039779 2 17.0632205281 -1.0632205281 -0.5001575016 3 18.4072145779 -1.4072145779 -0.6619783093 4 17.0632205281 0.9367794719 0.440677421 5 24.4551878022 -0.4551878022 -0.2141282903 6 27.8151729267 2.1848270733 1.0277808052 7 19.7512086277 0.2487913723 0.1170358057 8 24.4551878022 -0.4551878022 -0.2141282903 9 24.4551878022 -2.4551878022 -1.154963213 10 23.1111937523 -2.1111937523 -0.9931424053 Sheet1 Vehicle type/class Year Make Model Price MPG (city) MPG Highway Odometer/Miles Compact SUV 2021 Ford Bronco Sport $ 35,875 25 28 0 Pickup 2020 Ram 1500 Express $ 39,399 16 23 7 Compact SUV 2018 Jeep Wrangler Rubicon $ 34,000 17 25 35855 Pickup 2016 Toyota Tacoma $ 30,000 18 23 42061 Sedan 2018 Ford Focus Titanium $ 12,649 24 34 32792 Sedan 2019 Volkswagen Jetta S $ 13,900 30 39 5990 Crossover 2017 Ford Escape SE $ 15,699 20 27 7313 Sedan 2017 Mercedes-Benz C300 4Matic $ 26,580 24 34 34028 Coupe 2016 BMW 428i Gran Coupe xDrive $ 24,490 22 34 44336 Sedan 2019 Volvo S60 T6 Momentum AWD $ 25,991 21 32 19736 Correlations 0.8655 Positive Correlation R2 74.91\% Strongest Correlation = 100\%, 74.91\% is strong enough that it will still give us a good indication and we can further interpret the data. Significance F 0.0012130798 (P-value) < alpha - Yes, MPG Highway is a significant predictor of MPG City. Coefficients Intercept 1.6073 MPG Highway 0.6720 Regression Equation MPG City = .6720 (MPG Highway) + 1.6073 y^ = β1x + β0 MPG City = .6720 (MPG Hwy) + 1.6073 y intercept = MPG City = .6720 (0) + 1.6073 MPG City = 1.6073 Slope As the MPG Highway increases by 1, then the MPG City will increase by .6720. Regression MPG City = .6720 (50) + 1.6073 When the MPG Hwy is 50 miles per gallon, then the MPG City = 33.6 + 1.6073 MPG city is expected to be 35.21 miles per gallon. MPG City = 35.21Dylan Hello class, This week’s topic is Regression and Correlation. We are tasked to choose two variables that are correlated from week 1’s data set. The variables that I chose to work with were price and number of cylinders. I believe that as the numbers of cylinders increases that the price of the vehicle will increase. I think it will be a positive relationship. It is my novice understanding of engines that a high number of cylinders is needed for larger vehicles such as trucks. While researching car prices, trucks and luxury SUVs were mostly higher priced. The price is the dependent variable, and the number of cylinders is the independent variable. As defined the independent variable is what you change and the dependent variable changes because of that. First, I began by following the directions in the PDF. My data did not need to be adjusted because it was already in numerical form. I used the Excel function =CORREL(Price column,#cylinders column) with a value of 0.5323. This is a positive correlation and agrees with my assumption. With this value I was then able to determine R-squared with the equation 0.5323*0.5323= 28\%. This translates to there is a 28\% variation in the data between price and cylinders accounted for by my data set. The R-squared value is low and tells me that this is a weak positive correlation. This means that although the variables both go up the relationship is not strong. The number of cylinders in a vehicle is not a strong indicator for car price. I used this data to run a regression analysis in Excel. Following the steps in the pdf was easy. This tool allowed me to verify my calculations for the correlation and R-squared. Next, I examined the data to see if the number of cylinders was a significant predictor for price. My p value (0.11321025) is greater than the alpha 0.05. Therefore, I can state that this is not a significant predictor in price. Because my value was so low, I do not think you would continue with a regression equation, but I did anyway for the assignment. I wrote out my regression equation using the coefficients from the data table. Price= 4199.12921(# of cylinders)+7714.55056. My y intercept does not have practical meaning for this scenario because no vehicle will have 0 cylinders. I did have one outlier in my set and this price definitely effected my values. I did a second regression analysis and changed that high price to match the other vehicles a little better. It is included in my attachment. This did result in a stronger positive correlation. I think the relationship between the actual data sets is important. Perhaps all the same make with different models and cylinders. I hope my data is clear. Good luck this week! Valerie Hi everyone, Just based on exercise, I already feel like I will have an easier time this week than last. I hope this proves true as I move forward with the lesson, because last week I just kept getting lost. Based on my data set, I feel that MPG City and MPG Highway