Take Home Assignment 2

Anonymous
timer Asked: Oct 21st, 2018
account_balance_wallet $9.99

Question Description

Answer the following questions using the data in the spreadsheet attached.

  • Create a weighted score “WScore” column for each car as the following using formulae
  • List the make and price of
    • the most expensive foreign car and
    • most expensive domestic car.
    • Least expensive foreign car
    • Least expensive domestic car
    please use pivot table to solve this question

Unformatted Attachment Preview

make price mpg Merc. Zephyr 3291 Chev. Chevette 3299 Chev. Monza 3667 Toyota Corolla 3748 Subaru 3798 AMC Spirit 3799 Merc. Bobcat 3829 Renault Le Car 3895 Chev. Nova 3955 Dodge Colt 3984 Mazda GLC 3995 Dodge Diplomat 4010 Plym. Volare 4060 Buick Skylark 4082 AMC Concord 4099 Pont. Sunbird 4172 Olds Omega 4181 Ford Mustang 4187 Olds Starfire 4195 Fiat Strada 4296 Ford Fiesta 4389 Pont. Phoenix 4424 Plym. Champ 4425 Buick Opel 4453 Plym. Horizon 4482 Honda Civic 4499 Chev. Malibu 4504 Merc. Monarch 4516 Datsun 210 4589 Plym. Arrow 4647 VW Rabbit 4697 Pont. Le Mans 4723 Olds Cutlass 4733 AMC Pacer 4749 Buick Century 4816 Olds Delta 88 4890 Pont. Firebird 4934 Datsun 510 5079 Chev. Monte Carlo 5104 Olds Cutl Supr 5172 Buick Regal 5189 Pont. Grand Prix 5222 Merc. Cougar 5379 VW Diesel 5397 Chev. Impala 5705 Toyota Corona 5719 Buick LeSabre 5788 Pont. Catalina 5798 Honda Accord 5799 Dodge Magnum 5886 Toyota Celica 5899 rep78 20 29 24 31 35 22 22 26 19 30 30 18 18 19 22 24 19 21 24 21 28 19 34 26 25 28 22 18 35 28 25 19 19 17 20 18 18 24 22 19 20 19 14 41 16 18 18 18 25 16 18 3 3 2 5 5 4 3 3 5 4 2 2 3 3 2 3 3 1 3 4 5 3 4 3 3 5 3 4 3 3 3 3 4 1 4 2 3 3 3 4 5 4 5 3 4 5 2 5 headroom trunk 3.5 2.5 2 3 2.5 3 3 3 3.5 2 3.5 4 5 3.5 2.5 2 4.5 2 2 2.5 1.5 3.5 2.5 3 4 2.5 3.5 3 2 2 3 3.5 4.5 3 4.5 4 1.5 2.5 2 2 2 2 3.5 3 4 2 4 4 3 4 2.5 17 9 7 9 11 12 9 10 13 8 11 17 16 13 11 7 14 10 10 16 9 13 11 10 17 5 17 15 8 11 15 17 16 11 16 20 7 8 16 16 16 16 16 15 20 11 21 20 10 17 14 weight length turn 2830 195 2110 163 2750 179 2200 165 2050 164 2640 168 2580 169 1830 142 3430 197 2120 163 1980 154 3600 206 3330 201 3400 200 2930 186 2690 179 3370 200 2650 179 2730 180 2130 161 1800 147 3420 203 1800 157 2230 170 2200 165 1760 149 3180 193 3370 198 2020 165 3260 170 1930 155 3200 199 3300 198 3350 173 3250 196 3690 218 3470 198 2280 170 3220 200 3310 198 3280 200 3210 201 4060 221 2040 155 3690 212 2670 175 3670 218 3700 214 2240 172 3600 206 2410 174 43 34 40 35 36 35 39 34 43 35 33 46 44 42 40 41 43 43 40 36 33 43 37 34 36 34 31 41 32 37 35 40 42 40 40 42 42 34 41 42 42 45 48 35 43 36 43 42 36 46 36 Merc. Marquis Datsun 200 Audi Fox Merc. XR-7 Dodge St. Regis Plym. Sapporo VW Scirocco VW Dasher Buick Electra Datsun 810 Olds 98 Audi 5000 BMW 320i Olds Toronado Buick Riviera Cad. Deville Linc. Continental Volvo 260 Peugeot 604 Linc. Versailles Linc. Mark V Cad. Eldorado Cad. Seville 6165 6229 6295 6303 6342 6486 6850 7140 7827 8129 8814 9690 9735 10371 10372 11385 11497 11995 12990 13466 13594 14500 15906 15 23 23 14 17 26 25 23 15 21 21 17 25 16 16 14 12 17 14 14 12 14 21 3 4 3 4 2 4 4 4 4 4 5 4 3 3 3 3 5 3 3 2 3 3.5 1.5 2.5 3 4.5 1.5 2 2.5 4 2.5 4 3 2.5 3.5 3.5 4 3.5 2.5 3.5 3.5 2.5 3.5 3 23 6 11 16 21 8 16 12 20 8 20 15 12 17 17 20 22 14 14 15 18 16 13 3720 2370 2070 4130 3740 2520 1990 2160 4080 2750 4060 2830 2650 4030 3880 4330 4840 3170 3420 3830 4720 3900 4290 212 170 174 217 220 182 156 172 222 184 220 189 177 206 207 221 233 193 192 201 230 204 204 44 35 36 45 46 38 36 36 43 38 43 37 34 43 43 44 51 37 38 41 48 43 45 displacement 140 231 151 97 97 121 140 79 250 98 86 318 225 231 121 151 231 140 151 105 98 231 86 304 105 91 200 250 85 156 89 231 231 258 196 231 231 119 200 231 196 231 302 90 250 134 231 231 107 318 134 gear_ratio 3.079999924 2.930000067 2.730000019 3.210000038 3.809999943 3.079999924 2.730000019 3.720000029 2.559999943 3.539999962 3.730000019 2.470000029 3.230000019 3.079999924 3.579999924 2.730000019 3.079999924 3.079999924 2.730000019 3.369999886 3.150000095 3.079999924 2.970000029 2.869999886 3.369999886 3.299999952 2.730000019 2.430000067 3.700000048 3.049999952 3.779999971 2.930000067 2.930000067 2.529999971 2.930000067 2.730000019 3.079999924 3.539999962 2.730000019 2.930000067 2.930000067 2.930000067 2.75 3.779999971 2.559999943 3.049999952 2.730000019 2.730000019 3.049999952 2.470000029 3.059999943 foreign Domestic Domestic Domestic Foreign Foreign Domestic Domestic Foreign Domestic Domestic Foreign Domestic Domestic Domestic Domestic Domestic Domestic Domestic Domestic Foreign Domestic Domestic Domestic Domestic Domestic Foreign Domestic Domestic Foreign Domestic Foreign Domestic Domestic Domestic Domestic Domestic Domestic Foreign Domestic Domestic Domestic Domestic Domestic Foreign Domestic Foreign Domestic Domestic Foreign Domestic Foreign 302 119 97 302 225 119 97 97 350 146 350 131 121 350 231 425 400 163 163 302 400 350 350 2.25999999 3.890000105 3.700000048 2.75 2.940000057 3.539999962 3.779999971 3.74000001 2.410000086 3.549999952 2.410000086 3.200000048 3.640000105 2.410000086 2.930000067 2.279999971 2.470000029 2.980000019 3.579999924 2.470000029 2.470000029 2.190000057 2.24000001 Domestic Foreign Foreign Domestic Domestic Domestic Foreign Foreign Domestic Foreign Domestic Foreign Foreign Domestic Domestic Domestic Domestic Foreign Foreign Domestic Domestic Domestic Domestic ASSIGNMENT 2 Name of Team members: Panther IDs Total will be for 100 points (scaled to 700 points) Please submit the Excel sheet in addition to the assignment word document when you submit it. 1) List the make and price of – 20 points a. the most expensive foreign car and b. most expensive domestic car. c. Least expensive foreign car d. Least expensive domestic car 2.a) Create a weighted score “WScore” column for each car as the following using formulae i.e.:½ x Price + 100 x mpg + 1000 X trunkroom + weight - 20 points b) Next create a new column called Rating = “WScore” divided by 4000 - 20 points c) List the car with the highest rating - 20 points d) List the car with the lowest rating – 20 points ...
Purchase answer to see full attachment

