 Hello and welcome to this session. This is Professor Farhad and this session will look at regression analysis using Excel. This topic could be covered in an intro to data analytics course or data analytics and accounting. Also accounting students can use this as well as managerial accounting. As always I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is where you would need to subscribe. I have 1700 plus accounting, auditing, finance, stacks and Excel lectures. If you like my lectures please like them, share them, put them in playlists, let the world know about them, subscribe to the channel. If they help you it means they might help others. Connect with me on Instagram. On my website farhadlectures.com you will find additional resources to supplement your accounting education, your CPA exam, your CMA exam, your enrolled agent exam. So if you're interested in improving your exam score on the CPA exam and pass the exam please check out my website. So to illustrate the concept of a regression analysis I'm going to be using the example of advertising expense and revenue. So basically what is a regression and why do we use, why do we need to do a regression? The idea of a regression to see if there's any relationship between a dependent and an independent variable. What does that mean? Well we know that if we advertise, if we spend money on advertising it should have some effect on revenue. Well let's find out if what we advertise will have effect on revenue and this is very common. Think about a business, an e-business or a regular business. What they do is they play, is they place Google ads and they want to know for example how much Google ads is increasing the revenue because you are spending money to generate money. So you want to know how much of that money helps explain your increase in revenue because that's very important. If it's helping you should increase it. If it's not helping anymore you should reduce it. You should change your strategy and that's not the only way you could use a regression. Anytime you want to examine the correlation between two variables, if one drives the other, then you would use this regression. So it's very useful in the real world. So to run your regression you would highlight the advertising and the revenue columns and in this example I'm going to be using in seagull regression. You could use multiple regression. Maybe I will do that in another recording. You click on data analysis. Now again if you don't have this analysis tool pack please look in the description for this. You want to click on regression. You choose regression. If you don't know where it is just r, r and it's going to take you to regression. Click on okay and now you have to tell excel which one is the y, the input of the y and input of the x. Well you have to understand let me just take this out so you see what we are doing here. The x is the independent variable and the y is the dependent variable. So the y is the revenue. The revenue is dependent upon advertising expense and the reason I chose this example because it's easy to associate those two. Your revenue is dependent upon advertising. The more you advertise generally speaking the more exposure you have generally speaking the more revenue you should have. So your revenue is dependent on advertising. So for example I don't advertise a lot for my business which I should but I don't not yet at least. So let's go ahead and input y. I'm going to input the revenue y in the y, select the y and the x-axis is the advertising. Again I'm using a single regression. You could use multiple regression and I will have another recording down the road. I'm going to click on the label to indicate I have a label on the top. A confidence interval it's going to be 95 percent. I'll explain why 95 percent what I chose, why I chose this 95 percent. Now what do you want your output? You could have a new worksheet, a new workbook or you could have an output range here. I'm going to choose a new worksheet this way you'll have a clean slate. I'm going to click on the residual plot line fit plots and you'll see what there is. I'm going to click on residual maybe I'll click on residual to it doesn't matter and I'll click on residual you'll see what does that mean in the residual plot we'll talk about that. Then I'm going to click on okay and it's going to run my analysis my regression analysis in a new worksheet and I'll fix the I'll fix those later. So I'm going to now focus on what I think is important when you are reading this regression. So what are you looking for what are you looking for and how should you read this regression? Okay so the first thing I would look I would look for is this r-square this r-square right here okay what does this r-square represent? Well remember I'm looking at the relationship between advertisement and sales and this r-square it's going to tell me the relationship or the proportion of variability between in in in sales the variability in sales explained by the advertisement how much variability in sales explained by advertisement and what it says here what it's saying here I'm going to make this let's assume 30 percent it's 0.2872 and it comes going to make 30 percent it's easier to talk about this. So 30 my change in sale is explained 30 percent by the change in advertisement. Now this could be because of just a coincidence that's what that's what it is. So to find out how how strong is this relationship well you have to look at something called the significance of F F test and notice here it's 0.0000755105 that's a small well what I'm looking for is something that's less than five percent remember I said I want my confidence interval 95 it means I could be wrong five percent of the time but what it's saying here um you know there's no chance that this relationship is by chance or there's a low chance this relationship by chance so the lower this the lower this number the lower this the lower this significance F the smaller this number the greater the probability that this 30 percent relationship is not by chance now is this 30 percent large relationship and well if it's 50 it's better but 30 percent is significant I mean it's it's 30 percent of your sales is driven by advertisement that's pretty good that's a pretty good relationship that's what I'm trying to say here but the higher the better so it's not only this number you want to see is it by chance or you have a good significant number you would look at the F significance of F and this basically I don't want to you know I don't want to talk about this F but this F is is represented by this number you know it's transferred into this number you don't have to worry about the statistics this is not a statistical course but the point is those two are related to each other and actually those are all related but you would only look at the significance of F now the other thing you want to look at is the intercept the intercept basically the Y intercept and the co which is called the coefficient or the Y intercept and the advertising coefficient as well well what do we what do we need to understand we need to understand that the intercept is 81 924 dollars and 75 cent let me show you on the graph how do we how do we interpret the intercept so I'm gonna come up here and this is I'm gonna increase the size of this give me one moment please so it looks a little better so I'll be able to explain or help you understand what does it mean the Y intercept let me let me grab it and put it down here for now okay so this is the Y intercept it means I'm just gonna extend this line the predicted value it means if we have no advertisement this is Y and this is the x-axis okay so this is the advertisement and this is the revenue so if we have zero advertisement if we did not advertise not opinion advertisement our sales should be the Y intercept 81 924 so this is what we're saying okay so this is what the interest the coefficient intercept is 81 924 if we have zero advertisement now as we advertise more our revenue goes up but but how much does it go up by well it goes up by this amount 3.67 or 3.68 so for every dollar we put an advertisement we're gonna multiply it by 3.67 increase in sales so simply put if you really I want to know what's my the formula for my total sales my predicted formula or my predicted sales I can take it's 81 925 dollars I'm gonna round it plus three dollars and 68 cents routing times x which is my advertisement and this is gonna give me my predicted sales based on predicted sales based on this regression based on this regression now how good is this regression well what I did is I told you it's it explained it 28 percent of the time but what I did is I had a residual plot you remember I told you I'm going to be showing you the residual plot and this is the residual plot and basically this explain this tells you what I'm going to be looking at now notice sometime sometime the revenue is below sometime the revenue is above this regression line but if we net all these out they will net out to zero so sometime it's a little bit above sometime it's a little bit below this is a residual plot let me show you the numbers so if I take my first observation and I plug in this formula if I take my first observation I plug in this formula my predicted revenue will be 101 007 which it's going to be less than the 1000 and two dollars less than the that less than the actual revenue well the same thing if I take my second observation and I input the formula it's also less so at some point it's going to be less and if I scroll down in some situation I'm going to have more revenue but overall they will net out to zero they will net out to zero so what I did is I was able to explain or I was able to predict use this information to be able to predict what change would I expect if I increase my budget so simply put now I can come here and say well what happened if I make my budget ten thousand dollars what would happen to my total sales or what happened if I made my budget my advertised in budget seven thousand dollars and see if that materializes if it materializes it means the relationship is good maybe I would I should be able to able to advertise more and if it's if there's that positive relationship then that's a good idea if it's not having any effect then I should stop advertising so hopefully I gave you an idea of how to run the regression in excel how to read the major component and how to meet the the major numbers in excel again here you have the p-value as well again this is going to tell you the significance of the coefficient and you want it to be especially for the x variable and this is significant so it's it's a good it's a good relationship it's not by chance hopefully in the next session I will take a look at a multiple regression but again I would like to remind you if you have any questions or if you'd like to visit my website for additional resources if you're an accounting students or a CPA candidate I strongly suggest you check out my website for additional resources please like this recording share it put it in playlist study hard good luck and stay safe especially due to coronavirus