Question

Question 5: The historical data on sales, prices, and selling expenses of a company is given in table: Using the regression tool in EXCEL, answer the following Sales Price Selling questions: Expense 16 2 a. What is the estimated intercept (a) of the regression? 14 4 9 b. What is the estimated regression coefficient of the product's price (B1)? 10 7 c. What is the estimated regression coefficient of Selling Expense (B2)? 8 9 11 5 13 4 14 E 13 5 9 7

Answer 1

After entering the above table data on excel, we'll go to the data tab and click on data analysis. Then from the list click on regression. Under Y variable, select the value of sales (dependent variable) and under X variables, select the value of Price and selling expense (independent variables). The regression table thus obtained would be:

SUMMARY OUTPUT Regression Statistics Multiple R 0.996501978 R Square 0.993016192 Adjusted R Square 0.990688256 Standard Error 0.395259882 Observations ANOVA SS F 426.5650666 MS 66.64241999 0.156230374 Significance F 3.40625E-07 Regression Residual 133.28484 0.937382245 134.2222222 Total 8 Intercept Price Selling Expense Coefficients 14.19372714 -0.813365843 0.375152388 Standard Error 1.359658511 0.078511316 0.143903994 Stat 10.4391853 -10.35985492 2.606963004 P-value 4.53068E-05 4.73288E-05 0.040284165 L ower 95% 10.86676262 -1.005476112 0.023032 Upper 95% Lower 95.0% 17.52069166 10.86676262 -0.621255573 -1.005476112 0.727272777 0.023032 Upper 95.0% 1 7.52069166 -0.621255573 0.727272777

In this, we can see:

1. Estimated intercept is: 14.19372714

2. Estimated regression coefficient of product's price is: -0.813365843

3. Estimated regression coefficient of selling expense is: 0.375152388