Group Work is expected to have this arrangement.
Datetime Quantity Net sales
3-Jan-22 73.6 167990
4-Jan-22 20 42000
5-Jan-22 237.5 467620
6-Jan-22 88 228700
7-Jan-22 133.55 314985
8-Jan-22 71.1 97320
10-Jan-22 60 146360
11-Jan-22 73.25 176500
12-Jan-22 261.25 412588.2
13-Jan-22 62 88500
14-Jan-22 278.26 396955
15-Jan-22 67 106510
17-Jan-22 116 241000
19-Jan-22 186.32 273470.76
20-Jan-22 88.75 234160
21-Jan-22 178.25 89012.5
22-Jan-22 0.35 717.5
24-Jan-22 129.1 284684
25-Jan-22 123.67 256152
26-Jan-22 12.8 32400
27-Jan-22 130.25 178975
28-Jan-22 115.4 274645
Answer the following questions:
a) Plot a scatter diagram of the above data and comment on the relationship between the two
variables.
(5 marks)
500000
Scatter Diagram of Quantity and Net Sales
450000
400000
350000
300000
Net Sales
250000
200000
150000
100000
50000
0
0 50 100 150 200 250 300
Quantity
b) Find the
i. Pearson’s correlation coefficient.
1
n
∑ xy −x y
r=
√ ¿ ¿¿
7791309.484
r=
√ 5478.067 ×15223993788
r =0.853163875
The value of r is between 0.8 and 1.0 so that it is a very strong correlation.
ii. linear regression equation.
y=a+bx
n ∑ xy−∑ x ∑ y
b=
n ( ∑ x 2 ) −( ∑ x )
2
3770993790
b=
2561384.781
b=1422.273303
a=
∑ y −b ∑ x
n n
a=205056.5891− (1422.273303 × 113.9272727 )
a=43020.87059
iii. EXCEL software. Interpret the output. (Correlation coefficient, correlation of determination,
and regression equation).
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.853163875
R Square 0.727888597
Adjusted R Square 0.714283027
Standard Error 67504.70015
Observations 22
ANOVA
df SS MS F Significance F
Regression 1 2.4379E+11 2.4379E+11 53.49930861 4.52187E-07
Residual 20 91137690858 4556884543
Total 21 3.34928E+11
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 43020.87059 26417.71852 1.628485464 0.119073055 -12085.5246 98127.26577 -12085.5246 98127.26577
X Variable 1 1422.273303 194.4504593 7.314322157 4.52187E-07 1016.656753 1827.889854 1016.656753 1827.889854
(15 marks)
2) Calculate and explain the measures of central tendency and dispersion for both quantity and net
sales.
Use the following steps in Excel: Go to Data Analysis > Descriptive Statistics > Select your
input range > Tick the box for Summary Statistics > Click ok.
Focus on interpreting the mean, median, mode, variance, and standard deviation.
Describe what these values reveal about the distribution and behavior of each variable
individually.
👉 Do not compare the mean of quantity with the mean of net sales or compare the
statistics across different variables.
Quantity:
Mean (about 113.93):
The average amount of items per transaction is roughly 114.
Median (about 102.08):
The middle transaction falls at about 102, which is lower than the mean.
This suggests a slight positively skewed distribution; a few large transactions may be pulling
the average up.
Mode (N/A):
There is no mode, which means no particular Quantity occurs more frequently than others.
This suggests transactions are quite varied in size.
Standard deviation (about 75.76):
The large standard deviation signals substantial variability in the Quantity.
Some transactions are much smaller or much greater than the average.
Variance (about 5,738.93):
This highlights a large dispersion in transactions.
Values are spread far from the mean.
Net Sales:
Mean (about 205,056.59):
The average amount of net sales per transaction is about 205,057.
Median (about 203,837.5):
The middle transaction falls close to the mean.
This suggests the distribution is fairly symmetrical with no strong skew.
Mode (N/A):
There isn’t a single amount that occurs more frequently than others; transactions are all
different.
Standard deviation (about 126,289.14):
The large standard deviation highlights substantial variation in transactions.
Some transactions are much higher or much lower than the average.
Variance (about 15,948,945,873):
This underscores a large dispersion in the amounts of transactions.
Values are broadly spread across the range.
3) Create a histogram for the month.
Use Excel to construct a histogram that displays the frequency of data for the "Month" variable.
This is for group assignment only. This is not intended for individual assignments.