Question

In: Statistics and Probability

Price($) Promotional Exp (K) Quality City: 1/Suburban: 0 Sales(K) 949 5 100 1 168 941 4.3...

Price($)

Promotional Exp (K)

Quality

City: 1/Suburban: 0

Sales(K)

949

5

100

1

168

941

4.3

94

1

150

934

3

89

1

168

921

2

85

1

148

915

0.75

79

1

152

909

4.8

75

1

162

904

3.6

70

1

160

1014

3

63

0

123

1006

1.5

60

0

130

990

0.7

55

0

116

978

4.7

51

1

142

962

3.5

47

1

145

955

2.8

42

1

134

953

1.3

35

0

128

1050

0.25

30

0

117

1040

4.5

26

0

118

1038

3.2

22

0

107

1022

2.4

17

0

124

1021

1.2

12

0

104

1018

0

6

0

106

1010

2.9

60

0

120

935

4.4

91

1

153

Case Study

Please consider the data presented above for the monthly sales of Ever-cool brand of refrigerators in 1,000s of dollars (Dependent Variable) and the four independent variables.

Independent variables are:

Price (in dollars); Promotional Expenditure (in 1,000s of dollars); Quality of service (scale of 1-100); location (categorical variable: city area: 1; suburban area: 0).

Develop a multiple linear regression equation using either Excel or Minitab and based on the relevant outputs please answer the following questions:

a. Based on the relevant residual and normality plots, do you see any evidence of violation of assumptions (Linearity, Normality, Equal variance)? You must attach the relevant plots as part of your report.

b. State the multiple regression equation and interpret the statistical meaning of the estimated slopes, b1, b2, b3, and b4 (corresponding to the four independent variables).

c. At the 0.05 level of significance, determine whether each independent variable makes a significant contribution to the regression model. Based on these results, indicate the independent variable(s) to include in this model. (Based on t - test results)

d. Construct a 95% confidence interval estimate of the population slope between the independent variable ‘Quality’ and the dependent variable ‘Monthly Sales’ (B3) (please note that Minitab can’t do this directly, however you may use the relevant information from Minitab output and then construct the confidence interval manually)

e. Perform the overall F- test and comment on the significance of the model.

Please follow the following instructions:

  • Use Excel/ or Minitab to run the analysis.
  • Copy/Paste Excel/ or Minitab results to justify your answers.
  • submit original Excel/or minitab results along with your answers

Solutions

Expert Solution

Let the dependent variable, Y = Sales, and the independent variables, x1 = Price, x2 = Promotional Exp, x3 = Quality, and x4 = City

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Hours' column, Input X Range: 'Feet and Large' column, Click on Residual plot and Normal Probability Plot then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

a)

Linearity

From the line fit plot (scatter plot), there is a linear trend which indicates that the normality assumption is met.

Normality

From the normal probability plot, the residual values are along the straight line which indicates that the normality assumption is met.

Equality of variance

From the normal probability plot, the residual values are randomly distributed along the horizontal line which indicates that the equality of variances assumption is met.

b)

From the regression summary report

Coefficient Interpretation
x1 -0.136 For 1 unit increase in price, the sales decrease by 0.136
x2 1.759 For 1 unit increase in promotional experience, the sales increase by 1.759
x3 0.241 For 1 unit increase in quality, the sales increase by 0.241
x4 12.286 If suburban, the sales increase by 12.286

c)

From the regression summary report

P-value
x1 0.086 < 0.05 Significant
x2 0.187 > 0.05 Not Significant
x3 0.014 < 0.05 Significant
x4 0.089 > 0.05 Not Significant

Hence only variable x1 and x3 should be included

d)

From the regression summary report, the 95% CI for the coefficient estimate of Quality is,

Lower 95% Upper 95%
Quality 0.054963 0.426603

e)

From the regression summary report,

Significance F
Regression 2.31294E-08

The significance F value is 0.0000 which is less than 0.05 at a 5% significance level which mean the model fits the data value at the 5% significance level. Hence we can conclude that independent variables fit the model significantly compared to intercept only model.


Related Solutions

