Question

how to calculate this question tep by step plz tell me A company has recorded data on the weekly sales for its product (y), the unit price of the competitor's product (x1), and advertising expenditures (x2). The data resulting from a random sample of 7 weeks follows. Use Excel's Regression Tool to answer the following questions (data set also provided in accompanying MS Excel file). Week Price Advertising Sales 1 .33 5 20 2 .25 2 14 3 .44 7 22 4 .40 9 21 5 .35 4 16 6 .39 8 19 7 .29 9 15 a. What is the estimated regression equation? Show the regression output. b. Determine whether the model is significant overall. Use α = 0.10. c. Determine if competitor’s price and advertising is individually significantly related to sales. Use α = 0.10. d. e. Based on your answer to part (c), drop any insignificant independent variable(s) and re-estimate the model. What is the new estimated regression equation? Interpret the slope coefficient(s) of the model from part (d). (

Answer 1

Step 1- Arrange the data in excel as show below.

Step 2 - Go to the data tab and on the Data Analysis menu. You will get a series of function, select regression from it. Input the following parameter as shown below.

Y range is the sales.

X range range is the price and Advertising.

Change the confidence interval to 90%.

And press ok. The output will be generated on a new page.

The output is given below.

First we look at Adjusted R square, which tell us how much of the variation in the data is explained by the model. HIgh the value, better is the model.

In this case Adjusted R square is 65%, which ok. This helps us understand if the overall model is significant. In case at alpha = 0.10, is not very significant. Any valve over 70% is considered as significant.

Next we look at the p value of the coefficient. If the p value is less than 0.10, then the variable is significant.

Here we find the P value of Price to be less than 0.10 and hence it significantly related to sales. Advertising is not significant hence can be dropped.

The regression equation formed using the coefficients of the variables.

Sales = 3.59 + 41.32*Price + 0.0132 Advertising

Based on the inference from the 1st model, we drop advertising and re run the model. Follow the same steps as shown before and input the following parameter in the regression window.

Now the

Y range is the sales.

X range range is the price.

On running the model we get

In this model this model Adjusted R square = 72% which very good and indicate the model is significant. In other, the model can explain 72% of the variation in the data.

The p value of price is also less than 0.10, hence it is significant and has an impact on sales.

The new regression equation is given by

Sales = 3.58 + 41.60*Price

Interpreting the slope coefficient - If the competitor price increases by 1 unit, the sales will increase by 41.40 units.