Let's see, if we can continue using linear regression to learn more about our unit sales of our store brand toilet paper. For the same 22 stores, in addition to collecting the price and the unit sales, let's assume that we also collect the price of the leading brand, Angel Soft. That the price that they had during the same week of our experiment. Consider the data in front of us. Well, first we notice a few things. Our price compared to Angel Soft price, the leading brand, our price is lower. They are a more expensive brand that we carry in our stores. We also might want to ask ourselves well, before we rush into a full blown linear regression can we think about what we expect the price of Angel Soft to do to our unit sales or our store brand. Let's use a scatter plot to answer that question or at least investigate it in a little bit more detail. I'm going to highlight the two columns of the price of Angel Soft and our unit sales and I'm going to insert a scatter plot in a similar way to what we did before. I'm going to make it bigger. Hopefully you will see this better. And now, we can look at Cloud of points at this case. This is unit sales on the price of Angel Soft, our larger competitor. In this case, it looks, at least from the visual, it looks like it's trending up which means that a higher price increases the unit sales. Does that make sense? Well, of course it is. If our competitor raises their price by some amount let's say by a dollar, we expect to sell more of our store brand because it looks more favorable. Okay, let's get some more detail, how do both of these variables, our own price, and Angel Soft price come together to affect our unit sales. For this, we're going to turn to regression. We're going to run a multi regression or regression in which are y is going to be regressed on two different x, two different explanatory variables. Let's do it. Again, we use our data analysis option under the data ribbon, clicking it we choose regression, and here we're going to do the following. Our y range, our y variable, is still our unit sales. However, when we input our x range we're going to highlight both column B and column C, both our own prices and the price of our competitor. We're going to make sure that our labels are ticked, and we're going to find a convenient place on our spreadsheet to put the report, excuse me. This is the output from the analysis. Let me make the font as big as I can. Let me change the columns, so you can see it. And again, you can download this spreadsheet if you need to. And what do we see in front of us here? And let's focus again on the coefficients. I'll get rid of some decimals and tidy things up. That's always helpful to have things clearly, in a clear way. And what do we see in front of us? We see a coefficient, a negative coefficient on price. And a positive coefficient on the price of Angel Soft. Thinking about what this means to our model. It means that our unit sales are negatively affected. There is a negative sign in front of our own price and a positive sign in front of the price of Angel Soft. For a $1 change in our own price, in of our own brand. Let's say a $1 increase. We will see a reduction in our unit sales. And for one price increase in Angel Soft we will see an increase in our unit sales. Let me write out the equation, so it becomes clear. I'm going to move this a little bit and I'm going to bring back my pen and how does this look? In this case our model looks in the follow way our unit sales are going to be in this again as an estimate. Our forecast for how much we're going to sell is going to be a function of our intercept. In this case, it's 647.1 minus 27.4 times our price plus 15 times lets say 2, times Angel Soft's price. With this in hand, we can come up with some kind of prediction of how many units of our own brand we're going to sell, given a certain combination of price. Before we do that, let's understand what these coefficients tell us and how do we interpret it. When we say that Angel Soft has a coefficient of 15.2 this is controlling for our own price or holding our own price constant. What affect will we have when we change Angel Soft's price? And the same thing we can say about our coefficient. Holding Angel Soft's price constant, what change will we experience? Will we feel from changing our own price. This is why multiple regression is so important. Because it is only in this environment in which we can control for the other variables that we can make statements about the relationships for instance between our own price of our pack of toilet paper and the unit sales that we're going to achieve. Note that there are still some uncertainty in our model. If we look at our standard error, we still see that we have a standard error, we have some error, there are some noise. However, it is small, we were able to explain a lot more than we started off with. If you recall our standard deviation was 56. And so, with this model we have a powerful tool that allows us to not only understand the relationships between variables. But allows us to come up with a prediction for the future and some kind of forecast for a quantitative interest. For instance, the unit sales of our store brand toilet paper.
