Question
The table below shows data on annual expenditure, x (in dollars), on recreation and annual income, y (in dollars), of 20 families. Use Excel to find the best fit linear regression equation. Round the slope and intercept to the nearest integer.
x y
2400 41200
2650 50100
2350 52000
4950 66000
3100 44500
2500 37700
5106 73500
3100 37500
2900 56700
1750 35600
1450 37500
2020 36900
3750 48200
1675 34400
2400 29900
2550 44750
3880 60550
3330 52000
4050 67700
1150 20600
Question
The table below gives the average weight (in kilograms) of certain people ages 1–20. Use Excel to find the best fit linear regression equation, where age is the explanatory variable. Round the slope and intercept to two decimal places.
Age Weight
1 9.2
2 12.0
3 14.2
4 15.4
5 17.9
6 19.9
7 22.4
8 25.8
9 28.1
10 31.9
11 36.9
12 41.5
13 45.8
14 47.6
15 52.1
16 53.5
17 54.4
18 56.7
19 57.1
20 58.0
Question
An economist is studying the link between the total value of a country’s exports and that country’s gross domestic product, or GDP. The economist recorded the GDP and Export value (in millions of $’s) for 30 nations for the same fiscal year. This sample data is provided below.
GDP Exports
225274 214010
297106 205300
1470543 189700
511685 188300
930808 184300
261974 180500
542857 152000
331360 151100
703318 148400
269115 142900
154027 128970
101848 125927
271392 104968
97980 88546
181876 93763
266641 82414
139535 77731
76381 74824
212411 62118
169385 61143
594857 65205
144020 57241
353487 55861
99688 55691
155857 53762
137955 55400
164110 46322
422231 45940
149899 43284
257349 37922
Question
An economist is trying to understand whether there is a strong link between CEO pay ratio and corporate revenue. The economist gathered data including the CEO pay ratio and corporate revenue for 30 companies for a particular year. The pay ratio data is reported by the companies and represents the ratio of CEO compensation to the median employee salary. The data are provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.
CEO Pay Ratio Corporate Revenue (million $)
275 151007
293 20612
980 29498
336 39286
255 20338
181 25991
315 98334
131 36196
279 63827
224 60328
256 26675
90 25175
356 53525
1407 20764
220 17494
177 64190
316 45760
335 17476
126 33467
288 20142
267 63580
276 22302
137 20094
2433 20430
223 20867
1292 19183
164 22079
145 20478
226 34635
141 27072
Question
In the following table, the age (in years) of the respondents is given as the x value, and the earnings (in thousands of dollars) of the respondents are given as the y value. Use Excel to find the best fit linear regression equation in thousands of dollars. Round the slope and intercept to three decimal places.
x y
21 42.136
22 24.900
22 13.884
24 29.597
24 30.306
25 42.000
25 40.257
27 38.081
27 29.434
30 50.188
31 38.000
32 22.776
32 45.000
33 52.000
34 39.500
38 60.000
40 51.844
40 39.575
42 57.824
42 42.346
43 35.214
43 42.388
43 41.962
44 42.388
45 45.000
45 40.500
46 46.193
47 48.853
48 52.000
48 52.884
49 36.480
49 40.069
50 46.630
50 40.308
51 20.103
53 42.384
54 30.995
55 56.657
56 42.374
56 52.368
57 69.000
57 64.748
57 52.884
58 42.398
59 40.000
Question
The heights (in inches) and weights (in pounds) of 25 baseball players are given below. Use Excel to find the best fit linear regression equation, where height is the explanatory variable. Round the slope and intercept to two decimal places.
Height Weight
71 186
72 211
73 220
70 165
72 180
72 195
70 175
74 202
78 240
71 170
74 180
73 185
76 257
77 215
76 287
77 220
73 200
76 223
76 200
70 220
75 215
71 195
77 194
75 195
75 225
Question
A market researcher looked at the quarterly sales revenue for a large e-commerce store and for a large brick-and-mortar retailer over the same period. The researcher recorded the revenue in millions of dollars for 30 quarters. The data are provided below. Use Excel to calculate the correlation coefficient r between the two data sets. Round your answer to two decimal places.
E-Commerce Brick-and-mortar
1074 13.11
1155 13.08
1476 12.9
1548 13.77
1800 13.49
1977 13.83
2057 13.89
1955 13.86
1884 13.86
2193 12.9
2186 13.4
2301 12.72
2280 13.38
2457 12.86
2899 12.46
2663 12.28
3414 12.7
3251 12.83
3602 12.07
3697 12.93
3956 12.09
4349 12.63
4497 12.59
4664 12.46
4497 12.03
4473 11.28
4976 11.55
4853 11.84
5103 11.46
5204 11.91
Question
An amateur astronomer is researching statistical properties of known stars using a variety of databases. They collect the absolute magnitude or MV and stellar mass or M⊙ for 30 stars. The absolute magnitude of a star is the intensity of light that would be observed from the star at a distance of 10 parsecs from the star. This is measured in terms of a particular band of the light spectrum, indicated by the subscript letter, which in this case is V for the visual light spectrum. The scale is logarithmic and an MV that is 1 less than another comes from a star that is 10 times more luminous than the other. The stellar mass of a star is how many times the sun’s mass it has. The data is provided below. Use Excel to calculate the correlation coefficient r between the two data sets, rounding to two decimal places.
Absolute Magnitude V Stellar Mass
15.71 0.11
4.35 1.09
5.61 0.89
13.28 0.18
16.92 0.09
10.23 0.48
1.52 2.05
11.27 0.52
15.09 0.11
16.32 0.10
13.59 0.18
14.48 0.12
5.95 0.83
9.67 0.52
13.20 0.16
16.06 0.10
16.25 0.11
16.96 0.10
2.53 1.63
13.34 0.48
7.51 0.70
8.27 0.64
11.03 0.35
11.62 0.26
10.05 0.50
13.17 0.16
6.59 0.78
16.46 0.09
5.61 0.96
15.62 0.11
Solution
Question
The table below shows data on annual expenditure, x (in dollars), on recreation and annual income, y (in dollars), of 20 families. Use Excel to find the best fit linear regression equation. Round the slope and intercept to the nearest integer.
x y
2400 41200
2650 50100
2350 52000
4950 66000
3100 44500
2500 37700
5106 73500
3100 37500
2900 56700
1750 35600
1450 37500
2020 36900
3750 48200
1675 34400
2400 29900
2550 44750
3880 60550
3330 52000
4050 67700
1150 20600
Answer: y = 11 x +………………….…………….please follow the link below to purchase the solution at $5
