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

Running a multiple regression in Excel - Microsoft Excel Tutorial

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

Running a multiple regression in Excel

“

- [Instructor] Okay, so in the previous video, we set up our data. We prepped our data to run the multiple regression. Now it's time to run that regression. And as I said, there's so much to this, but the great thing is that Excel is such a great tool that it's going to do all the work for us. So how do we actually run a multiple regression in Excel? First thing you want to do is go over to the data tab up here. So there's all these different tabs. We want to go to the data tab. And when you go to the data tab, you'll see there's this analysis tools thing that you want to click on over here. And when you do that, you'll realize that there's a couple of add-ins possible, and yours already may be activated, but in case it's not, just click on analysis tool pack. You click okay, and then you'll see data analysis pops up over here. And that's what we're going to use. We're going to click on data analysis, and it's going to give you all of these things that the data tool pack can do for you. A lot of these are covered in statistics courses for Excel. So you may want to look, if you're curious, you can go look at those. But obviously what we're going to do is we're just going to stick to regression. That's what we want to do here. We want to run a multiple regression. So let's click okay, and then it's going to ask you, okay, so what's your input range for Ys and Xs? And remember, for Y, we have one Y that we're looking at. And in this case here, the Y that we're looking at is this, right here. So we're going to include our heading, and we're going to copy all the way down, or we're going to highlight all the way down 'til we get to row 37. You can let that go and now it knows exactly what you want. Input Y range, and, you know, the dollar signs, it's exactly what we want. So don't mess with that. And then the next thing we want is our X range and our X range, well, these are all the things that we're going to see whether or not they are valuable in telling us what our forecast is going to be. So here, it is all of my Xs, headings included, so all my gray headings, and I'm going to go all the way down to row 37. Oh, I put them in the wrong spot here. So let me fix that. Let's do this again. Gives us a little practice, right? So we're going to go to our Ys. And our Ys are all the revenues. Now I want to click on the X range. That's where we're going to go over here. And we're going to grab all of our data, including our headings. And because we grabbed our headings, our labels, we want to click on the labels box. In addition to that, we're going to have our data go onto a new worksheet because you're going to see this is going to be a lot of things that it gives us. And the other thing I like to click on is residuals, and we'll talk about that, once we look at our data. This is it. Give it the input range, Ys, give it your X range, all the different variables that you're looking at, labels, new worksheet residuals, and that's all you need to do. You're ready. Click okay. And what you'll notice is, where before, we had three different worksheets over here, there's a new one, and this is the output from our regression. Wow, it looks scary. Don't worry about that. We're going to look at this and see what all of this means in the next video.

Contents