Tutor Answer

Tutortitus
School: UT Austin

Hello buddy, attached are the complete solution of the assignment.

make
price
mpg
Merc. Zephyr
3291
Chev. Chevette
3299
Chev. Monza
3667
Toyota Corolla
3748
Subaru
3798
AMC Spirit
3799
Merc. Bobcat
3829
Renault Le Car
3895
Chev. Nova
3955
Dodge Colt
3984
Mazda GLC
3995
Dodge Diplomat
4010
Plym. Volare
4060
Buick Skylark
4082
AMC Concord
4099
Pont. Sunbird
4172
Olds Omega
4181
Ford Mustang
4187
Olds Starfire
4195
Fiat Strada
4296
Ford Fiesta
4389
Pont. Phoenix
4424
Plym. Champ
4425
Buick Opel
4453
Plym. Horizon
4482
Honda Civic
4499
Chev. Malibu
4504
Merc. Monarch
4516
Datsun 210
4589
Plym. Arrow
4647
VW Rabbit
4697
Pont. Le Mans
4723
Olds Cutlass
4733
AMC Pacer
4749
Buick Century
4816
Olds Delta 88
4890
Pont. Firebird
4934
Datsun 510
5079
Chev. Monte Carlo
5104
Olds Cutl Supr
5172
Buick Regal
5189
Pont. Grand Prix
5222
Merc. Cougar
5379
VW Diesel
5397
Chev. Impala
5705
Toyota Corona
5719
Buick LeSabre
5788
Pont. Catalina
5798
Honda Accord
5799
Dodge Magnum
5886
Toyota Celica
5899

