Question

You must include the Excel formulas used.

Background

You were hired by the United Nations Organization to determine if there is any correlation between the average cost of food and the average cost of entertainment activities in 26 countries. Initially you choose to correlate the average cost of a hamburguer combo and that of a movie ticket.

These were the results:

City Average hamburguer combo (US $) Average cost 2 movie tickets (US $)
A 7.99 30.66
B 7.44 29.41
C 5.80 21.00
D 6.45 22.14
E 4.97 18.41
F 5.16 18.60
G 3.39 18.50
H 3.21 15.00
I 4.00 17.05
J 3.01 9.80
K 7.69 28.31
L 5.36 21.12
M 4.45 20.71
N 4.87 19.33
O 5.30 19.40
P 4.77 18.00
Q 5.29 19.50
R 4.39 18.21
S 3.70 16.00
T 4.62 18.05
U 2.99 9.90
V 8.78 28.41
W 4.99 32.66
X 8.62 30.41
Y 5.75 20.00
Z 7.62 27.41

1) Generate the scatter plot [Score: 2]

2) Determine the coefficients [Score: 2]
     Coefficient of:
Correlation r=
Determination R2〖^2〗=

3) Generate the components of the simple linear regression [Score: 2]
Simple Regression:

4) If X is 9.12, how much would Y be [Score: 4]
Y=   ____________ X +   _____________
X=   9.12
Y=   ?
Y=   ____________



You must include the Excel formulas used.

Answer 1

3) y = 3.02x + 4.76

4 ) X = 9.12

y = 3.02(9.12) + 4.76

= 32.30