Let's try an exercise using regressions that you're likely to do in a business setting. And it requires becoming a little more familiar with the actual regression tool, but also looking at some stock market information. And so it will help you become familiar with how to engage in some different tasks, how to use the regression in the Excel. And then how to understand some of the numbers that are presented to you in some different financial settings. Lots of helpful stuff. We're going to calculate what's called a beta. The beta of a company is essentially a number that represents the relationship between the returns of a stock and the returns of the market in general. So as you may find out in your business studies, a higher beta means that their is more volatility. So as the market moves by a little percentage, your stock returns may move by a lot. A very low beta means that there's not as much volatility with your stock. And so we end up talking a lot about betas in finance. You might be talking about them in accounting and what have you. So it's a very, very important number. And you might ask the question, well, how do we find the company's beta? Let's do the exercise right here. I'm going to run it through for a company, Coca-Cola. And then we'll see if you can replicate it with another company like UPS or something like that. All right, so first off, I need my Excel spreadsheet that I have brought up here. And we're also going to use finance website, Yahoo Finance. Okay, I've pulled up Yahoo Finance, and I've already typed in the Coca-Cola Company, which is KO. And I've got a year worth of data. I've got from May 2, 2016 to May 2, 2017. So if I download these data, it will actually pull this information into a file for Excel. I'm going to open up that file right here. Okay, What I really want is I want data for the whole time period. So I want historical prices, I want daily data, right here. And so we'll notice, let's do prices only, historical, just the historical prices only here. Okay. All right, so I've got historical prices only. I'm going to download all these data and see if I can get the whole set of data here. When I download the data here, it's going to give me historical price going back for a full year, right? From 5/2 2016 all the way to 5/2 2017. So what I'm going to do is here I'm going to to create a filter. And I'm going to sort these data from high to low, From the oldest to newest, there we go. So I'm looking at the data from 2016 all the way through to 2015. So I've got the stock price, when it was opened, what its high was, what its low was and whats its close was. I'm going to end up looking at the closed data, okay? And I'm going to go back to my Yahoo Finance here, and I'm going to type in S&P, 500, okay, as an index. Right here as an S&P 500 index. And so I'm going to use the same dates, May 2nd of 2016 through May 2nd 2017. I've got historical prices here. I've got the frequencies daily here. I'm going to apply this filter right here. And I am going to download the data. Give it a second to download. And I'm going to pull up these data as well. I'm going to apply the same filter here. And I'm going to sort these from oldest to newest. So now I have two sets of data. I have essentially information about the stock prices for Coca-Cola, and I also have an S&P Index. What I'm going to do is, I'm going to take my S&P Index, right here, and I'm going to copy this and I'm going to bring it over to my other file right here, where I have Coca-Cola. And I'm going to paste it here. Okay, so now what I have is I have the close for Coca-Cola, KO, and I also have the closing stock price for the S&P, right here, right? So now what I'm going to do is I'm going to run a regression here. Let's move this filter so I can run a regression. I've got data from 5/2/2016 through 5/2/2017. What I want to do is I want to know how does the movement in one stock price, how is this related to the movement in the other stock price? Well, I'm going to create two additional variables here. And one variable's going to be the daily change in my stock price for Coca-Cola. So, Daily Change KO. And the other one is going to be my daily change for the S&P. Right there. So my daily change here, from the second to the third, this is equal to the new stock price, which is this guy, minus the old stock price, which is that guy, divided by the old stock price, which is that guy. Now you might say, we did this, right? Isn't that in my ratios in my percentage changes? Yes, that's correct, that's exactly what we did, okay? So I could turn that into a percentage, add a couple of decimal places here. And then I could copy this over. So over here, let's do the exact same thing. Let's take the closing price of my S&P today minus the closing price of my S&P yesterday, as a ratio of the closing price yesterday. I get a change in the return on that one. Let's also turn that into a percentage, And there we go. So now I've got a change in my Coca-Cola and a daily change in the S&P. I'm going to click these down and it will then populate my entire column here. So I'm going to use these little headings right here, I'm going to copy these right here. And so now what I'm going to do is I'm going to regress. I'm going to take a regression of my daily change in my stock price, my return, really, for Coca Cola relative to the daily change of the S&P. And so I am going to go to my Data, going to do Data Analysis, I'm going to click on Regression, here. My input range is this range right here, my daily change of my Coca-Cola, that's my dependent variable. And then that is going to be a function of the daily change, the daily rate of return, if you will, of my S&P. Right here, I'm including my labels, and I hit OK. I get this output right here. Now, there's a function. And so the function says that my daily change in the Coca-Cola is equal to 0.0000000492, right, plus 0.0644 times the daily change in the S&P. This number right here, we'll highlight in pink, That right there is my beta. That's the beta for Coca-Cola. That's a beta for one year. That's Coca-Cola's beta over the last year. What we do is if we're looking at the beta of Coca-Cola, they might actually calculate using three years of data or five years of data or different numbers of years. Because over a longer period of time, it sort of smooths out some of the variability that you see. But essentially, it's the coefficient of a regression equation. And the regression equation is the daily return for Coca-Cola is equal to a intercept, -0.000494, plus some beta, times the daily change for the S&P. So remember, the beta is that coefficient. And so when people refer to the beta of a stock, what they're really doing is they're referring to the coefficient of the stock's return relative to the return for the market at large. See if you can do this exact same thing for UPS. We already have the data for S&P. And so see if you can grab the data for the last year for UPS and the last year of the S&P and calculate the beta for UPS. It's a relatively simple calculation. Can be very, very helpful understanding not only how to calculate your regressions, but also when your instructor starts talking about the beta of a stock, understanding that they're really talking about a regression coefficient.