rep78
20
29
24
31
35
22
22
26
19
30
30
18
18
19
22
24
19
21
24
21
28
19
34
26
25
28
22
18
35
28
25
19
19
17
20
18
18
24
22
19
20
19
14
41
16
18
18
18
25
16
18

3
3
2
5
5
4
3
3
5
4
2
2
3
3
2
3
3
1
3
4
5
3
4
3
3
5
3
4
3
3
3
3
4
1
4
2
3
3
3
4
5
4
5
3
4
5
2
5

headroom trunk
3.5
2.5
2
3
2.5
3
3
3
3.5
2
3.5
4
5
3.5
2.5
2
4.5
2
2
2.5
1.5
3.5
2.5
3
4
2.5
3.5
3
2
2
3
3.5
4.5
3
4.5
4
1.5
2.5
2
2
2
2
3.5
3
4
2
4
4
3
4
2.5

17
9
7
9
11
12
9
10
13
8
11
17
16
13
11
7
14
10
10
16
9
13
11
10
17
5
17
15
8
11
15
17
16
11
16
20
7
8
16
16
16
16
16
15
20
11
21
20
10
17
14

weight
length
turn
2830
195
2110
163
2750
179
2200
165
2050
164
2640
168
2580
169
1830
142
3430
197
2120
163
1980
154
3600
206
3330
201
3400
200
2930
186
2690
179
3370
200
2650
179
2730
180
2130
161
1800
147
3420
203
1800
157
2230
170
2200
165
1760
149
3180
193
3370
198
2020
165
3260
170
1930
155
3200
199
3300
198
3350
173
3250
196
3690
218
3470
198
2280
170
3220
200
3310
198
3280
200
3210
201
4060
221
2040
155
3690
212
2670
175
3670
218
3700
214
2240
172
3600
206
2410
174

43
34
40
35
36
35
39
34
43
35
33
46
44
42
40
41
43
43
40
36
33
43
37
34
36
34
31
41
32
37
35
40
42
40
40
42
42
34
41
42
42
45
48
35
43
36
43
42
36
46
36

Merc. Marquis
Datsun 200
Audi Fox
Merc. XR-7
Dodge St. Regis
Plym. Sapporo
VW Scirocco
VW Dasher
Buick Electra
Datsun 810
Olds 98
Audi 5000
BMW 320i
Olds Toronado
Buick Riviera
Cad. Deville
Linc. Continental
Volvo 260
Peugeot 604
Linc. Versailles
Linc. Mark V
Cad. Eldorado
Cad. Seville

6165
6229
6295
6303
6342
6486
6850
7140
7827
8129
8814
9690
9735
10371
10372
11385
11497
11995
12990
13466
13594
14500
15906

15
23
23
14
17
26
25
23
15
21
21
17
25
16
16
14
12
17
14
14
12
14
21

3
4
3
4
2
4
4
4
4
4
5
4
3
3
3
3
5
3
3
2
3

1
a The most expensive foreign car
Make
Peugeot 604
Price
12990
b The most expensive domestic c...

flag Report DMCA
Review

Anonymous
Excellent job

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors