Question

• Using Excel, create scatter plots and display the regression equations for the following pairs of variables:

• ““LoyaltyCard(%)” versus “SalesGrowth(%)”

• In your report, include the scatter plots. For each scatter plot, designate the type of relationship observed (increasing/positive, decreasing/negative, or no relationship) and determine what you can conclude from these relationships.

LoyaltyCard%

2.07 2.54 1.66 2.06 2.48 2.96 2.28 2.34 2.20 2.34 2.09 2.47 2.04 2.02 2.01 2.64 2.22 2.07 1.94 2.17 0.72 2.00 1.81 2.13 2.50 2.63 1.95 2.04 1.41 2.05 2.13 2.08 2.73 1.95 2.04 1.62 1.95 1.64 1.78 2.23 2.15 2.83 2.37 3.07 2.19 1.28 1.76 2.51 1.90 1.98 2.41 2.17 2.16 0.29 1.85 1.88 2.19 2.56 2.16 2.10 1.98 0.87 1.07 3.38 1.17 2.14 0.93 2.22 1.68 2.41 2.81 1.09 0.64 1.77

SalesGrowth%

-8.31 -4.01 -3.94 -3.39 -3.30 -1.94 -0.77 -0.37 -0.25 -0.17 0.47 0.55 0.77 1.92 2.05 2.12 2.84 2.88 3.96 4.04 4.05 4.05 4.24 4.58 5.09 5.14 5.48 5.86 5.91 5.98 6.08 6.08 6.13 6.27 6.57 6.90 6.94 7.12 7.39 7.67 7.91 8.08 8.27 8.54 8.58 8.72 8.75 8.79 8.90 9.12 9.47 10.17 10.66 10.97 11.34 11.45 11.51 11.73 11.83 11.95 12.47 12.80 13.78 14.09 14.23 14.60 14.88 15.42 16.18 17.23 18.43 20.76 25.54 28.81

Answer 1

We want to plot ““LoyaltyCard(%)” versus “SalesGrowth(%)”, That is “LoyaltyCard(%) on y axis and SalesGrowth(%) on the x axis

Arrange the columns such that we have “SalesGrowth(%)” in the first column and then “LoyaltyCard(%)”

Select the data and the use insert--->scatter as below

get this raw graph

format as required

select the dots by clicking on one of the dots and press the right mouse and select add trend line as given below

check the boxes as below

get this

ans:

The scatter plot with the regression equation for ““LoyaltyCard(%)” versus “SalesGrowth(%)” is

The regression equation is

where y=Loyaly card %

x=Sales Growth %

We can see that the slope coefficient of x is negative. This indicates that there is a decreasing/negative relationship between Loyaly card % and Sales Growth %. Further, we can say that for 1 % point increase in the sales growth, the loyarly card decreases by 0.0247%