How to use Excel for linear demand regression

- Do you like strawberries? Well, it seems that most people do. Would you pay $1 for a pound of strawberries? Probably yes. Would you pay $500 for a pound of strawberries? Probably not. So the price of strawberries strikes the demand for strawberries, and in this video, you will learn to calculate how the price drives demand based on a few data points. Specifically, we will perform a linear regression for demand analysis in Microsoft Excel. A regression is a statistical technique that relates a dependent variable to one or more independent variables. Demand is our dependent variable because the demand depends on the price, which is the independent variable. Let's proceed using axle for linear demand regression. The dependent variable Y is demand. The independent variable is price and the function looks like this. We define the demand Y as a function of a constant plus the price multiplied by a variable B. I'm here in Excel 365 for Windows 11. Before performing a linear regression in Excel, you must load the add-on analysis tool pack. Go to file, more, options, add-ins, go and click on analysis tool pack and okay. Download and open 3-1 Excel for linear regression from the exercise files. In this table, I have two columns. The first column shows the demand in tons for strawberries and the second column shows different prices for strawberries. I use some prices from my home country, Switzerland. Strawberries are seasonal. In the summer, we have local fruits. In the winter, we need to import them, and so prices are much lower in the summer than in the winter. The typical package is 500 grams, which is about 1 pound. The first row indicates that when the price was CHF 3.50, 57.5 tons of strawberries were sold by one supermarket chain. When the price increased to CHF 3.80 the demand dropped to 6.8 tons as shown in the second row. To activate the regression, click on data, data analysis, and select regression. The input Y range is the demand. The input X range is the corresponding price. We click on labels to indicate that the first row includes the variable names. We should not click on constant zero. Finally, we click on new worksheet ply. For this video, I'll leave the other boxes unchecked. The regression is executed by clicking okay, and the results open in the new worksheet. The most important results is at the bottom of the output. The first two columns show the demand equation. Please note that the constant is called intercept. The demand in tons equals 9.3323 minus 0.6576 multiplied by the price. The other columns are statistically exciting, but beyond the scope of this video. The only column we check is the P value, which should be below 0.05. The result indicates that the regression is statistically significant. A linear regression based on 14 observations suggests that the demand in tons is 9.332 minus 0.658 multiplied by the price. This function allows us to estimate any demand. For example, we can predict that at CHF 5.55 per 500 grams of strawberry, 5.6828 tons will be sold.