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

Developing a multi-variable equation to develop a forecast using regression output - Microsoft Excel Tutorial

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

Developing a multi-variable equation to develop a forecast using regression output

“

- [Instructor] All right, time to work with our equation. Let's go through everything we've done so far. So we realized that we're doing a multiple regression because there's lots of different factors that we want to take into account, as we predict our revenue for a future period. We were given a data set, had lots of different factors in it, we cleaned it up, we ran our multiple regression using Excel and the tool pack, and that gave us results and we kind of investigated, what was going on with those results. What we want to do now is we want to take these results and create our formula. And you could do it a number of different ways. You can type it in the long way of doing something like, you know, Y is equal to our intercept plus our coefficient, times, and then we'd call it, you know, Q1, whatever. We'd go and create a Q1 here, and then we'd go on and so forth. But this would take a long time, and it would be a little bit opaque. You couldn't really see what was going on. So instead, what I'm going to do is I'm going to do it an easier, more visual way. And you'll notice over here, I'm going to sort of build this piece by piece. You don't necessarily have to do it this way, but I like doing it this way just so you can see all the pieces coming together. And the first thing we see over here is, I have my coefficients, so A0, A1, A2, A3 and so on. And then I have my variables, my X1, X2, unemployment, interest rate, those sorts of things. So the first thing we can do is, we can go grab our coefficients and those, we got those from Excel. And now I'm going to do is here, I'm going to go copy these. I'm going to go over here and I'm going to do a paste special values, and now we have our coefficients, and those coefficients are going to be multiplied. So here's our formula down here. A1, A0 rather, isn't multiplied times anything. So I just put a value here of one 'cause we're always going to multiply it by one, but A1 is going to be multiplied times Q. And so we'll get back to our quarters here in just a moment. Then there is the revenue for company, the global online company that we had, and then we have unemployment and the interest rate. And so what we're going to do is we're going to type our numbers in here, and once we have our coefficient and our value, we're going to multiply those. So we're going to take this times this, and that'll give us our product, and that's going to give us all of our pieces and we're going to copy that down. So that will multiply each one of these and give us our products. And then all we need to do is add them all up, which I've done for you right here. It's the sum of all of these things. So let's go ahead and type in a value here. And I'm going to go ahead and type in, let's see, let's do this one here. I'm going to highlight these, and what would happen if we were in quarter number one? So we'll put a one there, and these are going to be zeros because it's not quarter two and it's not quarter three. And so that's zeros out those particular parts of the formula. Our revenue for global online is 29.13. Our unemployment was 4.9, and then our interest rate was 3.74. And based on this, based on the formula that we created with these coefficients, and by plugging in the data, the real life data from this, we came out with a revenue, a forecasted revenue of 43.88. What was the actual revenue for that period? It was 50.66, and the difference between those is our actual, minus our forecast, and it's 6.78. Is that something we should worry about? Well, let's go back over here to our results and we find our standard error is 5.309. Let's go ahead and type that in. Our standard error is 5.309, and so two standard deviations would be 10.618, and this is within two standard deviations, so this is not so bad. Let's go ahead and do this for another set of data. Let's do pick these right here, and we'll turn those green. All right, so again, we're going to erase these, say, all right, so now it is quarter three, so I'm going to put a one in quarter three because it's quarter three, and the other ones, we zero out. By the way, if it was quarter four, they would all be zeros. And then Excel was smart enough to figure out that, oh, well, we wouldn't have any of those. I've adjusted the rest of the formula to accommodate, but this is quarter three. My revenue for global online was 32.17. My unemployment is 4.9, and then our interest rates are 3.46. And now we've come up with a revenue, a forecasted revenue of 50.66. In this case here, our actual revenue was 46.8. In this case here, we were off by 3.86, well within our two standard deviations. Let's do one last thing here. What would happen if we wanted to forecast a future number? And so let's do this. Let's say, hey, I'm going to create a forecast for period three, and let's say that in period three, so these would be zeros, what's my forecast for period three if tomorrow, global online revenue reports revenue of 188 million. And then I go look in online and I find that the unemployment rate presently is 4.1, and then I see that the interest rate is 6.7. In that case, my forecasted revenue for that period of time would be 115.17. So as you can see, it allows us to check whether or not our formula is reliable, which Excel has already done for us, but then this is how we create a forecast for a future period based on the data that we're seeing collected in real life. All right, you now have figured out, your multiple regression, the formula, and then how to use that to forecast a future date.

Contents