Create and calculate the following in Excel®

Anonymous
timer Asked: Jul 5th, 2018
account_balance_wallet $15

Question description

You manage the inventory for a car dealership. Your management would like you to review current inventory on the dealership lot.

Review the Week 2 Data Set.

You manage the inventory for a car dealership. Your management would like you to review current inventory on the dealership lot.

Review the Week 2 Data Set.

Create and calculate the following in Excel®:

  1. Create a Pie Chart which summarizes colors of the cars in the sample.
  2. Create a Bar Chart which summarizes the frequency of the models of the cars in the sample.
  3. Create a Frequency Table for classes of MPG, including Frequency and Relative Frequency for the cars in the sample.
    • Calculate the mean Days in Inventory for the cars in the sample.
    • Compare that to the median and the mode.
  4. Highlight the value that would be a better representation of the "typical" price of a car in inventory?
  5. Calculate the standard deviation of the Days in Inventory for the cars in the sample.
  6. Calculate the 5 number summary for the suggested retail prices of the cars in the sample. This consists of the 1st, 2nd, 3rd, 4th quartile and the IQR.
  1. Create a Pie Chart which summarizes colors of the cars in the sample.
  2. Create a Bar Chart which summarizes the frequency of the models of the cars in the sample.
  3. Create a Frequency Table for classes of MPG, including Frequency and Relative Frequency for the cars in the sample.
    • Calculate the mean Days in Inventory for the cars in the sample.
    • Compare that to the median and the mode.
  4. Highlight the value that would be a better representation of the "typical" price of a car in inventory?
  5. Calculate the standard deviation of the Days in Inventory for the cars in the sample.
  6. Calculate the 5 number summary for the suggested retail prices of the cars in the sample. This consists of the 1st, 2nd, 3rd, 4th quartile and the IQR.