X Y/1 Y/0 Years Exp Successful Unsccessful total 4 17 83 100 8 40 60 100...
X Y/1 Y/0 Years Exp Successful Unsccessful total 4 17 83 100 8 40 60 100 12 50 50 100 16 56 44 100 20 96 4 100 The data in tab #2 pertain to the years of experience of project managers and the numbers of successes and failures they have had on major projects. Use the data set given in tab #2 in the attached Excel workbook and logistic regression to find the following: The probability of success given...
Consider a gaseous feed, CA0=100, CB0=200, enters a flow reactor at T0=1000 K and ?0= 5...
Consider a gaseous feed, CA0=100, CB0=200, enters a flow reactor at T0=1000 K and ?0= 5 atm. The reaction, ? + ? → 5?, occurs in the reactor. Calculate the XA, XB and CB if the exit stream leaves at T=400 K and ? = 4 atm. (Assume ideal gas behavior).
Suppose: 1) 1 year futures price=$1000, 2) interest rate = 0%, and 3)for K=$1000, the premium...
Suppose: 1) 1 year futures price=$1000, 2) interest rate = 0%, and 3)for K=$1000, the premium on a 1 year call (C) is $100 and on a 1year put (P) $150. a. Does put-call parity hold? If not, relative to each other which option is overpriced and which underpriced? b. Describe a profitable and risk free arbitrage, that is, what would you long today and what would you short? c. For your arbitrage described in question b. what are your...
Given: Butter Guns MC 0 100 1 90 2 75 3 55 4 30 5 0...
Given: Butter Guns MC 0 100 1 90 2 75 3 55 4 30 5 0 Complete the marginal cost column (MC). Identify the opportunity cost increasing butter production from 1 to 2 units on the graph. Graphically illustrate the Keynesian versus classical position of the economy and their respective opportunity costs (two graphs). Explain the policy implications of these two alternative assumptions of the economy (Per lesson notes).
1.A company has provided the following data:   Sales 12,000 units   Sales price $100 per unit   Variable...
1.A company has provided the following data:   Sales 12,000 units   Sales price $100 per unit   Variable cost $50 per unit   Fixed cost $300,000             If the dollar contribution margin per unit is increased by 10%, total fixed cost is decreased by 20%, and all other factors remain the same, by how much will net income increase? 2.   Hamada Company sells a single product. The product has a selling price of $100 per unit and variable expenses of 80% of sales. If the...
The data below represents a demand schedule. Product Price ($) Quantity Demanded 6 0 5 1...
The data below represents a demand schedule. Product Price ($) Quantity Demanded 6 0 5 1 4 2 3 3 2 4 1 5 0 6 In the diagram below, draw a demand curve. Use the line tool to graphically show Demand. This line should only contain the two endpoints. Use the midpoint formula for Ed to determine price elasticity of demand for each of the four possible $1 price changes. Instructions: Input the your answers as positive values (absolute...
Price Coupon YTM Time to maturity ? 0 2% 1 year 890 5% ? 2 years...
Price Coupon YTM Time to maturity ? 0 2% 1 year 890 5% ? 2 years 945 ? 5% 3 years Assume semi-annualcoupon payments. Find missing values in the table above. Please show details of your solution.
0 1 2 3 4 5 6 7 Project A -$300 -$387 -$193 -$100 $600 $600...
0 1 2 3 4 5 6 7 Project A -$300 -$387 -$193 -$100 $600 $600 $850 -$180 Project B -$400 $134 $134 $134 $134 $134 $134 $0 Project Delta #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Crossover Rate = IRRΔ #N/A Project MIRR Calculations at WACC = 18% WACC 18.00% MIRRA #N/A MIRRB #N/A
Time 0 1 2 3 4 5 Cash flows -800 80 100 300 500 500 Financing...
Time 0 1 2 3 4 5 Cash flows -800 80 100 300 500 500 Financing rate=15% Reinvestment rate=20% a) Write the excel command to calculate the NPV: b) Write the NPV numerical value: c) Write the excel command to calculate the IRR: d) Write the IRR numerical value: e) Write the excel command to calculate the MIRR: f) Write the MIRR numerical value: h) Write the excel command to calculate the PI: i) Write the PI numerical value:
Date Transaction Units In Unit Cost Total Units Sold Sales Price Total 7/1 Balance 100 4.10...
Date Transaction Units In Unit Cost Total Units Sold Sales Price Total 7/1 Balance 100 4.10 410 7/6 Purchase 800 4.20 3,360 7/7 Sale 300 7.00 2,100 7/10 Sale 300 7.30 2,190 7/12 Purchase 400 4.50 1,800 7/15 Sale 200 7.40 1,480 7/18 Purchase 300 4.60 1,380 7/22 Sale 400 7.40 2,960 7/25 Purchase 500 4.58 2,290 7/30 Sale 200 7.50 1,500 Totals 2,100 9,240 1,400 10,230 Compute FIFO, LIFO, Average for the perpetual inventory system. Be sure to do...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT