Question

Al Greens is a franchise store that sells house plants and lawn and garden supplies. Although Al Greens is a franchise, each store is owned and managed by private individuals. Some friends have asked you to go into business with them to open a new All Greens store in the suburbs of San Diego. The national franchise headquarters sent you the following information at your request. These data are about 27 Al Greens stores in California. Each of the 27 stores has been doing very well, and you would like to use the information to help set up your own store. The variables for which we have data are detailed below. Xalet sales, in thousands of dollars 2 number of square feet of floor display in store in thousands of square feet *3 -value of store inventory, in thousands of dollars amount spent on local advertising, in thousands of dollars *y of sales district, in thousands of families * - number of competing or similar stores in sales district A es district was defined to be the region within a smile radius of an Al Greer store 12 231 3 294 B2 82 156 232 69 12 10 0.5 519 15 5.5 600 12 1 431 569 106 141 5 457 4.0 571 M 299 3.1 512 1 10 195 10.1 14 72 84 20 1.2 211 21 15 GB 016 102 5.4 174 1 577 7 464 47 113 15 150 163 25 65 12 3.3 11 98 16 27 10 342 4 26 72 13 997 139 7 SI 11.5 528 16.3 123 16.0 23 0.5 5.5 14 11 12 347 11.3 341 3.5 90 5 503 5.1 500 0 400 20 120 a) Generemy Menduding the mean and standard deviation of each with Compute the coefficient of varios for each wie decimal places) CV 15.

Answer 1

Note : allowed to solve only 4 questions in one post.

a. Put the data in excel and find the mean , standard deviation and CV using the forumulas as shown.

J13 > fx А B с D E F G H J к - L M N P 1 x1 X2 x3 4 хб x3 2 231 3 x4 294 232 8.2 4.1 286.57 192.06 X2 3.33 2.01 0.60 Mean Standard Deviation |cv 11 12 15 1 x4 8.10 3.77 0.47 3 x5 9.69 5.14 0.53 387.48 191.17 0.49 хб 7.74 4.90 0.63 2.2 0.5 5.5 4 149 4.3 156 10 519 437 487 0.67 600 5 6 16.1 x5 8.2 6.9 3 12 10.6 11.8 8.1 7.7 3.3 4.4 567 5 4.8 571 x2 X5 7 8 9 299 3.1 512 347 14.1 12.7 10.1 8.4 2.1 4 10 12 Formula used are given below |x1 x3 X4 |хб Mean =AVERAGEA(A2:A28) =AVERAGEA(B2:B28) =AVERAGEA(C2:C28) |=AVERAGEA(D2:D28) |=AVERAGEA(E2:E28) =AVERAGEA(F2:F28) Standard Deviation -STDEV.S(A2:A28) ESTDEV.S(B2:B28) ESTDEV.S(C2:C28) =STDEV.S(D2:D28) ESTDEV.S(E2:E28) =STDEV.S(F2:F28) Ісу =J3/J2 =K3/K2 =L3/L2 =M3/M2 =03/02 195 2.5 10 20 1.2 212 15 =N3/N2 11 68 0.6 102 4.9 8 4.7 12.3 570 5.4 788 17.4 1 428 4.2 577 10.5 14 7 12 13 14 15 16 464 4.7 535 11.3 15 3 15 0.6 163 2.5 2.5 14 65 1.2 168 4.7 11 3.3 2.7 17 98 1.6 151 4.6 10 18 4.3 342 5.5 16 4 19 2.6 196 7.2 6.3 13 20 453 13.9 398 161 397 497 528 99 3.8 5.3 21 22 5.6 518 615 278 142 7 1 0 14 12 23 24 10.4 11.5 12.3 2.8 3.1 9.6 9.8 0.8 1.1 3.6 0.5 16.3 16 6.5 1.6 11.3 11.5 15.7 12 25 347 461 6 26 3.5 382 5 341 507 27 5.1 12 0 590 517 28 400 8.6 7 8 29

variable with largest spread is x1

varaible with least spread is x3

Correct answer : x1, x3

b. Correlation and coefficient of determination.

PUt the data in excel as shown and find the correlation and R2, using the formulas used.

