From the course: Excel Data Analysis for Supply Chain: Forecasting

Preparing data for multiple regression in Excel - Microsoft Excel Tutorial

From the course: Excel Data Analysis for Supply Chain: Forecasting

Preparing data for multiple regression in Excel

“

- [Instructor] Now, Excel's going to do a lot of the hard work, pretty much all of the hard work, in multiple regression. But it's very particular about what the data is going to look like. So what we're going to need to do is we're going to need to clean up this data. And really, it's not a whole lot that we need to do, but it does need to be done in a particular way. And so the first thing that we're going to do is we have to realize that we're going to be working with things that are lagging. And so when you're living in quarter two, you don't know what the revenues and unemployment and interest rates are in your particular period. So very often what's happening is that the numbers are lagging. These numbers will be reported to you when you are in quarter two. And so that's what this lag over here is about, the fact that we're going to be taking these numbers, and we're going to shift them over one period when we will actually be experiencing them in real life. And I can go ahead and copy this across. And if I want now, I can then copy it down the bottom of our data set. And so now what we can see is that in period two, we will be seeing period one's revenue and unemployment and interest rate. And that's what this lag is about. And what I've done, you'll notice, is I've created headings here because since we won't be using this line of data, line number two. We're going to be using line number three of data, so it needs its own header, and that's why we have these headers copied over here in gray. And the color doesn't really matter, but I just wanted to kind of distinguish them. And the three different data sets, or three different things that we're going to be tracking here are, as you can see, the interest rates and unemployment, but then we also have the global, the revenue for a company that does online sales. So it's like a big company that sells stuff online, and we'll call them Global Online. And so these are their revenues in billions for a particular period. So Global Online, their quarterly revenue. We have the unemployment number for quarter number one and the interest rate for quarter number one. Now, since we won't be using this particular revenue number, 58.01, 'cause that happens in a period where we don't have the data, I'm going to type in revenue here. So now, I have my heading for periods, you know, 2015 quarter two all the way down to the bottom of that. I now have my revenue, my lagging unemployment, and my lagging interest rates. So these are all things that I think might be impacting the revenue of my company. Then the next thing we need to do is start dealing with the quarter 'cause maybe there's seasonality built in here, and that's one thing that perhaps Excel could help us figure out. But what we want to do here is we don't want to put it in the quarter number. We want to tell Excel whether or not this is quarter one, two, three, or four. You might say, hey, how about quarter four? Let me show you something. This is the way we're going to do this. We're going to do an if statement. So if this is equal to one, because we're looking to see if it's quarter one, we're going to put a one in here, and otherwise, we'll put a zero in here. And I'm going to do the same thing over here. Equals if, this right here is quarter two, 'cause now we're looking for quarter two. So if that is equal to two, we're going to put a one in here. Otherwise, we'll put a zero in here. And then finally, I'll do the same thing looking for quarter three equals. If this number over here in the quarter column is equal to three, well, then I want to know that by putting a one here. Otherwise, we'll just put a zero. And I'm going to copy this down. And I want you to notice what happens here. And let's do this a little bit longer. We're going to go all the way to the bottom eventually. But this is a good starting point because what we see here is that we have quarters one, two, three, and four over here in column B. And look what happens with our quarters. This is quarter one, so it gets a one. And this is quarter two, and that gets a one. And this is quarter three, and that gets a one. And then this is quarter four, and none of them get the one. And Excel is smart enough to figure out that, oh, if there's no quarter, then that must be quarter four. And interestingly, if you actually put in a quarter four, it would get confused and say, hey, what's going on here? Why does everybody get a one? So that's why you want to do it this way. And when you copy it down all the way to the bottom, our data set now knows, our data set now will tell us which quarter is it, what was the lagging revenue, the lagging unemployment, the lagging interest rate, and what was the revenue that we had in that particular quarter. Now we have our data set, and it's all ready to run the regression. And that's what we're going to do in the next video.

Contents