Sample Car # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Color Grey Grey Blue Black Blue Blue Red Silver Silver Silver Grey Red Black Silver Black Black Grey Red Red Red Blue Red Silver Black Red Red Blue Blue Red Red MPG 27,6 32,4 33,2 34,7 27,2 26,6 37,3 34,9 29,6 32,3 31,9 26,4 34,8 35,9 34,6 33,3 33,1 37,5 27,8 36,1 28,8 26,2 27,4 34,9 36,5 26,3 31,4 30,9 27,4 36 Suggest Retail Price $ 24.390,00 $ 21.780,00 $ 21.149,00 $ 22.069,00 $ 22.532,00 $ 20.345,00 $ 22.112,00 $ 21.289,00 $ 24.871,00 $ 25.389,00 $ 25.998,00 $ 19.713,00 $ 25.213,00 $ 24.467,00 $ 21.402,00 $ 20.351,00 $ 23.732,00 $ 24.558,00 $ 19.167,00 $ 19.903,00 $ 22.140,00 $ 22.180,00 $ 23.120,00 $ 19.837,00 $ 24.666,00 $ 19.446,00 $ 23.954,00 $ 21.201,00 $ 21.346,00 $ 19.406,00 Option Package LX Touring Touring LX Touring Touring EX Touring LX EX EX LX EX Touring LX LX Touring Touring LX Touring Touring EX Touring Touring EX EX Touring LX EX EX Days in Inventory 1 10 28 21 16 22 12 23 15 8 28 55 2 33 17 14 1 2 18 22 19 41 33 21 31 35 11 3 26 31 RESULTS OF CONCEPT RATING FOR NEW WINE COOLER Gender Age Rating of the appeal of the phrase as it Total relates to the new wine cooler Sample Male Female 21 - 24 25 -34 Extremely appealing (5) 151 68 83 48 63 Somewhat appealing (4) 91 51 40 36 32 Neither appealing nor not appealing (3) 36 21 15 9 16 Somewhat unappealing (2) 13 7 6 4 6 Not at all appealing (1) 9 3 6 4 3 QUANTITY PURCHASED PER PERSON IN ONE MONTH # people who purchased this amount # of six packs purchased 0 7 1 6 2 6 3 13 4 14 5 12 6 10 7 5 8 5 9 5 10 2 Age 35-49 40 23 11 3 2 IDEAL TEMPERATURE 52 46 50 42 52 50 39 44 49 39 47 46 43 52 49 47 49 52 50 47 49 38 57 49 49 57 45 58 54 44 Sales Rep # 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 SAMPLE OF WEEKLY SALES Average Week # Weekly Weekly Weekly Sales($) - Sales($) Sales($) Rep A Rep B 1228 7374 1055 1859 3938 1692 569 4059 3689 607 1370 3735 3305 7228 6279 1671 5708 2569 4163 1519 7734 784 6766 7261 5034 7115 6291 6287 2080 7621 1047 6517 5172 3876 5429 4538 3786 2510 4863 7246 1175 641 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 4657 6133 3438 7394 4327 2552 7063 7844 6898 4003 6884 4007 7214 2358 7745 1337 1052 6056 1495 3530 4749 3833 7869 4541 6882 3868 5934 4447 5504 5554 5839 2602 2830 4763 3740 2315 1599 1629 2416 2107 4237 6322 3710 5890 5119 5184 3439 4828 3667 3518 6073 5566 4555 5867 6039 1032 4834 3687 5500 4659 43 44 45 46 47 48 49 50 4269 7034 3406 2256 3182 5178 4428 1189 ORDERS VS. SHIPMENTS Size # Ordered # Received Extra Small 30 23 Small 50 54 Medium 85 92 Large 95 91 Extra Large 60 63 2X Large 45 42 CUSTOMERS IN PAST 6 MONTHS Customer # # Visits 1 8 2 6 3 8 4 2 5 10 6 4 7 6 8 2 9 4 10 1 11 3 12 9 13 7 14 7 15 6 16 10 17 9 18 5 19 6 20 7 21 4 22 7 23 7 24 1 25 7 26 9 27 3 28 7 29 2 30 6 CUSTOMERS IN PAST 6 MONTHS $ Purchases 468 384 463 189 542 299 345 197 293 119 211 479 430 404 359 544 522 327 353 405 289 386 403 146 416 485 333 241 391 268 MONTHLY SALES ($) Month $ Sales Jan 1375 Feb 1319 Mar 1222 Apr 1328 May 1493 Jun 1492 Jul 1489 Aug 1354 Sep 1530 Oct 1483 Nov 1450 Dec 1495 Jan 1545 Feb 1454 Mar 1322 Apr 1492 May 1678 Jun 1645 Jul 1580 Aug 1493 Sep 1719 Oct 1573 Nov 1629 Dec 1680

Tutor Answer

uoscar
School: University of Virginia

Sample
Car #
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Color
Grey
Grey
Blue
Black
Blue
Blue
Red
Silver
Silver
Silver
Grey
Red
Black
Silver
Black
Black
Grey
Red
Red
Red
Blue
Red
Silver
Black
Red
Red
Blue
Blue
Red
Red

Minimum value
Maximum Value
Class width
Number of classes

Suggest Retail Option
Price
Package

MPG
27.6
32.4
33.2
34.7
27.2
26.6
37.3
34.9
29.6
32.3
31.9
26.4
34.8
35.9
34.6
33.3
33.1
37.5
27.8
36.1
28.8
26.2
27.4
34.9
36.5
26.3
31.4
30.9
27.4
36

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

24,390.00
21,780.00
21,149.00
22,069.00
22,532.00
20,345.00
22,112.00
21,289.00
24,871.00
25,389.00
25,998.00
19,713.00
25,213.00
24,467.00
21,402.00
20,351.00
23,732.00
24,558.00
19,167.00
19,903.00
22,140.00
22,180.00
23,120.00
19,837.00
24,666.00
19,446.00
23,954.00
21,201.00
21,346.00
19,406.00

LX
Touring
Touring
LX
Touring
Touring
EX
Touring
LX
EX
EX
LX
EX
Touring
LX
LX
Touring
Touring
LX
Touring
Touring
EX
Touring
Touring
EX
EX
Touring...

flag Report DMCA
Review

Anonymous
Outstanding Job!!!!

Similar Questions
Hot Questions
Related Tags

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