B D E F J L M x1 x3 x5 1 2 A x2 231 156 C x4 294 232 149 600 11 x6 8.2 4.1 8.2 6.9 3 3 12 4 10 519 5 12 4.3 16.1 14.1 15 1 5 4 6 437 487 299 567 571 7 8 512 10.6 11.8 8.1 7.7 3.3 4.9 12.7 10.1 8.4 10 H Pair x1, x2 X1, X3 x1, x4 x1, x5 x1, x6 X2, X3 X2, x4 X2, x5 x2, x6 x3, x4 X3, X5 X3, X6 x4, x5 x4, x6 X5, X6 Correlation (r ) 0.8941 0.9455 0.9140 0.9537 -0.9122 0.8436 0.7486 0.8380 -0.7657 0.9062 0.8639 -0.8074 0.7954 -0.8413 -0.8696 R2 0.7994 0.8940 0.8354 0.9095 0.8322 0.7117 0.5604 0.7023 0.5864 0.8213 0.7464 0.6519 0.6327 0.7078 0.7562 Formulas used =CORREL(A2:A28,B2:B28) =12^2 =CORREL(A2:A28,C2:C28) =13^2 =CORREL(A2:A28,D2:D28) =14^2 =CORREL(A2:A28,E2:E28) =1542 =CORREL(A2:A28,F2:F28) =16^2 =CORREL(B2:B28,C2:C28) =1712 =CORREL(B2:B28,D2:028) =1842 =CORREL(B2:B28,E2:E28) =1912 =CORREL(B2:B28,F2:F28) =110^2 =CORREL(C2:C28,D2:D28) | =111^2 =CORREL(C2:C28,E2:E28) =11242 =CORREL(C2:C28,F2:F28) =11312 =CORREL(D2:D28,E2:E28) =114^2 =CORREL(D2:D28,F2:F28) =115^2 =CORREL(E2:E28,F2:F28) =11642 9 347 195 20 12 15 10 11 3 2.2 0.5 5.5 4.4 4.8 3.1 2.5 1.2 0.6 5.4 4.2 4.7 0.6 1.2 1.6 4.3 2.6 3.8 5.3 5.6 0.8 1.1 68 2.1 4.7 12.3 12 13 570 428 464 17.4 10.5 8 1 7 14 14 3 11.3 2.5 4.7 15 2.5 15 65 98 14 11 10 398 4 161 397 212 102 788 577 535 163 168 151 342 196 453 518 615 278 142 461 382 590 517 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 4.6 5.5 7.2 10.4 11.5 12.3 3.3 2.7 16 6.3 13.9 16.3 16 13 7 1 497 528 99 0 6.5 14 2.8 3.1 0.5 12 1.6 11.3 6 347 341 9.6 9.8 5 3.6 3.5 5.1 8.6 12 11.5 15.7 12 507 400 0 7 8

Note : X1 is the annuals sales, we need to check the values of correlation with x1 and other variables.
Variable that has the greatest influnece on annual sales : X5
Variable that has the least influnece on annual sales : X2

c. Regression equation
Using EXCEL’s Data Analysis add-in to determine the least squares trend equation.

Step 1 : Put the data in excel as shown.

Step 2 : go to DATA -> data analysis -> regression

A B C D E F G H 1 x1 k2 x3 X4 x5 2 231 3 294 11 8.2 6.9 x6 8.2 4.1 4.3 3 156 232 12 2.2 0.5 4 10 149 3 15 5 519 5.5 600 12 16.1 1 437 4.4 567 10.6 14.1 5 6 7 487 4.8 571 st 12.7 10.1 00 299 3.1 512 10 ON 195 2.5 8.4 347 212 12 15 10 20 1.2 68 102 2.1 4.7 12.3 11 12 13 8 788 1 7 14 570 428 464 15 65 3 11.8 8.1 7.7 3.3 4.9 17.4 10.5 11.3 2.5 4.7 4.6 5.5 7.2 10.4 11.5 0.6 5.4 4.2 4.7 0.6 1.2 1.6 4.3 2.6 15 16 17 14 577 535 163 168 151 342 196 11 14 15 2.5 3.3 2.7 16 6.3 13.9 98 10 18 4 19 398 161 397 13 7 20 3.8 453 497 5.3 518 1 21 22 16.3 16 528 5.6 615 12.3 0 23 99 0.8 278 2.8 6.5 14 24 0.5 1.1 142 3.1 1.6 12 25 347 3.6 461 9.6 11.3 6 26 341 3.5 382 9.8 11.5 27 507 5.1 590 12 15.7 in O 00 28 400 8.6 517 7 12 29 30

Step 3 : Input the values as shown.

Data Review View Developer Help S es & Connections + AL Clear Reapply Advanced erties Group Ungroup 119 Subtotal Le Data Analysis ? Solver Stocks Geography Sort Filter Links Text to Columns Es What If Forecast Analysis Sheet Forecast Connections Data Types Sort & Filter Data Tools Outline Analyze H Data Analysis ? Х P R S T V OK Cancel Analysis Tools Histogram Moving Average Random Number Generation Rank and Percentile Regression Sampling t-Test: Paired Two Sample for Means t-Test: Two-Sample Assuming Equal Variances t-Test: Two-Sample Assuming Unequal Variances Z-Test: Two Sample for Means Help

Step 4 : The output will be generated as follows.

X 1 fx B D E F G H M N O P о (2 <3 хб T 11 12 OK 15 1 SAS1:SAS28 SBS1SFS28 Cancel 1 5 6 Help Constant is Zero 95 16.1 14.1 12.7 10.1 8.4 2.1 4.7 12.3 0 Ե Ե Ց Ւ Մ $1511 1 2 A B C 1 x1 x5 2 3 8.2 3 4 10 149 3 5 519 5.5 600 12 6 437 4.4 567 102 7 487 4.8 571 11.8 8 299 3.1 512 8.1 9 195 2.5 347 7.7 10 20 1.2 212 3.3 111 68 0.6 102 4.9 12 570 5.4 788 17.4 13 428 4.2 577 10.5 14 464 535 11.3 15 15 0.6 163 2.5 16 65 1.2 168 4.7 17 98 1.6 151 4.6 18 398 4.3 342 5.5 19 161 2.6 196 7.2 20 397 3.8 453 10.4 497 5.3 518 11.5 528 5.6 615 12.3 99 0.8 278 2.8 0.5 1.1 142 3.1 347 3.6 461 9.6 341 3.5 9.8 507 5.1 12 400 8.6 7 о Б 1 7 3 3 14 11 Residual Plots Line Fit Plots LI 13 7 1 0 14 1 2.5 3.3 2.7 10 16 6.3 13.9 16.3 16 6.5 1.6 11.3 11.5 115 15.7 12 8 600 DO CON RUNNNN NA ло 0

J K L M N o P R S SUMMARY OUTPUT Regression Statistics Multiple R 0.996583913 R Square 0.993179497 Adjusted R Square 0.991555567 Standard Error 17.64924228 Observations 27 ANOVA df 5 Regression Residual Total SS MS F gnificance F 952538.941 190507.8 611.5903 5.4E-22 6541.410811 311.4958 959080.3519 21 26 Intercept X2 X3 Coefficients Standard Error t Stat P-value Lower 95%Upper 95%ower 95.0%pper 95.0% -18.85940731 30.15022856 -0.62551 0.538372 -81.5602 43.84143 -81.5602 43.84143 16.20157256 3.544437472 4.570986 0.000166 8.830511 23.57263 8.830511 23.57263 0.174635182 0.057606068 3.031541 0.006347 0.054837 0.294434 0.054837 0.294434 11.52626787 2.532103272 4.552053 0.000174 6.260471 16.79206 6.260471 16.79206 13.58031268 1.770456651 7.670514 1.61E-07 9.898447 17.26218 9.898447 17.26218 -5.310971819 1.705426574 -3.11416 0.005249 -8.8576 -1.76434 -8.8576 -1.76434 X4 X5 х6

Coefficient of multiple determination is (highlighed in yellow) = 99.3%

d. Regression equation.

Using the output of the regression equation, we use the coefficient highlighted in green to obtain the equation.

x1 = -18.9 + 16.2 x2 + 0.2 x3 + 11.5 x4 + 13.6 x5 -5.3 x6

Increasing in competing stores

Competing stores is represented by x6.

We see that for 1 unit increase in the competing stores the sales decreases by 5.3 thousands dollars.
(interpreting the coefficient of the equation is explained below)

Therefore an increase of 4 new competing stoes would decreases the sales by (5.3*4 = 21.24) 21.24 thousand dollars

Increase in local advertising

Local advertising is represented by x4.

We see that for 1 unit increase in the local advertising increases the sales by 11.5 thousands dollars

An increase of 5 thousand dollar in the local advertising increases the sales by (11.5*5 = 57.63) thousands dollars

How to intrepret the regression coefficient (Explanation)

Let us consider the regression equation

y = a + bx

In this equation

y is the dependent variable.(the one we are trying to predict)

x is the independent variable( or the predictor variable)

a is the y intercept (The point on the y axis, where the regression line cuts it in the graph)

b is the coefficient or the slope of the variable.

We can have many variable and each variable will have a coefficient.

Interpreting the meaning of the coefficient

check two thing from the regression output

= the sign of the coefficient

- the value of the coefficient.

The sign (positive or negative) indicate whether the predictor variable increase or decrease the dependent variable.

The value indicates the value or magnitude of the change.

We state it as follows : one unit increase in x (independent variable) causes an increase/decrease (depends on the sign) of (value of the coefficient of the variable) in y (dependent variable)

For example : one unit increase in x , cause an increase of b units in y.